Microsoft.NET

……………………………………………….Expertise in .NET Technologies

ADO.NET Core Classes: Updating the Data Source – Part 13

Posted by Ravi Varma Thumati on November 19, 2009

This chapter discusses how to use the DataAdapter object to submit changes made to a DataSet back the data source. First, the CommandBuilder object, which is easy to use but provides only limited functionality, is introduced, and an overview of using it to submit changes is presented. Following that, using custom command logic to submit the updates is described. Finally, techniques to handle common tasks such as retrieving new AutoIncrement values, refreshing updated data, updating data in related tables, and handling concurrency issues are discussed.

SqlCommandBuilder Class Overview

The command builder can automatically generate commands used by the data adapter to update changes made to a DataSet back to the data source. The class is limited to single-table updates using SQL statements.

The command builder uses the SelectCommand object of the DataAdapter to retrieve the metadata required to build the update command objects DeleteCommand , InsertCommand, and UpdateCommand. If the SelectCommand is changed, the RefreshSchema( ) method should be called to generate new update commands. This forces the command builder to regenerate its updating logic when the Update( ) method of the DataAdapter is called or when one of the update commands is retrieved from the command builder using GetDeleteCommand, GetInsertCommand, or GetUpdateCommand.

The SelectCommand must contain a primary key, or at least one unique column, so that records can be located by the generated DeleteCommand and UpdateCommand. The SelectCommand must also contain all the required columns without default values in the DataRow for the generated InsertCommand to work. Also, as previously mentioned, the SelectCommand must return data from only one table. An InvalidOperation exception is raised otherwise, and the commands aren’t generated.

The command builder is useful because it lets you update the data source with changes made to the DataSet using very little code. It also lets you create update logic without understanding how to code the actual delete, insert, and update SQL statements. There are drawbacks, however, including slower performance because of the time that it takes to request metadata and construct the updating logic, updates that are limited to simple single-table scenarios, and a lack of support for stored procedures.

Updating a Data Source Using Command Builder

To use a CommandBuilder, create it supplying a reference to the DataAdapter you used to retrieve the results:

SqlDataAdapter da = new sqlDataAdapter(sqlSelect, connString);

SqlCommandBuilder cb = new SqlCommandBuilder(da);

Once you create CommandBuilder, it registers itself as a listener for the DataAdapter RowUpdating event, which fires just before a row is updated in the data source. In the event handler, CommandBuilder creates and supplies the Command object required to perform the update, if it has not been specified.

The following example demonstrates how to use a CommandBuilder to generate the update logic for a data adapter:

// 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

// this creates SQL statements for the DeleteCommand, InsertCommand,

// and UpdateCommand properties for the data adapter based on the

// select command that the data adapter was initialized with

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”);

// … code to modify the data in the DataSet

// update the data in the Orders table in the DataSet to the data source

da.Update(ds, “Orders”);

To see the logic that the CommandBuilder generates, examine the CommandText property and the Parameters collection of the DeleteCommand, InsertCommand, and UpdateCommand objects returned by the GetDeleteCommand( ), GetInsertCommand( ), and GetUpdateCommand( ) methods, respectively, of the CommandBuilder. Some points become evident once the generated commands are examined:

  • The generated commands are modeled as parameterized SQL statements that use inline parameters.
  • Both current and original values are used for parameter values. For example, to update a record, the command searches for a record with the original value of the primary key and updates it using the new values for the fields.
  • When matching a row for DELETE or UPDATE operations, ADO.NET searches for an exact match. It isn’t satisfied with a record that has the same primary key unless all the other columns also match. This can add significant overhead when updating tables with a large number of fields, many of which aren’t indexed.

The CommandBuilder is convenient, but it also suffers from some significant limitations. Instead of using the CommandBuilder, custom update logic can be defined to overcome those limitations. The next section examines how to define and use custom updating logic to update the data source.

Updating a Data Source Using Custom Logic

The CommandBuilder provides an extremely convenient way to create the required Command objects, but it has definite limitations. Here are some reasons to avoid the CommandBuilder and use your own custom updating logic:

Stored procedures

Most significant applications use stored procedures because of their benefits, which include maintainability, security, and performance over SQL statements.

Table joins

In some cases, table joins are needed to retrieve aggregate information. However, even if you edit only fields from a single table, the CommandBuilder can’t automatically generate the Command objects.

