Creating a DataGrid

The Visual Studio .NET development environment provides a very powerful control for displaying tabular data: the DataGrid. You can display a great deal of information from a table just by binding the table to the DataGrid.

Create a new C# or VB.NET project called SimpleADODataGrid. Drag a DataGrid control onto the form. Visual Studio will name it DataGrid1. Rename the DataGrid dgBugs and widen the grid to the width of the form.

In the constructor, retrieve the Bugs table from the database, much as you did in Example 19-2, except that this time you'll modify the select statement to retrieve all the fields from the bugs table:

string connectionString =
 "server=YourServer; uid=sa; 
 pwd=YourPassword; database=WindForms_Bugs";
 
// get records from the Bugs table
string commandString = 
 "Select * from Bugs";
 
// create the data set command object 
// and the DataSet
SqlDataAdapter dataAdapter = 
 new SqlDataAdapter(
 commandString, connectionString);
 
DataSet DataSet = new DataSet( );
 
// fill the data set object
dataAdapter.Fill(DataSet,"Bugs");
 
// Get the one table from the DataSet
DataTable dataTable = DataSet.Tables[0];

Public Sub New( )
 MyBase.New( )
 
 'This call is required by the Windows Form Designer.
 InitializeComponent( )
 
 Dim connectionString As String
 connectionString = _
 "Server=YourServer; uid=sa; pwd=YourPassword; database=WindForm_Bugs"
 
 Dim commandString As String
 commandString = "Select * from Bugs"
 
 Dim myDataAdapter As New System.Data.SqlClient.SqlDataAdapter(_ 
 commandString, connectionString)
 
 Dim myDataSet As New DataSet( )
 
 myDataAdapter.Fill(myDataSet, "Bugs")
 
 Dim myDataTable As DataTable
 myDataTable = myDataSet.Tables(0)
 
 End Sub

In this example, rather than binding to a ListBox, you'll bind to the DataGrid control. To do so, set the DataGrid control's DataSource property to dataTable, the DataTable object you get from the DataSet:

dgBugs.DataSource=dataTable;

dgBugs.DataSource=myDataTable

When you run the program, the DataGrid is populated, as shown in Figure 19-20.

Figure 19-20. A simple DataGrid

Notice that the columns in the DataGrid have titles. These are the titles of the columns from the Bug table. Unless you tell it otherwise, the DataGrid picks up the titles from the columns in the database. You'll see how to modify this later.

Some columns have numeric field IDs that do not convey a lot of information to the user. It would be better to substitute the name of the product (rather than Product 1 or Product 2) and the name of the person filing the report (rather than Reporter 1 or 5). Accomplish this by using a more sophisticated SQL select statement in the command string:

string commandString =
"Select b.BugID, b.Description, p.ProductDescription, 
peo.FullName from Bugs b join lkProduct p on b.Product = p.ProductID 
join People peo on b.Reporter = peo.PersonID ";

In this select statement, you draw fields from three tables: Bugs, Product, and People. Join the Product table to the Bugs table on the ProductID in the Bugs record, and join the People table on the PersonID of the Reporter field in Bugs.

The results are shown in Figure 19-21.

Figure 19-21. Using the join statement

19.7.1 Controlling Column Headings

In the previous example, the column heading was created from the columns in the database. While this is convenient, it is not quite ready for prime time. If you want to control the names of the columns, you can do so, but it is tricky.

Here's how it works. Your DataGrid has a GridTableStylesCollection that contains DataGridTableStyle objects. Each DataGridTableStyle object represents the style for one table represented in the grid.

Within each DataGridTableStyle object is a GridColumnStylesCollection, which contains instances of type DataGridColumnStyle. To control the presentation of your columns, you'll create a DataGridColumnStyle object for each column and set its HeaderText to the text you want to display. You'll also map the DataGridColumnStyle object to the appropriate column in your data table.

Here are the steps:

1. Create an instance of a DataTable and assign to it a table from your DataSet.

   

   DataTable dataTable = DataSet.Tables[0];

   

   Dim myDataTable As DataTable
   myDataTable = myDataSet.Tables(0)

2. For each table you will display in the grid you must create a DataGridTableStyle object.

   

   DataGridTableStyle tableStyle = new DataGridTableStyle( );

   

   Dim tableStyle As New DataGridTableStyle( )

3. Set the DataGridTableStyle object's MappingName to the TableName property you get from your DataTable obtained in Step 1:

   

   tableStyle.MappingName = dataTable.TableName;

   

   tableStyle.MappingName = myDataTable.TableName

4. Create an instance of GridColumnStylesCollection and initialize it with the collection returned by the GridColumnStyles property of the DataGridTableStyle object you created in Step 2:

   

   GridColumnStylesCollection columnStyles = tableStyle.GridColumnStyles;

5. Create a DataGridColumnStyle object for each column you want to add to the grid.

   

   DataGridColumnStyle columnStyle = new DataGridTextBoxColumn( );

   

   Dim columnStyles As GridColumnStylesCollection = _ tableStyle.GridColumnStyles

6. For each DataGridColumnStyle, set the Mapping name to a column name from the data, and set the HeaderText to the text you want to appear in the header of the column.

   

   columnStyle.MappingName="BugID";
   columnStyle.HeaderText = "Bug ID";

