Use sqldataadapter. Update (Dataset DS) to update a database

I. Use sqldataadapter. Update (Dataset DS) to update the database.
1. When dbdataadapter calls the update method, dataadapter analyzes the changes and runs the corresponding commands (insert, update, or delete ). When dataadapter encounters a change to datarow, it uses insertcommand, updatecommand, or deletecommand to handle the change. In this way, you can try to improve the ADO. NET application by specifying the command syntax at design and using stored procedures whenever possible Program Performance. Before calling update, you must explicitly set these commands. If an update command is called but does not exist for a specific update (for example, deletecommand for deleted rows does not exist), an exception is thrown.
However, if datatable is mapped to a single database table or generated from a single database table, you can use the commandbuilder object to automatically generate the deletecommand, insertcommand, and updatecommand of dataadapter. To automatically generate commands, you must set the selectcommand attribute, which is the lowest requirement. The table schema retrieved by selectcommand determines the syntax of the automatically generated insert, update, and delete statements. If you modify the commandtext of selectcommand after the insert, update, or delete command is automatically generated, an exception may occur. If you have modified selectcommand. the architecture information contained in commandtext and the selectcommand used to automatically generate the insert, update, or delete command. commandtext is inconsistent. the update method may attempt to access a column that does not exist in the current table referenced by selectcommand and cause an exception. You can call the refreshschema method of commandbuilder to refresh the architecture information that commandbuilder uses to automatically generate commands.
For the dbdataadapter. Update method to update a database, DS. acceptchanges is required each time dbdataadapter. Update (DS) is called.
Otherwise, unexpected errors will occur to the DS used later.
Depressing ~ I changed the program for one day and found this error. It's so depressing ~~
2. sqlcommandbuilder automatically generates and updates the updated, modified, and deleted SQL statements.
3. If the table does not have a primary key, an exception occurs when the automatically generated SQL statement is updated.
Exception information: Use sqlcommandbuilder to update dataset. "selectcommand that does not return any key column information does not support dynamic SQL generation of updatecommand" is encountered.
4. Solution: 1. Modify the table definition and define a primary key;
2. Specify updatecommand (deletecommand, insertcommand should be the same) for sqldataadapter );

Example:

1. String emailsql = "select email, validflag from emailme ";
Dataset emailadd = new dataset ();
Sqldataadapter emailadapter = new sqldataadapter (emailsql, myconn );
Sqlcommandbuilder cb = new sqlcommandbuilder (emailadapter );
Emailadapter. Fill (emailadd, "Address ");
Myconn. Close ();
Datatable mydt = emailadd. Tables ["Address"];
Mydt. primarykey = new datacolumn [] {mydt. Columns ["email"]};
... // Modify myds data
Emailadapter. Update (emailadd, "Address ");

Automatically generate SQL statements.

2. String emailsql = "select email, validflag from emailme ";
Dataset emailadd = new dataset ();
Sqldataadapter emailadapter = new sqldataadapter (emailsql, myconn );
Sqlcommandbuilder cb = new sqlcommandbuilder (emailadapter );
Sqlcommand upcmd = new sqlcommand ("Update [" + strtablename + "] Set validflag = @ validflag where email = @ email", myconn );
Upcmd. Parameters. Add ("@ validflag", sqldbtype. Int, 8, "validflag ");
Upcmd. Parameters. Add ("@ email", sqldbtype. nvarchar, 100, "email ");
Emailadapter. updatecommand = upcmd;
Emailadapter. Fill (emailadd, "Address ");
Myconn. Close ();
... // Modify myds data
Emailadapter. Update (emailadd, "Address ");

Custom SQL statements.

2. Search for "ID" or "automatic number" Values
Retrieve "ID" or "automatic number" Value
This page only applies
Microsoft Visual Studio 2005/. NET Framework 2.0

