11.6 Creating Data Objects by Hand

In all of the examples so far, you have created the DataSet object and its DataTable and DataRow objects by selecting data from the database. There are, however, occasions when you will want to fill a dataset or a table by hand.

For example, you may want to gather data from a user and then push that data into the database. It can be convenient to add records to a table manually, and then update the database from that table.

The dataset is also an excellent transport mechanism for data. You may even want to create a dataset by hand only to pass it to another tier in your application where it will be used as a data source.

In the next example you will create a dataset and populate three tables by hand. You'll start by creating theBugs table and specifying its data structure. You'll then fill that table with records. You'll do the same for the lkProduct table and the People table.

Once the tables are created, you'll set constraints on a number of columns, set default values, establish identity columns, and create keys. In addition, you'll establish a foreign key relationship between two tables, and you'll create a data relation tying two tables together. It sounds like more work than it really is.

11.6.1 Creating the DataTable by Hand

Start by creating a method named CreateDataSet. The job of this method is to create a DataSet and to populate it by hand, and then to return that resulting DataSet to the calling method, in this case Page_Load.

CreateDataSet begins by instantiating a new DataTable object, passing in the name of the table as a parameter to the constructor:

DataTable tblBugs = new DataTable("Bugs");

The table you are creating should mimic the data structure of the Bugs table in SQL Server. Figure 11-15 shows that structure.

Figure 11-15. The structure of the Bugs table in SQL server

To add a column to this DataTable object, you do not call a constructor. Instead you call the Add method of the DataTable object's Columns collection. The Add method takes two parameters, the name of the column and its data type:

DataColumn newColumn;
newColumn = 
   tblBugs.Columns.Add("BugID", Type.GetType("System.Int32"));

In Visual Basic .NET, this is:

dim newColumn as DataColumn
newColumn = _
  tblBugs.Columns.Add("BugID", Type.GetType("System.Int32"));

11.6.1.1 Setting column properties

The Add method creates the new column and returns a reference to it, which you may now manipulate. Since this is to be an identity column (see the highlighted field in Figure 11-15), you'll want to set its AutoIncrement property to true, and you'll set the AutoIncrementSeed and AutoIncrementStep properties to set the seed and step values of the identity, respectively. The following code fragment does this:

newColumn.AutoIncrement = true;
newColumn.AutoIncrementSeed=1; 
newColumn.AutoIncrementStep=1;

The AutoIncrementSeed property sets the initial value for the identity column, and the AutoIncrementStep property sets the increment for each new record. Thus, if the seed were 5 and the step were 3, the first five records would have IDs of 5, 8, 11, 14, and 17. In the case shown, where both the seed and step are 1, the first four records have IDs of 1,2,3,4.

11.6.1.2 Setting constraints

Identity columns must not be null, so you'll set the AllowDBNull property of the new column to false:

newColumn.AllowDBNull=false;

You can set the Unique property to true to ensure that each entry in this column must be unique:

newColumn.Unique=true;

This creates an unnamed constraint in the Bugs table's Constraints collection. You can, if you prefer, add a named constraint. To do so, you create an instance of the UniqueConstraint class and pass a name for it into the constructor, along with a reference to the column:

UniqueConstraint constraint = 
   new UniqueConstraint("Unique_BugID",newColumn);

You then manually add that constraint to the table's Constraints collection:

tblBugs.Constraints.Add(constraint);

If you do add a named constraint, be sure to comment out the Unique property.

This completes the first column in the table. The second column is the Product column, as you can see in Figure 11-16. Notice that this column is of type integer, with no nulls and a default value of 1 (see the highlighted property in Figure 11-16). You create the Product column by calling the Add method of the Columns collection of the tblBugs table, this time passing in the type for an integer. You then set the AllowDBNull property as you did with the earlier column, and you set the DefaultValue property to set the default value for the column. This is illustrated in the following code fragment:

Figure 11-16. The Products column

newColumn = tblBugs.Columns.Add(
   "Product", Type.GetType("System.Int32"));
newColumn.AllowDBNull=false;
newColumn.DefaultValue = 1;

Looking at Figure 11-16 again, you can see that the third column is Version, with a type of varChar.

