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

Get ADO.NET in a Nutshell now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.