ADO Database ASP.Net

  1. Home
  2. ASP.Net
  3. ADO Database
  4. Create DataSet pragmatically
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="Default_aspx" %>



    Untitled Page


    
    

                              runat="server" 
                      CellPadding="4" 
                      ForeColor="#333333" 
                      GridLines="None">
            
            
                                    ForeColor="White" 
                        HorizontalAlign="Center" />
                                          Font-Bold="True" 
                              ForeColor="#333333" />
            
            
            
        
    
                              runat="server" 
                      BackColor="#DEBA84" 
                      BorderColor="#DEBA84"
                      BorderStyle="None" 
                      BorderWidth="1px" 
                      CellPadding="3" 
                      CellSpacing="2">
            
            
            
                            ForeColor="White" />
            
        
  

    


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;
    }
}