7. Add each DataGridColumnStyle object to the GridColumnStyles Collection:

   

   columnStyles.Add(columnStyle);

8. When all the DataGridColumnStyle objects are in the GridColumnStyles collection, create an instance of GridTableStylesCollection by obtaining the TableStyles property from the DataGrid.

   

   GridTableStylesCollection tableStyles = dgBugs.TableStyles;

   

   Dim tableStyles As GridTableStylesCollection = _
   dgBugs.TableStyles( )

9. Add the DataGridTableStyle object you created in Step 2 to the GridTableStylesCollection you obtained in Step 8.

   

   tableStyles.Add(tableStyle);

Piece of cake. The code in Example 19-12 illustrates these steps in action. Example 19-13 shows the same code in VB.NET:

Example 19-12. Adding column styles in C#

public Form1( )
{
 //
 // Required for Windows Form Designer support
 //
 InitializeComponent( );
 string connectionString = 
 "server=YourServer; uid=sa; pwd=YourPassword; database=WindForm_Bugs";
 
 // get records from the Bugs table
 string commandString = 
 "Select b.BugID, b.Description, p.ProductDescription, ";
 commandString += "peo.FullName from Bugs b ";
 commandString += "join lkProduct p on b.Product = p.ProductID ";
 commandString += "join People peo on b.Reporter = peo.PersonID ";
 
 // create the data set command object 
 // and the DataSet
 SqlDataAdapter dataAdapter = 
 new SqlDataAdapter(
 commandString, connectionString);
 
 DataSet DataSet = new DataSet( );
 
 // fill the data set object
 dataAdapter.Fill(DataSet,"Bugs");
 
 // Get the one table from the DataSet
 DataTable dataTable = DataSet.Tables[0];
 
 // create a DataGridTableStyle object and initialize
 // based on the dataTable's TableName.
 DataGridTableStyle tableStyle = new DataGridTableStyle( );
 tableStyle.MappingName = dataTable.TableName;
 
 // Get the GridColumnsStylesCollection 
 // from the table style's GridColumnStyles collection
 GridColumnStylesCollection columnStyles = tableStyle.GridColumnStyles;
 
 // Make a columnStyle object and add column info to it
 // then add the columnStyle to the columnStyles collection
 DataGridColumnStyle columnStyle = new DataGridTextBoxColumn( );
 columnStyle.MappingName="BugID";
 columnStyle.HeaderText = "Bug ID";
 columnStyles.Add(columnStyle);
 
 columnStyle = new DataGridTextBoxColumn( );
 columnStyle.MappingName = "ProductDescription";
 columnStyle.HeaderText="Product";
 columnStyles.Add(columnStyle);
 
 columnStyle = new DataGridTextBoxColumn( );
 columnStyle.MappingName = "Description";
 columnStyle.HeaderText="Description";
 columnStyles.Add(columnStyle);
 
 columnStyle = new DataGridTextBoxColumn( );
 columnStyle.MappingName = "FullName";
 columnStyle.HeaderText="Reporter";
 columnStyles.Add(columnStyle);
 
 // Get the table Styles collection from the gird
 // Add the tableStyle object just created
 GridTableStylesCollection tableStyles = dgBugs.TableStyles;
 tableStyles.Add(tableStyle);
 
 dgBugs.DataSource=dataTable;
}

Example 19-13. Column headings in VB.NET

 Public Sub New( )
 MyBase.New( )
 
 InitializeComponent( )
 
 Dim connectionString As String
 connectionString = _
 "Server=YourServer; uid=sa; pwd=YourPW; database=Bugs"
 
 
 Dim commandString As String
 commandString = _
 "Select b.BugID, b.Description, p.ProductDescription, "
 commandString += "peo.FullName from Bugs b "
 commandString += "join lkProduct p on b.Product = p.ProductID "
 commandString += "join People peo on b.Reporter = peo.PersonID "
 
 Dim myDataAdapter As New _
 System.Data.SqlClient.SqlDataAdapter( _
 commandString, connectionString)
 
 Dim myDataSet As New DataSet( )
 
 myDataAdapter.Fill(myDataSet, "Bugs")
 
 Dim myDataTable As DataTable
 myDataTable = myDataSet.Tables(0)
 
' create a DataGridTableStyle object and initialize
' based on the dataTable's TableName.
Dim tableStyle As New DataGridTableStyle( )
tableStyle.MappingName = myDataTable.TableName
 
' Get the GridColumnsStylesCollection 
' from the table style's GridColumnStyles collection
Dim columnStyles As GridColumnStylesCollection = tableStyle.GridColumnStyles
 
' Make a columnStyle object and add column info to it
' then add the columnStyle to the columnStyles collection
Dim columnStyle As New DataGridTextBoxColumn( )
columnStyle.MappingName = "BugID"
columnStyle.HeaderText = "Bug ID"
columnStyles.Add(columnStyle)
 
columnStyle = New DataGridTextBoxColumn( )
columnStyle.MappingName = "ProductDescription"
columnStyle.HeaderText = "Product"
columnStyles.Add(columnStyle)
 
columnStyle = New DataGridTextBoxColumn( )
columnStyle.MappingName = "Description"
columnStyle.HeaderText = "Description"
columnStyles.Add(columnStyle)
 
columnStyle = New DataGridTextBoxColumn( )
columnStyle.MappingName = "FullName"
columnStyle.HeaderText = "Reporter"
columnStyles.Add(columnStyle)
 
' Get the table Styles collection from the gird
' Add the tableStyle object just created
Dim tableStyles As GridTableStylesCollection = dgBugs.TableStyles( )
tableStyles.Add(tableStyle)
 dgBugs.DataSource = myDataTable
End Sub

The result is that the columns are displayed with the column heading you asked for in the order you've added them to the collection, as shown in Figure 19-22.

Figure 19-22. Using column styles

19.7.2 Displaying Parent/Child Relationships

The relationship between Bugs and BugHistory is that every Bug includes a BugID, which is a primary key in Bugs and a foreign key in BugHistory. Thus, you have a one-to-many relationship in which one Bug relates to many BugHistory records, but each BugHistory relates to exactly one Bug order. This is also known as a parent/child relationship, with the Bug record as parent and the BugHistory records as children.

You can model that relationship in your DataSet and use a DataGrid to display the relationship. The DataGrid is designed to reflect the parent/child relationship by putting a plus mark next to each Bug record. When the user clicks on the plus mark, a link is displayed. Clicking on the link brings up a DataGrid with the child records (the bug history entries for that bug), as shown in Figures Figure 19-23 and Figure 19-24.

Figure 19-23. DataGrid with child records

Figure 19-24. BugHistory child records

Notice in Figure 19-23 that the Bug for which you are seeing the History records is shown in a row across the top. You can navigate back to the bug record by clicking on the white left-pointing arrow in the upper-righthand corner.

To model the relationship between the Bugs table and the BugHistory table, you will need two command objects and two DataAdapter objects. Use them to fill two tables within the DataSet.

Once your two tables are filled, you'll create references to two DataColumn objects that represent the Bugs column in each table, and you'll use these DataColumn objects to create a DataRelation object that will encapsulate the relationship between the two tables.

The complete listing is shown in Example 19-14 and Example 19-15, and is followed by a detailed analysis.

