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