Update a database using dataadapter and Dataset

DataadapterUpdateMethod callableDatasetThe changes are resolved back to the data source. AndFillThe method is similar,UpdateMethodDatasetAnd optionalDatatableObject orDatatableThe name is used as a parameter.DatasetThe instance contains the modifiedDataset, AndDatatableIdentifies the table from which changes are retrieved.

WhenUpdateMethod,DataadapterThe changes are analyzed and corresponding commands (insert, update, or delete) are executed ). WhenDataadapterEncounter a pairDatarowIt will useInsertcommand,UpdatecommandOrDeletecommandTo process the changes. In this way, you can try to improve the performance of the ADO. NET application by specifying the command syntax at design and using stored procedures whenever possible. Before callingUpdateThese commands must be explicitly set before. IfUpdateHowever, there are no commands for specific updates (for example, the commands for deleting rows do not exist ).Deletecommand.

CommandParameters can be usedDatasetSpecify the input and output values for each modified SQL statement or stored procedure. For more information, see use parameters for dataadapter.

IfDatatableYou can useCommandbuilderAutomatic Object generationDataadapterOfDeletecommand,InsertcommandAndUpdatecommand. For more information, see automatically generated commands.

UpdateMethod will resolve the changes back to the data source, but since the last fillingDatasetOther clients may have modified the data in the data source. To use the current data to refreshDataset, Please use it againDataadapterFill (Fill) Dataset. The new row is added to the table, and the updated information is merged into the existing row.

If you want to processUpdateYou can useRowupdatedThe response line update error occurs when these exceptions occur. For more information, see use dataadapter events.UpdatePreviouslyDataadapter. continueupdateonerrorSetTrueAnd thenUpdateWhen the response is completed, the response is stored in a specific rowRowerrorAttribute error information (see add and read row error information ).

Note:IfDataset,DatatableOrDatarowCallAcceptchangesDatarowAllOriginalThe value isDatarowOfCurrentValue rewriting. If you have modified the field value that identifies this row as a unique rowAcceptchangesAfter,OriginalThe value does not match the value in the data source.

The following example shows how to explicitly setDataadapterOfUpdatecommandTo update the modified rows. Note that the parameter specified in the WHERE clause of the update statement is set to useSourcecolumnOfOriginalValue. This is important becauseCurrentThe value may have been modified and may not match the value in the data source.OriginalThe value was used to populate the data source.Datatable.

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

[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, 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 );

Auto increment Column

If the table from the data source contains an auto-incrementing column, you can use the value generated by the data source to fill itDatasetThe method is to return an auto-incrementing value in the form of a stored procedure output parameter and map it to a column in the table, or useDataadapterOfRowupdatedEvent. For examples, see retrieve "ID" or "automatic number" values.

However,DatasetThe value in may not be synchronized with the value in the data source and cause unexpected behavior. For example, consider an automatically incrementing primary key column.CustomerID. IfDatasetAdd two new customers, they will receive the auto-incrementingCustomerIdValue1And2. ForwardDataadapterOfUpdateMethod to pass the second line of the customer, the newly added line will receive the auto increment from the data sourceCustomerIDValue1, The value correspondsDatasetValue in2Does not match. WhenDataadapterFill with return valuesDatasetInCustomerIDAlready Yes1Therefore, a constraint conflict occurs.

To avoid such behavior, we recommend that you use auto-incrementing columns andDatasetInDatasetCreateAutoincrementstepIs-1 andAutoincrementseed0, and make sure that the data source generates an auto-incrementing id value starting from 1 and increasing with the positive step value. In this way,DatasetA negative number is generated for the auto-incrementing value, which does not conflict with the positive auto-incrementing value generated by the data source. Another method is to useGuidInstead of auto-incrementing columns. GenerateGuidThe Value algorithm isDatasetGenerated inGuidNever generated with the data sourceGuidSame. DefinitionDatatableFor more information about columns in, see define the schema of a data table.

Sort insert, update, and delete

In many cases, the order in whichDatasetThe changes made are important. For example, if you have updated the primary key value of an existing row and added a new row with the new primary key value, you must process the update before processing the insert operation.

AvailableDatatableOfSelectMethod to return only references with specificRowstateOfDatarowArray. Then you canDatarowArray passedDataadapterOfUpdateTo process the modified rows. You can control the order of insert, update, and delete operations by specifying the subset of rows to be updated.

For example, the following code first processes the deleted rows in the table, then the updated rows, and then the inserted rows.

[Visual Basic]
Dim updtable as datatable = custds. Tables ("MERs ")

'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 ["MERs"];

// 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 ));

OfUpdateMethod callableDatasetThe changes are resolved back to the data source. AndFillThe method is similar,UpdateMethodDatasetAnd optionalDatatableObject orDatatableThe name is used as a parameter.DatasetThe instance contains the modifiedDataset, AndDatatableIdentifies the table from which changes are retrieved.