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

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

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 = "]";D ataset custDS = new DataSet (); connection. Open (); adapter. Fill (custDS, "Customers");//Code to modify data in the DataSet here.//without the SqlCommandBuilder, this line would FAI L.adapter.update (custDS, "Customers"); 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:

Assumes connection is a valid Sqlconnection.sqldataadapter Dataadpater = new SqlDataAdapter ("Select CategoryID, Categor Yname from Categories ", connection);d Ataadpater.updatecommand = new SqlCommand (" UPDATE Categories SET CategoryName = @Cat Egoryname "+" WHERE CategoryID = @CategoryID ", connection);d ataAdpater.UpdateCommand.Parameters.Add (" @CategoryName ", SqlDbType.NVarChar, "CategoryName"); SqlParameter parameter = DATAADPATER.UPDATECOMMAND.PARAMETERS.ADD ("@CategoryID", SqlDbType.Int);p Arameter. SourceColumn = "CategoryID";p arameter. SourceVersion = Datarowversion.original;dataset DataSet = new DataSet ();d Ataadpater.fill (DataSet, "Categories");D Atarow row = dataset.tables["Categories"]. Rows[0];row ["CategoryName"] = "New Category";d ataadpater.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:

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

Third, update with SQL statement

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, the filter is reset to null, otherwise the filter data will appear.

Other:

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

It is hoped that this article will be helpful to everyone's C # database programming.

In addition to the Declaration, Running GuestArticles are original, reproduced please link to the form of the address of this article
Three ways to update a database using DataSet DataTable in C #

This address: http://www.paobuke.com/develop/c-develop/pbk23579.html

Related content WinForm the way to limit the number of software uses by manipulating the Registry C # List implements a common scenario for row-to-column-in-between C # picture and byte[] and byte[] and string conversion WPF Textboxoípasswordboxìí?ó?? Ó?
C # How to open and read a USB file directory C # method to determine if a local file is open in C # WPF method of calling form components using multithreading C # Distributed Transaction time-out processing instance analysis

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