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.
DataTable parentTable = new DataTable("Orders");

DataColumnCollection cols = parentTable.Columns;
// Add the identity field.
DataColumn column = cols.Add("OrderID", typeof(System.Int32));            
column.AutoIncrement = true;
column.AutoIncrementSeed = -1;
column.AutoIncrementStep = -1;
// Add the other fields.
cols.Add("CustomerID", typeof(System.String)).MaxLength = 5;  
cols.Add("EmployeeID", typeof(System.Int32));  
cols.Add("OrderDate", typeof(System.DateTime));  
cols.Add("RequiredDate", typeof(System.DateTime));  
cols.Add("ShippedDate", typeof(System.DateTime));  
cols.Add("ShipVia", typeof(System.Int32));  
cols.Add("Freight", typeof(System.Decimal));  
cols.Add("ShipName", typeof(System.String)).MaxLength = 40;  
cols.Add("ShipAddress", typeof(System.String)).MaxLength = 60;  
cols.Add("ShipCity", typeof(System.String)).MaxLength = 15;  
cols.Add("ShipRegion", typeof(System.String)).MaxLength = 15;  
cols.Add("ShipPostalCode", typeof(System.String)).MaxLength = 10;  
cols.Add("ShipCountry", typeof(System.String)).MaxLength = 15;  
// Set the primary key.
parentTable.PrimaryKey = new DataColumn[] {cols["OrderID"]};
// Add the Orders table to the DataSet.
ds.Tables.Add(parentTable);

// Build the Order Details (child) table.
DataTable childTable = new DataTable("Order Details");

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,
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
da.Fill(parentTable);

sqlText = "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " +
    "FROM [Order Details]";
da = new SqlDataAdapter(sqlText,
    ConfigurationSettings.AppSettings["Sql_ConnectString"]);
da.Fill(childTable);

Discussion

The steps to build a complex DataSet programmatically, as shown in the code for the solution, are:

The steps continue, demonstrating how to fill the new DataSet: