Updating the database using DataAdapter and Datasets [C #]

The DataAdapter Update method is used to parse changes in the dataset back to the data source. Like the Fill method, the Update method uses an instance of the DataSet and an optional DataTable object or DataTable name as parameters. The dataset instance is a dataset that contains the changes made, and the DataTable identifies the table from which the changes were retrieved.

When the Update method is called, DataAdapter analyzes the changes made and executes the appropriate command (INSERT, Update, or DELETE). When DataAdapter encounters a change to DataRow, it uses InsertCommand, UpdateCommand, or DeleteCommand to handle the change. This allows you to maximize the performance of your ado.net application by specifying the command syntax at design time and, if possible, by using stored procedures. You must explicitly set these commands before calling Update. If you call update but do not have a corresponding command for a specific update (for example, there is no DeleteCommand for the deleted row), an exception is thrown.

The Command parameter can be used to specify input and output values for SQL statements or stored procedures for each modified row in the DataSet. For more information, see Using Parameters with DataAdapter.

If a DataTable is mapped to a single database table or generated from a single database table, you can use the CommandBuilder object to automatically generate DataAdapter DeleteCommand, InsertCommand, and UpdateCommand. For more information, see automatically generated commands.

The Update method resolves the changes back to the data source, but other clients may have modified the data in the data source since the last time the dataset was populated. To refresh the dataset with the current data, use the DataAdapter Fill (fill) dataset again. The new row is added to the table, and the updated information is merged into the existing rows.

To handle exceptions that may occur during the update operation, you can use the RowUpdated event to respond to row update errors when these exceptions occur (see Using the DataAdapter event), or you can DataAdapter.ContinueUpdateOnError set to True, and then respond to an error message stored in the RowError property of a particular row when the Update completes (see Adding and Reading Row error messages).

Note If you call AcceptChanges on a DataSet, DataTable, or DataRow, all Original values for a DataRow will be overwritten by the current value of the DataRow. If you have modified a field value that identifies the row as a unique row, the Original value will no longer match the value in the data source when AcceptChanges is invoked.
The following example shows how to perform an update to a modified row by explicitly setting the DataAdapter UpdateCommand. Note that the parameter specified in the WHERE clause of the UPDATE statement is set to use the Original value of SourceColumn. This is important because the current value may have been modified and may not match the values in the data source. The Original value is the value that was used to populate the DataTable from the data source.

SqlClient
[Visual Basic]
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, "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, "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);
OLE DB
[Visual Basic]
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, "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, "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);
Incrementing columns automatically
If the table from the data source contains AutoIncrement columns, you can populate the dataset with values generated by the data source by returning the AutoIncrement increment in the form of a stored procedure output parameter and mapping it to a column in the table, or by using the DataAdapter rowupdated event. For an example, see retrieving Identification or AutoNumber values.

However, the values in the dataset may be out of sync with the values in the data source and cause unexpected behavior. For example, consider a table that contains an AutoIncrement primary key column, CustomerID. If you add two new customers to the DataSet, they will receive an automatically incremented CustomerId value of 1 and 2. When a second customer row is passed to the DataAdapter Update method, the newly added line receives an AutoIncrement CustomerID value of 1 in the data source that does not match the value 2 in the dataset. When DataAdapter fills rows in a DataSet with a return value, a constraint conflict occurs because the CustomerID of the first customer row is already 1.

To avoid this behavior, it is recommended that you create AutoIncrementStep-1 and autoincrementseed 0 columns in the dataset when using AutoIncrement columns in the data source and AutoIncrement columns in the dataset, and ensure that the data source generation starts at 1 and An automatically incremented identity value that is incremented with a positive step value. In this way, the DataSet generates negative numbers for autoincrement values that do not conflict with the positive AutoIncrement values generated by the data source. Another method is to use a column of the Guid type instead of an AutoIncrement column. An algorithm that generates a GUID value generates a GUID in the dataset that is never the same as the GUID generated by the data source. For more information about defining columns in a DataTable, see Defining a data table's schema.

Sort of inserts, updates, and deletes
In many cases, it is important to send the changes made through the dataset to the data source in what order. For example, if you have updated the primary key value of an existing row and added a new row with a new primary key value, be sure to process the update before you process the insert.

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

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

[Visual Basic]
Dim updtable as DataTable = Custds.tables ("Customers")

' The deletes process.
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"];

The deletes process.
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));