Updating the Database with a DataAdapter and the DataSet

Article
10/12/2006

The Update method of the DataAdapter is called to resolve changes from a DataSet back to the data source. The Update method, like the Fill method, takes as arguments an instance of a DataSet, and an optional DataTable object or DataTable name. The DataSet instance is the DataSet that contains the changes that have been made, and the DataTable identifies the table from which to retrieve the changes.

When you call the Update method, the DataAdapter analyzes the changes that have been made and executes the appropriate command (INSERT, UPDATE, or DELETE). When the DataAdapter encounters a change to a DataRow, it uses the InsertCommand, UpdateCommand, or DeleteCommand to process the change. This allows you to maximize the performance of your ADO.NET application by specifying command syntax at design-time and, where possible, through the use of stored procedures. You must explicitly set the commands before calling Update. If Update is called and the appropriate command does not exist for a particular update (for example, no DeleteCommand for deleted rows), an exception will be thrown.

Command parameters can be used to specify input and output values for an SQL statement or stored procedure for each modified row in a DataSet. For more information, see Using Parameters with a DataAdapter.

If your DataTable maps to or is generated from a single database table, you can take advantage of the CommandBuilder object to automatically generate the DeleteCommand, InsertCommand, and UpdateCommand of the DataAdapter. For more information, see Automatically Generated Commands.

The Update method will resolve your changes back to the data source, however other clients may have modified data at the data source since the last time you filled the DataSet. To refresh your DataSet with current data, use the DataAdapter and Fill the DataSet again. New rows will be added to the table, and updated information will be incorporated into existing rows. The Fill method determines whether a new row will be added or an existing row will be updated by examining the primary key values of the rows in the DataSet and the rows returned by the SelectCommand. If the Fill method encounters a primary key value for a row in the DataSet that matches a primary key value from a row in the results returned by the SelectCommand, it updates the existing row with the information from the row returned by the SelectCommand and sets the RowState of the existing row to Unchanged. If a row returned by the SelectCommand has a primary key value that does not match any of the primary key values of the rows in the DataSet, the Fill method adds a new row with a RowState of Unchanged.

Note If the SelectCommand returns the results of an OUTER JOIN, the DataAdapter will not set a PrimaryKey value for the resulting DataTable. You will need to define the PrimaryKey yourself to ensure that duplicate rows are resolved correctly. For more information, see Defining a Primary Key for a Table.

To handle exceptions that may occur during an Update, you can use the RowUpdated event to respond to row update errors when they occur (see Working with DataAdapter Events), or you can set DataAdapter.ContinueUpdateOnError to true before calling Update, and respond to the error information stored in the RowError property of a particular row when the Update is completed (see Adding and Reading Row Error Information).

**Note **Calling AcceptChanges on the DataSet, DataTable, or DataRow will cause all Original values for a DataRow to be overwritten with the Current values for the DataRow. If the field values that identify the row as unique have been modified, after calling AcceptChanges the Original values will no longer match the values in the data source.

The following examples demonstrate how to perform updates to modified rows by explicitly setting the UpdateCommand of the DataAdapter. Notice that the parameter specified in the WHERE clause of the UPDATE statement is set to use the Original value of the SourceColumn. This is important, because the Current value may have been modified and may not match the value in the data source. The Original value is the value that was used to populate the DataTable from the data source.

SqlClient

Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn)

catDA.UpdateCommand = New SqlCommand("UPDATE Categories SET CategoryName = @CategoryName " & _
                                     "WHERE CategoryID = @CategoryID", nwindConn)

catDA.UpdateCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")

Dim workParm As SqlParameter = catDA.UpdateCommand.Parameters.Add("@CategoryID", SqlDbType.Int)
workParm.SourceColumn = "CategoryID"
workParm.SourceVersion = DataRowVersion.Original

Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")  

Dim cRow As DataRow = catDS.Tables("Categories").Rows(0)
cRow("CategoryName") = "New Category"

catDA.Update(catDS)
[C#]SqlDataAdapter catDA = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn);       

catDA.UpdateCommand = new SqlCommand("UPDATE Categories SET CategoryName = @CategoryName " +
                                     "WHERE CategoryID = @CategoryID" , nwindConn);

catDA.UpdateCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName");

SqlParameter workParm = catDA.UpdateCommand.Parameters.Add("@CategoryID", SqlDbType.Int);
workParm.SourceColumn = "CategoryID";
workParm.SourceVersion = DataRowVersion.Original;

DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");   

DataRow cRow = catDS.Tables["Categories"].Rows[0];
cRow["CategoryName"] = "New Category";

catDA.Update(catDS);

OleDb

Dim catDA As OleDbDataAdapter = New OleDbDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn)       

catDA.UpdateCommand = New OleDbCommand("UPDATE Categories SET CategoryName = ? " & _
                                       "WHERE CategoryID = ?" , nwindConn)

catDA.UpdateCommand.Parameters.Add("@CategoryName", OleDbType.VarChar, 15, "CategoryName")

Dim workParm As OleDbParameter = catDA.UpdateCommand.Parameters.Add("@CategoryID", OleDbType.Integer)
workParm.SourceColumn = "CategoryID"
workParm.SourceVersion = DataRowVersion.Original

Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")    

Dim cRow As DataRow = catDS.Tables("Categories").Rows(0)
cRow("CategoryName") = "New Category"

