Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about how to read most data tables in ADO.NET. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Based on some of my experience, I will give examples of transaction-enabled operations during the execution of multiple table fills, associated table updates, and multiple Command objects in ADO.NET.

I. preparatory work

Everyone is familiar with the NorthWind database, so take it as an example. I combine Customers (customer table), Orders (order table) and Order Details (order detail table) to create a typed dataset named DatasetOrders. Each table contains only a few fields. Here is a screenshot created in Visual Studio .NET:

Figure 1-1

The two relationships established above are represented as Customers-> Orders-> Order Details. Because the OrderID field of the Orders table is an auto-growing column, its AutoIncrementSeed and AutoIncrementStep values are set to-1, which may be obvious in the actual process of adding an order, but there is no problem if it is not set

II. Populate the dataset

Create a form program to demonstrate the actual operation, the interface is as follows:

Figure 2-1

The whole application is a Form, and the above three DataGrid are used to display the data of the related tables, but they are interactive. The other two radio boxes are used to determine how the data is updated, and the two buttons perform the corresponding function just as their names do.

Here we use a DataAdapter to populate the dataset, and the stored procedure is 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 OrderID FROM Orders WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE CustomerID LIKE'A%')) GO

In order to reduce the amount of data, only the data that CustomerID starts with'A'is taken here. Set up a DataAccess class to manage the interaction between the 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 function completes a single data adapter to populate 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", "Customers"); dataAdapter.TableMappings.Add ("Table1", "Orders"); dataAdapter.TableMappings.Add ("Table2", "Order Details"); dataAdapter.Fill (ds);}

If you use SqlHelper to populate, it's even easier:

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

Aside from the topic, an error will occur when the SqlHelper.FillDataset method in Data Access Application Block 2.0 exceeds the filling of two tables. In fact, the logic in it is wrong. It just so happens that when the two tables are in place, the following code is truncated from 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

< tableNames.Length; index++)　{if( tableNames[index] == null tableNames[index].Length == 0 ) 　throw new ArgumentException( "The tableNames parameter mustcontain a list of tables, a value was 　provided as null or empty string.", "tableNames" );　tableName += (index + 1).ToString();//这里出现错误　}}dataAdapter.Fill(dataSet);command.Parameters.Clear();　}　if( mustCloseConnection )connection.Close();} 这里把tableName += (index + 1).ToString();修改成 dataAdapter.TableMappings.Add((index>

0) (tableName+index.ToString ()): tableName, tableNames [index]); can solve the problem.

Next, take a look at the code of the form program:

Public class Form1: System..Forms.Form {private DataAccess _ dataAccess; private DatasetOrders _ ds; / /. / / Constructor public Form1 () {InitializeComponent (); _ dataAccess = new DataAccess (); _ ds = new DatasetOrders (); _ ds.EnforceConstraints = false; / / turn off constraint checking to 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] .RelationName; this.dataGridOrderDetails.DataSource = _ ds This.dataGridOrderDetails.DataMember = _ ds.Customers.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 the data instead of specifying the DataSource and DataMemger properties of the DataGride, respectively.

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] .RelationName + "." + _ ds.Orders.ChildRelations [0] .RelationName);}

The data fill event is handled as follows:

Private void buttonFillData_Click (object sender, System.EventArgs e) {_ ds.Clear (); / / repopulate the dataset _ dataAccess.FillCustomerOrdersInfo (_ ds); / / _ dataAccess.FillCustomerOrdersInfoWithSqlHelper (_ ds);}

Execute the event handler above and we will see the data displayed on the corresponding DataGrid, as shown in figure 2-1.

If you use a data reader to obtain multi-table records, the following is achieved.

After reading the above, do you have any further understanding of how to read multi-data tables in ADO.NET? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.