Using Ado.net to process hierarchical data

ado| Data Ado.net provides a new model for the operation and storage of data in memory. Therefore, we should change the brain when dealing with hierarchical data. This article will give a simple description of its uniqueness.

Reader requirements: Basic mastery of Visual c#.net, DataAdapter, and Datasets.

　Environment

[Configuration One]
Operating system: Windows 2000 Server
Computer: Dell Inspiron 8000 Notebooks
Content: 512MB
Processor: PIII 750 MHz
Tools:. NET SDK Beta 2
Database: The Pubs database for SQL Server 2000

[Configuration II]
Operating system: Windows XP Professional
Computer: network! 3000 Notebook
Content: 256MB
Processor: PIII 850 MHz
Tools:. NET Final
Database: The Pubs database for MSDE

Simple Data retrieval

The first thing we have to do is to submit two query statements to the database through Sqladapter.

In this example, Sqladapter uses a SQL command consisting of two SELECT statements to issue a query request to two table in the Pubs database, respectively:

string sSQL = "Select pub_id, Title, price from;" SELECT pub_id, pub_name from publishers "

In fill mode, Sqladapter inserts sp_executesql before the query command and submits it to the database in the form of RPC:

Exec sp_executesql
N ' SELECT pub_id, Title, price from Titles; SELECT pub_id, pub_name from publishers '

The database also returns two rowset via RPC. In the Dataset, the rowset is one by one corresponding to the base table. Unfortunately, these basic tables cannot be named in fill mode. Instead, it provides a common base name for all basic tables. In fact, the basic name is the first basic table name. The underlying table naming is followed by a different number to distinguish it from the base name, for example: Titles, Titles1, etc. However, with simple property settings, you can name all of the basic tables:

Datest.fill (Dstest, "Titles")
DSTEST.TABLES[1]. TableName = "publishers"

This explicit naming facilitates the processing and referencing of basic tables.

About Stored Procedures

How do you use stored procedures in ado.net? God, it's too complicated! But I would like to briefly introduce one or two points, for future discussion of the level of data as a foreshadowing it!

There are two ways to use stored procedures to get multiple rowset (rowset) at the same time.

The first method is "a stored procedure, multiple output rowsets." For example, we could add a stored procedure based on the previous example and include two SELECT statements:

CREATE PROCEDURE [dbo]. [Titlesperpublisher]
As
Begin
SELECT pub_id, Title, price from Titles
SELECT pub_id, pub_name from publishers
End

It's easy enough! This code submits two SELECT statements and thus returns two rowset.

To improve efficiency, we can use the DataAdapter SelectCommand property to set the instruction type to

CommandType.StoredProcedure:

Dahdata = New SqlDataAdapter (Ssqlcmd, cnstring)
DahData.SelectCommand.CommandType = CommandType.StoredProcedure

This can instruct DataAdapter to execute the stored procedure using the more efficient T-SQL statement Exec. If this step is omitted, DataAdapter executes it with inefficient sp_executesql.

The second method is "two stored procedures, two output rowsets." However, this method causes data to be transmitted back and forth, and regardless of data transfer or RPC establishment is time-consuming process, the efficiency of the natural greatly compromised.

From this we conclude: try to return all rowsets with a stored procedure. For this example, the simplest approach would be to bundle two stored procedures with a new process. The law may not be perfect, but don't forget it's the simplest.

For information on how to call two stored procedures simultaneously in the application and Ado.net, refer to the article about the SqlCommand object for limited space.

Relationship

In order to deal with the hierarchical data in reality, it is necessary to clarify the relationship between the basic tables. It is easy to establish a relationship by using the Dataset's relational collection. The grammar is simple and clear and should not be a problem:

public void Add (DataRelation);
Public virtual DataRelation Add (DataColumn, DataColumn);
Public virtual DataRelation Add (datacolumn[], datacolumn[]);
Public virtual DataRelation Add (String, DataColumn, DataColumn);
Public virtual DataRelation Add (String, datacolumn[], datacolumn[]);
Public virtual DataRelation Add (String, DataColumn, DataColumn, BOOL);
Public virtual DataRelation Add (String, datacolumn[], datacolumn[], BOOL);