A varChar is a variable length character string. A varChar can be declared to be any length between 1 and 8000 bytes. Typically you will limit the length of the string as a form of documentation indicating the largest string you expect in the field.

You declare the column type to be string for a varchar, and you can set the length of the string with the MaxLength property, as shown in the following code fragment:

newColumn = tblBugs.Columns.Add(
   "Version", Type.GetType("System.String"));
newColumn.AllowDBNull=false;
newColumn.MaxLength=50;
newColumn.DefaultValue = "0.1";

You declare the Description and Reporter columns in a like manner:

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;

11.6.1.3 Adding data to the table

With all the columns declared, you're ready to add rows of data to the table. You do so by calling the DataTable object's NewRow method, which returns an empty DataRow object with the right structure:

newRow = tblBugs.NewRow(  );

You can use the column name as an index into the row's collection of DataColumns, assigning the appropriate value for each column, one by one:

newRow["Product"] = 1;
newRow["Version"] = "0.1";
newRow["Description"] = "Crashes on load";
newRow["Reporter"] = 5;

The authors of the DataRows class have implemented the indexer for their class to access the contained Columns collection invisibly. Thus, when you write newRow["Product"], you actually access the Product column within the Columns collection of the DataRow object.

When the columns are complete, you add the row to the table's Rows collection by calling the Add method, passing in the row you just created:

tblBugs.Rows.Add(newRow);

You are now ready to create a new row:

newRow = tblBugs.NewRow(  );
newRow["Product"] = 1;
newRow["Version"] = "0.1";
newRow["Description"] = "Does not report correct owner of bug";
newRow["Reporter"] = 5;
tblBugs.Rows.Add(newRow);

When all the rows have been created, you can create an instance of a DataSet object and add the table:

DataSet dataSet = new DataSet(  );
dataSet.Tables.Add(tblBugs);

11.6.1.4 Adding additional tables to the DataSet

With the Bugs table added to the new dataset, you are ready to create a new table for lkProduct:

DataTable tblProduct = new DataTable("lkProduct")

Once again you'll define the columns and then add data. You'll then go on to add a new table for People. In theory, you could also add all the other tables from the previous example, but to keep things simpler, you'll stop with these three.

11.6.1.5 Adding rows with an array of objects

The DataRowCollection object's Add method is overloaded. In the code shown above, you created a new DataRow object, populated its columns, and added the row. You are also free to create an array of Objects, fill the array, and pass the array to the Add method. For example, rather than writing:

newRow = tblPeople.NewRow(  );
newRow["FullName"] = "Jesse Liberty";
newRow["email"] = "jliberty@libertyassociates.com";
newRow["Phone"] = "617-555-7301";
newRow["Role"] = 1;
tblPeople.Rows.Add(newRow);

you can instead create an array of five objects and fill that array with the values you would have added to the columns of the row:

Object[] PersonArray = new Object[5];
PersonArray[0] = 1;
PersonArray[1] = "Jesse Liberty";
PersonArray[2] = "jliberty@libertyassociates.com";
PersonArray[3] = "617-555-7301";
PersonArray[4] = 1;
tblPeople.Rows.Add(PersonArray);

Note that in this case, you must manually add a value for the identity column, BugID. When you created the row object, the identity column value was automatically created for you with the right increment from the previous row, but since you are now just creating an array of objects, you must do this by hand.

While this technique works, it is generally not very desirable. The overloaded version of the Add method that takes a DataRow object is typesafe. Each column must match the definition of the column you've created. With an array of objects, just about anything goes; remember that in .NET, everything derives from Object and thus you can pass in any type of data to an array of objects.

11.6.2 Creating Primary Keys

The Bugs table uses the PersonID as a foreign key into the People table. To re-create this, you'll first need to create a primary key in the People table.

You start by declaring the PersonID column as a unique non-null identity column, just as you did earlier for the BugID column in Bugs:

newColumn = tblPeople.Columns.Add("PersonID", Type.GetType("System.Int32"));
newColumn.AutoIncrement = true;     // autoincrementing
newColumn.AutoIncrementSeed=1;      // starts at 1
newColumn.AutoIncrementStep=1;      // increments by 1
newColumn.AllowDBNull=false;        // nulls not allowed

// add the unique constraint
UniqueConstraint uniqueConstraint = 
  new UniqueConstraint("Unique_PersonID",newColumn);