More flexible concurrency handling

The UPDATE and DELETE statements generated by the CommandBuilder search the data source for a row that matches all fields in the original row exactly. If any original values have changed, the update will fail for that row. In some cases, this approach isn’t ideal.

There is only one real difference between using the CommandBuilder and custom update logic. The CommandBuilder generates the DeleteCommand, InsertCommand, and UpdateCommand objects used by the DataAdapter to reconcile changes made to the DataSet with the data source. With custom update logic, those update objects have to be defined.

The SourceColumn and SourceVersion properties of the Parameter object bind associate a Parameter with a DataColumn. The DataAdapter uses these properties to determine the source of the values within the DataRow; these values are loaded into the Parameter for the appropriate update Command for the DataRow before the update is performed for the row. The default value for SourceVersion is the Current row, so this value needs to be set only when a different version is required. The following two examples illustrate the effect of the SourceColumn and SourceVersion properties. The first example maps the CustomerID column from the Current version of the DataRow to the Parameter named @CustomerID:

params.Add(“@CustomerID”, SqlDbType.NChar, 5, “CustomerID”);

The second example maps the OrderID column from the Original version of the DataRow to the Parameter named @OrderID:

params.Add(“@OrderID”, SqlDbType.Int, 0, “OrderID”);

params[“@OrderID”].SourceVersion = DataRowVersion.Original;

It’s important to understand that this mapping occurs each time a row is updated and is defined separately for each update Command object. The actual Command object that updates a particular row when the Update( ) method is called is based on the DataRowState of that row.

The update Command objects can be based on parameterized SQL statements, as is demonstrated by CommandBuilder, or on stored procedures, as is more commonly the case. One obvious drawback to using SQL statements is that updated values in the data source can’t be returned back to the DataSet. Refreshing the DataSet after an update using stored procedures is discussed in more detail later in this chapter.

The following example uses the Orders table from Northwind database to demonstrate how to use stored procedures to define the update Command objects and then how to use these custom update Command objects. The stored procedures are presented first followed by the code that uses these stored procedures.

Example 15-1 uses the stored procedures that delete, select, insert and update the data source.

Example 15-1. Stored procedures for commands

— stored procedure for DeleteCommand

CREATE PROCEDURE DeleteOrders

@OrderID int

AS

SET NOCOUNT ON

delete

from

Orders

where

OrderID=@OrderID

return

GO

–stored procedure for SelectCommand

CREATE PROCEDURE GetOrders

AS

SET NOCOUNT ON

select

OrderID,

CustomerID,

EmployeeID,

OrderDate,

RequiredDate,

ShippedDate,

ShipVia,

Freight,

ShipName,

ShipAddress,

ShipCity,

ShipRegion,

ShipPostalCode,

ShipCountry

from

Orders

return

GO

–stored procedure for InsertCommand

CREATE PROCEDURE InsertOrders

@OrderID int output,

@CustomerID nchar(5),

@EmployeeID int,

@OrderDate datetime,

@RequiredDate datetime,

@ShippedDate datetime,

@ShipVia int,

@Freight money,

@ShipName nvarchar(40),

@ShipAddress nvarchar(60),

@ShipCity nvarchar(15),

@ShipRegion nvarchar(15),

@ShipPostalCode nvarchar(10),

@ShipCountry nvarchar(15)

AS

SET NOCOUNT ON

insert Orders(

CustomerID,

EmployeeID,

OrderDate,

RequiredDate,

ShippedDate,

ShipVia,

Freight,

ShipName,

ShipAddress,

ShipCity,

ShipRegion,

ShipPostalCode,

ShipCountry)

values (

@CustomerID,

@EmployeeID,

@OrderDate,

@RequiredDate,

@ShippedDate,

@ShipVia,

@Freight,

@ShipName,

@ShipAddress,

@ShipCity,

@ShipRegion,

@ShipPostalCode,

@ShipCountry)

if @@rowcount=0

return 1

set @OrderID=Scope_Identity()

select @OrderId OrderId

return

GO

–stored procedure for UpdateCommand

CREATE PROCEDURE UpdateOrders

@OrderID int,

@CustomerID nchar(5),

@EmployeeID int,

@OrderDate datetime,

@RequiredDate datetime,

@ShippedDate datetime,

@ShipVia int,

@Freight money,

@ShipName nvarchar(40),

@ShipAddress nvarchar(60),

