ASP.NET Tutorial/ADO.net Database/DataSetRelation
DataSetRelation ships
<source lang="csharp">
<%@ 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.ruponentModel; 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.rumandText = 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(""); htmlStr.Append(row["CategoryName"].ToString());htmlStr.Append("
- ");
DataRow[] childRows = row.GetChildRows(relat);
foreach (DataRow childRow in childRows)
{
htmlStr.Append("
- "); htmlStr.Append(childRow["ProductName"].ToString()); htmlStr.Append(" ");
}htmlStr.Append("");
} HtmlContent.Text = htmlStr.ToString(); }
}</source>