Multi-data table operations in ADO.NET - Modify
Author: Zheng Zuo
2004-8-5
Third, update data set
The first thing to explain is that I have dropped the ORDER DETAILS table here, and the operations of the two tables are just a few fields. Below is the form interface:
Figure 3-1
The radio box is used to select a different update method.
Add two class member variables in the DataAccess class:
Private sqldataadapter_customerdataadapter; // Customer Data Adapter
Private sqldataadapter_orderdataadapter; // Order Data Adapter
CustomerDataAdapter initialization in the constructor
// Installation _CustomerDataAdapter
SQLCommand SelectCustomerCommmmnd SelectCustomerComm = New SQLCommand ("getcustomer", _ conn
SelectCustomerComm.commandType = CommandType.StoredProcedure;
SelectCustomerComm.Parameters.Add ("@ Customerid", SqldbType.Nchar, 5, "Customerid");
SQLCommand InsertCustomerCommand INSERTCUSTOMERCOMM = New SQLCOMMAND ("AddCustomer", _ conn
INSERTCUSTOMERCMM.COMMAVANDTYPE = CommandType.StoredProcedure;
INSERTCUSTOMERCOMM.Parameters.Add ("@ Customerid", SqldbType.Nchar, 5, "Customerid");
INSERTCUSTOMERCMM.Parameters.Add ("@ companyName", SqldbType.nvarchar, 40, "companyname");
INSERTCUSTOMERCMM.Parameters.add ("@ ContactName", SqldbType.nvarchar, 30, "ContactName");
Sqlcommand updatecustomerCommnd UpdateCustomerCommmmmmmm = New Sqlcommand ("UpdateCustomer", _ conn
UpdateCustomerComm.commandtype = commandtype.storedprocedure;
UpdateCustomerComm.Parameters.Add ("@ Customerid", SqldbType.Nchar, 5, "Customerid");
UpdateCustomerComm.Parameters.Add ("@ companyName", SqldbType.nvarchar, 40, "companyname");
UpdateCustomerComm.Parameters.Add ("@ contactname", sqldbtype.nvarchar, 30, "contactname");
SQLCommand deletecustomercomm = new sqlcommand ("deletecustomer", _ conn;
DeletecustomerComm.commandtype = commandtype.storedProcedure;
DeletecustomerComm.Parameters.add ("@ Customerid", SqldbType.Nchar, 5, "Customerid"); _ CustomerDataAdapter = New SqlDataAdapter (SELECTCUSTOMMM);
_CUSTOMERDATADAPTER.INSERTCOMMAND = INSERTCUSTOMERCMM;
_CUSTOMERDATADAPTER.UPDATECOMMAND = UpdateCustomerComm;
_CUSTOMERDATADAPTER.DELETECMMMAND = DeleteCustomerComm;
The above code can be generated using the designer, thinking that some things you have to write better, but the code is still a lot.
For the initialization of _ORDERDATAADAPTER, we only look at the process of adding orders, below is the stored procedure:
Create Procedure Adorder
(
@ORDERID INT OUT,
@Customerid nchar (5),
@Orderdate datetime
)
AS
INSERT INTO Orders
(
Customerid,
ORDERDATE
)
Values
(
@Customerid,
@Orderdate
)
--Select @orderid = @@ identity // Using triggers may have problems
Set @orderid = scope_identity ()
Go
ORDERID automatic growth value is done by output parameters, this is quite good if using the SqlDataAdapter.RowUpdated event to handle the efficiency is low.
The definition of the INSERTORDERCOMM object is:
Sqlcommand insertorderComm = New SQLCommand ("Addorder", _ conn;
INSERTORDERCOMM.COMMANDTYPE = CommandType.StoredProcedure;
Insertordercomm.Parameters.Add ("@ Orderid", SqldbType.int, 4, "ORDERID");
INSERTORDERCOMM.Parameters ["@ OrderID"]. Direction = parameterDirection.output;
INSERTORDERCOMM.Parameters.Add ("@ OrderDate", SqldbType.Datetime, 8, "ORDERDATE");
Insertordercomm.Parameters.Add ("@ Customerid", SqldbType.Nchar, 5, "Customerid");
Let's clarify some update logices before implementing data update methods:
For rows marked as deleted, delete the data of the order table first, then delete the data of the customer table;
For rows marked as added, add the data of the customer table, add the data of the order table.
(1) Implement the method of updating data with a copy subset of Dataset that has been modified.
This is also a common way to call the XML Web Service update data. First, let's see the first version, the acquisition of the subset is completed by the DataSet.getChangs method.
// Use data set to update data
Public Void UpdateCustomerorders (DatasetRDERS DS) {
DataSet DSModified = ds.getchange.modified; // Get a modified row
DataSet dsdeleted = ds.getchange.deleted; // Get tagged as deleted row
Dataset dsadded = ds.getChanges (DATAROWSTATE.Added); // Get an increased row
Try
{
_Conn.open (); // Add a customer table data, add an order table data
IF (dsadded! = NULL)
{
_CUSTOMERDATAADAPTER.UPDATE (DSADDED, "Customers");
_orderdataadapter.Update (Dsadded, "Orders");
DS.MERGE (DSADDED);
}
IF (dsmodified! = null) // update data table
{
_CUSTOMERDATAADAPTER.UPDATE (DSModified, "Customers");
_OrderDataAdapter.Update (DSModified, "Orders");
DS.MERGE (DSModified);
}
IF (dsdeleted! = null) // first delete the order table data, then delete the client table data
{
_OrderDataAdapter.Update (DSDeleded, "Orders");
_CUSTOMERDATAADAPTER.UPDATE (DSDELETED, "Customers");
DS.MERGE (DSDELETED);
}
}
Catch (Exception EX)
{
Throw new Exception ("Update Data Error", EX);
}
Finally
{
IF (_Conn.State! = connectionState.closed)
_Conn.close ();
}
}
The above method looks clear, but the efficiency is not very high, and three DataSets have been created at least in the middle, and then multi-merger.
(2) Another method is to reference updates and do not create a copy.
Relatively speaking, performance is much higher, but if the amount of data transmitted on the web service is larger (can be improved in conjunction with two methods). The specific implementation is to be implemented by the DataTable.select method to select a row state.
/ / Reference mode Update data
Public Void UpdateCustomerRorders (DataSet DS)
{
Try
{
_Conn.open ();
/ Adding a client table data first, then add an order table data _CustomerDataAdapter.Update (DS.Tables ["Customers"]. SELECT (",", ", dataviewrowstate.added);
_OrderDataAdapter.Update (DS.Tables ["Orders"]. Select (",", "" "," "", "" "," ",", ","
// Update data sheet
_CUSTOMERDATADAPTER.UPDATE (DS.Tables "]. SELECT (", ",", "" ",", ",", ",", ",", ");
_ORDERDATAADAPTER.UPDATE (DS.Tables "]. Select (", "", "" "" "", "" "", "" "" "," "" "" "" "," ",", ",", ",", ",", "," // delete the order table data, then delete the client table data
_OrderDataAdapter.Update (DS.Tables ["Orders"]. Select (",", "," ",", ",", ","
_CUSTOMERDATADAPTER.UPDATE (DS.Tables "]. SELECT (", "," ",", ",", ",", ");
}
Catch (Exception EX)
{
Throw new Exception ("Update Data Error", EX);
}
Finally
{
IF (_Conn.State! = connectionState.closed)
_Conn.close ();
}
}
In conjunction with the two methods we think that calling the Web Service has a more reasonable way to complete.
(3) Using transactions
Public Void UpdateCustomerRorderswithTransaction (DataSet DS)
{
Sqltransaction trans = null;
Try
{
_Conn.open ();
Trans = _Conn.begintransaction ();
_CUSTOMERDATADAPTER.DELETECOMMAND.TRANSACTION = Trans;
_CUSTOMERDATADAPTER.INSERTCOMMAND.TRANSACTION = Trans;
_CUSTOMERDATADAPTER.UPDATECOMMAND.TRANSACTION = Trans;
_OrderDataAdapter.deleteCommand.Transaction = TRANS;
_ORDERDATAADAPTER.INSERTCOMMAND.TRANSACTION = Trans;
_OrderDataAdapter.UpdateCommand.Transaction = Trans;
_CUSTOMERDATAADAPTER.UPDATE (DS.Tables ["Customers"]. Select (",", ", dataviewrowstate.added);
_OrderDataAdapter.Update (DS.Tables ["Orders"]. Select (",", "" "," "", "" "," ",", ","
_CUSTOMERDATADAPTER.UPDATE (DS.Tables "]. SELECT (", ",", "" ",", ",", ",", ",", ");
_OrderDataAdapter.Update (DS.Tables "]. Select (", ",", "", ",", ",", ",", "ot
_OrderDataAdapter.Update (DS.Tables ["Orders"]. Select (",", "," ",", ",", ","
_CUSTOMERDATAADAPTER.UPDATE (DS.Tables "]. SELECT (", ",", dataviewrowstate.delete); trans.commit ();
}
Catch (Exception EX)
{
TRANS. ROLLBACK ();
Throw new Exception ("Update Data Error", EX);
}
Finally
{
IF (_Conn.State! = connectionState.closed)
_Conn.close ();
}
}
Finally let us take a look at the form of the button update the code:
Private Void ButtonUpdate_Click (Object Sender, System.Eventargs E)
{
/ / Submit editing data
This.BindingContext [this._ds] .endcurrented ();
IF (radiobuttonref.checked == true) // Reference mode update
_DataAccess.UpdateCustomerORDERS ((Dataset )_DS);
Else if (Radiobuttontrans.checked == true) // Enables transaction update data sheet
_DataAccess.UpdateCustMerRorderswithTransaction ((Dataset )_ds);
Else
{
DataSetRDERS ChangeDData = (DatasetRDERS )_DS.GetChanges ();
IF (RadiobuttonWeb.checked == true) // Web service correction update
{
_DataAccess.UpdateCustomerorders (DataSet) ChangeDData);
}
ELSE // Create a copy merge method update
{
_DataAccess.UpdateCustMerRorders (ChangeDData);
}
/ / Remove the virtual line added to the order table
Foreach (DataRow Row In_Ds.Orders.select (",", DataViewRowState.Added))
_ds.ORDERS.RemoveRDERDERSROW ((DatasetRDERS.OrDersRow);
// Remove the virtual line added to the customer table
Foreach (DataRow Row in _ds.customers.select (",", "" "" "" "" "" "" "," "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "
_ds.customers.RemoveCustomersrow (DatasetRDERS.CUSTOMERSROW);
_ds.merge (changeddata);
}
/ / Submit a data set status
_ds.acceptchanges ();
}
Reference: "ADO.NET CORE Reference"