@ShipCity nvarchar(15),

@ShipRegion nvarchar(15),

@ShipPostalCode nvarchar(10),

@ShipCountry nvarchar(15)

AS

SET NOCOUNT ON

update

Orders

set

CustomerID = @CustomerID,

EmployeeID = @EmployeeID,

OrderDate = @OrderDate,

RequiredDate = @RequiredDate,

ShippedDate = @ShippedDate,

ShipVia = @ShipVia,

Freight = @Freight,

ShipName = @ShipName,

ShipAddress = @ShipAddress,

ShipCity = @ShipCity,

ShipRegion = @ShipRegion,

ShipPostalCode = @ShipPostalCode,

ShipCountry = @ShipCountry

where

OrderID = @OrderID

if @@rowcount = 0

return 1

return

GO

Example 15-2 demonstrates how to create the update Command objects that retrieve and update the data, configure the stored procedure parameters, create the DataAdapter, and assign the command objects to the data adapter.

Example 15-2. Creating update objects

// connection and select command strings

String connString = “Data Source=(local);Integrated security=SSPI;” +

“Initial Catalog=Northwind;”;

SqlConnection conn = new SqlConnection(connString);

// create command objects using stored procedures

SqlCommand selectCommand = new SqlCommand(“GetOrders”, conn);

selectCommand.CommandType = CommandType.StoredProcedure;

SqlCommand deleteCommand = new SqlCommand(“DeleteOrders”, conn);

deleteCommand.CommandType = CommandType.StoredProcedure;

SqlCommand insertCommand = new SqlCommand(“InsertOrders”, conn);

insertCommand.CommandType = CommandType.StoredProcedure;

SqlCommand updateCommand = new SqlCommand(“UpdateOrders”, conn);

updateCommand.CommandType = CommandType.StoredProcedure;

// set up the parameters

SqlParameterCollection cparams;

// delete command parameters

cparams=deleteCommand.Parameters;

cparams.Add(“@OrderID”, SqlDbType.Int, 0, “OrderID”);

cparams[“@OrderID”].SourceVersion=DataRowVersion.Original;

// insert command parameters

cparams = insertCommand.Parameters;

cparams.Add(“@OrderID”, SqlDbType.Int, 0, “OrderID”);

cparams[“@OrderID”].Direction = ParameterDirection.Output;

cparams[“@OrderID”].SourceVersion = DataRowVersion.Original;

cparams.Add(“@CustomerID”, SqlDbType.NChar, 5, “CustomerID”);

cparams.Add(“@EmployeeID”, SqlDbType.Int, 0, “EmployeeID”);

cparams.Add(“@OrderDate”, SqlDbType.DateTime, 0, “OrderDate”);

cparams.Add(“@RequiredDate”, SqlDbType.DateTime, 0, “RequiredDate”);

cparams.Add(“@ShippedDate”, SqlDbType.DateTime, 0, “ShippedDate”);

cparams.Add(“@ShipVia”, SqlDbType.Int, 0, “ShipVia”);

cparams.Add(“@Freight”, SqlDbType.Money, 0, “Freight”);

cparams.Add(“@ShipName”, SqlDbType.NVarChar, 40, “ShipName”);

cparams.Add(“@ShipAddress”, SqlDbType.NVarChar, 60, “ShipAddress”);

cparams.Add(“@ShipCity”, SqlDbType.NVarChar, 15, “ShipCity”);

cparams.Add(“@ShipRegion”, SqlDbType.NVarChar, 15, “ShipRegion”);

cparams.Add(“@ShipPostalCode”, SqlDbType.NVarChar, 10, “ShipPostalCode”);

cparams.Add(“@ShipCountry”, SqlDbType.NVarChar, 15, “ShipCountry”);

// update command parameters

cparams = updateCommand.Parameters;

cparams.Add(“@OrderID”, SqlDbType.Int, 0, “OrderID”);

cparams[“@OrderID”].SourceVersion=DataRowVersion.Original;

cparams.Add(“@CustomerID”, SqlDbType.NChar, 5, “CustomerID”);

cparams.Add(“@EmployeeID”, SqlDbType.Int, 0, “EmployeeID”);

cparams.Add(“@OrderDate”, SqlDbType.DateTime, 0, “OrderDate”);

cparams.Add(“@RequiredDate”, SqlDbType.DateTime, 0, “RequiredDate”);

