Saturday, July 23, 2005 12:42 PM bart

A SOAP Data Provider

Introduction

Last night I posted about the "MSMQ Service Model" development I did earlier this year, in the context of my thesis research. In this post, I'll show you another development called the "SOAP Data Provider". I assume everyone who's reading this blog is familiar with the ADO.NET data access model used in the .NET Framework to access data. A data provider is the means by which an application can use the ADO.NET model to access a specific database implementation of some vendor. By default, the .NET Framework ships with data providers for its own database flaship product SQL Server (which is in fact a client for the TDS protocol behind the scenes), a data provider for Oracle, one for ODBC and one for OLEDB. The two latter ones are helpful when you have to talk to any database that supports OLEDB, e.g. Access, Excel (if you want to use Excel as a kind of database), Sybase, MySQL, etc. However, vendors can develop their own data provider too, and thus they are given a chance to write it using (proprietary) native protocols to talk with the underlying database, resulting in a better performance.

Data providers support the following concepts:

  • a connection - to connect to the database (or to connect to some database on a database server)
  • a command - to execute (SQL) commands against the database for data retrieval and/or data manipulation, possibly with parameters
  • a data adapter - the bridge between a DataSet and a series of command objects to allow offline data to be synchronized with the online database
  • a data reader - a forward-only reader to crawl through a result set that was retrieved by executing a query command
  • a transaction - well euhm, to support database transactions

Now, one thing that's missing (in my opinion) is a similar data adapter to access and manipulate a database through web services using the SOAP protocol. Assume you've created a database back-end and you want to expose this to several clients using web services to masquerade the physical database that's used behind the scenes. If you want to support data retrieval and data manipulation, you'll typically end up with four web methods for each operation, i.e. select, insert, update, delete. The way you implement these inside the web service is up to you. Maybe you pass the web method's arguments to parameters in a parameterized SqlCommand that's used to connect to SQL Server in the back. Or maybe you don't use some data provider in the web service logic but just call another service or call some legacy application to perform the operation. What's more important however is how to consume the webservice with its four "DML methods".

 

How to use the SOAP Data Adapter?

Because of this thought I decided to create a SOAP Data Adapter which allows you to use the DataSet-driven ADO.NET data access model combined with the power of Service-Oriented design to layer the application and to use the protocol standards such as SOAP and XML. Assume you have a web service, created by one of the following:

  • an .asmx in .NET
  • a web service through SQLXML for SQL Server 2000/2005
  • a web service created using native support for web services in SQL Server 2005
  • other web service implementation mechanisms (e.g. on another platform)

Given this service with four web methods, like this:

[WebMethod]
public SoapSampleDatabase GetProducts();

[WebMethod]
public int InsertProduct(string name, decimal price);

[WebMethod]
public int UpdateProduct(int id, string name, decimal price);

[WebMethod]
public int DeleteProduct(int id);

we want to be able to do this:

  1. add a web reference to the web service, assume this is the class ProductsService
  2. write code to build a connection and a dataadapter instance

    SoapConnection conn = new SoapConnection(new ProductsService()); //pass in a proxy class instance
    SoapDataAdapter adap = new SoapDataAdapter();

    adap.SelectCommand = new SoapCommand("GetProducts", conn); //map to web method GetProducts

    adap.InsertCommand = new SoapCommand("InsertProduct", conn); //map to web method InsertProduct
    adap.InsertCommand.Parameters.Add(new SoapParameter("@ProductName", DbType.String, "productname")); //parameterize
    adap.InsertCommand.Parameters.Add(new SoapParameter("@Price", DbType.Decimal, "price")); //parameterize

    //for clarity, the UpdateCommand and DeleteCommand were omitted

  3. fill a DataSet

    SoapSampleDatabase db = new SoapSampleDatabase(); //strongly typed DataSet
    adap.Fill(db);

  4. manipulate the DataSet, e.g. by binding it to Windows Forms controls or so
  5. synchronize changes through the adapter

    adap.Update(db.Products);

 

Simplifications

One simplification I made is the generation of the data adapter initialization code for a SOAP Data Adapter consumer. Given the web service proxy class, it's possible to generate all of this code automatically by using some reflection. This stuff is explained in the (Dutch) document on http://www.bartdesmet.net/download/thesis in Chapter 7. In the download zip file, you'll find this tool (called the SoapAdapter Code Generator) in the project SoapImporter.

 

Download

The sample code can be downloaded over here. Note there is no support for transactions in the provider, as I didn't want to go through the WS-* transactional support stuff because of the lack of support in WSE at the time of writing the code. Also keep in mind that the code provided in the sample is for demonstration purposes only. If you want to use and/or adapter it, feel free to do so. Any comments ang suggestions are highly appreciated.

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

Filed under:

Comments

# re: A SOAP Data Provider

Sunday, August 21, 2005 11:31 AM by bart

Good article. We have used the described DataSet/ CRUD-Service approach for several years in an enterprise environment (8000 Users) and have been quite successful - in a Powerbuilder/DataStore incarnation however from which Microsoft got certainly "enlightened" when designing DataSets. Despite, it might be called "the poor man's SOA", it is comprehensible and performs well.