14.4 Updating the Data Source

The Update( ) method can submit DataSet changes back to the data source. It uses the statements in the DeleteCommand, InsertCommand, and UpdateCommand objects to attempt to update the data source with records that have been deleted, inserted, or updated in the DataSet. Each row is updated individually and not as part of a batch process. Furthermore, the order in which the rows are processed is determined by the indexes on the DataTable and not by the update type. Figure 14-2 illustrates how the DataAdapter is used both to reconcile changed data in the DataSet with the data source using the Update() method and to retrieve data from the data source using the Fill() method.

Figure 14-2. Retrieving and updating data using the DataAdapter

The delete, insert, and update statements can be automatically generated using the CommandBuilder object, but this is probably not the best approach for production systems. The CommandBuilder object is discussed in more detail in Chapter 15. Alternatively, custom update logic can be used where the DeleteCommand, InsertCommand, and UpdateCommand are each defined. Compared with using the CommandBuilder, custom logic can significantly improve performance and can implement solutions to complex updating and conflict-resolution scenarios. Custom update logic is also examined in detail in Chapter 15.

The following example demonstrates the Update( ) method. For simplicity, a CommandBuilder generates the update logic.

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

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

SqlDataAdapter da = new SqlDataAdapter(selectSql, 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");

As with the Fill( ) and FillSchema( ) methods, opening and closing the connection are performed by the data adapter, if necessary.