Other versions of the following products are also available:
Microsoft Visual Studio 2008/. NET Framework 3.5
. NET Framework developer Guide
Retrieve "ID" or "automatic number" Value
To ensure that each row in the table has a unique value, you can set the columns in the able to auto-increment primary keys. However, your application may have multiple clients, and each client may use a separate able instance. In this case, duplicate values may eventually occur between individual able instances. Because all clients use a single data source, the data source can be defined to automatically increment values to resolve this conflict. To complete this task, use the "ID" column in Microsoft SQL Server or the "Automatic number" field in Microsoft Access.

If you use the data source to fill in the "ID" or "auto number" column for the new row added to the dataset, a unique situation will occur because the dataset is not directly connected to the data source. Therefore, dataset does not recognize any values automatically generated by the data source. However, for data sources (such as Microsoft SQL Server) that can create stored procedures with output parameters, values (such as new ID values) can be automatically generated) specify as output parameter and use dataadapter to map this value back to the corresponding column in dataset.

Data sources may not support stored procedures with output parameters. In this case, you can use the rowupdated event to retrieve automatically generated values and insert them into the dataset insert row or update row. This section contains an example to show how to setCodeAdd to the rowupdated event to determine whether the insertion has occurred. Then retrieve the automatically generated value and store it in the currently updated row.

Retrieve the value of the "ID" column of SQL Server

The following stored procedure and code example shows how to map the auto-incrementing id value from the Microsoft SQL Server table back to the corresponding column added to the table added to the dataset table. This stored procedure is used to Insert a new row into the categories table of the northwind database and return the id value returned from the transact-SQL scope_identity () function as an output parameter.

Copy code
Create procedure insertcategory
@ Categoryname nchar (15 ),
@ Identity int out
As
Insert into categories (categoryname) values (@ categoryname)
Set @ identity = scope_identity ()
The insertcategory stored procedure can be specified as the insertcommand source. Create a parameter for the output parameter that receives the "identifier. This parameter has output parameterdirection and specifies sourcecolumn as the categoryid column of the local categories table in dataset. After insertcommand is processed for the added row, the Automatically increasing id value is returned as this output parameter and then placed in the categoryid column of the current row.

The following code example shows how to return an auto-incrementing value as an output parameter and specify it as the source value of the categoryid column in dataset.

Visual Basic
Copy code
'Assumes that connection is a valid sqlconnection object.
Dim adapter as sqldataadapter = new sqldataadapter (_
"Select categoryid, categoryname from DBO. Categories", connection)

Adapter. insertcommand = new sqlcommand ("insertcategory", connection)
Adapter. insertcommand. commandtype = commandtype. storedprocedure

Adapter. insertcommand. Parameters. Add (_
"@ Categoryname", sqldbtype. nchar, 15, "categoryname ")

Dim parameter as sqlparameter = Adapter. insertcommand. Parameters. Add (_
"@ Identity", sqldbtype. Int, 0, "categoryid ")
Parameter. Direction = parameterdirection. Output

Connection. open ()

Dim categories as dataset = new dataset
Adapter. Fill (categories, "categories ")

Dim newrow as datarow = categories. Tables ("categories"). newrow ()
Newrow ("categoryname") = "new category"
Categories. Tables ("categories"). Rows. Add (newrow)

Adapter. Update (categories, "categories ")

Connection. Close ()
C #
Copy code
// Assumes that connection is a valid sqlconnection object.
Sqldataadapter adapter = new sqldataadapter (
"Select categoryid, categoryname from DBO. Categories", connection );

Adapter. insertcommand = new sqlcommand ("insertcategory", connection );
Adapter. insertcommand. commandtype = commandtype. storedprocedure;

Adapter. insertcommand. Parameters. Add (
"@ Categoryname", sqldbtype. nchar, 15, "categoryname ");

Sqlparameter parameter = Adapter. insertcommand. Parameters. Add (
"@ Identity", sqldbtype. Int, 0, "categoryid ");
Parameter. Direction = parameterdirection. output;

Connection. open ();

Dataset categories = new dataset ();
Adapter. Fill (categories, "categories ");

