15.2 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.