tblPeople.Constraints.Add(uniqueConstraint);

To create the primary key you must set the PrimaryKey property of the table. This property takes an array of DataColumn objects.

In many tables, the primary key is not a single column but rather two or more columns. For example, you might keep track of orders for a customer. A given order might be order number 17. Your database may have many orders whose order number is 17. What uniquely identifies a given order is the order number combined with the customer number. Thus, that table would use a compound key of the order number and the customer number.

The primary key for the People table is a single column: PersonID. To set the primary key, you create an array (in this case with one member), and assign to that member the column(s) you want to make the primary key:

columnArray = new DataColumn[1];
columnArray[0] = newColumn;

The newColumn object contains a reference to the PersonID column returned from calling Add. You assign the array to the PrimaryKey property of the table:

tblPeople.PrimaryKey=columnArray;

11.6.3 Creating Foreign Keys

The PersonID acts as a primary key in People and as a foreign key in Bugs. To create the foreign key relationship, you'll instantiate a new object of type ForeignKeyConstraint, passing in the name of the constraint ("FK_BugToPeople") as well as a reference to the two columns.

To facilitate passing references to the key fields to the ForeignKeyConstraint constructor, you'll want to squirrel away a reference to the PersonID column in People and the Reporter column in Bugs. Immediately after you create the columns, save a reference:

newColumn = 
   tblBugs.Columns.Add("Reporter", Type.GetType("System.Int32"));
newColumn.AllowDBNull=false;
DataColumn bugReporterColumn = 
     newColumn; // save for foreign key creation

Assuming you've saved the Reporter column in bugReporterColumn and the PersonID column from People in PersonIDColumn, you are ready to create the ForeignKeyConstraint object:

ForeignKeyConstraint fk = 
  New ForeignKeyConstraint(
        "FK_BugToPeople",PersonIDColumn,bugReporterColumn);

This creates the Foreign Key Constraint named fk. Before you add it to the Bugs table, you must set two properties:

fk.DeleteRule=Rule.Cascade;
fk.UpdateRule=Rule.Cascade;

The DeleteRule determines the action that will occur when a row is deleted from the parent table. Similarly, the UpdateRule determines what will happen when a row is updated in the parent column. The potential values are enumerated by the Rule enumeration, as shown in Table 11-6.

Table 11-6. Rule enumeration

Member name	Description
Cascade	Delete or update related rows (this is the default)
None	Take no action on related rows
SetDefault	Set the values in the related rows to the value contained in the DefaultValue property
SetNull	Set the related rows to null

In the case shown, the value is set to Rule.Cascade; if a record is deleted from the parent table, all the child records will be deleted as well. You are now ready to add the foreign key constraint to the Bugs table:

tblBugs.Constraints.Add(fk);

11.6.4 Creating Data Relations

As you saw earlier in the chapter, you can encapsulate the relationship among tables in a DataRelation object. The code for building relationships among hand-crafted DataTables is just like the code you saw earlier when you pulled the data structure from the database itself:

System.Data.DataRelation dataRelation;
System.Data.DataColumn dataColumn1;
System.Data.DataColumn dataColumn2;

// set the dataColumns to create the relationship
// between Bug and BugHistory on the BugID key
dataColumn1 = 
   dataSet.Tables["People"].Columns["PersonID"];
dataColumn2 = 
   dataSet.Tables["Bugs"].Columns["Reporter"];
   
dataRelation = 
   new System.Data.DataRelation(
   "BugsToReporter", 
   dataColumn1, 
   dataColumn2);

// add the new DataRelation to the dat
dataSet.Relations.Add(dataRelation);

To display this output, you'll use two DataGrids: one to show the Bugs table, and another to show the Constraints you've added to that table:

