15.8 Optimization

To maximize performance and use of bandwidth in multitier or distributed applications, it is important to minimize the amount of data passed back and forth between components. The GetChanges( ) method can select the data that has been modified in a DataSet so that only the changed data is passed rather than the entire DataSet. The GetChanges( ) method returns a new DataSet with the same schema as the original DataSet, but it contains only changed records and any related records required so that foreign key constraints aren't violated. These related records can be omitted by setting the EnforceConstraints property of the DataSet to false prior to calling GetChanges( ).

If the data being updated contains changes after the update is called, in cases such as AutoIncrement field inserts and timestamp updates, the updated data must also be returned to the client and reintegrated with the original DataSet. This is done by merging the returned updated data back into the original DataSet and accepting the changes to set the RowState of the modified and successfully updated rows back to Unchanged.

The merge doesn't, however, remove the originally inserted rows that now have data source-generated AutoIncrement values. This is because the Merge( ) method uses the primary key to synchronize the rows. The solution is to delete inserted rows from the original DataSet prior to merging.

Example 15-6 demonstrates using the GetChanges( ) method to optimize data updating between a client and a web service and merging the data back into the original DataSet.

Example 15-6. Optimizing data updates

// Client code

MyWebService ws = new MyWebService();

DataSet ds = new DataSet();
ds = ws.GetData();

// ... code to modify the data in the DataSet

// create the DataSet of changes
DataSet dsChanges = ds.GetChanges();

// update the DataSet to the client and store the returned DataSet
dsChanges = ws.UpdateData(dsChanges);

// primary key OrderID is AutoIncrement. Delete the inserted rows from the
// original DataSet to prevent duplicate rows due to the OrderID changing
// to the data source generated value
foreach(DataRow row in ds.Tables("Orders").Select("", "",
    DataRowViewState.Added)
{
    ds.Tables("Orders").Remove(row);
}

// merge the returned DataSet back into the original changed DataSet
ds.Merge(dsChanges);
ds.AcceptChanges();

Example 15-7 shows the web service code.

Example 15-7. Web service code

// Web Service MyWebService

// connection and select command strings
String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";
String sqlSelect = "SELECT * FROM Orders";

// create a new DataSet to receive the data
DataSet ds = new DataSet();

SqlDataAdapter da = new sqlDataAdapter(sqlSelect, connString);

// create the command builder
SqlCommandBuilder cb = new SqlCommandBuilder(da);

// read all of the data from the orders table and load it into the 
// Orders table in the DataSet
da.Fill(ds, "Orders");

public DataSet GetData()
{
    return ds;
}


public DataSet UpdateData(DataSet dsWS)
{
    // update the changed data from the client to the data source
    da.Update(dsWS, "Orders");

    // return the updated DataSet to the client
    return dsWS;
}