cparams.Add(“@ShippedDate”, SqlDbType.DateTime, 0, “ShippedDate”);

cparams.Add(“@ShipVia”, SqlDbType.Int, 0, “ShipVia”);

cparams.Add(“@Freight”, SqlDbType.Money, 0, “Freight”);

cparams.Add(“@ShipName”, SqlDbType.NVarChar, 40, “ShipName”);

cparams.Add(“@ShipAddress”, SqlDbType.NVarChar, 60, “ShipAddress”);

cparams.Add(“@ShipCity”, SqlDbType.NVarChar, 15, “ShipCity”);

cparams.Add(“@ShipRegion”, SqlDbType.NVarChar, 15, “ShipRegion”);

cparams.Add(“@ShipPostalCode”, SqlDbType.NVarChar, 10, “ShipPostalCode”);

cparams.Add(“@ShipCountry”, SqlDbType.NVarChar, 15, “ShipCountry”);

// create the data adapter

SqlDataAdapter da = new SqlDataAdapter(selectCommand.CommandText, conn);

// assign the custom update logic to the DataAdapter

da.DeleteCommand = deleteCommand;

da.InsertCommand = insertCommand;

da.UpdateCommand = updateCommand;

// create a new DataSet to receive the data and load the data

DataSet ds = new DataSet();

da.Fill(ds, “Orders”);

// … code to modify the DataSet

// update the data source using the custom update logic

da.Update(ds, “Orders”);

Refreshing Data After Updating

The DeleteCommand , InsertCommand, and UpdateCommand objects submit the changes made to a DataSet back to a data source. This is however a one-way process; the updated data isn’t automatically returned back to the DataSet. Often this is acceptable because after the update, the data in the data source implicitly matches the data in the DataSet. This isn’t, however, the case for some data such as AutoIncrement columns when rows are inserted into the data source and timestamp columns, which are updated with a new value whenever the row is updated in the data source. In both cases, a new value must be retrieved from the data source row when it’s inserted, or in the case of the timestamp column, updated or inserted.

The UpdatedRowSource property of each Command object that submits updates to the data source determines how data is returned back to the DataSet. By default, this property is set to Both, resulting in both the data in the first returned row and the return values for stored procedures updating the DataSet after the update. The property can be set so that only the first returned record or only the return parameters update the DataSet, or so that the DataSet isn’t updated.

Retrieving Updated Values from the Data Source

There are three techniques that can retrieve updated data from the data source.

The first technique is to use a batch query to return the new value using a query after the update command has executed. Example 15-3 demonstrates this for an AutoIncrement column by creating a batch insert statement containing the statements:

“SET @OrderID = Scope_Identity();” +

“SELECT @OrderID OrderID;”;

If the row also contains a timestamp column named rowversion, the following code can be used with the insert statement to retrieve the new timestamp value:

“SET @OrderID=Scope_Identity();” +

“SELECT @OrderID OrderId, rowversion WHERE OrderID = @OrderID;”;

This technique requires that the UpdatedRowSource property update commands be set to either Both or FirstReturnedRecord.

The second technique uses output parameters to retrieve updated data. Example 15-3 demonstrates the use of this method with an AutoIncrement column by creating an output parameter for the OrderID on the InsertCommand:

params.Add(“@OrderID”, SqlDbType.Int, 0, “OrderID”);

params.Direction = ParameterDirection.Output;

This technique requires that the UpdatedRowSource property for the update command be set to either Both or OutputParameters.

The third technique handles the DataAdapter RowUpdated event. An event handler is first attached to the data adapter:

da.RowUpdated += new SqlRowUpdateEventHandler(da_RowUpdated);

The event handler retrieves the new AutoIncrement value and stores it in the OrderID field if the update was a successful insert. AcceptChanges( ) is called so that the row doesn’t appear to be modified:

private void da_RowUpdated(object sender, SqlRowUpdatedEventArgs e)

{

if(e.Status == UpdateStatus.Continue &&

e.StatementType == StatementType.Insert)

{

e.Row[“OrderId”] = (Int32)cmdIdentity.ExecuteScalar();

e.Row.AcceptChanges();

}

}

Example 15-3 demonstrates the three techniques for returning updated data from the data source described earlier. All three techniques have been included in the same example in the interest of saving space. Normally, only one technique is used.

Example 15-3. Returning updated data

// following variable has class scope