Datarow newrow = categories. Tables ["categories"]. newrow ();
Newrow ["categoryname"] = "new category ";
Categories. Tables ["categories"]. Rows. Add (newrow );

Adapter. Update (categories, "categories ");

Connection. Close ();
Retrieve the Microsoft Access "automatic number" Value

Microsoft Access does not support stored procedures or batch command processing. Therefore, output parameters cannot be mapped to the source columns in the table shown in the preceding example. However, Microsoft Access 2000 or later supports the @ identity attribute to retrieve the value of the "Automatic number" field after "insert. With the rowupdated event, you can determine whether "insert" has occurred, retrieve the latest "automatic number" value, and then put the value in the "ID" column on the local surface of the dataset.

The following code example shows how to use oledbdataadapter to insert a new value to the Microsoft Access 2000 northwind database categories table. This example uses the rowupdated event to fill in the "Automatic number" value generated by the jet engine and the Access database when a record is inserted into the categories table. Note that this applies only to the JET 4.0 ole db provider and Microsoft Access 2000 or later.

Visual Basic
Copy code
'Assumes that connection is a valid oledbconnection object.
'Use the dataadapter to fill and update the dataset.
Dim adapter as oledbdataadapter = new oledbdataadapter (_
"Select categoryid, categoryname from categories order by categoryid ",_
Connection)

Adapter. insertcommand = new oledbcommand (_
"Insert into categories (categoryname) values (?) ", Connection)
Adapter. insertcommand. commandtype = commandtype. Text

Adapter. insertcommand. Parameters. Add (_
"@ Categoryname", oledbtype. Char, 15, "categoryname ")

Connection. open ()

'Fill the dataset.
Dim categories as dataset = new dataset
Adapter. Fill (categories, "categories ")

'Add a new row.
Dim newrow as datarow = categories. Tables ("categories"). newrow ()
Newrow ("categoryname") = "new category"
Categories. Tables ("categories"). Rows. Add (newrow)

'Include an event to fill in the autonumber value.
Addhandler Adapter. rowupdated ,_
New oledbrowupdatedeventhandler (addressof onrowupdated)

'Update the dataset.
Adapter. Update (categories, "categories ")
Connection. Close ()

'Event procedure for onrowupdated
Private shared sub onrowupdated (_
Sender as object, argS as oledbrowupdatedeventargs)
'Include a variable and a command to retrieve the identity value
'From the ACCESS database.
Dim newid as integer = 0
Dim idcmd as oledbcommand = new oledbcommand (_
"Select @ identity", connection)

If args. statementtype = statementtype. insert
'Retrieve the identity value and store it in the categoryid column.
Newid = CINT (idcmd. executescalar ())
Args. Row ("categoryid") = newid
End if
End sub
C #
Copy code
// Assumes that connection is a valid oledbconnection object.
Oledbdataadapter adapter = new oledbdataadapter (
"Select categoryid, categoryname from categories order by categoryid ",
Connection );

Adapter. insertcommand = new oledbcommand (
"Insert into categories (categoryname) values (?) ", Connection );
Adapter. insertcommand. commandtype = commandtype. text;

Adapter. insertcommand. Parameters. Add (_
"@ Categoryname", oledbtype. Char, 15, "categoryname ");

Connection. open ();

// Fill the dataset.
Dataset categories = new dataset ();
Adapter. Fill (categories, "categories ");

// Add a new row.
Datarow newrow = categories. Tables ["categories"]. newrow ();
Newrow ["categoryname"] = "new category ";
Categories. Tables ["categories"]. Rows. Add (newrow );

// Include an event to fill in the autonumber value.
Adapter. rowupdated + = new oledbrowupdatedeventhandler (onrowupdated );

// Update the dataset.
Adapter. Update (categories, "categories ");

Connection. Close ();

