In all of the examples so far, you have created the DataSet object and its DataTable and DataRow objects by selecting data from the database. There are, however, occasions when you will want to fill a dataset or a table by hand.
For example, you may want to gather data from a user and then push that data into the database. It can be convenient to add records to a table manually, and then update the database from that table.
The dataset is also an excellent transport mechanism for data. You may even want to create a dataset by hand only to pass it to another tier in your application where it will be used as a data source.
In the next example you will create a dataset and populate three tables by hand. You’ll start by creating theBugs table and specifying its data structure. You’ll then fill that table with records. You’ll do the same for the lkProduct table and the People table.
Once the tables are created, you’ll set constraints on a number of columns, set default values, establish identity columns, and create keys. In addition, you’ll establish a foreign key relationship between two tables, and you’ll create a data relation tying two tables together. It sounds like more work than it really is.
Start by creating a method named CreateDataSet.
The job of this method is to create a DataSet and to populate it by hand,
and then to return that resulting DataSet to the calling method, in
this case Page_Load.
CreateDataSet begins by instantiating a new DataTable object, passing in the name of the table as a parameter to the constructor:
DataTable tblBugs = new DataTable("Bugs");The table you are creating should mimic the data structure of the Bugs table in SQL Server. Figure 11-15 shows that structure.
To add a column to this DataTable object, you do not call a constructor. Instead you call the Add method of the DataTable object’s Columns collection. The Add method takes two parameters, the name of the column and its data type:
DataColumn newColumn;
newColumn =
tblBugs.Columns.Add("BugID", Type.GetType("System.Int32"));In Visual Basic .NET, this is:
dim newColumn as DataColumn
newColumn = _
tblBugs.Columns.Add("BugID", Type.GetType("System.Int32"));The
Add method creates the new column and
returns a reference to it, which you may now manipulate. Since this
is to be an identity column (see the highlighted
area of Figure 11-16), you’ll want
to set its AutoIncrement property to
true, and you’ll set the
AutoIncrementSeed and
AutoIncrementStep properties to set the
seed and step values of the identity, respectively. The following
code fragment does this:
newColumn.AutoIncrement = true; newColumn.AutoIncrementSeed=1; newColumn.AutoIncrementStep=1;
Tip
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.
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
false to ensure that each entry in this column
must be unique:
newColumn.Unique=true;
This creates an unnamed constraint in the Bugs table’s Constraints collection. You can, if you prefer, add a named constraint. To do so, you create an instance of the UniqueConstraint class and pass a name for it into the constructor, along with a reference to the column:
UniqueConstraint constraint =
new UniqueConstraint("Unique_BugID",newColumn);You then manually add that constraint to the table’s
Constraints collection:
tblBugs.Constraints.Add(constraint);
This completes the first column in the table. The second column is
the Product column, as you can see in Figure 11-16.
Notice that this column is of type integer, with no nulls and a
default value of 1 (see the highlighted property
in Figure 11-16). You create the Product column by
calling the Add method of the
Columns collection of the tblBugs table, this time passing in the
type for an integer. You then set the AllowDBNull property as you did
with the earlier column, and you set the
DefaultValue property to set the default
value for the column. This is illustrated in the following code
fragment:
newColumn = tblBugs.Columns.Add(
"Product", Type.GetType("System.Int32"));
newColumn.AllowDBNull=false;
newColumn.DefaultValue = 1;Looking at Figure 11-16 again, you can see that the
third column is Version, with a type of
varChar.
Tip
A varChar is a variable length character string. A
varChar can be declared to be any length between 1 and 8000 bytes.
Typically you will limit the length of the string as a form of
documentation indicating the largest string you expect in the field.
You declare the column type to be string for a
varchar, and you can set the length of the
string with the
MaxLength property, as shown in the
following code fragment:
newColumn = tblBugs.Columns.Add(
"Version", Type.GetType("System.String"));
newColumn.AllowDBNull=false;
newColumn.MaxLength=50;
newColumn.DefaultValue = "0.1";You declare the Description and Reporter columns in a like manner:
newColumn = tblBugs.Columns.Add("Description", Type.GetType("System.String"));
newColumn.AllowDBNull=false;
newColumn.MaxLength=8000;
newColumn.DefaultValue = "";
newColumn = tblBugs.Columns.Add(
"Reporter", Type.GetType("System.Int32"));
newColumn.AllowDBNull=false;With all the columns declared, you’re ready to add rows of data to the table. You do so by calling the DataTable object’s NewRow method, which returns an empty DataRow object with the right structure:
newRow = tblBugs.NewRow( );
You can use the column name as an index into the row’s collection of DataColumns, assigning the appropriate value for each column, one by one:
newRow["Product"] = 1; newRow["Version"] = "0.1"; newRow["Description"] = "Crashes on load"; newRow["Reporter"] = 5;
Tip
The authors of the DataRows class have implemented the indexer for their class to access the contained Columns collection invisibly. Thus, when you write newRow["Product"], you actually access the Product column within the Columns collection of the DataRow object.
When the columns are complete, you add the row to the table’s Rows collection by calling the Add method, passing in the row you just created:
tblBugs.Rows.Add(newRow);
You are now ready to create a new row:
newRow = tblBugs.NewRow( ); newRow["Product"] = 1; newRow["Version"] = "0.1"; newRow["Description"] = "Does not report correct owner of bug"; newRow["Reporter"] = 5; tblBugs.Rows.Add(newRow);
When all the rows have been created, you can create an instance of a DataSet object and add the table:
DataSet dataSet = new DataSet( ); dataSet.Tables.Add(tblBugs);
With the Bugs table added to the new dataset, you are ready to create a new table for lkProduct:
DataTable tblProduct = new DataTable("lkProduct")Once again you’ll define the columns and then add data. You’ll then go on to add a new table for People. In theory, you could also add all the other tables from the previous example, but to keep things simpler, you’ll stop with these three.
The DataRowCollection object’s Add method is overloaded. In the code shown above, you created a new DataRow object, populated its columns, and added the row. You are also free to create an array of Objects, fill the array, and pass the array to the Add method. For example, rather than writing:
newRow = tblPeople.NewRow( ); newRow["FullName"] = "Jesse Liberty"; newRow["email"] = "[email protected]"; newRow["Phone"] = "617-555-7301"; newRow["Role"] = 1; tblPeople.Rows.Add(newRow);
you can instead create an array of five objects and fill that array with the values you would have added to the columns of the row:
Object[] PersonArray = new Object[5]; PersonArray[0] = 1; PersonArray[1] = "Jesse Liberty"; PersonArray[2] = "[email protected]"; PersonArray[3] = "617-555-7301"; PersonArray[4] = 1; tblPeople.Rows.Add(PersonArray);
Note that in this case, you must manually add a value for the identity column, BugID. When you created the row object, the identity column value was automatically created for you with the right increment from the previous row, but since you are now just creating an array of objects, you must do this by hand.
Tip
While this technique works, it is generally not very desirable. The overloaded version of the Add method that takes a DataRow object is typesafe. Each column must match the definition of the column you’ve created. With an array of objects, just about anything goes; remember that in .NET, everything derives from Object and thus you can pass in any type of data to an array of objects.
The Bugs table uses the PersonID as a foreign key into the People table. To recreate this, you’ll first need to create a primary key in the People table.
You start by declaring the PersonID column as a unique non-null identity column, just as you did earlier for the BugID column in Bugs:
newColumn = tblPeople.Columns.Add("PersonID", Type.GetType("System.Int32"));
newColumn.AutoIncrement = true; // autoincrementing
newColumn.AutoIncrementSeed=1; // starts at 1
newColumn.AutoIncrementStep=1; // increments by 1
newColumn.AllowDBNull=false; // nulls not allowed
// add the unique constraint
UniqueConstraint uniqueConstraint =
new UniqueConstraint("Unique_PersonID",newColumn);
tblPeople.Constraints.Add(uniqueConstraint);To create the primary key you must set the PrimaryKey property of the table. This property takes an array of DataColumn objects.
Tip
In many tables, the primary key is not a single column but rather two or more columns. For example, you might keep track of orders for a customer. A given order might be order number 17. Your database may have many orders whose order number is 17. What uniquely identifies a given order is the order number combined with the customer number. Thus, that table would use a compound key of the order number and the customer number.
The primary key for the People table is a single column: PersonID. To set the primary key, you create an array (in this case with one member), and assign to that member the column(s) you want to make the primary key:
columnArray = new DataColumn[1]; columnArray[0] = newColumn;
The newColumn object contains a reference to the PersonID column returned from calling Add. You assign the array to the PrimaryKey property of the table:
tblPeople.PrimaryKey=columnArray;
The PersonID acts as a primary key in People and as a foreign key in Bugs. To create the foreign key relationship, you’ll instantiate a new object of type ForeignKeyConstraint, passing in the name of the constraint (“FK_BugToPeople”) as well as a reference to the two columns.
To facilitate passing references to the key fields to the ForeignKeyConstraint constructor, you’ll want to squirrel away a reference to the PersonID column in People and the Reporter column in Bugs. Immediately after you create the columns, save a reference:
newColumn =
tblBugs.Columns.Add("Reporter", Type.GetType("System.Int32"));
newColumn.AllowDBNull=false;
DataColumn bugReporterColumn =
newColumn; // save for foreign key creationAssuming you’ve saved the Reporter column in bugReporterColumn and the PersonID column from People in PersonIDColumn, you are ready to create the ForeignKeyConstraint object:
ForeignKeyConstraint fk =
New ForeignKeyConstraint(
"FK_BugToPeople",PersonIDColumn,bugReporterColumn);This creates the Foreign Key Constraint named fk.
Before you add it to the Bugs table, you must set two properties:
fk.DeleteRule=Rule.Cascade; fk.UpdateRule=Rule.Cascade;
The DeleteRule determines the action that
will occur when a row is deleted from the parent table. Similarly,
the UpdateRule determines what will happen
when a row is updated in the parent column. The potential values are
enumerated by the Rule enumeration, as shown in
Table 11-6.
In the case shown, the value is set to
Rule.Cascade; if a record is deleted from the
parent table, all the child records will be deleted as well. You are
now ready to add the foreign key constraint to the Bugs table:
tblBugs.Constraints.Add(fk);
As you saw earlier in the chapter, you can encapsulate the relationship among tables in a DataRelation object. The code for building relationships among hand-crafted DataTables is just like the code you saw earlier when you pulled the data structure from the database itself:
System.Data.DataRelation dataRelation; System.Data.DataColumn dataColumn1; System.Data.DataColumn dataColumn2; // set the dataColumns to create the relationship // between Bug and BugHistory on the BugID key dataColumn1 = dataSet.Tables["People"].Columns["PersonID"]; dataColumn2 = dataSet.Tables["Bugs"].Columns["Reporter"]; dataRelation = new System.Data.DataRelation( "BugsToReporter", dataColumn1, dataColumn2); // add the new DataRelation to the dat dataSet.Relations.Add(dataRelation);
To display this output, you’ll use two DataGrids: one to show the Bugs table, and another to show the Constraints you’ve added to that table:
<body>
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="DataGrid1" runat="server" DataKeyField="BugID"
CellPadding="5" HeaderStyle-BackColor="PapayaWhip" BorderWidth="5px"
BorderColor="#000099" AlternatingItemStyle-BackColor="LightGrey"
HeaderStyle-Font-Bold AutoGenerateColumns="False"
EnableViewState="true">
<Columns>
<asp:BoundColumn DataField="BugID"
HeaderText="Bug ID" />
<asp:BoundColumn DataField="Description"
HeaderText="Description" />
<asp:BoundColumn DataField="Reporter"
HeaderText="Reported By" />
</Columns>
</asp:DataGrid>
<br />
<asp:DataGrid ID="BugConstraints" Runat="server"
HeaderStyle-Font-Bold AlternatingItemStyle-BackColor="LightGrey"
BorderColor="#000099" BorderWidth="5px"
HeaderStyle-BackColor="PapayaWhip" CellPadding="5" Runat="server" />
</form>
</body>The output is shown in Figure 11-17. The complete source code for this version of the application is shown in Example 11-8.
Example 11-8. Creating a DataSet by hand
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace BugHistoryByHand
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
// the Bugs Data Grid
protected System.Web.UI.WebControls.DataGrid DataGrid1;
// display the constraints added to the bug table
protected System.Web.UI.WebControls.DataGrid BugConstraints;
// unchanged from previous example
public WebForm1( )
{
Page.Init += new System.EventHandler(Page_Init);
}
// bind to the bug grid and the constraints grid
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
// call the method which creates the tables and the relations
DataSet ds = CreateDataSet( );
// set the data source for the grid to the first table
DataGrid1.DataSource=ds.Tables[0];
DataGrid1.DataBind( );
BugConstraints.DataSource = ds.Tables["Bugs"].Constraints;
BugConstraints.DataBind( );
}
}
//hand carved
private DataSet CreateDataSet( )
{
// instantiate a new DataSet object that
// you will fill with tables and relations
DataSet dataSet = new DataSet( );
// make the bug table and its columns
// mimic the attributes from the SQL database
DataTable tblBugs = new DataTable("Bugs");
DataColumn newColumn; // hold the new columns as you create them
newColumn =
tblBugs.Columns.Add(
"BugID", Type.GetType("System.Int32"));
newColumn.AutoIncrement = true; // autoincrementing
newColumn.AutoIncrementSeed=1; // starts at 1
newColumn.AutoIncrementStep=1; // increments by 1
newColumn.AllowDBNull=false; // nulls not allowed
// or you can provide a named constraint
UniqueConstraint constraint =
new UniqueConstraint("Unique_BugID",newColumn);
tblBugs.Constraints.Add(constraint);
// create an array of columns for the primary key
DataColumn[] columnArray = new DataColumn[1];
columnArray[0] = newColumn;
// add the array to the Primary key property
tblBugs.PrimaryKey=columnArray;
// The Product column
newColumn = tblBugs.Columns.Add(
"Product", Type.GetType("System.Int32"));
newColumn.AllowDBNull=false;
newColumn.DefaultValue = 1;
// save for foreign key creation
DataColumn bugProductColumn = newColumn;
// The Version column
newColumn = tblBugs.Columns.Add(
"Version", Type.GetType("System.String"));
newColumn.AllowDBNull=false;
newColumn.MaxLength=50;
newColumn.DefaultValue = "0.1";
// The Description column
newColumn = tblBugs.Columns.Add(
"Description", Type.GetType("System.String"));
newColumn.AllowDBNull=false;
newColumn.MaxLength=8000;
newColumn.DefaultValue = "";
// The Reporter column
newColumn = tblBugs.Columns.Add(
"Reporter", Type.GetType("System.Int32"));
newColumn.AllowDBNull=false;
// save for foreign key creation
DataColumn bugReporterColumn = newColumn;
// Add rows based on the db schema you just created
DataRow newRow; // holds the new row
newRow = tblBugs.NewRow( );
newRow["Product"] = 1;
newRow["Version"] = "0.1";
newRow["Description"] = "Crashes on load";
newRow["Reporter"] = 5;
tblBugs.Rows.Add(newRow);
newRow = tblBugs.NewRow( );
newRow["Product"] = 1;
newRow["Version"] = "0.1";
newRow["Description"] =
"Does not report correct owner of bug";
newRow["Reporter"] = 5;
tblBugs.Rows.Add(newRow);
newRow = tblBugs.NewRow( );
newRow["Product"] = 1;
newRow["Version"] = "0.1";
newRow["Description"] =
"Does not show history of previous action";
newRow["Reporter"] = 6;
tblBugs.Rows.Add(newRow);
newRow = tblBugs.NewRow( );
newRow["Product"] = 1;
newRow["Version"] = "0.1";
newRow["Description"] =
"Fails to reload properly";
newRow["Reporter"] = 5;
tblBugs.Rows.Add(newRow);
newRow = tblBugs.NewRow( );
newRow["Product"] = 2;
newRow["Version"] = "0.1";
newRow["Description"] = "Loses data overnight";
newRow["Reporter"] = 5;
tblBugs.Rows.Add(newRow);
newRow = tblBugs.NewRow( );
newRow["Product"] = 2;
newRow["Version"] = "0.1";
newRow["Description"] = "HTML is not shown properly";
newRow["Reporter"] = 6;
tblBugs.Rows.Add(newRow);
// add the table to the dataset
dataSet.Tables.Add(tblBugs);
// Product Table
// make the Products table and add the columns
DataTable tblProduct = new DataTable("lkProduct");
newColumn = tblProduct.Columns.Add(
"ProductID", Type.GetType("System.Int32"));
newColumn.AutoIncrement = true; // autoincrementing
newColumn.AutoIncrementSeed=1; // starts at 1
newColumn.AutoIncrementStep=1; // increments by 1
newColumn.AllowDBNull=false; // nulls not allowed
newColumn.Unique=true; // each value must be unique
newColumn = tblProduct.Columns.Add(
"ProductDescription", Type.GetType("System.String"));
newColumn.AllowDBNull=false;
newColumn.MaxLength=8000;
newColumn.DefaultValue = "";
newRow = tblProduct.NewRow( );
newRow["ProductDescription"] = "BugX Bug Tracking";
tblProduct.Rows.Add(newRow);
newRow = tblProduct.NewRow( );
newRow["ProductDescription"] =
"PIM - My Personal Information Manager";
tblProduct.Rows.Add(newRow);
// add the products table to the dataset
dataSet.Tables.Add(tblProduct);
// People
// make the People table and add the columns
DataTable tblPeople = new DataTable("People");
newColumn = tblPeople.Columns.Add(
"PersonID", Type.GetType("System.Int32"));
newColumn.AutoIncrement = true; // autoincrementing
newColumn.AutoIncrementSeed=1; // starts at 1
newColumn.AutoIncrementStep=1; // increments by 1
newColumn.AllowDBNull=false; // nulls not allowed
UniqueConstraint uniqueConstraint =
new UniqueConstraint(
"Unique_PersonID",newColumn);
tblPeople.Constraints.Add(uniqueConstraint);
// stash away the PersonID column for the foreign
// key constraint
DataColumn PersonIDColumn = newColumn;
columnArray = new DataColumn[1];
columnArray[0] = newColumn;
tblPeople.PrimaryKey=columnArray;
newColumn = tblPeople.Columns.Add(
"FullName", Type.GetType("System.String"));
newColumn.AllowDBNull=false;
newColumn.MaxLength=8000;
newColumn.DefaultValue = "";
newColumn = tblPeople.Columns.Add(
"eMail", Type.GetType("System.String"));
newColumn.AllowDBNull=false;
newColumn.MaxLength=100;
newColumn.DefaultValue = "";
newColumn = tblPeople.Columns.Add(
"Phone", Type.GetType("System.String"));
newColumn.AllowDBNull=false;
newColumn.MaxLength=20;
newColumn.DefaultValue = "";
newColumn = tblPeople.Columns.Add(
"Role", Type.GetType("System.Int32"));
newColumn.DefaultValue = 0;
newColumn.AllowDBNull=false;
newRow = tblPeople.NewRow( );
newRow["FullName"] = "Jesse Liberty";
newRow["email"] = "[email protected]";
newRow["Phone"] = "617-555-7301";
newRow["Role"] = 1;
tblPeople.Rows.Add(newRow);
newRow = tblPeople.NewRow( );
newRow["FullName"] = "Dan Hurwitz";
newRow["email"] = "[email protected]";
newRow["Phone"] = "781-555-3375";
newRow["Role"] = 1;
tblPeople.Rows.Add(newRow);
newRow = tblPeople.NewRow( );
newRow["FullName"] = "John Galt";
newRow["email"] = "[email protected]";
newRow["Phone"] = "617-555-9876";
newRow["Role"] = 1;
tblPeople.Rows.Add(newRow);
newRow = tblPeople.NewRow( );
newRow["FullName"] = "John Osborn";
newRow["email"] = "[email protected]";
newRow["Phone"] = "617-555-3232";
newRow["Role"] = 3;
tblPeople.Rows.Add(newRow);
newRow = tblPeople.NewRow( );
newRow["FullName"] = "Ron Petrusha";
newRow["email"] = "[email protected]";
newRow["Phone"] = "707-555-0515";
newRow["Role"] = 2;
tblPeople.Rows.Add(newRow);
newRow = tblPeople.NewRow( );
newRow["FullName"] = "Tatiana Diaz";
newRow["email"] = "[email protected]";
newRow["Phone"] = "617-555-1234";
newRow["Role"] = 2;
tblPeople.Rows.Add(newRow);
// add the People table to the dataset
dataSet.Tables.Add(tblPeople);
// create the Foreign Key constraint
// pass in the parent column from people
// and the child column from Bugs
ForeignKeyConstraint fk =
new ForeignKeyConstraint(
"FK_BugToPeople",PersonIDColumn,bugReporterColumn);
fk.DeleteRule=Rule.Cascade; // like father like son
fk.UpdateRule=Rule.Cascade;
tblBugs.Constraints.Add(fk); // add the new constraint
// declare the DataRelation and DataColumn objects
System.Data.DataRelation dataRelation;
System.Data.DataColumn dataColumn1;
System.Data.DataColumn dataColumn2;
// set the dataColumns to create the relationship
// between Bug and BugHistory on the BugID key
dataColumn1 =
dataSet.Tables["People"].Columns["PersonID"];
dataColumn2 =
dataSet.Tables["Bugs"].Columns["Reporter"];
dataRelation =
new System.Data.DataRelation(
"BugsToReporter",
dataColumn1,
dataColumn2);
// add the new DataRelation to the dataset
dataSet.Relations.Add(dataRelation);
return dataSet;
}
// unchanged from previous example
private void Page_Init(object sender, EventArgs e)
{
InitializeComponent( );
}
#region Web Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent( )
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}