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  method="post" runat="server">       <asp:DataGrid  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  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