Building a DataSet Programmatically

Problem

You want to build a DataSet programmaticallyincluding adding tables, columns , primary keys, and relationsfrom 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:

1. Design the DataSet identifying the tables, columns, indexes, constraints, and data relations that need to be created.
2. Create a new DataSet , naming it in the constructor.
3. Create a new DataTable , naming it in the constructor.
4. Add a column to the ColumnCollection of the table using the Add( ) method exposed by the Columns property of the DataTable specifying the name and data type of the column. If the column is a character-type column, define its maximum length. If the column is an auto-increment column, set the AutoIncrement property to true and set both the AutoIncrementSeed and AutoIncrementStep properties of the column to -1 . (For more information about using auto-increment columns, see Recipe 4.1). Repeat step 4 for each column in the table.
5. Define the primary key for the table by setting the PrimaryKey property of the DataTable to the array of primary key DataColumn objects.
6. Add the new table to the DataSet using the Add( ) method of the DataTableCollection exposed by the Tables property of the DataSet .
7. Repeat steps 3-6 for each table in the DataSet .
8. Create a data relationship between two related tables in the DataSet by using the Add( ) method of the DataRelationCollection exposed by the Relations property of 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 :

1. 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.
2. 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.
3. 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.