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 the data set to 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 GetCustomerOrdersInfoASSELECT 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 Customerid from Customers Where Customerid Like 'a%))
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; ";"; private sqlconnection _conn; /// Constructor PUBLIC DATAACCESS () {_conn = new SqlConnection (_CONNSTRING);} The following function completes a single data adapter to complete the population of the data set,
public void FillCustomerOrdersInfo (DatasetOrders 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 "); DataApter," Table2 "," ORDER DETAILS "); DataAdapter.Fill (DS);}
If you use SQLHELPER to fill it is simpler:
public void FillCustomerOrdersInfoWithSqlHelper (DatasetOrders ds) {SqlHelper.FillDataset (_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 DataAdapter.TableMappings.add ((INDEX> 0) (TableName index.tostring ()): 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 DatasetOrders _ds; // ...... // Constructors public Form1 () {InitializeComponent (); _dataAccess = new DataAccess (); _ds = new DatasetOrders () ; _ds.EnforceConstraints = false; // Close constraint checking, charging efficiency increase data this.dataGridCustomers.DataSource = _ds; this.dataGridCustomers.DataMember = _ds.Customers.TableName; this.dataGridOrders.DataSource = _ds; this.dataGridOrders.DataMember "." = _ds.Customers.TableName _ ds.Customers.ChildRelations [0] .RelationName; this.dataGridOrderDetails.DataSource = _ds; "." this.dataGridOrderDetails.DataMember = _ds.Customers.TableName _ds.Customers.ChildRelations [ 0] .rectionName "." _Ds.orders.childrelations [0] .Relationname;} For the three dynamic associations of the above three tables, you can also use the setDatabase method to complete the dynamic binding of the data, rather than specifying DataGride DataSource and DataMemger properties. this.dataGridCustomers.SetDataBinding (_ds, _ds.Customers.TableName); this.dataGridOrders.SetDataBinding ( "." _ds, _ds.Customers.TableName _ ds.Customers.ChildRelations [0] .RelationName); this.dataGridOrderDetails.SetDataBinding ( _ds, _ds.customers.tablename "." _ds.customers.childrelations [0] .ecationname "." _ ds.orders.childrelations [0] .elyname); The data fill event is processed as follows: private void buttonFillData_Click (object sender, System.EventArgs e) {_ds.Clear (); // refill dataset _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):