<body>
   <form id="Form1" method="post" runat="server">
      <asp:DataGrid id="DataGrid1" runat="server" DataKeyField="BugID" 
      CellPadding="5" HeaderStyle-BackColor="PapayaWhip" BorderWidth="5px" 
      BorderColor="#000099" AlternatingItemStyle-BackColor="LightGrey" 
      HeaderStyle-Font-Bold="True" AutoGenerateColumns="False" 
      EnableViewState="true">
         <Columns>
            <asp:BoundColumn DataField="BugID" 
            HeaderText="Bug ID" />
            <asp:BoundColumn DataField="Description" 
            HeaderText="Description" />
            <asp:BoundColumn DataField="Reporter" 
            HeaderText="Reported By" />
         </Columns>
      </asp:DataGrid>
      <br />
      <asp:DataGrid ID="BugConstraints" Runat="server"
       HeaderStyle-Font-Bold="True" AlternatingItemStyle-BackColor="LightGrey" 
       BorderColor="#000099" BorderWidth="5px" 
      HeaderStyle-BackColor="PapayaWhip" CellPadding="5" Runat="server" />
   </form>
</body>

The output is shown in Figure 11-17. The complete C# source code for this version of the application is shown in Example 11-10.

Figure 11-17. The hand-coded table

Example 11-10. Creating a DataSet by hand

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace BugHistoryByHand
{
   /// <summary>
   /// Summary description for WebForm1.
   /// </summary>
   public class WebForm1 : System.Web.UI.Page
   {
      // the Bugs Data Grid
      protected System.Web.UI.WebControls.DataGrid DataGrid1;

      // display the constraints added to the bug table
      protected System.Web.UI.WebControls.DataGrid BugConstraints;

      // unchanged from previous example
      public WebForm1(  )
      {
         Page.Init += new System.EventHandler(Page_Init);
      }

 
      // bind to the bug grid and the constraints grid
      private void Page_Load(object sender, System.EventArgs e)
      {
         if (!IsPostBack)
         {
            // call the method which creates the tables and the relations
            DataSet ds = CreateDataSet(  );

            // set the data source for the grid to the first table 
            DataGrid1.DataSource=ds.Tables[0];
            DataGrid1.DataBind(  );

            BugConstraints.DataSource = ds.Tables["Bugs"].Constraints;
            BugConstraints.DataBind(  );
              
         }      
      }

      //hand carved
      private DataSet CreateDataSet(  )
      {
         // instantiate a new DataSet object that
         // you will fill with tables and relations
         DataSet dataSet = new DataSet(  );
         
         // make the bug table and its columns
         // mimic the attributes from the SQL database
         DataTable tblBugs = new DataTable("Bugs");
         
         DataColumn newColumn; // hold the new columns as you create them
         
         newColumn = 
           tblBugs.Columns.Add(
               "BugID", Type.GetType("System.Int32"));
         newColumn.AutoIncrement = true;     // autoincrementing
         newColumn.AutoIncrementSeed=1;      // starts at 1
         newColumn.AutoIncrementStep=1;      // increments by 1
         newColumn.AllowDBNull=false;        // nulls not allowed
         
         // or you can provide a named constraint
         UniqueConstraint constraint = 
            new UniqueConstraint("Unique_BugID",newColumn);
         tblBugs.Constraints.Add(constraint);

         // create an array of columns for the primary key
         DataColumn[] columnArray = new DataColumn[1];
         columnArray[0] = newColumn;

         // add the array to the Primary key property
         tblBugs.PrimaryKey=columnArray;

         // The Product column
         newColumn = tblBugs.Columns.Add(
              "Product", Type.GetType("System.Int32"));
         newColumn.AllowDBNull=false;
         newColumn.DefaultValue = 1;

         // save for foreign key creation
         DataColumn bugProductColumn = newColumn; 

         // The Version column
         newColumn = tblBugs.Columns.Add(
           "Version", Type.GetType("System.String"));
         newColumn.AllowDBNull=false;
         newColumn.MaxLength=50;
         newColumn.DefaultValue = "0.1";

         // The Description column
         newColumn = tblBugs.Columns.Add(
           "Description", Type.GetType("System.String"));
         newColumn.AllowDBNull=false;
         newColumn.MaxLength=8000;
         newColumn.DefaultValue = "";

         // The Reporter column
         newColumn = tblBugs.Columns.Add(
           "Reporter", Type.GetType("System.Int32"));
         newColumn.AllowDBNull=false;

         // save for foreign key creation
         DataColumn bugReporterColumn = newColumn; 

         // Add rows based on the db schema you just created
         DataRow newRow;      // holds the new row 

         newRow = tblBugs.NewRow(  );
         newRow["Product"] = 1;
         newRow["Version"] = "0.1";
         newRow["Description"] = "Crashes on load";
         newRow["Reporter"] = 5;
         tblBugs.Rows.Add(newRow);

         newRow = tblBugs.NewRow(  );
         newRow["Product"] = 1;
         newRow["Version"] = "0.1";
         newRow["Description"] = 
             "Does not report correct owner of bug";
         newRow["Reporter"] = 5;
         tblBugs.Rows.Add(newRow);

         newRow = tblBugs.NewRow(  );
         newRow["Product"] = 1;
         newRow["Version"] = "0.1";
         newRow["Description"] = 
            "Does not show history of previous action";
         newRow["Reporter"] = 6;
         tblBugs.Rows.Add(newRow);

         newRow = tblBugs.NewRow(  );
         newRow["Product"] = 1;
         newRow["Version"] = "0.1";
         newRow["Description"] = 
            "Fails to reload properly";
         newRow["Reporter"] = 5;
         tblBugs.Rows.Add(newRow);
                
         newRow = tblBugs.NewRow(  );
         newRow["Product"] = 2;
         newRow["Version"] = "0.1";
         newRow["Description"] = "Loses data overnight";
         newRow["Reporter"] = 5;
         tblBugs.Rows.Add(newRow);

         newRow = tblBugs.NewRow(  );
         newRow["Product"] = 2;
         newRow["Version"] = "0.1";
         newRow["Description"] = "HTML is not shown properly";
         newRow["Reporter"] = 6;
         tblBugs.Rows.Add(newRow);

         // add the table to the dataset
         dataSet.Tables.Add(tblBugs);


         // Product Table

         // make the Products table and add the columns
         DataTable tblProduct = new DataTable("lkProduct");
         newColumn = tblProduct.Columns.Add(
            "ProductID", Type.GetType("System.Int32"));
         newColumn.AutoIncrement = true;     // autoincrementing
         newColumn.AutoIncrementSeed=1;      // starts at 1
         newColumn.AutoIncrementStep=1;      // increments by 1
         newColumn.AllowDBNull=false;        // nulls not allowed
         newColumn.Unique=true;              // each value must be unique

         newColumn = tblProduct.Columns.Add(
            "ProductDescription", Type.GetType("System.String"));
         newColumn.AllowDBNull=false;
         newColumn.MaxLength=8000;
         newColumn.DefaultValue = "";
         
         newRow = tblProduct.NewRow(  );
         newRow["ProductDescription"] = "BugX Bug Tracking";
         tblProduct.Rows.Add(newRow);

         newRow = tblProduct.NewRow(  );
         newRow["ProductDescription"] = 
             "PIM - My Personal Information Manager";
         tblProduct.Rows.Add(newRow);

         // add the products table to the dataset 
         dataSet.Tables.Add(tblProduct);


         // People


         // make the People table and add the columns
         DataTable tblPeople = new DataTable("People");
         newColumn = tblPeople.Columns.Add(
           "PersonID", Type.GetType("System.Int32"));
         newColumn.AutoIncrement = true;     // autoincrementing
         newColumn.AutoIncrementSeed=1;      // starts at 1
         newColumn.AutoIncrementStep=1;      // increments by 1
         newColumn.AllowDBNull=false;        // nulls not allowed

         UniqueConstraint uniqueConstraint = 
           new UniqueConstraint(
                "Unique_PersonID",newColumn);
         tblPeople.Constraints.Add(uniqueConstraint);

         // stash away the PersonID column for the foreign
         // key constraint
         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=8000;
         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"] = "Jesse Liberty";
         newRow["email"] = "jliberty@libertyassociates.com";
         newRow["Phone"] = "617-555-7301";
         newRow["Role"] = 1;
         tblPeople.Rows.Add(newRow);

         newRow = tblPeople.NewRow(  );
         newRow["FullName"] = "Dan Hurwitz";
         newRow["email"] = "dhurwitz@stersol.com";
         newRow["Phone"] = "781-555-3375";
         newRow["Role"] = 1;
         tblPeople.Rows.Add(newRow);

         newRow = tblPeople.NewRow(  );
         newRow["FullName"] = "John Galt";
         newRow["email"] = "jGalt@franconia.com";
         newRow["Phone"] = "617-555-9876";
         newRow["Role"] = 1;
         tblPeople.Rows.Add(newRow);

         newRow = tblPeople.NewRow(  );
         newRow["FullName"] = "John Osborn";
         newRow["email"] = "jOsborn@oreilly.com";
         newRow["Phone"] = "617-555-3232";
         newRow["Role"] = 3;
         tblPeople.Rows.Add(newRow);

         newRow = tblPeople.NewRow(  );
         newRow["FullName"] = "Ron Petrusha";
         newRow["email"] = "ron@oreilly.com";
         newRow["Phone"] = "707-555-0515";
         newRow["Role"] = 2;
         tblPeople.Rows.Add(newRow);

         newRow = tblPeople.NewRow(  );
         newRow["FullName"] = "Tatiana Diaz";
         newRow["email"] = "tatiana@oreilly.com";
         newRow["Phone"] = "617-555-1234";
         newRow["Role"] = 2;
         tblPeople.Rows.Add(newRow);

         // add the People table to the dataset
         dataSet.Tables.Add(tblPeople);   
      
         // create the Foreign Key constraint
         // pass in the parent column from people
         // and the child column from Bugs
         ForeignKeyConstraint fk = 
            new ForeignKeyConstraint(
               "FK_BugToPeople",PersonIDColumn,bugReporterColumn);
         fk.DeleteRule=Rule.Cascade;   // like father like son
         fk.UpdateRule=Rule.Cascade;
         tblBugs.Constraints.Add(fk);  // add the new constraint


         // declare the DataRelation and DataColumn objects
         System.Data.DataRelation dataRelation;
         System.Data.DataColumn dataColumn1;
         System.Data.DataColumn dataColumn2;

         // set the dataColumns to create the relationship
         // between Bug and BugHistory on the BugID key
         dataColumn1 = 
            dataSet.Tables["People"].Columns["PersonID"];
         dataColumn2 = 
            dataSet.Tables["Bugs"].Columns["Reporter"];
            
         dataRelation = 
            new System.Data.DataRelation(
            "BugsToReporter", 
            dataColumn1, 
            dataColumn2);

         // add the new DataRelation to the dataset
         dataSet.Relations.Add(dataRelation);  
         
         return dataSet;
      }

      // unchanged from previous example
      private void Page_Init(object sender, EventArgs e)
      {
         InitializeComponent(  );
      }

      #region Web Form Designer generated code
      /// <summary>
      /// Required method for Designer support - do not modify
      /// the contents of this method with the code editor.
      /// </summary>
      private void InitializeComponent(  )
      {    
         this.Load += new System.EventHandler(this.Page_Load);

      }
      #endregion
   }      
}

