Some time ago, I published an article here about a DataAdapter Helper Class called "Extending the DataAdapter with a Helper Class". Basically it covers the idea that you can supply a DataAdapter (either OleDb or Sql) with a select Command, and have it generate its own update, insert and delete logic. What's more, you can supply Stored Procedures for all these and just call the Update method on the DataAdapter, passing in a DataTable or a DataSet. The DA will iterate over the rows, check their current status via the RowState property, and handle updates, inserts and deletes to your database all in one fell swoop. If you are not familiar with this technique, visit the link above to get the general concept first, then come back here.

Now having digested the above and seeing that "It was good", I postulated that certain situations might involve the requirement that all data access be able to go through a firewall - via a WebService, which would be the primary choice for firewall - traversing access, I'd think. So what if we could create a WebService that could "generically" call any stored procedure in any database on the enterprise, handle updates to any DataSet and run any stored procedure to return a DataSet, all through a firewall over port 80 via HTTP? Could we do it? Yes!

In addition to my utility class with the DataAdapter helper, we now have an updated Version 2 of the Microsoft Application Blocks Data Access SqlHelper. I've included a "somewhat fixed" version of this with the code download below. Namely, I added a Command Timeout property. The default CommandTimeout on a SqlCommand instance is 30 seconds. Not long enough for long-running queries. So I added a "CmdTimeout" property to the SqlHelper class, with a default value of 60 seconds. In addition, I corrected a spelling error in the VB.NET version of the SqlHelper class where the variable "dataadatpter" [sic] was misspelled.

The version 2 provides a number of new methods, the most interesting of which is the ability to call the Update method on a DataSet via a SqlDataAdapter, as well as methods to perform operations based on passing in a DataRow from a DataTable as the parameters, rather than a set of SqlParameters. So I've used both my DAHelper class here as well as the new version 2.0 Data Access SqlHelper class.

Now the key to understanding how to pass a DataSet over the wire via a WebService is that you really only need to pass over the changed rows. Do this by simply calling the GetChanges method on the DataSet:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim ws As New localhost.Service1
Dim ds As DataSet = New DataSet
Dim dt As DataTable = DataGrid1.DataSource
Dim dt2 As DataTable = dt.Copy().GetChanges()
ds.Tables.Add(dt2)
ws.UpdateDataSet("localSqlConn", "NewInsertCommand", "NewDeleteCommand", "newUpdateCommand", ds, 0)
End Sub

Note that above, which is the button click handler for my WinForm - based webservice client app, I actually grab the DataTable back out of the DataGrid.DataSource after you may have added, deleted or updated however many rows suits your fancy. Then I use the Copy method and GetChanges to provide myself with a brand - new unrelated DataTable containing only the changed rows (inserts, updates and deletes), add this into a new DataSet, and toss it over the wire in the WebService call along with a specified connection name and the names of the stored procs that do the Insert, Update or Deletes for this table in the database. My DAHandler class on the server will take care of all the rest!

I really only need to do two things to make all this happen correctly:

1) The stored procs need to return a select of the row(s) that they have affected so that the DataSet on the client side can be updated and concurrency issues addressed, if needed.

2) The names of the parameters in these stored procs must be the same as the name of the table column that they correspond to (e.g., if the table column is "FirstName" then the parameter should be "@FirstName", not "@Fname").

Right now the WebService supports only three methods, but I bet you can think of more:

<WebMethod()> _
Public Function UpdateDataSet(ByVal ConnectionName As String, ByVal insertCommandName As String, _
ByVal deleteCommandName As String, _
ByVal updateCommandName As String, _
ByVal ds As DataSet, _
ByVal TableName As Object) As Boolean
Dim connectionString As String = System.Configuration.ConfigurationSettings.AppSettings(ConnectionName)
Dim connection As New SqlConnection(connectionString)
Dim result As Boolean = False
If TypeOf (TableName) Is Integer Then
Dim name As Integer = DirectCast(TableName, Integer)
result = PAB.Data.DAHandler.SubmitChanges(ds.Tables(name), connectionString, updateCommandName, insertCommandName, deleteCommandName)
End If
If TypeOf (TableName) Is String Then
result = PAB.Data.DAHandler.SubmitChanges(ds.Tables(TableName), connectionString, updateCommandName, insertCommandName, deleteCommandName)
End If
Return result
End Function

<WebMethod()> _
Public Function GetDataSet(ByVal ConnectionName As String, ByVal selectCommandName As String, _
ByVal selectCommandParms As Object()) As DataSet
Dim connectionString As String = System.Configuration.ConfigurationSettings.AppSettings(ConnectionName)
SqlHelper.CmdTimeout = 60
Return SqlHelper.ExecuteDataset(connectionString, selectCommandName, selectCommandParms)
End Function

<WebMethod()> _
Public Function ExecuteNonQuery(ByVal ConnectionName As String, ByVal spName As String, _
ByVal spParms As Object()) As Integer
Dim connectionString As String = System.Configuration.ConfigurationSettings.AppSettings(ConnectionName)
Return SqlHelper.ExecuteNonQuery(connectionString, spName, spParms)
End Function

The downloadable VS.NET 2003 solution below contains the entire webservice including my DAHandler class, the Revised SqlHelper MS Application block code, and a front - end Windows Forms test harness with a DataGrid so you can update, delete or insert records and press the Update button to send everything over the wire. The WebService connection strings are stored in the web.config appSettings section so you can have as many as you need and refer to them by name. In addition I've included Sql script to put in the stored procedures which work against the trusty old Northwind database on SQL Server or MSDE (Poor Nancy Davolio). Finally, the root folder of DBWebService is the IIS Application for the webservice, so you should unzip this into a new DBWebService folder under your wwwroot folder and mark it as an Application in IIS. Enjoy!

Download the Source Code that accompanies this article