ADO.NET multi-data table operation analysis - read

ADO.NET multi-data table operation analysis - read

Author: Zheng Zuo

2004-8-5

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 ();