Mega Code Archive
Build a DataSet with relationship
<![CDATA[
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="SettingRelations" Debug="true" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Setting Relationships in a DataSet</title>
</head>
<body>
<form id="form1" runat="server">
<div id="container">
<h1>Setting Relationships in a DataSet</h1>
<p>This example demonstrates how to relate two DataTables in a DataSet</p>
<asp:GridView id="grdAuthors" runat="server" />
<hr />
<asp:GridView id="grdBooks" runat="server" />
<hr />
<dl>
<asp:Label id="labReport" runat="server" />
</dl>
</div>
</form>
</body>
</html>
File: Default.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class SettingRelations : System.Web.UI.Page
{
DataSet ds = null;
protected void Page_Load(object sender, EventArgs e)
{
ds = new DataSet();
ds.Tables.Add(MakeAuthorData());
ds.Tables.Add(MakeBookData());
DataRelation relation = new DataRelation("Book2Author", ds.Tables["Authors"].Columns["AuthorId"], ds.Tables["Books"].Columns["AuthorId"]);
ds.Relations.Add(relation);
grdAuthors.DataSource = ds.Tables["Authors"].DefaultView;
grdAuthors.DataBind();
grdBooks.DataSource = ds.Tables["Books"].DefaultView;
grdBooks.DataBind();
foreach (DataRow artistRow in ds.Tables["Authors"].Rows)
{
string fname = (string)artistRow["FirstName"];
string lname = (string)artistRow["LastName"];
labReport.Text += "<dt>" + fname + " " + lname;
foreach (DataRow bookRow in artistRow.GetChildRows(relation))
{
string title = (string)bookRow["Title"];
labReport.Text += "<dd>" + title;
}
}
}
private DataTable MakeAuthorData()
{
DataTable table = new DataTable();
table.TableName = "Authors";
DataColumn idCol = new DataColumn();
idCol.ColumnName = "AuthorId";
idCol.DataType = typeof(Int32);
idCol.AllowDBNull = false;
idCol.Unique = true;
DataColumn firstNameCol = new DataColumn("FirstName", typeof(string));
DataColumn lastNameCol = new DataColumn("LastName", typeof(string));
table.Columns.Add(idCol);
table.Columns.Add(firstNameCol);
table.Columns.Add(lastNameCol);
DataRow r1 = table.NewRow();
r1[0] = 10;
r1[1] = "A";
r1[2] = "a";
table.Rows.Add(r1);
DataRow r2 = table.NewRow();
r2[0] = 20;
r2["FirstName"] = "B";
r2["LastName"] = "b";
table.Rows.Add(r2);
DataRow r3 = table.NewRow();
r3[0] = 30;
r3["FirstName"] = "C";
r3["LastName"] = "c";
table.Rows.Add(r3);
DataRow r4 = table.NewRow();
r4[0] = 40;
r4["FirstName"] = "D";
r4["LastName"] = "d";
table.Rows.Add(r4);
return table;
}
private DataTable MakeBookData()
{
DataTable table = new DataTable();
table.TableName = "Books";
DataColumn idCol = new DataColumn();
idCol.ColumnName = "Id";
idCol.DataType = typeof(Int32);
idCol.AllowDBNull = false;
idCol.Unique = true;
idCol.AutoIncrement = true;
DataColumn authorCol = new DataColumn("AuthorId", typeof(Int32));
DataColumn nameCol = new DataColumn("Title", typeof(string));
DataColumn priceCol = new DataColumn("Price", typeof(double));
table.Columns.Add(idCol);
table.Columns.Add(authorCol);
table.Columns.Add(nameCol);
table.Columns.Add(priceCol);
DataRow r1 = table.NewRow();
r1[1] = 30;
r1[2] = "Java";
r1[3] = 49.99;
table.Rows.Add(r1);
DataRow r2 = table.NewRow();
r2[1] = 10;
r2[2] = "C#";
r2[3] = 19.99;
table.Rows.Add(r2);
DataRow r3 = table.NewRow();
r3[1] = 40;
r3[2] = "Javascript";
r3[3] = 24.99;
table.Rows.Add(r3);
DataRow r4 = table.NewRow();
r4[1] = 40;
r4[2] = "Oracle";
r4[3] = 24.99;
table.Rows.Add(r4);
return table;
}
}
]]>