private SqlCommand insertCommand;

// …

// the SQL statements, select and update

String sqlSelect = “SELECT OrderID, CustomerID, EmployeeID, ” +

“OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ” +

“ShipName, ShipAddress, ShipCity, ShipRegion, ” +

“ShipPostalCode, ShipCountry FROM Orders”;

String sqlDelete = “DELETE FROM Orders WHERE OrderID=@OrderID”;

String sqlInsert = “INSERT Orders(CustomerID, ” +

“EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, ” +

“Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ” +

“ShipPostalCode, ShipCountry) ” +

“VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, ” +

“@ShippedDate, @ShipVia, @Freight, @ShipName, @ShipAddress, ” +

“@ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry);” +

“SET @OrderID=Scope_Identity();” +

“SELECT @OrderID OrderID;”;

String sqlUpdate = “UPDATE Orders SET ” +

“CustomerID=@CustomerID, EmployeeID=@EmployeeID, ” +

“OrderDate=@OrderDate, RequiredDate=@RequiredDate, ” +

“ShippedDate=@ShippedDate, ShipVia=@ShipVia, Freight=@Freight, ” +

“ShipName=@ShipName, ShipAddress=@ShipAddress, ShipCity=@ShipCity, ” +

“ShipRegion=@ShipRegion, ShipPostalCode=@ShipPostalCode, ” +

“ShipCountry=@ShipCountry WHERE OrderID=@OrderID”;

// build the connection

String connString = “Data Source=(local);Integrated security=SSPI;” +

“Initial Catalog=Northwind;”;

SqlConnection conn = new SqlConnection(connString);

// create command objects using SQL statements

// UpdatedRowSource property of Command objects defaults to Both

SqlCommand selectCmd = new SqlCommand(sqlSelect, conn);

SqlCommand deleteCommand = new SqlCommand(sqlDelete, conn);

insertCommand = new SqlCommand(sqlInsert, conn);

SqlCommand updateCommand = new SqlCommand(sqlUpdate, conn);

// set up the parameters

SqlParameterCollection cparams;

// delete command parameters

cparams = deleteCommand.Parameters;

cparams.Add(“@OrderID”, SqlDbType.Int, 0, “OrderID”);

// insert command parameters

cparams = insertCommand.Parameters;

SqlParameter orderid =

cparams.Add(“@OrderID”, SqlDbType.Int, 0, “OrderID”);

orderid.Direction = ParameterDirection.Output;

cparams.Add(“@CustomerID”, SqlDbType.NChar, 5, “CustomerID”);

cparams.Add(“@EmployeeID”, SqlDbType.Int, 0, “EmployeeID”);

cparams.Add(“@OrderDate”, SqlDbType.DateTime, 0, “OrderDate”);

cparams.Add(“@RequiredDate”, SqlDbType.DateTime, 0, “RequiredDate”);

cparams.Add(“@ShippedDate”, SqlDbType.DateTime, 0, “ShippedDate”);

cparams.Add(“@ShipVia”, SqlDbType.Int, 0, “ShipVia”);

cparams.Add(“@Freight”, SqlDbType.Money, 0, “Freight”);

cparams.Add(“@ShipName”, SqlDbType.NVarChar, 40, “ShipName”);

cparams.Add(“@ShipAddress”, SqlDbType.NVarChar, 60, “ShipAddress”);

cparams.Add(“@ShipCity”, SqlDbType.NVarChar, 15, “ShipCity”);

cparams.Add(“@ShipRegion”, SqlDbType.NVarChar, 15, “ShipRegion”);

cparams.Add(“@ShipPostalCode”, SqlDbType.NVarChar, 10, “ShipPostalCode”);

cparams.Add(“@ShipCountry”, SqlDbType.NVarChar, 15, “ShipCountry”);

// update command parameters

cparams = updateCommand.Parameters;

cparams.Add(“@OrderID”, SqlDbType.Int, 0, “OrderID”);

cparams.Add(“@CustomerID”, SqlDbType.NChar, 5, “CustomerID”);

cparams.Add(“@EmployeeID”, SqlDbType.Int, 0, “EmployeeID”);

cparams.Add(“@OrderDate”, SqlDbType.DateTime, 0, “OrderDate”);

cparams.Add(“@RequiredDate”, SqlDbType.DateTime, 0, “RequiredDate”);

