CSharp Build Dataset With Relationship – ADO.Net Database

<%@ 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.csusing 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;
   }
}

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.