Read multiple data table operations in ADO. NET

In development. for database applications on the. NET platform, DataSet is generally used as ADO. NET core class, which provides us with powerful functions, and looks like a small database in memory, including DataTable, DataView, DataRow, DataColumn, Constraint, and DataRelation. I was a little excited when I saw it.
The following example illustrates how to enable transaction operations during multi-Table filling, associated table update, and execution of multiple Command objects in ADO. NET. You are welcome to contact us or leave a message on your Blog.

I. Preparations

We are familiar with the NorthWind database, so here we take it as an example. I use Customers (customer table), Orders (Order table), Order Details (Order Details table) A typed dataset named DatasetOrders is created. Each table contains only some fields. NET:

Figure 1-1

The preceding two relationships are represented as MERS-> Orders-> Order Details. Because the OrderID field of the Orders table is an auto-increment column, the AutoIncrementSeed and AutoIncrementStep values are set to-1, which may be obvious during the actual order addition process, but it's okay if you don't set it.



2. Fill in the dataset

Create a form program to demonstrate the actual operations. The interface is as follows:

Figure 2-1

The entire application is a Form. The three DataGrid above are used to display the data of the relevant table, but they are interactive. The other two single keys are used to determine the way data is updated. The two buttons are used to complete the corresponding functions just like their names.

Here we use a DataAdapter to fill the dataset. The stored procedure is as follows:

Create procedure GetCustomerOrdersInfo AS SELECT CustomerID, CompanyName, ContactName FROM MERs 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 (SELECT CustomerID FROM Customers WHERE CustomerID like a % )) GO

To reduce the data volume, only data starting with 'A' with mermerid is obtained. Create a DataAccess class to manage the interaction between a form and the 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 ;"; Private SqlConnection _ conn; /// Constructor Public DataAccess () { _ Conn = new SqlConnection (_ connstring ); }

The following functions complete a single data adapter to fill the dataset,

Public void FillCustomerOrdersInfo (DatasetOrders ds) { SqlCommand comm = new SqlCommand ("GetCustomerOrdersInfo", _ conn ); Comm. CommandType = CommandType. StoredProcedure; SqlDataAdapter dataAdapter = new SqlDataAdapter (comm ); DataAdapter. TableMappings. Add ("Table", "MERs "); DataAdapter. TableMappings. Add ("Table1", "Orders "); DataAdapter. TableMappings. Add ("Table2", "Order Details "); DataAdapter. Fill (ds ); }

If SqlHelper is used for filling, it is simpler:

Public void FillCustomerOrdersInfoWithSqlHelper (DatasetOrders ds) { SqlHelper. FillDataset (_ connstring, CommandType. StoredProcedure, "GetCustomerOrdersInfo", ds, new string [] {"MERs", "Orders", "Order Details "}); }

For more information, see SqlHelper in Data Access Application Block 2.0. when the FillDataset method is used to fill more than two tables, an error occurs. In fact, the logic in the method is wrong, but the two tables are just coincidentally. The following code is captured from the table:

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 <tableNames. Length; 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 (); // The error is returned. } } DataAdapter. Fill (dataSet ); Command. Parameters. Clear (); } If (mustCloseConnection) Connection. Close (); }

Modify tableName + = (index + 1). ToString ();

DataAdapter. TableMappings. Add (index> 0) (tableName + index. ToString (): tableName, tableNames [index]); to solve the problem.

Next let's take a look at the code of the Form program:

Public class Form1: System. Windows. Forms. Form { Private DataAccess _ dataAccess; Private DatasetOrders _ ds; //...... // Constructor Public Form1 () { InitializeComponent (); _ DataAccess = new DataAccess (); _ Ds = new DatasetOrders (); _ Ds. EnforceConstraints = false; // disable the constraints check to improve data filling efficiency. This. dataGridCustomers. DataSource = _ ds; This. dataGridCustomers. DataMember = _ ds. MERs. TableName; This. dataGridOrders. DataSource = _ ds; This. dataGridOrders. DataMember = _ ds. MERs. TableName + "." + _ ds. MERs. ChildRelations [0]. RelationName; This. dataGridOrderDetails. DataSource = _ ds; This. dataGridOrderDetails. DataMember = _ ds. MERs. TableName + "." + _ Ds. Customers. ChildRelations [0]. RelationName + "." + _ Ds. Orders. ChildRelations [0]. RelationName; }

For the dynamic Association of the above three tables, you can also use the SetDataBinding method to dynamically bind data, instead of specifying the DataSource and DataMemger attributes of the DataGride respectively.

This. dataGridCustomers. SetDataBinding (_ ds, _ ds. Customers. TableName ); This. dataGridOrders. SetDataBinding (_ ds, _ ds. MERs. TableName + "." + _ ds. MERs. ChildRelations [0]. RelationName ); This. dataGridOrderDetails. SetDataBinding (_ ds, _ ds. MERs. TableName + "." + _ Ds. MERs. ChildRelations [0]. RelationName + "." + _ ds. Orders. ChildRelations [0]. RelationName ); }

Data filling event processing is as follows:

Private void buttonFillData_Click (object sender, System. EventArgs e) { _ Ds. Clear (); // refill the dataset _ DataAccess. FillCustomerOrdersInf