Developing a Data Transfer Plan

In Chapter 10, we examined the first stage of planning an application, which involves separating the parts of the system into components and tiers. The next step is to determine exactly how these parts should communicate and how data should be shuttled back and forth over the network. This decision can go a long way toward determining how frequently the client and server need to exchange information and how large each communication is, which ultimately affects the overall scalability of the system.

The first goal is to strive to use "chunky" rather than "chatty" communication. For example, you should provide methods that return an entire structure of related entity information, such as GetCustomerDetails, rather than methods that provide individual details, such as GetCustomerName and GetCustomerAddress. The disadvantage of this approach is that a client might wind up with more information than is needed. Clients that need several pieces of information won't need to call multiple methods, however, which would impose additional network overhead and require additional database trips. As a side benefit, you'll also find it's easier to tune a database for optimum performance if it provides only a few stored procedures rather than every imaginable combination, depending on the required information.

The same reasoning applies when you commit updates to the data source. You should follow the service provider pattern demonstrated in the first part of this book and create a generic Update method that accepts a custom structure, instead of implementing multiple separate methods, such as UpdateCustomerName and UpdateCustomerStatus. In some cases, you can create Web methods that represent higher-level business tasks (such as CommitOrder), accept related pieces of information (such as a customer record and an order record), and perform all the work with a single connection.

If the client requires a group of information, a custom collection or array of custom structures is generally ideal. However, you should limit the client's ability to perform wide-open queries without limiting criteria. It might even be a good idea for your remote methods to verify the submitted criteria and reject queries that would return a large number of results (such as date ranges that span 10 years).

Batch Updates

The principles discussed so far are all a matter of common sense. However, one data task can be particularly complicated: batch updates. If your system needs this ability, you might add a method such as UpdateCustomers that accepts an array of customer structures. This works well if all you need to do is commit record changes. However, this approach is less ideal if the client needs to perform updates and additions and record deletions simultaneously. You can create separate dedicated methods for each function (UpdateCustomers, DeleteCustomers, and AddCustomers methods), but the client programming will become more cumbersome. You also will be forced to use three connections for a task that would otherwise only take one.

.NET provides the perfect package for this type of problem: the DataSet. Chapter 3 showed an example (in Listing 3-18) of a SubmitBatchChanges method that accepts a DataSet and commits all the contained changes, including additions, deletions, and insertions. Unfortunately, that SubmitBatchChanges method is far from ideal for a distributed application. First of all, it requires an entire DataSet, which might contain many unchanged rows that will just waste network bandwidth. Even worse, the method returns the DataSet to the client after it has been updated, doubling the amount of network traffic required to perform this task.

Here's a better approach:

1. The client uses the DataSet.GetChanges or DataTable.GetChanges method to create a duplicate DataSet or DataTable that includes only changed rows.

2. The client submits this smaller DataSet to the remote method.

3. The remote method applies the changes in the best possible way using a single connection. If you're using relational data, you might need to separate the DataSet into multiple DataSet objects that each contain a subset (one with deleted rows, one with updated rows, and so on). This technique is shown in Chapter 3.

4. Assuming the remote method does throw an exception, the client should assume that all the changes are committed and should call the DataSet.AcceptChanges method to update the original database so that it reflects the changes. The AcceptChanges method updates all the original values to match the current values and sets the state of each row to DataRowState.Unchanged.

Listing 12-4 shows the implementation of this changes-only DataSet approach.

Listing 12-4 Submitting DataSet changes only

 Dim Proxy As New CustomerDBService() Dim ds As DataSet = Proxy.GetCustomers() ' (Modify the DataSet here.) ' Create the DataSet with the changed rows. Dim dsChanges As DataSet = ds.GetChanges() ' Submit the DataSet with the changes. Proxy.SubmitBatchChanges(dsChanges) ' Update the DataSet to reflect the fact that the changes have been ' applied. ds.AcceptChanges() 

One drawback with this approach is that it requires clients to be on their best behavior. If clients submit a large DataSet object that includes unchanged rows, it might put an unnecessary strain on the network. One way to discourage this sort of abuse is to examine the DataSet in the SubmitBatchChanges method and immediately throw an exception if an unchanged row is detected.

The SubmitBatchChanges method requires only a minor modification to change it from a function to a subroutine (as shown in Listing 12-5).

Listing 12-5 The revised SubmitBatchChanges

 Public Sub SubmitBatchChanges(ds As DataSet)     Dim Sql As String = "SELECT * FROM Customers"     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand(Sql, con)     Dim Adapter As New SqlDataAdapter(cmd)     ' Generate insert, delete, and update commands.     Dim Builder As New SqlCommandBuilder(adapter)     Adapter.InsertCommand = Builder.GetInsertCommand()     Adapter.UpdateCommand = Builder.GetUpdateCommand()     Adapter.DeleteCommand = Builder.GetDeleteCommand()     Try         con.Open()         Adapter.Update(ds, "Customers")     Catch Err As Exception         ' Use caller inform pattern. 

         Throw New ApplicationException( _          "Exception encountered when executing command.", Err)     Finally         con.Close()     End Try End Sub 

Of course, the DataSet is notorious for concurrency problems, and it is quite possible that at least one row in the submitted DataSet will fail to be properly updated. Ideally, the remote method won't just abandon processing if an error is encountered. Instead, it should report the problem to the client.

Listing 12-6 shows a rewritten SubmitBatchChanges method that uses error handling. Note that it no longer returns the updated DataSet. Instead, it returns an array of rows that could not be updated.

Listing 12-6 SubmitBatchChanges with error handling

 Public Function SubmitBatchChanges(ds As DataSet) As DataRow()     Dim Sql As String = "SELECT * FROM Customers"     Dim con As New SqlConnection(ConnectionString)     Dim cmd As New SqlCommand(Sql, con)     Dim Adapter As New SqlDataAdapter(cmd)     ' Generate insert, delete, and update commands.     Dim Builder As New SqlCommandBuilder(adapter)     Adapter.InsertCommand = Builder.GetInsertCommand()     Adapter.UpdateCommand = Builder.GetUpdateCommand()     Adapter.DeleteCommand = Builder.GetDeleteCommand()     Adapter.ContinueUpdateOnError = True     Try         con.Open()         Adapter.Update(ds, "Customers")     Catch Err As Exception         ' Use caller inform pattern.         Throw New ApplicationException( _          "Exception encountered when executing command.", Err) 

     Finally         con.Close()     End Try     ' Return the rows that were not updated.     Return ds.Tables("Customers").GetErrors() End Function 

The client needs to check these errors to determine whether the DataSet was successfully updated (as shown in Listing 12-7). Depending on the type of error, it might need to refresh its DataSet by retrieving the original DataSet again.

Listing 12-7 Checking for errors at the client

 Dim Proxy As New CustomerDBService() Dim ds As DataSet = Proxy.GetCustomers() ' (Modify the DataSet here.) ' Create the DataSet with the changed rows. Dim dsChanges As DataSet = ds.GetChanges() ' Submit the DataSet with the changes. Dim RowErrors() As DataRow() = Proxy.SubmitBatchChanges(dsChanges) ' Check for errors, and refresh the DataSet. If RowErrors.Length = 0 Then     ds.AcceptChanges() Else     ' Re-query the DataSet.     ds = Proxy.GetCustomers() End If