This article describes three implementation methods of using DataSet Datatable to update databases in an example form, including CommandBuilder method, DataAdapter update data source, and using SQL statement update. Share it for your reference. The specific methods are as follows:
1. Conditions for automatically generating commands CommandBuilder method
a) Dynamically specify the SelectCommand property
b) Use the CommandBuilder object to automatically generate the DeleteCommand, InsertCommand and UpdateCommand of DataAdapter.
c) To return construct INSERT, UPDATE, and DELETE. SQL CommandBuilder must execute SelectCommand.
That is, you must go through an additional trip to the data source, which may degrade performance. This is also a disadvantage of automatically generating commands.
d)SelectCommand must also return at least one primary key or unique column.
When CommandBuilder and DataAdapter are associated, empty commands in DeleteCommand, InsertCommand and UpdateCommand will be automatically generated. That is, non-empty and no generation.
e) must be one table, and SELECT cannot be a union of multiple tables.
Rules for generating commands:
Insert a row at the data source for all rows in the table with Added RowState (excluding columns such as identification, expression, or timestamp).
Updates rows for Modified rows (column values match the primary key column value of the row).
Deleted row deletes rows (column values match the primary key column value of row). This is why the requirements are required
Notice:
a) Because from SELECT data to UPDATE data, it is possible that other users have modified the data during this period. Automatically generate commands This UPDATE is only updated when the row contains all the original values and has not been deleted from the data source.
b) Automatic command generation logic generates INSERT, UPDATE, or DELETE statements for independent tables, regardless of any relationship with other tables in the data source. Therefore, it may fail when calling Update to submit changes to columns participating in foreign key constraints in the database. To avoid this exception, instead of using CommandBuilder to update columns participating in foreign key constraints, explicitly specify the statement used to perform the operation.
Below is an example of automatically generating commands
// Assumes that connection is a valid SqlConnection object. SqlDataAdapter adapter = new SqlDataAdapter( "SELECT * FROM ", connection); SqlCommandBuilder builder = new SqlCommandBuilder(adapter); = "["; = "]"; DataSet custDS = new DataSet(); (); (custDS, "Customers"); // Code to modify data in the DataSet here. // Without the SqlCommandBuilder, this line would fail. (custDS, "Customers"); ();
2. Use DataAdapter to update data sources
Need to note:
a) If SelectCommand returns the result of OUTER JOIN, DataAdapter does not set the PrimaryKey value for the generated DataTable. You must define the PrimaryKey yourself to ensure duplicate rows are parsed correctly.
b) If AcceptChanges is called on DataSet, DataTable, or DataRow, all Original values of DataRow will be rewritten to the Current values of the DataRow. If the field value that identifies the row as a unique row has been modified, the Original value no longer matches the value in the data source when AcceptChanges is called.
Take a look at the following example:
// Assumes connection is a valid SqlConnection. SqlDataAdapter dataAdpater = new SqlDataAdapter( "SELECT CategoryID, CategoryName FROM Categories", connection); = new SqlCommand( "UPDATE Categories SET CategoryName = @CategoryName " + "WHERE CategoryID = @CategoryID" , connection); ( "@CategoryName", , 15, "CategoryName"); SqlParameter parameter = ( "@CategoryID", ); = "CategoryID"; = ; DataSet dataSet = new DataSet(); (dataSet, "Categories"); DataRow row = ["Categories"].Rows[0]; row ["CategoryName"] = "New Category"; (dataSet, "Categories");
Sort of insertion, update, and delete
In many cases, it is important to send changes made through the DataSet to the data source in order.
For example, if the primary key value of an existing row has been updated and a new row with a new primary key value is added, it is important to process the update before processing the insert.
You can use the DataTable's Select method to return a DataRow array that only references a specific RowState. The returned DataRow array can then be passed to the DataAdapter's Update method to handle the modified rows. By specifying a subset of rows to be updated, you can control the order in which insertion, update, and delete are processed.
Examples are as follows:
DataTable table = ["Customers"]; // First process deletes. ((null, null, )); // Next process updates. ((null, null, )); // Finally, process inserts. ((null, null, ));
3. Update using SQL statements
For example:
cmd = new OleDbCommand((@"insert into worker(workerid,workername,password,phoneno) values ('{0}','{1}','{2}','{3}') ", , , , ),oc);
();
try
{
int i = ();
}
catch (Exception ex)
{
}
The performance and the situation of use are not fully understood.
Generally, bind bindingsource and bind bindingsource with datatable (the datatable is bound to be datatable. The filter function can also be used to the dataview filter, but after filtering, the filter needs to be reset to null, otherwise the filtered data will always appear)
other:
What is the use of builder:
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
This is mainly to allow C# to automatically generate the corresponding DeleteCommand and UpdateCommand for OleDbDataAdapter da!
I hope this article will be helpful to everyone's C# database programming.