catDA.Update(catDS)
[C#]OleDbDataAdapter catDA = new OleDbDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn);            

catDA.UpdateCommand = new OleDbCommand("UPDATE Categories SET CategoryName = ? " +
                                       "WHERE CategoryID = ?" , nwindConn);

catDA.UpdateCommand.Parameters.Add("@CategoryName", OleDbType.VarChar, 15, "CategoryName");

OleDbParameter workParm = catDA.UpdateCommand.Parameters.Add("@CategoryID", OleDbType.Integer);
workParm.SourceColumn = "CategoryID";
workParm.SourceVersion = DataRowVersion.Original;

DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");    

DataRow cRow = catDS.Tables["Categories"].Rows[0];
cRow["CategoryName"] = "New Category";
catDA.Update(catDS);

Odbc

Dim catDA As OdbcDataAdapter = New OdbcDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn)

catDA.UpdateCommand = New OdbcCommand("UPDATE Categories SET CategoryName = ? " & _
                                      "WHERE CategoryID = ?" , nwindConn)

catDA.UpdateCommand.Parameters.Add("@CategoryName", OdbcType.VarChar, 15, "CategoryName")

Dim workParm As OdbcParameter = catDA.UpdateCommand.Parameters.Add("@CategoryID", OdbcType.Int)
workParm.SourceColumn = "CategoryID"
workParm.SourceVersion = DataRowVersion.Original

Dim catDS As DataSet = New DataSet
catDA.Fill(catDS, "Categories")    

Dim cRow As DataRow = catDS.Tables("Categories").Rows(0)

cRow("CategoryName") = "New Category"

Dim modRows() As DataRow = catDS.Tables("Categories").Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent)

catDA.Update(modRows)
[C#]OdbcDataAdapter catDA = new OdbcDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn);

catDA.UpdateCommand = new OdbcCommand("UPDATE Categories SET CategoryName = ? " +
                                      "WHERE CategoryID = ?" , nwindConn);

catDA.UpdateCommand.Parameters.Add("@CategoryName", OdbcType.VarChar, 15, "CategoryName");

OdbcParameter workParm = catDA.UpdateCommand.Parameters.Add("@CategoryID", OdbcType.Int);
workParm.SourceColumn = "CategoryID";
workParm.SourceVersion = DataRowVersion.Original;

DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");    

DataRow cRow = catDS.Tables["Categories"].Rows[0];

cRow["CategoryName"] = "New Category";

DataRow[] modRows = catDS.Tables["Categories"].Select(null, null, DataViewRowState.ModifiedCurrent);
catDA.Update(modRows);

AutoIncrement Columns

If the tables from your data source have auto-incrementing columns, you can fill the columns in your DataSet with the values generated by the data source by returning the auto-increment value as an output parameter of a stored procedure and mapping that to a column in a table, or by using the RowUpdated event of the DataAdapter. For an example of this, see Retrieving Identity or Autonumber Values.

However, the values in your DataSet can become out-of-sync with the values at the data source and result in unexpected behavior. For example, consider a table with an auto-incrementing primary key column of CustomerID. If you add two new customers within the DataSet, they receive auto-incremented CustomerId values of 1 and 2. When the second customer row is passed to the Update method of the DataAdapter, the newly added row receives an auto-incremented CustomerID value of 1 at the data source, which does not match the value, 2, in the DataSet. When the DataAdapter fills the row in the DataSet with the returned value, a constraint violation occurs because the first customer row already has a CustomerID of 1.

To avoid this behavior, it is recommended that, when working with auto-incrementing columns at a data source and auto-incrementing columns in a DataSet, you create the column in the DataSet with an AutoIncrementStep of -1 and an AutoIncrementSeed of 0, as well as ensuring that your data source generates auto-incrementing identity values starting from 1 and incrementing with a positive step value. As a result, the DataSet will generate negative numbers for auto-incremented values that will not conflict with the positive auto-increment values generated by the data source. Another option is to use columns of type Guid instead of auto-incrementing columns. The algorithm that generates Guid values should never generate the same Guid in the DataSet as is generated by the data source. For more information about defining columns in a DataTable, see Defining the Schema of a DataTable.

Ordering of Inserts, Updates, and Deletes

In many circumstances, the order in which changes made through the DataSet are sent to the data source is important. For example, if a primary key value for an existing row is updated, and a new row has been added with the new primary key value, it is important to process the update before the insert.

You can use the Select method of the DataTable to return a DataRow array that only references rows with a particular RowState. You can then pass the returned DataRow array to the Update method of the DataAdapter to process the modified rows. By specifying a subset of rows to be updated, you can control the order in which inserts, updates, and deletes are processed.

For example, the following code ensures that the deleted rows of the table are processed first, then the updated rows, and then the inserted rows.

Dim updTable As DataTable = custDS.Tables("Customers")

' First process deletes.
custDA.Update(updTable.Select(Nothing, Nothing, DataViewRowState.Deleted))

' Next process updates.
custDA.Update(updTable.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))

' Finally, process inserts.
custDA.Update(updTable.Select(Nothing, Nothing, DataViewRowState.Added))
[C#]DataTable updTable = custDS.Tables["Customers"];

// First process deletes.
custDA.Update(updTable.Select(null, null, DataViewRowState.Deleted));

// Next process updates.
custDA.Update(updTable.Select(null, null, DataViewRowState.ModifiedCurrent));

// Finally, process inserts.
custDA.Update(updTable.Select(null, null, DataViewRowState.Added));