cparams.Add(“@ShippedDate”, SqlDbType.DateTime, 0, “ShippedDate”);

cparams.Add(“@ShipVia”, SqlDbType.Int, 0, “ShipVia”);

cparams.Add(“@Freight”, SqlDbType.Money, 0, “Freight”);

cparams.Add(“@ShipName”, SqlDbType.NVarChar, 40, “ShipName”);

cparams.Add(“@ShipAddress”, SqlDbType.NVarChar, 60, “ShipAddress”);

cparams.Add(“@ShipCity”, SqlDbType.NVarChar, 15, “ShipCity”);

cparams.Add(“@ShipRegion”, SqlDbType.NVarChar, 15, “ShipRegion”);

cparams.Add(“@ShipPostalCode”, SqlDbType.NVarChar, 10, “ShipPostalCode”);

cparams.Add(“@ShipCountry”, SqlDbType.NVarChar, 15, “ShipCountry”);

// create the data adapter and an event handler for after row inserts

SqlDataAdapter da = new SqlDataAdapter(sqlSelect, conn);

da.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated);

// load all of Orders data from the data source into the DataSet

DataSet ds = new DataSet();

da.Fill(ds, “Orders”);

// … code to modify the data in the DataSet

// update the data source

da.Update(ds, “Orders”);

//…

private void da_RowUpdated(object sender, SqlRowUpdatedEventArgs e)

{

if(e.Status == UpdateStatus.Continue &&

e.StatementType == StatementType.Insert)

{

e.Row[“OrderId”] = (Int32)insertCommand.ExecuteScalar();

e.Row.AcceptChanges();

}

}

Updating Data in Related Tables

To avoid referential integrity problems when updating the data source from a DataSet containing related rows, especially in situations involving batch updates, the rows must be updated in the following order:

  1. Deleted grandchild rows
  2. Deleted child rows
  3. Deleted parent rows
  4. Updated parent rows
  5. Inserted parent rows
  6. Updated child rows
  7. Inserted child rows
  8. Updated grandchild rows
  9. Inserted grandchild rows

To obtain the set of deleted rows, pass DataViewRowState.Deleted to the DataTable.Select( ) method. To obtain the set of inserted rows, pass DataViewRowState.Added to the DataTable.Select( ) method. To obtain the set of modified rows, pass DataViewRowState.ModifiedCurrent to the DataTable.Select( ) method.

There are few other considerations involving the primary key. If the primary key can’t be modified once added, the updated and inserted rows can be processed in the same statement. If, on the other hand, the primary key can be modified, the database must cascade the updated primary key values to the child records; otherwise a referential integrity violation will occur. The UpdateCommand property of child tables must accept either the Original or the Current value of the foreign key if it is used as part of a concurrency handling process. Finally, if the primary key is an AutoIncrement value, and the value is generated by the database, the InsertCommand must return the primary key value from the data source and use it to update the value in the DataSet. The DataSet then automatically cascades this new value to the child records.

Example 15-4 demonstrates the ordering of updates using a parent and child table; because there are no grandchild records, only six update commands instead of nine are required. As discussed earlier, the code uses the Select( ) method of the tables with the DataViewRowState argument to select the subset of records to update.

Example 15-4. Update ordering

SqlDataAdapter daOrders;

SqlDataAdapter daOrderDetails;

// setup data adapters, and the SelectCommand and UpdateCommands for the

// parent Orders table and the child Order Details table

// load the data for parent and child tables into the DataSet

DataSet ds = new DataSet();

daOrders.Fill(ds, “Orders”);

daOrderDetails.Fill(ds, “Order Details”);

// … code to modify the data in the DataSet

// update the modified data in the DataSet back to the data source

daOrderDetails.Update(ds.Tables[“Order Details”].Select(null, null,

DataViewRowState.Deleted));

daOrders.Update(ds.Tables[“Orders”].Select(null, null,

DataViewRowState.Deleted));

daOrders.Update(ds.Tables[“Orders”].Select(null, null,

DataViewRowState.ModifiedCurrent));

daOrders.Update(ds.Tables[“Orders”].Select(null, null,

DataViewRowState.Added));

daOrderDetails.Update(ds.Tables[“Order Details”].Select(null, null,

DataViewRowState.ModifiedCurrent));

daOrderDetails.Update(ds.Tables[“Order Details”].Select(null, null,

DataViewRowState.Added));

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;

}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: