Link two tables of dataset localized data

1. First, generate a table for link operations. If the two tables are in the same database, I want to use SQL statements directly. There is no need to obtain the local database before link operations; if the two files are not in the same database (for example, the two files in Excel are equivalent to the two databases), you have to extract the data tables you are interested in from each database first, put it in the local datase to form a local database.

2. The datatable object of C # cannot belong to multiple dataset at the same time. Therefore, some skills are required to transfer a able from one dataset to another. On the one hand, you can use the clone or copy method to generate a completely identical able object. However, copying data is a waste of time and space when the data table is large; on the other hand, we can discard a less interested dataset to support the dataset we are interested in. The specific solution is as follows:

Public datatable gettable (string ppath, string Psql, string pname)
{
Try
{
String scon = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + ppath + "; extended properties = 'excel 8.0 ;'";
Dataset sset = new dataset ();
Oledbdataadapter sadapter = new oledbdataadapter (Psql, scon );
Sadapter. Fill (sset, pname );
Datatable stable = sset. Tables [pname];
Sset. Tables. Remove (pname); // must be removed. The same table cannot exist in two sets at the same time.
Sset. Dispose ();
Return stable;

}
Catch (exception E)
{
MessageBox. Show (E. Message );
Return NULL;
}
}

3. After adding the tables you are interested in to dataset, You can construct a local database. Next, you should create a link between the two tables. First, the relationship between tables in C # is defined as a parent-child relationship or a master-slave relationship. The parent and master here correspond to tables with primary keys in SQL, the child and slave tables correspond to tables with foreign keys in SQL. Second, it is prone to the two problems mentioned in the first step. One is that the column specified as the parent key cannot have duplicate values; otherwise, an exception is thrown, this is definitely not allowed in the framework. The other is to specify a column as the parent key. If some values in the Child key column are not included due to filtering, an exception will also be thrown, this can be solved in the framework. The specific solution is as follows:

Sdataset. relations. Add (srelationname, sbaseinfotable. Columns ["resource ID"], smemberrestable. Columns ["resource ID"], false );

The first parameter is the link name for future reference. The second parameter is the parent key column and the third parameter is the Child key column, if the fourth option is set to false, the parent key column cannot contain all child key columns.

There are two specific link query methods. One is to use the parent table to traverse each row object and call getchildrenrow (s) of the row object) the function obtains the interest information in the sub-table. The other is to traverse each row object and call the getparentrow (s) of the row object) function to get the interest information in the parent table. The following table lists the access methods for the sub-table:

Foreach (datarow srow in sdataset. Tables [sname]. Rows)
{
If (srow ["resource ID"]. tostring () = "")
{
MessageBox. Show ("Total:" + Count + "resource library ");
Break;
}

Datarow [] sparent = srow. getparentrows (srelationname );
Foreach (datarow sprow in sparent)
{
++ Count;
Swriter. writeline ("<resdbinfo> ");

Swriter. Write ("<resid>"); swriter. Write (sprow ["resource ID"]); swriter. writeline ("</resid> ");
Swriter. Write ("<isconfiged>"); swriter. Write ("1"); swriter. writeline ("</isconfiged> ");
Swriter. Write ("<ishot>"); swriter. Write ("0"); swriter. writeline ("</ishot> ");

Swriter. Write ("<ressub> ");
String sSub = "";
For (INT I = 1; I <= 4; ++ I)
{
If (sprow ["category" + I]. tostring ()! = "")
{
SSub = sSub + sprow ["category" + I]. tostring () + ",";
}
}
Swriter. Write (sSub. Trim (New char [] {','});
Swriter. writeline ("</ressub> ");

Swriter. write ("<Name>"); swriter. write (srow ["Resource Name"]. tostring (). replace ("&", "& amp;"); swriter. writeline ("</Name>"); // use the resource name in the member information table as the Resource Name
// Swriter. write ("<Name>"); swriter. write (sprow ["show title"]. tostring (). replace ("&", "& amp;"); swriter. writeline ("</Name>"); Use the title field in the basic information table as the Resource Name.
Swriter. write ("

Swriter. Write ("<description> ");
Swriter. Write (sprow ["resource library Introduction"]. tostring (). Replace ("&", "& amp ;"));
Swriter. writeline ("</description> ");

Swriter. writeline ("</resdbinfo> ");
}
}