Three ways to update a database using the dataset Datatable

1: 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
The disadvantages of automatically generating commands.
D) SelectCommand must also return at least one primary key or a unique column.
When CommandBuilder and DataAdapter are associated, they are automatically generated DeleteCommand,
Empty commands in InsertCommand and UpdateCommand. That is, the non-empty is not generated.
e) must be a table, select cannot be a union of multiple tables.

#动生成命令的规则 #
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 an INSERT, UPDATE, or DELETE statement for a stand-alone table,
Regardless of any relationship with other tables in the data source. Therefore, when you call the Update
To commit changes to a column that participates in a FOREIGN KEY constraint on the database, it may fail. To avoid this exception,
Instead of using CommandBuilder to update the columns participating in the FOREIGN KEY constraint, you should explicitly specify the statement that is used to perform the operation.
The following is an example of an auto-generated command
Assumes that connection is a valid SqlConnection object.
SqlDataAdapter adapter = new SqlDataAdapter (
"SELECT * FROM dbo. Customers ", connection);
SqlCommandBuilder builder = new SqlCommandBuilder (adapter);
Builder. QuotePrefix = "[";
Builder. QuoteSuffix = "]";

DataSet custDS = new DataSet ();

Connection. Open ();
Adapter. Fill (custDS, "Customers");

Code to modify data in the DataSet.

Without the SqlCommandBuilder, this line would fail.
Adapter. Update (custDS, "Customers");
Connection. Close ();

2: Update data source with DataAdapter
Attention:
A) If SelectCommand returns the result of the OUTER JOIN, the DataAdapter
The PrimaryKey value is not set for the generated DataTable. You must define your own
PrimaryKey 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 current value of the DataRow.
If a field value that identifies the row as a unique row has been modified, then when AcceptChanges is called, the
The Original value will no longer match the value in the data source.
The following example
Assumes connection is a valid SqlConnection.
SqlDataAdapter dataadpater = new SqlDataAdapter (
"Select CategoryID, CategoryName from Categories", connection);

Dataadpater.updatecommand = new SqlCommand (
"UPDATE Categories SET CategoryName = @CategoryName" +
"WHERE CategoryID = @CategoryID", connection);

DATAADPATER.UPDATECOMMAND.PARAMETERS.ADD (
"@CategoryName", SqlDbType.NVarChar, "CategoryName");

SqlParameter parameter = DATAADPATER.UPDATECOMMAND.PARAMETERS.ADD (
"@CategoryID", SqlDbType.Int);
Parameter. SourceColumn = "CategoryID";
Parameter. SourceVersion = datarowversion.original;

DataSet DataSet = new DataSet ();
Dataadpater.fill (DataSet, "Categories");

DataRow row = dataset.tables["Categories"]. Rows[0];
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,
It is important 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.
DataTable table = dataset.tables["Customers"];

First process deletes.
Adapter. Update (table. Select (null, NULL, dataviewrowstate.deleted));

Next process updates.
Adapter. Update (table. Select (null, NULL,
DataViewRowState.ModifiedCurrent));

Finally, Process inserts.
Adapter. Update (table. Select (null, NULL, dataviewrowstate.added));

3. Updating with SQL statements

For example:

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);
Oc. Open ();
Try
{
int i = cmd. ExecuteNonQuery ();
}
catch (Exception ex)
{

}

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, filter is reset to NULL, otherwise it will always be filtered data. See BindingSource article

Other:

1. Use Builder's role:

OleDbCommandBuilder cb = new OleDbCommandBuilder (DA);
This is primarily to allow C # to automatically generate the corresponding deletecommand,updatecommand! for the OleDbDataAdapter da