SQL示例代码
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 )
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
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