Tutorials to .com

Tutorials to .com » Dotnet » Database » Use DataAdapter and DataSet to update database [C #]

Use DataAdapter and DataSet to update database [C #]

Print View , by: iSee ,Total views: 28 ,Word Count: 1623 ,Date: Sun, 14 Jun 2009 Time: 2:46 AM

Adjustable DataAdapter's Update method of the DataSet is used to change the resolution back to the data source. Fill method with a similar, Update method of the DataSet instance and an optional DataTable object or DataTable name used as a parameter. Example is the DataSet that contains the changes have been made of the DataSet, and DataTable logo change from one search form.

When you call Update method, DataAdapter will analyze the changes have been made and the implementation of the appropriate command (INSERT, UPDATE or DELETE). When the DataAdapter changes encountered on the DataRow, it will use the InsertCommand, UpdateCommand, or DeleteCommand to process the change. In this way, you can specify in the design of the command syntax and, where possible, through the use of stored procedures to maximize the performance of ADO.NET applications. Update in the call, you must explicitly set these commands. If you call the Update does not exist but the corresponding updates for a specific command (for example, does not exist for the trip has been deleted DeleteCommand), will lead to anomalies.

Command parameters can be used for the DataSet has been revised for each line of SQL statement or stored procedure specified input and output values. For more information, please refer to the parameters for the DataAdapter.

If the DataTable is mapped to a single database table or generated from a single database table, you can use automatically generated DataAdapter object CommandBuilder the DeleteCommand, InsertCommand and UpdateCommand. For more information, please refer to the order automatically.

Update method will change the resolution back to the data source, but has been since the last fill DataSet, other clients may have modified the data in the data source. To use the current data set DataSet, use the DataAdapter to fill again (Fill) DataSet. New will be added to the table, updated information will be incorporated into the existing line.

To deal with possible operation in the Update anomalies occur, you can use RowUpdated events occur in these abnormal response to update the wrong line (please refer to the use of DataAdapter events), or can call Update before DataAdapter.ContinueUpdateOnError set to true, then Update is completed in response to a specific line stored in the attributes of RowError error message (see line to add and read error message).

Note If the DataSet, DataTable or DataRow call AcceptChanges, then will all of a DataRow by the Original Value of the Current value to rewrite DataRow. If you have modified the line identified as the only line of field values, then after AcceptChanges When you call, Original value will no longer match the data source value.
The following examples demonstrate how the adoption of explicit DataAdapter's UpdateCommand settings have been modified to perform the update line. Please note that in the UPDATE statement's WHERE clause to specify the parameters set to use the Original value SourceColumn. This is important, because the Current value may have been modified, and may not match data source values. Original value is used from the data source has filled the value of DataTable.

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);
If auto-increment column from the data source contains the auto-increment column table, you can use the data sources generated by the value of the DataSet to fill out through the output parameters to stored procedure to return to the form and to automatically increase the value mapped to the table in one, or use the DataAdapter events RowUpdated. Of the sample, see the Search "logo" or "Auto ID" value.

However, DataSet value in the data source may be of value is not synchronized and lead to unexpected behavior. For example, consider an auto-increment primary key contains the CustomerID column of the table. If in the DataSet to add two new customers, they will receive automatic incremental CustomerId value 1 and 2. To the DataAdapter's Update method of delivery when the second customer, the new line added to the data source will receive an automatic increase in the CustomerID value 1, the value of the DataSet does not match the value of 2. When the DataAdapter to use the return value of the DataSet when filled, as the first line of a customer CustomerID is 1, it will be bound by the conflict.

To avoid this behavior, it is proposed that the use of data source and the DataSet automatically increments out of the auto-increment column, the DataSet created in AutoIncrementStep for -1 and 0 out AutoIncrementSeed and to ensure that the data source to generate the start and from 1 to way of the long value of incremental value automatically increments logo. This, DataSet will automatically generate a negative incremental value, the negative will not be generated by the data source is automatically increased the value of conflict. Another method is to use the Guid column type instead of auto-increment column. Guid value generated in the algorithm DataSet generated Guid from a data source will not be generated with the same Guid. The definition set out in the DataTable For more information, please refer to the definition of the structure of data tables.

Insert, update, and delete the order in many cases, the order in which the data source to send through the DataSet changes is very important. For example, if you have to update the existing line and add the primary key values with a new key New Main Line, it must be dealt with before in dealing with updates inserted.

Can use the DataTable's Select method to return only with a specific reference RowState array of DataRow. And then return can be delivered to the DataRow array DataAdapter's Update method to handle the line has been modified. To be updated through the designation of a subset of rows, you can control processing to insert, update, and delete the order.

For example, the following code to ensure that the first table row has been deleted, and then the line has been updated to deal with, and then insert the line to deal with.

[Visual Basic]
Dim updTable As DataTable = custDS.Tables ( "Customers")

'First process deletes.
custDA.Update (updTable.Select (Nothing, Nothing, DataViewRowState.Deleted))

'Next process updates.
custDA.Update (updTable.Select (Nothing, Nothing, DataViewRowState.ModifiedCurrent))

'Finally, process inserts.
custDA.Update (updTable.Select (Nothing, Nothing, DataViewRowState.Added))
[C #]
DataTable updTable = custDS.Tables [ "Customers"];

/ / First process deletes.
custDA.Update (updTable.Select (null, null, DataViewRowState.Deleted));

/ / Next process updates.
custDA.Update (updTable.Select (null, null, DataViewRowState.ModifiedCurrent));

/ / Finally, process inserts.
custDA.Update (updTable.Select (null, null, DataViewRowState.Added));


.Net Database Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.