ADO.NET Cookbook

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", ...