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"] = "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["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") = "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 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;

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

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.

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;

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"] = "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 in 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);

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.

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.

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