// Event procedure for onrowupdated
Protected static void onrowupdated (
Object sender, oledbrowupdatedeventargs ARGs)
{
// Include a variable and a command to retrieve the identity value from the ACCESS database.
Int newid = 0;
Oledbcommand idcmd = new oledbcommand (
"Select @ identity", connection );

If (ARGs. statementtype = statementtype. insert)
{
// Retrieve the identity value and store it in the categoryid column.
Newid = (INT) idcmd. executescalar ();
Args. Row ["categoryid"] = newid;
}
3. Use dataadapter and dataset to update the database [C #]
The update method of dataadapter can be called to resolve changes in dataset back to the data source. Similar to the fill method, the update method uses the dataset instance and optional able object or able name as parameters. A dataset instance is a dataset that contains the changes that have been made, and a datatable identifies the tables from which changes are retrieved.

When the update method is called, dataadapter analyzes the changes and runs the corresponding commands (insert, update, or delete ). When dataadapter encounters a change to datarow, it uses insertcommand, updatecommand, or deletecommand to handle the change. 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 calling update, you must explicitly set these commands. If an update command is called but does not exist for a specific update (for example, deletecommand for deleted rows does not exist), an exception is thrown.

The command parameter can be used to specify the input and output values for each modified SQL statement or stored procedure in dataset. For more information, see use parameters for dataadapter.

If datatable is mapped to a single database table or generated from a single database table, you can use the commandbuilder object to automatically generate the deletecommand, insertcommand, and updatecommand of dataadapter. For more information, see automatically generated commands.

The update method resolves the changes to the data source. However, since the dataset was last filled, other clients may have modified the data in the data source. To use the current data to refresh the dataset, use the dataadapter to fill (fill) the dataset again. The new row is added to the table, and the updated information is merged into the existing row.

To handle exceptions that may occur during the update operation, you can use the rowupdated event to respond to a row update error when these exceptions occur (see using the dataadapter event ), alternatively, you can call the dataadapter before calling update. continueupdateonerror is set to true, and then respond to the error information stored in the rowerror attribute of a specific row when update is complete (see add and read row error information ).

Note: If you call acceptchanges for dataset, able, or datarow, all original values of a datarow will be overwritten by the current value of the datarow. If you have modified the field value that identifies the row as a unique row, after you call acceptchanges, the original value does not match the value in the data source.

The following example shows how to explicitly set the dataadapter updatecommand to update modified rows. Note that the parameter specified in the WHERE clause of the update statement is set to the original value of 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 fill 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, 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 a table from the data source contains an auto-incrementing column, you can use the value generated by the data source to fill the columns in the dataset, the 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 use the rowupdated event of dataadapter. For examples, see retrieve "ID" or "automatic number" values.

However, the value in dataset may not be synchronized with the value in the data source and cause unexpected behavior. For example, consider a table that contains the customerid column of the auto-incrementing primary key column. If two new customers are added to the dataset, they will receive the auto-incrementing customerid values 1 and 2. When the second customer row is passed to the update method of dataadapter, the newly added row receives the customerid value 1 Automatically increasing from the data source, which does not match the value 2 in dataset. When dataadapter uses the return value to fill the rows in dataset, the constraints conflict because the customerid of the first customer row is already 1.

To avoid this behavior, we recommend that you create columns with autoincrementstep-1 and autoincrementseed 0 in dataset when using auto-incrementing columns in the data source and auto-incrementing columns in dataset, make sure that the data source automatically increments the id value starting from 1 and increasing with the positive step value. In this way, dataset generates a negative number for the auto-increment value, which does not conflict with the positive auto-increment value generated by the data source. Another method is to use columns of the guid type instead of auto-incrementing columns. The GUID ValueAlgorithmThe GUID generated in dataset is never the same as the guid generated by the data source. For more information about defining columns in a able, see define the schema of a data table.

Sort insert, update, and delete

In many cases, the order in which changes made via dataset are sent to the data source is 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.

you can use the select method of datatable to return a datarow array that only references a specific rowstate. Then, you can pass the returned datarow array to the update method of dataadapter to Process modified rows. You can control the order of insert, update, and delete operations by specifying the subset of rows to be updated.