Three ways to update a database using DataSet DataTable in C #

(http://www.jb51.net/article/54418.htm)

This article mainly introduces the method of C # data acquisition from a DataTable, related to C # operation of the DataTable, the need for friends can refer to the following

This article describes in an instance the three implementation methods for updating a database using the DataSet DataTable, including the CommandBuilder method, DataAdapter updating the data source, and updating using SQL statements. Share it for everyone's reference. Here's how:

I. Conditional CommandBuilder method for automatically generating commands

A) dynamically specify SelectCommand properties

b) Use CommandBuilder objects to automatically generate DataAdapter DeleteCommand, InsertCommand, and UpdateCommand.

c) To return the construction of INSERT, UPDATE, and DELETE. SQL CommandBuilder must perform SelectCommand.

That is, you must experience an additional trip to the data source, which may degrade performance. This is also a disadvantage of automatic command generation.

D) SelectCommand must also return at least one primary key or a unique column.

When CommandBuilder and DataAdapter are associated, commands that are empty in DeleteCommand, InsertCommand, and UpdateCommand are automatically generated. That is, the non-empty is not generated.

e) must be a table, select cannot be a union of multiple tables.

Rules for generating commands:

Inserts a row at the data source for all rows in the table that are RowState Added (excluding columns such as identities, expressions, or timestamps).
Update rows for Modified rows (column values match the primary key column values of the row).
Deleted row Delete Row (column value matches the row's primary key column value). That's why the requirements C.D

Attention:

A) because from the select data to the update data, it is possible that the data has been modified by other users in the middle of the period. Automatically generate commands this update is only updated when the row contains all the original values and has not been removed from the data source.

b) Automatic command generation logic generates INSERT, UPDATE, or DELETE statements for stand-alone tables regardless of any relationship to other tables in the data source. Therefore, when you call Update to commit changes to a column that participates in a FOREIGN KEY constraint on the database, you may fail. To avoid this exception, do not use CommandBuilder to update the columns that participate in the foreign KEY constraint, but you should explicitly specify the statement that is used to perform the operation.

The following is an example of an auto-generated command

2. Assumes that connection is a valid SqlConnection
4. Object. SqlDataAdapter adapter = New SqlDataAdapter ("select * FROM dbo. Customers", connection);
6. SqlCommandBuilder builder = new SqlCommandBuilder (adapter);
8. Builder. QuotePrefix = "["; builder. QuoteSuffix = "]";
10. DataSet custDS = new dataset (); Connection. Open ();
12. Adapter. Fill (custDS, "Customers");
14. Code to modify data in the DataSet.
16. Without the SqlCommandBuilder, this line would fail.
18. Adapter. Update (custDS, "Customers");
20. Connection. Close ();

?

Second, update the data source with DataAdapter

Need to note:

A) If SelectCommand returns the result of the OUTER JOIN, DataAdapter does not set the PrimaryKey value for the generated DataTable. You must define the PrimaryKey yourself to ensure that duplicate rows are parsed correctly.

b) If AcceptChanges is called on a DataSet, DataTable, or DataRow, all Original values of the DataRow will be rewritten as the DataRow's current value. 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 called.

Take a look at the following example:

2. Assumes connection is a valid SqlConnection.
4. ??????? SqlDataAdapter dataadpater = new SqlDataAdapter ("Select CategoryID, CategoryName from Categories", connection);
6. ??????? Dataadpater.updatecommand = new SqlCommand ("UPDATE Categories SET CategoryName = @CategoryName " + " WHERE CategoryID = @CategoryID", connection);
8. ??????? DATAADPATER.UPDATECOMMAND.PARAMETERS.ADD ("@CategoryName", SqlDbType.NVarChar,CategoryName ");
10. ??????? SqlParameter parameter = DATAADPATER.UPDATECOMMAND.PARAMETERS.ADD ("@CategoryID", SqlDbType.Int);
12. ??????? Parameter. SourceColumn = "CategoryID";
14. ??????? Parameter. SourceVersion = datarowversion.original;
16. ??????? DataSet DataSet = New dataset ();d Ataadpater.fill (DataSet, "Categories");
18. ??????? DataRow row = dataset.tables["Categories"]. Rows[0];
20. ??????? row ["CategoryName"] = "New Category"; Dataadpater.update (DataSet, "Categories");

?

Insert, update, and delete sort

In many cases, it is important to send changes made through a 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 processing 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 handle the modified row. You can control the order in which insertions, updates, and deletions are processed by specifying a subset of rows to update.

Examples are as follows:

1. datatable table = dataset.tables["customers "];
2. //first process deletes.
3. adapter. Update (table. Select (null , null , dataviewrowstate.deleted));
4. //Next process updates.
5. adapter. Update (table. Select (null , null , dataviewrowstate.modifiedcurrent));
6. //Finally, Process inserts.
7. adapter. Update (table. Select (null , null , dataviewrowstate.added));

?

Third, update with SQL statement

For example:

1. cmd = new OleDbCommand (string. Format (@ "insert INTO worker (Workerid,workername,password,phoneno) values (' {0} ', ' {1} ', ' {2} ', ' {3} ') ", TextBox1.Text, TextBox2.Text, TextBox3.Text, Textbox4.text), OC);
2. Oc. Open ();
3. Try
4. {
5. int i = cmd. ExecuteNonQuery ();
6. }
7. Catch (Exception ex)
8. {
9. }

The performance of the pros and cons, and the use of the situation is not fully understood.

In general, bind the BindingSource, bind BindingSource with a DataTable (essentially, a DataTable is bound. DefaultView, which can be used to filter the DataView, but after filtering, the filter is reset to null, otherwise the filter data will appear.

Other:

Use builder's role:

?

1. OleDbCommandBuilder cb = new oledbcommandbuilder (DA);

This is primarily to allow C # to automatically generate the corresponding deletecommand,updatecommand! for the OleDbDataAdapter da

?

?

Three ways to update a database using DataSet DataTable in C #