csharp:DataRelation 对象访问相关数据表中的记录

 1 create table Employee
 2 (
 3     EmployeeID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
 4     CompanyName nvarchar(20),
 5     PersonName nvarchar(20),
 6     Sex nvarchar(8)
 7 )
 8 GO
 9 
10 INSERT INTO Employee(CompanyName,PersonName,Sex) VALUES('六福公司','涂聚文','男')
11 INSERT INTO Employee(CompanyName,PersonName,Sex) VALUES('缔友计算机信息技术有限公司','赵金红','女')
12 INSERT INTO Employee(CompanyName,PersonName,Sex) VALUES('捷为工作室','涂斯博','男')
13 INSERT INTO Employee(CompanyName,PersonName,Sex) VALUES('','','')
14 INSERT INTO Employee(CompanyName,PersonName,Sex) VALUES('','','')
15 INSERT INTO Employee(CompanyName,PersonName,Sex) VALUES('','','')
16 INSERT INTO Employee(CompanyName,PersonName,Sex) VALUES('','','')
17 INSERT INTO Employee(CompanyName,PersonName,Sex) VALUES('','','')
18 
19 SELECT TOP 2 EmployeeID,PersonName,CompanyName FROM Employee ORDER BY EmployeeID
20 
21 
22 create table Dept
23 (
24     DeptID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
25     EmployeeID int not null,
26         DeptName nvarchar(20),
27     City  nvarchar(20),
28     Province nvarchar(20)
29 )
30 GO
31 
32 INSERT INTO Dept(EmployeeID,DeptName,City,Province) VALUES (1,'开发部','深圳市','广东省')
33 INSERT INTO Dept(EmployeeID,DeptName,City,Province) VALUES (2,'运营部','吉安市','江西省')
34 INSERT INTO Dept(EmployeeID,DeptName,City,Province) VALUES (3,'','','')
35 INSERT INTO Dept(EmployeeID,DeptName,City,Province) VALUES (4,'','','')
36 INSERT INTO Dept(EmployeeID,DeptName,City,Province) VALUES (5,'','','')
37 
38 
39 SELECT DeptID, EmployeeID,DeptName, City FROM Dept WHERE EmployeeID IN (  SELECT TOP 2 EmployeeID   FROM Employee   ORDER BY EmployeeID )　　

 

 

 

 1 using System;
 2 using System.Data;
 3 using System.Configuration;
 4 using System.Collections;
 5 using System.Web;
 6 using System.Web.Security;
 7 using System.Web.UI;
 8 using System.Web.UI.WebControls;
 9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11 using System.Data.SqlClient;
12 using System.Collections.Generic;
13 using System.Text;
14 using System.Xml;
15 
16 namespace HierarchicalMenu
17 {
18     public partial class WebFormNestedProperty : System.Web.UI.Page
19     {
20         /// <summary>
21         /// 2010-09-18 Geovin Du
22         /// </summary>
23         /// <param name="sender"></param>
24         /// <param name="e"></param>
25         protected void Page_Load(object sender, EventArgs e)
26         {
27             /* 
28              */
29 
30             SqlConnection mySqlConnection = new SqlConnection("server=(local);database=geovindu;Integrated Security=SSPI;");
31 
32             SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
33             mySqlCommand.CommandText =
34               "SELECT TOP 2 EmployeeID, PersonName, CompanyName " +
35               "FROM Employee " +
36               "ORDER BY EmployeeID;" +
37               "SELECT DeptID, EmployeeID, DeptName, City " +
38               "FROM Dept " +
39               "WHERE EmployeeID IN (" +
40               "  SELECT TOP 2 EmployeeID " +
41               "  FROM Employee " +
42               "  ORDER BY EmployeeID " +
43               ")";
44             SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
45             mySqlDataAdapter.SelectCommand = mySqlCommand;
46             DataSet myDataSet = new DataSet();
47             mySqlConnection.Open();
48             int numberOfRows = mySqlDataAdapter.Fill(myDataSet);
49             Response.Write("numberOfRows = " + numberOfRows);
50             mySqlConnection.Close();
51             DataTable customersDT = myDataSet.Tables["Table"];
52             DataTable ordersDT = myDataSet.Tables["Table1"];
53 
54             DataRelation customersDeptDataRel =
55               new DataRelation(
56                 "EmployeeDept",
57                 customersDT.Columns["EmployeeID"],
58                 ordersDT.Columns["EmployeeID"]
59               );
60             myDataSet.Relations.Add(
61               customersDeptDataRel
62             );
63 
64             myDataSet.WriteXml("nonNestedXmlFile.xml");
65 
66             myDataSet.Relations["EmployeeDept"].Nested = true;
67 
68             myDataSet.WriteXml("nestedXmlFile.xml");
69         }
70     }
71 }
72