Example 19-14. Parent/child grid (C#)

public class Form1 : System.Windows.Forms.Form
{
 private System.Data.SqlClient.SqlConnection connection;
 private System.Data.DataSet DataSet;
 private System.Data.SqlClient.SqlCommand bugCommand;
 private System.Data.SqlClient.SqlCommand historyCommand;
 private System.Data.SqlClient.SqlDataAdapter bugDataAdapter;
 private System.Data.SqlClient.SqlDataAdapter historyDataAdapter;
 private System.Windows.Forms.DataGrid dgBugs;
 private System.ComponentModel.Container components = null;
 
 public Form1( )
 {
 InitializeComponent( );
 
 string connectionString = 
 "server=YourServer; uid=sa; pwd=YourPassword; database=WindForm_Bugs";
 
 // create the connection, open it, and create the DataSet
 connection = new 
 System.Data.SqlClient.SqlConnection(connectionString);
 connection.Open( );
 DataSet = new System.Data.DataSet( );
 DataSet.CaseSensitive=true;
 
 //The first command gets the bugs table
 string bugsCommandString = "Select * from bugs";
 bugCommand = new System.Data.SqlClient.SqlCommand( );
 bugCommand.Connection=connection;
 bugCommand.CommandText= bugsCommandString;
 
 // the second command gets the history table
 string historyCommandString = "Select * from bugHistory";
 historyCommand = new System.Data.SqlClient.SqlCommand( );
 historyCommand.Connection=connection;
 historyCommand.CommandText= historyCommandString;
 
 // create a dataAdapter to get the Bugs table and use it
 // to populate the dataset
 bugDataAdapter = new SqlDataAdapter( );
 bugDataAdapter.SelectCommand = bugCommand;
 bugDataAdapter.TableMappings.Add("Table", "Bugs");
 bugDataAdapter.Fill(DataSet);
 
 // create a dataAdapter to get the history table and use it
 // to populate the dataset
 historyDataAdapter = new SqlDataAdapter( );
 historyDataAdapter.SelectCommand = historyCommand;
 historyDataAdapter.TableMappings.Add("Table", "BugHistory");
 historyDataAdapter.Fill(DataSet);
 
 // create a DataRelation object and references to two 
 // dataColumn objects
 System.Data.DataColumn dataColumn1;
 System.Data.DataColumn dataColumn2;
 
 // Use the dataColumns to represent the Bugs field in both tables
 dataColumn1 = DataSet.Tables["Bugs"].Columns["BugID"];
 dataColumn2 = DataSet.Tables["BugHistory"].Columns["BugID"];
 
 // Instantiate the DataRelation object with the two columns
 // name the relationship BugsToHistory
 DataRelation bugsToHistory = new System.Data.DataRelation(
 "BugsToHistory",dataColumn1, dataColumn2);
 
 // Add the DataRelation object to the Relations table in 
 // the dataset
 DataSet.Relations.Add(bugsToHistory);
 
 // Bind the DataSet to the DataGrid
 DataViewManager dataView = DataSet.DefaultViewManager;
 dgBugs.DataSource= dataView;
 
 }

Example 19-15. Parent/child relationships (VB.NET)

Public Class Form1
 Inherits System.Windows.Forms.Form
 
 Private myConnection As System.Data.SqlClient.SqlConnection
 Private myDataSet As System.Data.DataSet
 Private bugCommand As System.Data.SqlClient.SqlCommand
 Private historyCommand As System.Data.SqlClient.SqlCommand
 Private bugDataAdapter As System.Data.SqlClient.SqlDataAdapter
 Private historyDataAdapter As System.Data.SqlClient.SqlDataAdapter
#Region " Windows Form Designer generated code "
#End Region
 
 Public Sub New( )
 MyBase.New( )
 
 'This call is required by the Windows Form Designer.
 InitializeComponent( )
 
 Dim connectionString As String
 connectionString = _
 "Server=YourServer; uid=sa; pwd=YourPassword; database=WindForm_Bugs"
 
 ' create the connection, open it, and create the DataSet
 myConnection = New _
 System.Data.SqlClient.SqlConnection(connectionString)
 myConnection.Open( )
 myDataSet = New System.Data.DataSet( )
 myDataSet.CaseSensitive = True
 
 'The first command gets the bugs table
 Dim bugCommandString As String
 bugCommandString = "Select * from bugs"
 bugCommand = New System.Data.SqlClient.SqlCommand( )
 bugCommand.Connection = myConnection
 bugCommand.CommandText = bugCommandString
 
 'the second command gets the history table
 Dim historyCommandString As String
 historyCommandString = "Select * from bugHistory"
 historyCommand = New System.Data.SqlClient.SqlCommand( )
 historyCommand.Connection = myConnection
 historyCommand.CommandText = historyCommandString
 
 ' create the dataAdapter to get the Bugs table and use 
 ' it to populate the dataset
 bugDataAdapter = New SqlDataAdapter( )
 bugDataAdapter.SelectCommand = bugCommand
 bugDataAdapter.TableMappings.Add("Table", "Bugs")
 bugDataAdapter.Fill(myDataSet)
 
 
 ' create the dataAdapter to get the history table and 
 ' use it to populate the dataset
 historyDataAdapter = New SqlDataAdapter( )
 historyDataAdapter.SelectCommand = historyCommand
 historyDataAdapter.TableMappings.Add("Table", "BugHistory")
 historyDataAdapter.Fill(myDataSet)
 
 Dim dataColumn1 As DataColumn
 Dim dataColumn2 As DataColumn
 dataColumn1 = myDataSet.Tables("Bugs").Columns("BugID")
 dataColumn2 = myDataSet.Tables("BugHistory").Columns("BugID")
 
 Dim bugHistory As New DataRelation("BugsToHistory", dataColumn1, dataColumn2)
 
 myDataSet.Relations.Add(bugHistory)
 
 Dim dataView As DataViewManager = myDataSet.DefaultViewManager
 dgBugs.DataSource = dataView
 
 End Sub
 
End Class

Begin by creating a command and dataAdapter objects as member variables:

private System.Data.SqlClient.SqlCommand bugCommand;
private System.Data.SqlClient.SqlCommand historyCommand;
private System.Data.SqlClient.SqlDataAdapter bugDataAdapter;
private System.Data.SqlClient.SqlDataAdapter historyDataAdapter;

Private bugCommand As System.Data.SqlClient.SqlCommand
Private historyCommand As System.Data.SqlClient.SqlCommand
Private bugDataAdapter As System.Data.SqlClient.SqlDataAdapter
Private historyDataAdapter As System.Data.SqlClient.SqlDataAdapter

Within the constructor, create the connection, and then create the command objects, using select statements to retrieve all the fields in the two tables Bugs and BugHistory:

string bugsCommandString = "Select * from bugs";
bugCommand = new System.Data.SqlClient.SqlCommand( );
bugCommand.Connection=connection;
bugCommand.CommandText= bugsCommandString;
 
string historyCommandString = "Select * from bugHistory";
historyCommand = new System.Data.SqlClient.SqlCommand( );
historyCommand.Connection=connection;
historyCommand.CommandText= historyCommandString;

Dim bugCommandString As String
bugCommandString = "Select * from bugs"
bugCommand = New System.Data.SqlClient.SqlCommand( )
bugCommand.Connection = myConnection
bugCommand.CommandText = bugCommandString
 
Dim historyCommandString As String
historyCommandString = "Select * from bugHistory"
historyCommand = New System.Data.SqlClient.SqlCommand( )
historyCommand.Connection = myConnection
historyCommand.CommandText = historyCommandString

Create a DataAdapter for the Bugs table, and use that adapter to create a Bugs table within the DataSet. Then do the same for the BugHistory table (in C#; the VB.NET version is nearly identical):

bugDataAdapter = new SqlDataAdapter( );
bugDataAdapter.SelectCommand = bugCommand;
bugDataAdapter.TableMappings.Add("Table", "Bugs");
bugDataAdapter.Fill(DataSet);
 
historyDataAdapter = new SqlDataAdapter( );
historyDataAdapter.SelectCommand = historyCommand;
historyDataAdapter.TableMappings.Add("Table", "BugHistory");
historyDataAdapter.Fill(DataSet);

Your DataSet now has two tables: Bugs and BugHistory. You are ready to create the relationship between the tables. Each DataSet has a Relations collection that consists of DataRelation objects. A DataRelation object maps the relationship between two columns through DataColumn objects. Each DataColumn object represents a column in a table. A DataRelation encapsulates the primary/foreign key relationship through those columns:

System.Data.DataColumn dataColumn1;
System.Data.DataColumn dataColumn2;

Dim dataColumn1 As DataColumn
Dim dataColumn2 As DataColumn

Start by creating DataColumn objects for the two data columns in Bugs and BugHistory, respectively (in C#; the VB.NET version is identical except for the semicolon):

dataColumn1 = DataSet.Tables["Bugs"].Columns["BugID"];
dataColumn2 = DataSet.Tables["BugHistory"].Columns["BugID"];

Instantiate the DataRelation object, passing in the two DataColumn objects and a string representing the name of the relationship ("BugsToHistory"):

DataRelation bugsToHistory = new System.Data.DataRelation(
 "BugsToHistory",dataColumn1, dataColumn2);

Dim bugsToHistory As New DataRelation("BugsToHistory", _
 dataColumn1, dataColumn2)

Add the new DataRelation object to the Relations collection in your DataSet:

myDataSet.Relations.Add(bugsToHistory)

Extract the default view from the DataSet and bind the grid to that view:

DataViewManager dataView = DataSet.DefaultViewManager;
dgBugs.DataSource= dataView;

Dim dataView As DataViewManager = myDataSet.DefaultViewManager
dgBugs.DataSource = dataView

The DataGrid will display the Bugs table members, recognize the relationship, and automatically put in the links for the BugHistory records.

19.7.3 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 the Bugs table and specifying its data structure. You'll then fill that table with records. You'll do the same for the lkProducts 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 create a data relation tying two tables together. It sounds like more work than it really is.

The complete source is shown in Example 19-16 for C# and in Example 19-17 for VB.NET, followed by a detailed analysis. Create a new Windows project and name it DataSetByHand. Add a DataGrid to the form, and size it large enough to display a few records, as shown in Figure 19-25.

Figure 19-25. DataSet by hand design form

Example 19-16. DataSet built by hand in C#

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
 
 UniqueConstraint constraint = 
 new UniqueConstraint("UniqueBugID",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);
 
 
 DataTable tblBugHistory = new DataTable("BugHistory");
 
 
 newColumn = 
 tblBugHistory.Columns.Add(
 "BugHistoryID", 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
 
 
 constraint = new UniqueConstraint("UniqueBugHistoryID",newColumn);
 tblBugHistory.Constraints.Add(constraint);
 
 
 DataColumn secondColumn;
 secondColumn = tblBugHistory.Columns.Add(
 "BugID", Type.GetType("System.Int32"));
 secondColumn.AllowDBNull=false;
 secondColumn.DefaultValue = 1;
 
 // create an array of columns for the primary keys
 columnArray = new DataColumn[2];
 columnArray[0] = newColumn;
 columnArray[1] = secondColumn; 
 
 // add the array to the Primary key property
 tblBugHistory.PrimaryKey=columnArray;
 
 // The Status column
 newColumn = tblBugHistory.Columns.Add(
 "Status", Type.GetType("System.Int32"));
 newColumn.AllowDBNull=false;
 newColumn.DefaultValue = 1;
 
 // The Severity column
 newColumn = tblBugHistory.Columns.Add(
 "Severity", Type.GetType("System.Int32"));
 newColumn.AllowDBNull=false;
 newColumn.DefaultValue = 1;
 
 // The Response column
 newColumn = tblBugHistory.Columns.Add(
 "Response", Type.GetType("System.String"));
 newColumn.AllowDBNull=false;
 newColumn.MaxLength=8000;
 newColumn.DefaultValue = "";
 
 // The Owner column
 newColumn = tblBugHistory.Columns.Add(
 "Owner", Type.GetType("System.Int32"));
 newColumn.AllowDBNull=false;
 newColumn.DefaultValue = 1;
 
 // The DateStamp column
 newColumn = tblBugHistory.Columns.Add(
 "DateStamp", Type.GetType("System.DateTime"));
 newColumn.AllowDBNull=false;
 newColumn.DefaultValue = System.DateTime.Now;
 
 
 // Add rows based on the db schema you just created
 newRow = tblBugHistory.NewRow( );
 newRow["bugID"] = 1;
 newRow["Status"] = "1";
 newRow["Severity"] = "2";
 newRow["Response"] = "Created";
 newRow["Owner"] = 1;
 newRow["DateStamp"] = System.DateTime.Now;
 tblBugHistory.Rows.Add(newRow);
 
 newRow = tblBugHistory.NewRow( );
 newRow["bugID"] = 1;
 newRow["Status"] = "2";
 newRow["Severity"] = "2";
 newRow["Response"] = "Assigned to Jesse";
 newRow["Owner"] = 1;
 newRow["DateStamp"] = System.DateTime.Now;
 tblBugHistory.Rows.Add(newRow);
 
 newRow = tblBugHistory.NewRow( );
 newRow["bugID"] = 1;
 newRow["Status"] = "3";
 newRow["Severity"] = "2";
 newRow["Response"] = "I'll Look into it";
 newRow["Owner"] = 1;
 newRow["DateStamp"] = System.DateTime.Now;
 tblBugHistory.Rows.Add(newRow);
 
 newRow = tblBugHistory.NewRow( );
 newRow["bugID"] = 1;
 newRow["Status"] = "4";
 newRow["Severity"] = "2";
 newRow["Response"] = "Fixed by resetting initial values";
 newRow["Owner"] = 1;
 newRow["DateStamp"] = System.DateTime.Now;
 tblBugHistory.Rows.Add(newRow);
 
 newRow = tblBugHistory.NewRow( );
 newRow["bugID"] = 2;
 newRow["Status"] = "1";
 newRow["Severity"] = "3";
 newRow["Response"] = "Created";
 newRow["Owner"] = 1;
 newRow["DateStamp"] = System.DateTime.Now;
 tblBugHistory.Rows.Add(newRow);
 
 
 newRow = tblBugHistory.NewRow( );
 newRow["bugID"] = 2;
 newRow["Status"] = "2";
 newRow["Severity"] = "3";
 newRow["Response"] = "Assigned to Jesse";
 newRow["Owner"] = 1;
 newRow["DateStamp"] = System.DateTime.Now;
 tblBugHistory.Rows.Add(newRow);
 
 // add the table to the dataset
 DataSet.Tables.Add(tblBugHistory);
 
 
 
 // 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 data set
 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(
 "UniquePersonID",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"] = "[e-mail]";
 newRow["Phone"] = "617-555-7301";
 newRow["Role"] = 1;
 tblPeople.Rows.Add(newRow);
 
 newRow = tblPeople.NewRow( );
 newRow["FullName"] = "Dan Hurwitz";
 newRow["email"] = "[e-mail]";
 newRow["Phone"] = "781-555-3375";
 newRow["Role"] = 1;
 tblPeople.Rows.Add(newRow);
 
 newRow = tblPeople.NewRow( );
 newRow["FullName"] = "John Galt";
 newRow["email"] = "[e-mail]";
 newRow["Phone"] = "617-555-9876";
 newRow["Role"] = 1;
 tblPeople.Rows.Add(newRow);
 
 newRow = tblPeople.NewRow( );
 newRow["FullName"] = "John Osborn";
 newRow["email"] = "[e-mail]";
 newRow["Phone"] = "617-555-3232";
 newRow["Role"] = 3;
 tblPeople.Rows.Add(newRow);
 
 newRow = tblPeople.NewRow( );
 newRow["FullName"] = "Ron Petrusha";
 newRow["email"] = "[e-mail]";
 newRow["Phone"] = "707-555-0515";
 newRow["Role"] = 2;
 tblPeople.Rows.Add(newRow);
 
 newRow = tblPeople.NewRow( );
 newRow["FullName"] = "Tatiana Diaz";
 newRow["email"] = "[e-mail]";
 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["Bugs"].Columns["BugID"];
 dataColumn2 = 
 DataSet.Tables["BugHistory"].Columns["BugID"];
 
 dataRelation = 
 new System.Data.DataRelation(
 "BugsToHistory", 
 dataColumn1, 
 dataColumn2);
 
 // add the new DataRelation to the dataset
 DataSet.Relations.Add(dataRelation); 
 
 return DataSet;
 
}
public Form1( )
{
 //
 // Required for Windows Form Designer support
 //
 InitializeComponent( );
 
 DataSet ds = CreateDataSet( );
 dgBugs.DataSource = ds.Tables[0];
}

Example 19-17. DataSet built by hand in VB.NET

Public Class Form1
 Inherits System.Windows.Forms.Form
 
#Region " Windows Form Designer generated code "
 
 Public Sub New( )
 MyBase.New( )
 
 'This call is required by the Windows Form Designer.
 InitializeComponent( )
 Dim ds As DataSet = CreateDataSet( )
 dgBugs.DataSource = ds.Tables(0)
 
 
 'Add any initialization after the InitializeComponent( ) call
 
 End Sub
 
 
#End Region
 Private Function CreateDataSet( ) As DataSet
 
 ' instantiate a new DataSet object that
 ' you will fill with tables and relations
 Dim myDataSet 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 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
 
 Dim constraint As _
 New UniqueConstraint("UniqueBugID", newColumn)
 tblBugs.Constraints.Add(Constraint)
 
 ' create an array of columns for the primary key
 Dim columnArray(1) As 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
 myDataSet.Tables.Add(tblBugs)
 
 
 Dim tblBugHistory As New DataTable("BugHistory")
 
 
 newColumn = tblBugHistory.Columns.Add( _
 "BugHistoryID", 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
 
 
 constraint = New UniqueConstraint("UniqueBugHistoryID", newColumn)
 tblBugHistory.Constraints.Add(constraint)
 
 
 Dim secondColumn As New DataColumn( )
 secondColumn = tblBugHistory.Columns.Add( _
 "BugID", Type.GetType("System.Int32"))
 secondColumn.AllowDBNull = False
 secondColumn.DefaultValue = 1
 
 ' create an array of columns for the primary keys
 
 Dim secondColumnArray(2) As DataColumn
 secondColumnArray(0) = newColumn
 secondColumnArray(1) = secondColumn
 
 ' add the array to the Primary key property
 tblBugHistory.PrimaryKey = secondColumnArray
 
 ' The Status column
 newColumn = tblBugHistory.Columns.Add( _
 "Status", Type.GetType("System.Int32"))
 newColumn.AllowDBNull = False
 newColumn.DefaultValue = 1
 
 ' The Severity column
 newColumn = tblBugHistory.Columns.Add( _
 "Severity", Type.GetType("System.Int32"))
 newColumn.AllowDBNull = False
 newColumn.DefaultValue = 1
 
 ' The Response column
 newColumn = tblBugHistory.Columns.Add( _
 "Response", Type.GetType("System.String"))
 newColumn.AllowDBNull = False
 newColumn.MaxLength = 8000
 newColumn.DefaultValue = ""
 
 ' The Owner column
 newColumn = tblBugHistory.Columns.Add( _
 "Owner", Type.GetType("System.Int32"))
 newColumn.AllowDBNull = False
 newColumn.DefaultValue = 1
 
 ' The DateStamp column
 newColumn = tblBugHistory.Columns.Add( _
 "DateStamp", Type.GetType("System.DateTime"))
 newColumn.AllowDBNull = False
 newColumn.DefaultValue = System.DateTime.Now
 
 
 ' Add rows based on the db schema you just created
 newRow = tblBugHistory.NewRow( )
 newRow("bugID") = 1
 newRow("Status") = "1"
 newRow("Severity") = "2"
 newRow("Response") = "Created"
 newRow("Owner") = 1
 newRow("DateStamp") = System.DateTime.Now
 tblBugHistory.Rows.Add(newRow)
 
 newRow = tblBugHistory.NewRow( )
 newRow("bugID") = 1
 newRow("Status") = "2"
 newRow("Severity") = "2"
 newRow("Response") = "Assigned to Jesse"
 newRow("Owner") = 1
 newRow("DateStamp") = System.DateTime.Now
 tblBugHistory.Rows.Add(newRow)
 
 newRow = tblBugHistory.NewRow( )
 newRow("bugID") = 1
 newRow("Status") = "3"
 newRow("Severity") = "2"
 newRow("Response") = "I'll Look into it"
 newRow("Owner") = 1
 newRow("DateStamp") = System.DateTime.Now
 tblBugHistory.Rows.Add(newRow)
 
 newRow = tblBugHistory.NewRow( )
 newRow("bugID") = 1
 newRow("Status") = "4"
 newRow("Severity") = "2"
 newRow("Response") = "Fixed by resetting initial values"
 newRow("Owner") = 1
 newRow("DateStamp") = System.DateTime.Now
 tblBugHistory.Rows.Add(newRow)
 
 newRow = tblBugHistory.NewRow( )
 newRow("bugID") = 2
 newRow("Status") = "1"
 newRow("Severity") = "3"
 newRow("Response") = "Created"
 newRow("Owner") = 1
 newRow("DateStamp") = System.DateTime.Now
 tblBugHistory.Rows.Add(newRow)
 
 
 newRow = tblBugHistory.NewRow( )
 newRow("bugID") = 2
 newRow("Status") = "2"
 newRow("Severity") = "3"
 newRow("Response") = "Assigned to Jesse"
 newRow("Owner") = 1
 newRow("DateStamp") = System.DateTime.Now
 tblBugHistory.Rows.Add(newRow)
 
 ' add the table to the dataset
 myDataSet.Tables.Add(tblBugHistory)
 
 
 
 ' 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 data set
 myDataSet.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 myUniqueConstraint As New UniqueConstraint( _
 "UniquePersonID", newColumn)
 tblPeople.Constraints.Add(myUniqueConstraint)
 
 ' stash away the PersonID column for the foreign
 ' key constraint
 Dim PersonIDColumn As DataColumn = newColumn
 
 Dim thirdColumnArray(1) As DataColumn
 thirdColumnArray(0) = newColumn
 tblPeople.PrimaryKey = thirdColumnArray
 
 
 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") = "[e-mail]"
 newRow("Phone") = "617-555-7301"
 newRow("Role") = 1
 tblPeople.Rows.Add(newRow)
 
 newRow = tblPeople.NewRow( )
 newRow("FullName") = "Dan Hurwitz"
 newRow("email") = "[e-mail]"
 newRow("Phone") = "781-555-3375"
 newRow("Role") = 1
 tblPeople.Rows.Add(newRow)
 
 newRow = tblPeople.NewRow( )
 newRow("FullName") = "John Galt"
 newRow("email") = "[e-mail]"
 newRow("Phone") = "617-555-9876"
 newRow("Role") = 1
 tblPeople.Rows.Add(newRow)
 
 newRow = tblPeople.NewRow( )
 newRow("FullName") = "John Osborn"
 newRow("email") = "[e-mail]"
 newRow("Phone") = "617-555-3232"
 newRow("Role") = 3
 tblPeople.Rows.Add(newRow)
 
 newRow = tblPeople.NewRow( )
 newRow("FullName") = "Ron Petrusha"
 newRow("email") = "[e-mail]"
 newRow("Phone") = "707-555-0515"
 newRow("Role") = 2
 tblPeople.Rows.Add(newRow)
 
 newRow = tblPeople.NewRow( )
 newRow("FullName") = "Tatiana Diaz"
 newRow("email") = "[e-mail]"
 newRow("Phone") = "617-555-1234"
 newRow("Role") = 2
 tblPeople.Rows.Add(newRow)
 
 ' add the People table to the dataset
 myDataSet.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 myDataRelation 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 = myDataSet.Tables("Bugs").Columns("BugID")
 dataColumn2 = myDataSet.Tables("BugHistory").Columns("BugID")
 
 myDataRelation = New System.Data.DataRelation( _
 "BugsToHistory", dataColumn1, dataColumn2)
 
 ' add the new DataRelation to the dataset
 myDataSet.Relations.Add(myDataRelation)
 
 Return myDataSet
 
 End Function
 
End Class

This code centers on a method named CreateDataSet that will be responsible for creating the DataSet and returning it to the constructor. You will create this DataSet by hand (not from a DataBase), so you must fill it with DataTables. Each DataTable must be created by hand, and you are responsible for adding each column to the table and then establishing any constraints or key fields, again by hand. Finally, you will fill the table with rows of data.

Once you've created the DataSet in the CreateDataSet method, return to the form's constructor where you assign the returned value to a reference to a DataSet. You can then extract the first table and set it as the DataGrid's DataSource.

DataSet ds = CreateDataSet( );
dgBugs.DataSource = ds.Tables[0];

19.7.4 Creating the DataSet by Hand

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");

Dim tblBugs As New DataTable("Bugs")

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

Figure 19-26. The structure of the Bugs table in SQL Server

To add a column to this DataTable object, do not call a constructor. Instead, 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"));

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

19.7.4.1 Setting column properties

The Add method creates the new column and returns a reference to it, which you may now manipulate. Since it will be an identity column (see the highlighted area of Figure 19-26), you'll want to set its AutoIncrement property to true and 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.

 

19.7.4.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 is unique:

newColumn.Unique=true;

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

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

Dim constraint As _
 New UniqueConstraint("UniqueBugID", newColumn)

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

tblBugs.Constraints.Add(constraint);

You may call your constraint anything you like. It is good programming practice to call it something that identifies the constraint clearly.

If you do add a named constraint, be sure to comment out the Unique property. Creating a primary key is covered later in this chapter, in Section 19.7.5.

This completes the first column in the table. The second column is the Product column. Notice that this column is of type integer, with no nulls and a default value of 1 (see the highlighted property in Figure 19-27). 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. Then set the AllowDBNull property as you did with the earlier column, and set the DefaultValue property to set the default value for the column. This is illustrated in the code fragment shown next.

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

Figure 19-27. The Product column

Looking at Figure 19-27 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 about the largest string you expect in the field.

Declare the column type to be string for a varchar and 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";

Declare the Description and Reporter columns similarly:

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;

19.7.4.3 Adding data to the table

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

newRow = tblBugs.NewRow( );

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 DataRow 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, add the row to the table's Rows collection by calling the Add method and pass in the row you just created:

tblBugs.Rows.Add(newRow);

You are now ready to create another 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 are created, you can create an instance of a DataSet object and add the table:

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

19.7.4.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 the BugHistory:

DataTable tblBugHistory = new DataTable("BugHistory");

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

19.7.4.5 Adding rows with an array of objects

The DataRowCollection object's Add method is overloaded. In the code shown earlier, you created a new DataRow object, populated its columns, and added the row. You can also 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"] = "[e-mail]";
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 in the row:

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

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 creating an array of objects, you must do it by hand.

19.7.5 Creating Primary Keys

The Bugs table uses the PersonID as a foreign key into the People table. To recreate this, first create a primary key in the People table.

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("UniquePersonID",newColumn);
tblPeople.Constraints.Add(uniqueConstraint);

To create the primary key, set the table's PrimaryKey property. 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 Bugs table is a single column: BugID. To set the primary key, create an array (in this case, with one member), and assign to that member the column(s) you want to make the primary key.

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

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

tblBugs.PrimaryKey=columnArray;

The BugHistory table has a more complex Primary Key, consisting of the BugID and the BugHistoryID. Create the Primary Key by holding a reference to the two columns and creating an array with two members:

columnArray = new DataColumn[2];
columnArray[0] = newColumn;
columnArray[1] = secondColumn; 
tblBugHistory.PrimaryKey=columnArray;

19.7.6 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, 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, 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 a ForeignKeyConstraint named fk. Before you add it to the Bugs table, 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 19-6.

Table 19-6. The rows enumeration

Member name	Description
Cascade	Delete or update related rows (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 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);

19.7.7 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 handcrafted 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;
 
dataColumn1 =
 DataSet.Tables["Bugs"].Columns["BugID"];
dataColumn2 = 
 DataSet.Tables["BugHistory"].Columns["BugID"];
 
dataRelation = 
 new System.Data.DataRelation(
 "BugsToHistory", 
 dataColumn1, 
 dataColumn2);
DataSet.Relations.Add(dataRelation);

The DataGrid can now display the data from the DataSet, complete with the relationships between the Bug table and the BugHistory table, as shown in Figure 19-28.

Figure 19-28. Displaying handcrafted DataSet