[ Team LiB ]Recipe 2.2 Building a DataSet Programmatically
Problem
You want to build a DataSet programmatically—including adding tables, columns,
primary keys, and relations—from a schema that you have designed.
Solution
The following example shows how to build a complex DataSet programmatically,
including how to build and add tables, columns, primary key constraints, relations, and
column mappings. Use this as a template for building your own DataSet.
The sample code creates a DataSet. A DataTable object is created representing the Orders
table in Northwind. Columns are added, including the auto-increment primary key, to the
table. The table is added to the DataSet. The process is repeated for a DataTable
representing the Order Details table in Northwind. A DataRelation is created relating the
two tables. Finally, the tables are filled with data from Northwind.
The C# code is shown in Example 2-2
.
Example 2-2. File: BuildDataSetProgramaticallyForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

// . . .

// Create the DataSet.
DataSet ds = new DataSet("MyDataSet");

// Build the Orders (parent) table.

cols = childTable.Columns;
// Add the PK fields.
cols.Add("OrderID", typeof(System.Int32)).AllowDBNull = false;
cols.Add("ProductID", typeof(System.Int32)).AllowDBNull = false;
// Add the other fields.
cols.Add("UnitPrice", typeof(System.Decimal)).AllowDBNull = false;
cols.Add("Quantity", typeof(System.Int16)).AllowDBNull = false;
cols.Add("Discount", typeof(System.Single)).AllowDBNull = false;
// Set the primary key.
childTable.PrimaryKey = new DataColumn[]
{
cols["OrderID"],
cols["ProductID"]
};
// Add the Order Details table to the DataSet.
ds.Tables.Add(childTable);

// Add the relationship between parent and child tables.
ds.Relations.Add("Order_OrderDetails_Relation",
parentTable.Columns["OrderID"], childTable.Columns["OrderID"], true);

// Fill the tables from the data source.
SqlDataAdapter da;
String sqlText;

sqlText = "SELECT OrderID, CustomerID, EmployeeID, OrderDate, " +
"RequiredDate, ShippedDate, ShipVia, Freight, ShipName, " +
"ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry " +
"FROM Orders";
da = new SqlDataAdapter(sqlText,

Add( ) method of the DataRelationCollection exposed by the Relations property o
f
the DataSet. Specify the relationship name, the related columns, and whether
constraints are to be created when calling the Add( ) method. Repeat step 8 for
each data relationship in the DataSet.
The steps continue, demonstrating how to fill the new DataSet:
9. To fill the DataSet with data from the data source, create a DataAdapter defining
the SQL select statement and the connection string in the constructor.
10. Use the Fill( ) method of the DataSet to fill the table. Specify the table name to be
filled in the second argument of the Fill( ) method.
11. Repeat steps 9 and 10 for each table to be filled. See Recipe 2.1
for information
about how to fill related tables from the data source without raising constraint
violation errors.

[ Team LiB ]