The complete VB.NET source code for this version of the application is shown in Example 11-11.

Example 11-11. Creating a DataSet by hand

Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Text
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls

Public Class Example_11_11
  Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

  'This call is required by the Web Form Designer.
  <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent(  )

  End Sub
  Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
  Protected WithEvents BugConstraints As System.Web.UI.WebControls.DataGrid

  'NOTE: The following placeholder declaration is required by the Web Form Designer.
  'Do not delete or move it.
  Private designerPlaceholderDeclaration As System.Object

  Private Sub Page_Init(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Init
    'CODEGEN: This method call is required by the Web Form Designer
    'Do not modify it using the code editor.
    InitializeComponent(  )
  End Sub

#End Region

  Private Sub Page_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load

    If Not IsPostBack Then

      ' call the method which creates the tables and the relations
      Dim ds As DataSet = CreateDataSet(  )

      ' set the data source for the grid to the first table 
      DataGrid1.DataSource = ds.Tables(0)
      DataGrid1.DataBind(  )

      BugConstraints.DataSource = ds.Tables("Bugs").Constraints
      BugConstraints.DataBind(  )

    End If

  End Sub

  'hand carved
  Private Function CreateDataSet(  ) As DataSet

    ' instantiate a new DataSet object that
    ' you will fill with tables and relations
    Dim dataSet As New DataSet

    ' make the bug table and its columns
    ' mimic the attributes from the SQL database
    Dim tblBugs As New DataTable("Bugs")

    Dim newColumn As New DataColumn ' hold the new columns as you create them

    newColumn = tblBugs.Columns.Add("BugID", Type.GetType("System.Int32"))
    newColumn.AutoIncrement = True     ' autoincrementing
    newColumn.AutoIncrementSeed = 1    ' starts at 1
    newColumn.AutoIncrementStep = 1    ' increments by 1
    newColumn.AllowDBNull = False      ' nulls not allowed

    ' or you can provide a named constraint
    Dim constraint As New UniqueConstraint("Unique_BugID", newColumn)
    tblBugs.Constraints.Add(constraint)

    ' create an array of columns for the primary key
    Dim columnArray(  ) As DataColumn = {New DataColumn}
    columnArray(0) = newColumn

    ' add the array to the Primary key property
    tblBugs.PrimaryKey = columnArray

    ' The Product column
    newColumn = tblBugs.Columns.Add("Product", Type.GetType("System.Int32"))
    newColumn.AllowDBNull = False
    newColumn.DefaultValue = 1

    ' save for foreign key creation
    Dim bugProductColumn As DataColumn = newColumn

    ' The Version column
    newColumn = tblBugs.Columns.Add("Version", Type.GetType("System.String"))
    newColumn.AllowDBNull = False
    newColumn.MaxLength = 50
    newColumn.DefaultValue = "0.1"

    ' The Description column
    newColumn = tblBugs.Columns.Add("Description", Type.GetType("System.String"))
    newColumn.AllowDBNull = False
    newColumn.MaxLength = 8000
    newColumn.DefaultValue = ""

    ' The Reporter column
    newColumn = tblBugs.Columns.Add("Reporter", Type.GetType("System.Int32"))
    newColumn.AllowDBNull = False

    ' save for foreign key creation
    Dim bugReporterColumn As DataColumn = newColumn

    ' Add rows based on the db schema you just created
    Dim newRow As DataRow       ' holds the new row 

    newRow = tblBugs.NewRow(  )
    newRow("Product") = 1
    newRow("Version") = "0.1"
    newRow("Description") = "Crashes on load"
    newRow("Reporter") = 5
    tblBugs.Rows.Add(newRow)

    newRow = tblBugs.NewRow(  )
    newRow("Product") = 1
    newRow("Version") = "0.1"
    newRow("Description") = "Does not report correct owner of bug"
    newRow("Reporter") = 5
    tblBugs.Rows.Add(newRow)

    newRow = tblBugs.NewRow(  )
    newRow("Product") = 1
    newRow("Version") = "0.1"
    newRow("Description") = "Does not show history of previous action"
    newRow("Reporter") = 6
    tblBugs.Rows.Add(newRow)

    newRow = tblBugs.NewRow(  )
    newRow("Product") = 1
    newRow("Version") = "0.1"
    newRow("Description") = "Fails to reload properly"
    newRow("Reporter") = 5

    tblBugs.Rows.Add(newRow)
    newRow = tblBugs.NewRow(  )
    newRow("Product") = 2
    newRow("Version") = "0.1"
    newRow("Description") = "Loses data overnight"
    newRow("Reporter") = 5
    tblBugs.Rows.Add(newRow)

    newRow = tblBugs.NewRow(  )
    newRow("Product") = 2
    newRow("Version") = "0.1"
    newRow("Description") = "HTML is not shown properly"
    newRow("Reporter") = 6
    tblBugs.Rows.Add(newRow)

    ' add the table to the dataset
    dataSet.Tables.Add(tblBugs)

    ' Product Table

    ' make the Products table and add the columns
    Dim tblProduct As New DataTable("lkProduct")
    newColumn = tblProduct.Columns.Add("ProductID", Type.GetType("System.Int32"))
    newColumn.AutoIncrement = True     ' autoincrementing
    newColumn.AutoIncrementSeed = 1    ' starts at 1
    newColumn.AutoIncrementStep = 1    ' increments by 1
    newColumn.AllowDBNull = False      ' nulls not allowed
    newColumn.Unique = True            ' each value must be unique

    newColumn = tblProduct.Columns.Add("ProductDescription", _
        Type.GetType("System.String"))
    newColumn.AllowDBNull = False
    newColumn.MaxLength = 8000
    newColumn.DefaultValue = ""

    newRow = tblProduct.NewRow(  )
    newRow("ProductDescription") = "BugX Bug Tracking"
    tblProduct.Rows.Add(newRow)

    newRow = tblProduct.NewRow(  )
    newRow("ProductDescription") = "PIM - My Personal Information Manager"
    tblProduct.Rows.Add(newRow)

    ' add the products table to the dataset 
    dataSet.Tables.Add(tblProduct)

    ' People

    ' make the People table and add the columns
    Dim tblPeople As New DataTable("People")
    newColumn = tblPeople.Columns.Add("PersonID", Type.GetType("System.Int32"))
    newColumn.AutoIncrement = True     ' autoincrementing
    newColumn.AutoIncrementSeed = 1    ' starts at 1
    newColumn.AutoIncrementStep = 1    ' increments by 1
    newColumn.AllowDBNull = False      ' nulls not allowed

    Dim uniqueConstraint As New UniqueConstraint("Unique_PersonID", newColumn)
    tblPeople.Constraints.Add(uniqueConstraint)

    ' stash away the PersonID column for the foreign
    ' key constraint
    Dim PersonIDColumn As DataColumn = newColumn

    columnArray(0) = newColumn
    tblPeople.PrimaryKey = columnArray

    newColumn = tblPeople.Columns.Add("FullName", Type.GetType("System.String"))
    newColumn.AllowDBNull = False
    newColumn.MaxLength = 8000
    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") = "Jesse Liberty"
    newRow("email") = "jliberty@libertyassociates.com"
    newRow("Phone") = "617-555-7301"
    newRow("Role") = 1
    tblPeople.Rows.Add(newRow)

    newRow = tblPeople.NewRow(  )
    newRow("FullName") = "Dan Hurwitz"
    newRow("email") = "dhurwitz@stersol.com"
    newRow("Phone") = "781-555-3375"
    newRow("Role") = 1
    tblPeople.Rows.Add(newRow)

    newRow = tblPeople.NewRow(  )
    newRow("FullName") = "John Galt"
    newRow("email") = "jGalt@franconia.com"
    newRow("Phone") = "617-555-9876"
    newRow("Role") = 1
    tblPeople.Rows.Add(newRow)

    newRow = tblPeople.NewRow(  )
    newRow("FullName") = "John Osborn"
    newRow("email") = "jOsborn@oreilly.com"
    newRow("Phone") = "617-555-3232"
    newRow("Role") = 3
    tblPeople.Rows.Add(newRow)

    newRow = tblPeople.NewRow(  )
    newRow("FullName") = "Ron Petrusha"
    newRow("email") = "ron@oreilly.com"
    newRow("Phone") = "707-555-0515"
    newRow("Role") = 2
    tblPeople.Rows.Add(newRow)

    newRow = tblPeople.NewRow(  )
    newRow("FullName") = "Tatiana Diaz"
    newRow("email") = "tatiana@oreilly.com"
    newRow("Phone") = "617-555-1234"
    newRow("Role") = 2
    tblPeople.Rows.Add(newRow)

    ' add the People table to the dataset
    dataSet.Tables.Add(tblPeople)

    ' create the Foreign Key constraint
    ' pass in the parent column from people
    ' and the child column from Bugs
    Dim fk As New ForeignKeyConstraint("FK_BugToPeople", PersonIDColumn, _
        bugReporterColumn)
    fk.DeleteRule = Rule.Cascade ' like father like son
    fk.UpdateRule = Rule.Cascade
    tblBugs.Constraints.Add(fk)  ' add the new constraint

    ' declare the DataRelation and DataColumn objects
    Dim dataRelation As System.Data.DataRelation
    Dim dataColumn1 As System.Data.DataColumn
    Dim dataColumn2 As System.Data.DataColumn

    ' set the dataColumns to create the relationship
    ' between Bug and BugHistory on the BugID key
    dataColumn1 = dataSet.Tables("People").Columns("PersonID")
    dataColumn2 = dataSet.Tables("Bugs").Columns("Reporter")

    dataRelation = New System.Data.DataRelation("BugsToReporter", _
        dataColumn1, dataColumn2)

    ' add the new DataRelation to the dataset
    dataSet.Relations.Add(dataRelation)

    Return dataSet

  End Function

End Class