CSharp Create Dataset Pragmatically – ADO.Net Database

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="Default_aspx" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="Bugs"
                      runat="server"
                      CellPadding="4"
                      ForeColor="#333333"
                      GridLines="None">
            <footerStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <rowStyle BackColor="#EFF3FB" />
            <pagerStyle BackColor="#2461BF"
                        ForeColor="White"
                        HorizontalAlign="Center" />
            <selectedRowStyle BackColor="#D1DDF1"
                              Font-Bold="True"
                              ForeColor="#333333" />
            <headerStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <editRowStyle BackColor="#2461BF" />
            <alternatingRowStyle BackColor="White" />
        </asp:GridView>
 
        <asp:GridView ID="BugConstraints"
                      runat="server"
                      BackColor="#DEBA84"
                      BorderColor="#DEBA84"
                      BorderStyle="None"
                      BorderWidth="1px"
                      CellPadding="3"
                      CellSpacing="2">
            <footerStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
            <rowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
            <pagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
            <selectedRowStyle BackColor="#738A9C" Font-Bold="True"
                ForeColor="White" />
            <headerStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
        </asp:GridView>
  </div>
    </form>
</body>
</html>File: Default.aspx.cs
 
using System;
using System.Data;
using System.Configuration;
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 Default_aspx : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    if (!IsPostBack)
    {
      DataSet ds = CreateDataSet();      Bugs.DataSource = ds.Tables["Bugs"];
      Bugs.DataBind();      BugConstraints.DataSource = ds.Tables["Bugs"].Constraints;
      BugConstraints.DataBind();
    }
  }    private DataSet CreateDataSet(  )
    {
      DataSet dataSet = new DataSet();      DataTable tblBugs = new DataTable("Bugs");      DataColumn newColumn;       newColumn = tblBugs.Columns.Add("BugID", Type.GetType("System.Int32"));
      newColumn.AutoIncrement = true;
      newColumn.AutoIncrementSeed = 1;
      newColumn.AutoIncrementStep = 1;
      newColumn.AllowDBNull = false;
      //newColumn.Unique = true;      UniqueConstraint constraint = new UniqueConstraint("Unique_BugID", newColumn);
      tblBugs.Constraints.Add(constraint);      DataColumn[] columnArray = new DataColumn[1];
      columnArray[0] = newColumn;      tblBugs.PrimaryKey = columnArray;      newColumn = tblBugs.Columns.Add("Product", Type.GetType("System.Int32"));
      newColumn.AllowDBNull = false;
      newColumn.DefaultValue = 1;      DataColumn bugProductColumn = newColumn;      newColumn = tblBugs.Columns.Add("Version", Type.GetType("System.String"));
      newColumn.AllowDBNull = false;
      newColumn.MaxLength = 50;
      newColumn.DefaultValue = "0.1";      newColumn = tblBugs.Columns.Add( "Description", Type.GetType("System.String"));
      newColumn.AllowDBNull = false;
      newColumn.MaxLength = 8000;
      newColumn.DefaultValue = "";      newColumn = tblBugs.Columns.Add("Reporter", Type.GetType("System.Int32"));
      newColumn.AllowDBNull = false;      DataColumn bugReporterColumn = newColumn;      DataRow newRow;      newRow = tblBugs.NewRow();
      newRow["Product"] = 1;
      newRow["Version"] = "0.1";
      newRow["Description"] = "D1";
      newRow["Reporter"] = 5;
      tblBugs.Rows.Add(newRow);      newRow = tblBugs.NewRow();
      newRow["Product"] = 1;
      newRow["Version"] = "0.1";
      newRow["Description"] = "D2";
      newRow["Reporter"] = 5;
      tblBugs.Rows.Add(newRow);      newRow = tblBugs.NewRow();
      newRow["Product"] = 1;
      newRow["Version"] = "0.1";
      newRow["Description"] = "D3";
      newRow["Reporter"] = 6;
      tblBugs.Rows.Add(newRow);      newRow = tblBugs.NewRow();
      newRow["Product"] = 1;
      newRow["Version"] = "0.1";
      newRow["Description"] = "D4";
      newRow["Reporter"] = 5;
      tblBugs.Rows.Add(newRow);      newRow = tblBugs.NewRow();
      newRow["Product"] = 2;
      newRow["Version"] = "0.1";
      newRow["Description"] = "D5";
      newRow["Reporter"] = 5;
      tblBugs.Rows.Add(newRow);      newRow = tblBugs.NewRow();
      newRow["Product"] = 2;
      newRow["Version"] = "0.1";
      newRow["Description"] = "D6";
      newRow["Reporter"] = 6;
      tblBugs.Rows.Add(newRow);      dataSet.Tables.Add(tblBugs);      DataTable tblProduct = new DataTable("lkProduct");
      newColumn = tblProduct.Columns.Add("ProductID", Type.GetType("System.Int32"));
      newColumn.AutoIncrement = true;
      newColumn.AutoIncrementSeed = 1;
      newColumn.AutoIncrementStep = 1;
      newColumn.AllowDBNull = false;
      newColumn.Unique = true;              newColumn = tblProduct.Columns.Add("ProductDescription", Type.GetType("System.String"));
      newColumn.AllowDBNull = false;
      newColumn.MaxLength = 8000;
      newColumn.DefaultValue = "";      newRow = tblProduct.NewRow();
      newRow["ProductDescription"] = "Bug Tracking";
      tblProduct.Rows.Add(newRow);      newRow = tblProduct.NewRow();
      newRow["ProductDescription"] = "Information Manager";
      tblProduct.Rows.Add(newRow);      dataSet.Tables.Add(tblProduct);      DataTable tblPeople = new DataTable("People");
      newColumn = tblPeople.Columns.Add("PersonID", Type.GetType("System.Int32"));
      newColumn.AutoIncrement = true;
      newColumn.AutoIncrementSeed = 1;
      newColumn.AutoIncrementStep = 1;
      newColumn.AllowDBNull = false;            UniqueConstraint uniqueConstraint = new UniqueConstraint("Unique_PersonID", newColumn);
      tblPeople.Constraints.Add(uniqueConstraint);      DataColumn PersonIDColumn = newColumn;      columnArray = new DataColumn[1];
      columnArray[0] = newColumn;
      tblPeople.PrimaryKey = columnArray;      newColumn = tblPeople.Columns.Add("FullName", Type.GetType("System.String"));
      newColumn.AllowDBNull = false;
      newColumn.MaxLength = 80;
      newColumn.DefaultValue = "";      newColumn = tblPeople.Columns.Add("eMail", Type.GetType("System.String"));
      newColumn.AllowDBNull = false;
      newColumn.MaxLength = 100;
      newColumn.DefaultValue = "";      newColumn = tblPeople.Columns.Add("Phone", Type.GetType("System.String"));
      newColumn.AllowDBNull = false;
      newColumn.MaxLength = 20;
      newColumn.DefaultValue = "";      newColumn = tblPeople.Columns.Add("Role", Type.GetType("System.Int32"));
      newColumn.DefaultValue = 0;
      newColumn.AllowDBNull = false;      newRow = tblPeople.NewRow();
      newRow["FullName"] = "Jason";
      newRow["email"] = "j@server.com";
      newRow["Phone"] = "123-111-1111";
      newRow["Role"] = 1;
      tblPeople.Rows.Add(newRow);      newRow = tblPeople.NewRow();
      newRow["FullName"] = "Dole";
      newRow["email"] = "d@server.com";
      newRow["Phone"] = "234-111-2222";
      newRow["Role"] = 1;
      tblPeople.Rows.Add(newRow);      newRow = tblPeople.NewRow();
      newRow["FullName"] = "John";
      newRow["email"] = "j@server.com";
      newRow["Phone"] = "345-222-3333";
      newRow["Role"] = 1;
      tblPeople.Rows.Add(newRow);      newRow = tblPeople.NewRow();
      newRow["FullName"] = "Jess";
      newRow["email"] = "j@server.com";
      newRow["Phone"] = "456-333-4444";
      newRow["Role"] = 3;
      tblPeople.Rows.Add(newRow);      newRow = tblPeople.NewRow();
      newRow["FullName"] = "Ron";
      newRow["email"] = "ron@server.com";
      newRow["Phone"] = "567-555-5555";
      newRow["Role"] = 2;
      tblPeople.Rows.Add(newRow);      newRow = tblPeople.NewRow();
      newRow["FullName"] = "Tank";
      newRow["email"] = "t@server.com";
      newRow["Phone"] = "617-555-1234";
      newRow["Role"] = 2;
      tblPeople.Rows.Add(newRow);      dataSet.Tables.Add(tblPeople);      ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_BugToPeople", PersonIDColumn, bugReporterColumn);
      fk.DeleteRule = Rule.Cascade;
      fk.UpdateRule = Rule.Cascade;
      tblBugs.Constraints.Add(fk);        System.Data.DataRelation dataRelation;
      System.Data.DataColumn dataColumn1;
      System.Data.DataColumn dataColumn2;      dataColumn1 = dataSet.Tables["People"].Columns["PersonID"];
      dataColumn2 = dataSet.Tables["Bugs"].Columns["Reporter"];      dataRelation = new System.Data.DataRelation("BugsToReporter",dataColumn1,dataColumn2);      dataSet.Relations.Add(dataRelation);      return dataSet;
    }
}

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.