ADO.NET multi-data table operation analysis - read
When developing a .NET platform-based database application, we generally use Dataset, as an ado.net, which provides us with powerful features, and the whole look like a small database in memory. Interior includes DataTable, DataView, DataRow, Datacolumn, Constraint, and DataRelation. It was really excited when I saw it.
Because of my experiences, the multi-table fill in ADO.NET, the association table update, and the operation of the transaction are enabled during the execution of multiple Command objects. Welcome everyone to communicate or leave a message on Blog.
First, ready to work
For the Northwind database, everyone is more familiar, so take it as an example, I put the Customers, Orders, Order Details, set up a type of dataset, the type name is DataSetRDERS, each table only includes some fields, one is a screenshot created in Visual Studio .NET:
Picture 1-1
The above established two relationships are represented as Customers -> Orders -> Order Details. Because the ORDERID field of the ORDERS table is an automatic growth column, it is set to -1 here, which may be more obvious during the actual addition order, but no problem is no problem.
two. Fill data set
Create a form program to demonstrate the actual operation, the interface is as follows:
picture 2-1
The entire application is an Form, the three DataGrids above are used to display data for related tables, but they are interactive. The other two radios are used to determine how the data is updated, and the two buttons are as their name to complete the corresponding function.
Here we use a DataAdapter to complete the population of the data set, the stored procedures executed are as follows:
Create Procedure GetCustomerOrordersInfo
AS
Select Customerid, CompanyName, ContactName from Customers Where Customerid Like 'a%'
Select ORDERID, OrderDate, Customerid from Orders Where Customerid in
(Select Customerid from Customers Where Customerid Like 'a%)
Select ORDERID, PRODUCTID, Unitprice, Quantity, Discount from [Order Details] Where OrderID in
(Select Orderid from Orders Where Customerid in) WHERE CUSTOMERID IN
(Select Customerid from Customers Where Customerid Like 'a%)))
Go
In order to reduce the amount of data, only the CustomerID starts with 'A' in 'a'.
Establish the DataAccess class to manage the interaction of the form with data layer:
Using system;
Using system.data;
Using system.data.sqlclient;
Using Microsoft.ApplicationBlocks.data;
Namespace WinFormTest
{
Public Class DataAccess
{
Private string _connstring = "data source = (local); initial catalog = northwind; uid = csharp; pwd = c # .NET2004;";
///Constructor
Public DataAccess ()
{
_CONN = New SqlConnection (_CONNSTRING);
}
The following functions complete a single data adapter to complete the population of the data set.
Public Void FillCustomerRordersInfo (DataSetRDERS DS)
{
Sqlcommand Comm = New Sqlcommand ("GetCustomerORDERSINFO", _ conn);
Comm.commandtype = commandtype.storedProcedure;
SqlDataAdapter DataAdapter = New SqlDataAdapter (Comm);
DataAdapter.tableMappings.add ("Table", "Customers");
DataAdapter.tableMappings.add ("Table1", "ORDERS");
DataAptapter.tableMappings.add ("Table2", "Order Details");
DataAdapter.Fill (DS);
}
If you use SQLHELPER to fill it is simpler,
Public Void FillCustomerRordersInfowithsqlhelper (DatasetRDERS DS)
{SQLHELPER.FILDATASET (_Connstring, CommandType.storedProcedure, "GetCustomerordersInfo", DS, New String [] {"Customers", "Orders", "Order Details";
}
Fork open topics, the SQLHELPER.FILLDATATI in Data Access Application Block 2.0 will have an error when the fill of the two tables, in fact, the logic is wrong, but only two tables, just make it, below The code inside:
Private static void FillDataSet (SqlConnection Connection, Sqltransaction Transaction, CommandType CommandType,
String Commandtext, Dataset Dataset, String [] Tablenames,
Params Sqlparameter [] CommandParameters)
{
IF (Connection == NULL) Throw new Argumentnullexception ("Connection");
IF (Dataset == Null) Throw new ArgumentnullException ("Dataset");
SQLCommand command = new sqlcommand ();
Bool MustCloseConnection = false;
PrepareCommand (Command, Connection, Transaction, CommandType, CommandText, CommandParameters, Out MustCloseConnection); Using (SqlDataAdapter DataAdapter = New SqlDataAdapter (Command))
{
IF (TableNames! = Null && Tablenames.length> 0)
{
String TableName = "Table";
For (int index = 0; index { IF (TableNames [index] == null || Tablenames [index] .length == 0) Throw New ArgumentException ("The Tablenames Parameter Must Contain A List of Tables, A Value Was Provided As Null or Empty String.", "TableNames"); Tablename = (INDEX 1) .tostring (); // This error } } DataAdapter.Fill (DataSet); Command.parameters.clear (); } IF (MustCloseConnection) Connection.Close (); } Takename = (INDEX 1) here (); DataAdapter.tableMappings.add ((INDEX> 0)? (TableName Index.toTRING ()): TableName, TableNames [INDEX]); you can solve the problem. Next, look at the code of the form program: Public Class Form1: System.Windows.Forms.form { PRIVATE DATAACCESS _DATAACCESS; Private DatasetRDERS_DS; // ...... //Constructor Public Form1 () { InitializationComponent (); _DataAccess = new dataAccess (); _ds = new datasetorders (); _ds.enforceconstraints = false; // Close constraint check, improve data filling efficiency THIS.DATAGRIDCUSTOMERS.DATASOURCE = _ds; This.dataGridcustomers.DataMember = _ds.customers.tablename THIS.DATAGRIDORDERS.DATASOURCE = _ds; This.dataGridorders.DataMember = _ds.customers.tablename "." _ ds.customers.childrelations [0] .e ;; This.DataGridorderDetails.DataSource = _ds; THIS.DATAGRIDORDETAILS.DATAMEMBER = _ds.customers.tablename "." _ ds.customers.childrelations [0] .eLationName "." _ ds.orders.childrelations [0] .ecationName;} For the dynamic association of the three tables above, you can also use the setDatabase method to complete the dynamic binding of the data, not the DataGride's DataSource and DataMEMger properties. This.dataGridcustomers.SetDataBinding (_ds, _ds.customers.tablename); This.DataGridorder.SetDataBinding (_ds, _ds.customers.tablename "." _ ds.customers.childrelations [0] .elyname; This.DataGridorderDetails.SetDataBinding (_ds, _ds.customers.tablename "." _ ds.customers.childrelations [0] .elyname "." _ ds.orders.childrelations [0] .ReLATIONName); } The data fill event is processed as follows: Private void ButtonFillData_Click (Object Sender, System.EventArgs E) { _ds.clear (); // Re-filled the data set _DataAccess.FillCustomerORDERSINFO (_DS); //dataaccess.fillcustomerordersinfowithsqlhelper (_ds); } Executing the above event handler We will see that the data is displayed on the corresponding DataGrid, as shown in (Fig. 2-1). If you use the Data Reader to get a multi-table record below is a way to implement (reference): Sqlcommand Comm = New Sqlcommand ("GetCustomerORDERSINFO", _ conn); Comm.commandtype = commandtype.storedProcedure; _Conn.open (); SqldataReader Reader = Comm.ExecuteReader (); DO { While (Reader.Read ()) { Console.writeline (Reader [0] .tostring ()); // Get data code } } while (reader.nextResult ()); Console.readline (); _Conn.close (); Multi-data table operations in ADO.NET - Modify 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 selectCustomerCommmmand 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 processing of orders, the following 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 UpdateCustomerRorders (DatasetOrDers 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 (", ",", "", ",", ",", ",", "ot // Delete the order table data first, then delete the client table data _OrderDataAdapter.Update (DS.Tables ["Orders"]. Select (",", "," ",", ",", "", "", "," oteta); _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 (",", "," ",", ",", "", "", "," oteta); _CUSTOMERDATADAPTER.UPDATE (DS.Tables "]. SELECT (", "," ",", ",", ",", "); 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) / / Enable transaction update data table _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"