In order to establish a relationship, you must supply a relationship name string and at least two columns. If the relationship already exists, or if there are problems with the column (for example, they do not exist), the running environment will produce an exception. For more information, see the. NET framework SDK.

The following code adds a simple relationship between the existing base tables:

DSTEST.RELATIONS.ADD ("Pubtitles",
dstest.tables["Publishers"]. columns["pub_id"],
dstest.tables["Titles"]. columns["pub_id"])

This code creates a Relation object and a relationship in the relational collection named Pubtitles: publishers.pub_id is the parent table, and titles.pub_id is the child table.

Display data

To select a child column, the DataRow object provides a GetChildRows method whose arguments are the relationship name or the relational object name:

Public datarow[] GetChildRows (DataRelation);
Public datarow[] GetChildRows (string);
Public datarow[] GetChildRows (DataRelation, DataRowVersion);
Public datarow[] GetChildRows (string, DataRowVersion);

Similar methods include GetParentRow and Getparentrows. They return the name of the parent column based on the child column.

Now with the GetChildRows method, to the data to enter it! GetChildRows returns a DataRowCollection object, whose parent class internaldatacollectionbase is a concrete implementation of ICollection and IEnumerable.

The next round of processing is just a snap. The following code demonstrates an easy way to display data relationships:

foreach (DataRow drpublisher in Dtpublishers.rows)
{
Console.WriteLine (drpublisher["pub_id"] + "T" + drpublisher["pub_name"]);
Console.WriteLine ("=====================");

foreach (DataRow drtitle in Drpublisher.getchildrows ("Pubtitles"))
{
Console.Write (drtitle["Title"] + "t");
Console.Write (drtitle["price"). ToString ()!= null? drtitle["Price"]: "N/A"));
}
}

Of course, you can also explicitly specify a Relation object:

DataRelation drpubstitles = DsHData.Relations.Add ("Pubtitles",
dtpublishers.columns["pub_id"],
dshdata.tables["Titles"]. columns["pub_id"]);
foreach (DataRow drpublisher in Dtpublishers.rows)
{
Console.WriteLine (drpublisher["pub_id"] + "T" + drpublisher["pub_name"]);
Console.WriteLine ("=====================");

foreach (DataRow drtitle in Drpublisher.getchildrows (drpubstitles))
{
Console.Write (drtitle["Title"] + "t");
Console.Write (drtitle["price"). ToString ()!= null? drtitle["Price"]: "N/A"));
}
}

Ado.net lets programmers create custom views in a datasheet. This is implemented by the DataView class:

public class Dataview:marshalbyvaluecomponent, IBindingList,
IList, ICollection, IEnumerable, ITypedList, isupportinitialize

Of course, limited to space, only part of the function is enumerated here.

The Data view provides two interesting properties: RowFilter and Sort. RowFilter is similar to the Filter property of an ADO Recordset object, which is equivalent to a WHERE statement in SQL syntax that screens the matching columns:

Dtpublishers.defaultview.rowfilter= "pub_id < 2000";

The resulting columns are placed in the DataRowView collection, so they can be recycled with the for each statement.

The Sort property is used to specify how the output data is sorted. It is similar to the order by command in SQL syntax:

dtpublishers.defaultview.sort= "pub_id Desc";

Each basic table corresponds to a DataView object, and the above DefaultView is its property. So, with just a little modification, we have the option to loop through the data.

foreach (DataRow drpublisher in Dtpublishers.rows)
{
Console.WriteLine (drpublisher["pub_id"] + "T" + drpublisher["pub_name"]);
Console.WriteLine ("=====================");

foreach (DataRow drtitle in Drpublisher.getchildrows ("Pubtitles"))
{
Console.Write (drtitle["Title"] + "t");
Console.Write (drtitle["price"). ToString ()!= null? drtitle["Price"]: "N/A"));
}
}

Conclusion

Ado.net greatly simplifies the processing of hierarchical data and provides improved solutions.

Read this article, do you want to be tempted? To pursue a stronger function, I'm afraid I have to talant.

This article does not consider performance optimization because the SDK we are discussing is still Beta 2.