|
<%@ Page language="c#" Inherits="DataSetRelationships" CodeFile="Default.aspx.cs" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>DataSetRelationships</title>
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp:Literal id="HtmlContent" runat="server"></asp:Literal>
</form>
</body>
</HTML>
File: Default.aspx.cs
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Text;
using System.Data.SqlClient;
public partial class DataSetRelationships : System.Web.UI.Page
{
protected void Page_Load(object sender, System.EventArgs e)
{
string connectionString = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";
SqlConnection con = new SqlConnection(connectionString);
string sqlCat = "SELECT CategoryID, CategoryName FROM Categories";
string sqlProd = "SELECT ProductName, CategoryID FROM Products";
SqlDataAdapter da = new SqlDataAdapter(sqlCat, con);
DataSet ds = new DataSet();
try
{
con.Open();
da.Fill(ds, "Categories");
da.SelectCommand.CommandText = sqlProd;
da.Fill(ds, "Products");
}
finally
{
con.Close();
}
DataRelation relat = new DataRelation("CatProds",
ds.Tables["Categories"].Columns["CategoryID"],
ds.Tables["Products"].Columns["CategoryID"]);
ds.Relations.Add(relat);
StringBuilder htmlStr = new StringBuilder("");
foreach (DataRow row in ds.Tables["Categories"].Rows)
{
htmlStr.Append("<b>");
htmlStr.Append(row["CategoryName"].ToString());
htmlStr.Append("</b><ul>");
DataRow[] childRows = row.GetChildRows(relat);
foreach (DataRow childRow in childRows)
{
htmlStr.Append("<li>");
htmlStr.Append(childRow["ProductName"].ToString());
htmlStr.Append("</li>");
}
htmlStr.Append("</ul>");
}
HtmlContent.Text = htmlStr.ToString();
}
}
|