[Transfer]二种嵌套的DataGrid数据表格方法[DataRelation,FindControl]
http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/cpguide/html/cpconnesteddatarelations.asp
MSDN Home > .NET Framework > 使用 .NET Framework 编程 > 通过 ADO.NET 访问数据 > XML 和 DataSet
.NET Framework 开发员指南
嵌套的 DataRelation
在数据的关系表示形式中,各个表都包含使用一个列或一组列来相互关联的行。在 ADO.NET DataSet 中,表之间的关系使用 DataRelation 来实现。当创建 DataRelation 时,列的父子关系仅通过关系来管理。表和列是独立的实体。在 XML 提供的数据的分层表示形式中,父子关系通过包含嵌套子元素的父元素来表示。为了方便子对象在 DataSet 与 XmlDataDocument 同步或使用 WriteXml 以 XML 数据形式来编写时进行嵌套,DataRelation 会公开 Nested 属性。如果将 DataRelation 的 Nested 属性设置为 true,将使关系的子行在以 XML 数据形式编写或与 XmlDataDocument 同步时嵌套在父列中。默认情况下,DataRelation 的 Nested 属性为 false。例如,考虑以下 DataSet:
2
3[Visual Basic]
4Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;" & _
5 "Integrated Security=SSPI;Initial Catalog=Northwind;")
6Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
7Dim orderDA As SqlDataAdapter = New SqlDataAdapter("SELECT OrderID, CustomerID, OrderDate FROM Orders", nwindConn)
8
9nwindConn.Open()
10
11Dim custDS As DataSet = New DataSet("CustomerOrders")
12custDA.Fill(custDS, "Customers")
13orderDA.Fill(custDS, "Orders")
14
15nwindConn.Close()
16
17Dim custOrderRel As DataRelation = custDS.Relations.Add("CustOrders",
18 custDS.Tables("Customers").Columns("CustomerID"),
19 custDS.Tables("Orders").Columns("CustomerID"))
20
21[C#]
22SqlConnection nwindConn = new SqlConnection("Data Source=localhost;" +
23 "Integrated Security=SSPI;Initial Catalog=Northwind;");
24SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn);
25SqlDataAdapter orderDA = new SqlDataAdapter("SELECT OrderID, CustomerID, OrderDate FROM Orders", nwindConn);
26
27nwindConn.Open();
28
29DataSet custDS = new DataSet("CustomerOrders");
30custDA.Fill(custDS, "Customers");
31orderDA.Fill(custDS, "Orders");
32
33nwindConn.Close();
34
35DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
36 custDS.Tables["Customers"].Columns["CustomerID"],
37 custDS.Tables["Orders"].Columns["CustomerID"]);
38
39
因为对于该 DataSet,DataRelation 的 Nested 属性未设置为 true,所以当该 DataSet 表示为 XML 数据时,子对象将不会嵌套在父元素中。
以下代码显示对 DataSet 调用 WriteXml 将生成的输出。
<CustomerOrders>
<Customers>
<CustomerID>ALFKI</CustomerID>
<CompanyName>Alfreds Futterkiste</CompanyName>
</Customers>
<Customers>
<CustomerID>ANATR</CustomerID>
<CompanyName>Ana Trujillo Emparedados y helados</CompanyName>
</Customers>
<Orders>
<OrderID>10643</OrderID>
<CustomerID>ALFKI</CustomerID>
<OrderDate>1997-08-25T00:00:00</OrderDate>
</Orders>
<Orders>
<OrderID>10692</OrderID>
<CustomerID>ALFKI</CustomerID>
<OrderDate>1997-10-03T00:00:00</OrderDate>
</Orders>
<Orders>
<OrderID>10308</OrderID>
<CustomerID>ANATR</CustomerID>
<OrderDate>1996-09-18T00:00:00</OrderDate>
</Orders>
</CustomerOrders>
请注意,Customers 元素和 Orders 元素显示为同辈元素。如果您要让 Orders 元素显示为它们各自父元素的子元素,则需要将 DataRelation 的 Nested 属性设置为 true,为此将添加以下代码:
[Visual Basic]
custOrderRel.Nested = True
[C#]
custOrderRel.Nested = true;
以下代码显示当 Orders 元素嵌套在它们各自的父元素中时所生成的输出的可能形式。
2
3<CustomerOrders>
4 <Customers>
5 <CustomerID>ALFKI</CustomerID>
6 <Orders>
7 <OrderID>10643</OrderID>
8 <CustomerID>ALFKI</CustomerID>
9 <OrderDate>1997-08-25T00:00:00</OrderDate>
10 </Orders>
11 <Orders>
12 <OrderID>10692</OrderID>
13 <CustomerID>ALFKI</CustomerID>
14 <OrderDate>1997-10-03T00:00:00</OrderDate>
15 </Orders>
16 <CompanyName>Alfreds Futterkiste</CompanyName>
17 </Customers>
18 <Customers>
19 <CustomerID>ANATR</CustomerID>
20 <Orders>
21 <OrderID>10308</OrderID>
22 <CustomerID>ANATR</CustomerID>
23 <OrderDate>1996-09-18T00:00:00</OrderDate>
24 </Orders>
25 <CompanyName>Ana Trujillo Emparedados y helados</CompanyName>
26 </Customers>
27</CustomerOrders>
28
29
SQL 运用
2
3<%@ Page language="c#"%>
4<%@ Import Namespace="System.Data" %>
5<%@ Import Namespace="System.Data.SqlClient" %>
6<HTML>
7<body>
8<script language="c#" runat="server">
9public void Page_Load(object sender, EventArgs e)
10 {
11 // 为Authors表创建 Connection 和 DataAdapter
12
13
14
15
16 SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["Pubs"].ConnectionString);
17 SqlDataAdapter cmd1 = new SqlDataAdapter("select * from authors", cnn);
18
19 //创建填充 DataSet.
20 DataSet ds = new DataSet();
21 cmd1.Fill(ds, "authors");
22
23 // 为Titles表创建 DataAdapter
24 SqlDataAdapter cmd2 = new SqlDataAdapter("select * from titleauthor", cnn);
25 cmd2.Fill(ds, "titles");
26
27 // 创建 Authors 表和 Titles 表之间的关系.
28 ds.Relations.Add("myrelation",
29 ds.Tables["authors"].Columns["au_id"],
30 ds.Tables["titles"].Columns["au_id"]);
31
32 // 绑定Authors到父Repeater
33 parentRepeater.DataSource = ds.Tables["authors"];
34 Page.DataBind();
35
36 cnn.Close();
37 cnn.Dispose();
38 }
39</script>
40<form id="Form1" runat="server">
41<!-- 父Repeater开始 -->
42<asp:repeater id="parentRepeater" runat="server">
43 <itemtemplate>
44 <b>
45 <%# DataBinder.Eval(Container.DataItem,"au_id") %>
46 </b>
47 <br>
48 <!-- 子Repeater开始 -->
49 <asp:repeater id="childRepeater" runat="server"
50 datasource='<%# ((DataRowView)Container.DataItem).Row.GetChildRows("myrelation") %>'>
51 <itemtemplate>
52 <%# DataBinder.Eval(Container.DataItem, "[\"title_id\"]")%>
53 <br>
54 </itemtemplate>
55 </asp:repeater>
56 <!-- 子Repeater结束 -->
57 </itemtemplate>
58</asp:repeater>
59<!-- 父Repeater结束 -->
60</form>
61</body>
62</HTML>
63
64
2<%@ Page Language="VB"%>
3<%@ Import Namespace="System.Data" %>
4<%@ Import Namespace="system.Data.SqlClient"%>
5
6<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
7
8<html xmlns="http://www.w3.org/1999/xhtml"; >
9<head runat="server">
10 <title>DataRelation</title>
11
12
13<script language="vb" runat="server">
14
15
16 Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
17
18
19 Dim cnn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Pubs").ConnectionString)
20
21 Dim ds As New DataSet()
22
23 Dim cmd1 As SqlDataAdapter
24
25 cmd1 = New SqlDataAdapter("select * from [Authors]", cnn)
26 cmd1.Fill(ds, "Authors")
27 Dim cmd2 As SqlDataAdapter
28 cmd2 = New SqlDataAdapter("select * from [titleauthor]", cnn)
29 cmd2.Fill(ds, "titles")
30
31
32 'Create the relation bewtween the Authors and Titles tables.
33 ds.Relations.Add("myrelation", ds.Tables("authors").Columns("au_id"), ds.Tables("titles").Columns("au_id"))
34
35
36 'Bind the Authors table to the parent Repeater control, and call DataBind.
37 parentRepeater.DataSource = ds.Tables("Authors")
38 'Page.DataBind()
39
40 parentRepeater.DataBind()
41
42
43 End Sub
44
45
46</script>
47
48
49</head>
50<body>
51 <form id="form1" runat="server">
52 <div>
53 <!-- start parent repeater -->
54<asp:repeater id="parentRepeater" runat="server">
55 <itemtemplate>
56 <b><%#DataBinder.Eval(Container.DataItem, "[au_id]")%></b><br>
57
58
59
60 <!-- 子Repeater开始 -->
61 <asp:repeater id="childRepeater" runat="server" datasource='<%# CType(Container.DataItem, DataRowView).Row.GetChildRows("myrelation") %>'>
62 <itemtemplate>
63 vb.net:2级- <%#Eval("[au_ord]")%>
64 <br>
65 </itemtemplate>
66 </asp:repeater>
67 <!-- 子Repeater结束 -->
68
69 </itemtemplate>
70</asp:repeater>
71
72<!-- end parent repeater -->
73
74
75 </div>
76 </form>
77</body>
78</html>
79
80
二、DataGridParent.Items(i).FindControl("DataGridChild") 方法
2<%@ Import Namespace="System.Data" %>
3<%@ Import Namespace="system.Data.SqlClient"%>
4<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
5
6<html xmlns="http://www.w3.org/1999/xhtml"; >
7<head id="Head1" runat="server">
8 <title>vb-DataRelation03</title>
9 <script language="vb" runat="server">
10
11 Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
12 Dim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Northwind").ConnectionString)
13 Dim str As String = "select top 5 [orderID],[customerID],[orderDate] From [Orders]"
14
15 Dim adapter As SqlDataAdapter = New SqlDataAdapter(str, conn)
16
17 Dim ds As New DataSet
18
19 Dim mytable As New DataTable() ''一定要建立DATATABLE
20 adapter.Fill(ds, "Orders")
21 mytable = ds.Tables("Orders")
22
23 ' Me.DataGridParent.DataSource =
24 Me.DataGridParent.DataSource = ds.Tables("Orders").DefaultView
25
26 Me.DataGridParent.DataBind()
27
28 Dim i As Integer
29
30
31
32
33
34 For i = 0 To mytable.Rows.Count - 1
35 '這句非常關鍵..
36 Dim DataGridChild As DataGrid = Me.DataGridParent.Items(i).FindControl("DataGridChild")
37 Dim str2 As String = "select [orderID],[ProductID] From [Order Details] where [orderID]='" + mytable.Rows(i).Item(0).ToString + "'"
38 Dim ds2 As New DataSet
39
40 Dim adapter2 As SqlDataAdapter
41 adapter2 = New SqlDataAdapter(str2, conn)
42 adapter2.Fill(ds2, "ordersDetails")
43 DataGridChild.DataSource = ds2.Tables("ordersDetails").DefaultView
44 DataGridChild.DataBind()
45
46
47
48
49 Next
50
51
52
53 End Sub
54 </script>
55</head>
56<body>
57 <form id="form1" runat="server">
58 <div>
59 <asp:DataGrid AutoGenerateColumns="False" ID="DataGridParent" runat="server" Width="166px" ShowHeader="False">
60 <columns>
61 <asp:TemplateColumn>
62 <itemtemplate>
63 <%# DataBinder.Eval (container.DataItem,"orderID") %>
64 <asp:DataGrid AutoGenerateColumns="False" ID="DataGridChild" runat="server" Width="227px" ShowHeader="False">
65 <Columns>
66 <asp:TemplateColumn>
67 <ItemTemplate >
68 <%#DataBinder.Eval(Container.DataItem, "ProductID")%>
69 </ItemTemplate>
70 </asp:TemplateColumn>
71 </Columns>
72 </asp:DataGrid>
73 </itemtemplate>
74 </asp:TemplateColumn>
75 </columns>
76
77 </asp:DataGrid>
78 </div>
79 </form>
80</body>
81</html>
82
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步