How to Create DataTable Manually with Specific Schema Definitions (CSDataTableCreateManually)

Introduction

Sometimes we may need to create the DataTable manually and some specific schema definitions, such as Foreign Key constraints, expression columns and so on.

In this sample, we will demonstrate how to create the DataTable manually with specific schema definitions:

1. Create multiple DataTable and define the initial columns.

2. Create the constraints on the tables.

3. Insert the values and show the tables.

4. Create the expression columns and show the tables.

Running the Sample

Press F5 to run the sample, the following is the result.

First, create OrderTable and OrderDetailTable, and then define the initial columns.

Second, create the relation and constraint on the tables. If inserting a row in OrderDetailTable with the wrong OrderId, we will get the error message.

Third, insert the rows into the tables and show the tables. The initial Order table has two columns, OrderId and OrderDate.

At last, create the expression columns and show the result. Now the Order table has five columns, and the new three column are the expression columns.

Using the Code

1. Create tables and define columns.

a. Define one column once.

 

C#
Edit|Remove
DataTable orderTable = new DataTable("Order");


DataColumn colId = new DataColumn("OrderId", typeof(String));
orderTable.Columns.Add(colId);


DataColumn colDate = new DataColumn("OrderDate", typeof(DateTime));
orderTable.Columns.Add(colDate);

 

b. Define all columns once.

C#
Edit|Remove
DataTable orderDetailTable = new DataTable("OrderDetail");


DataColumn[] cols ={
                      new DataColumn("OrderDetailId",typeof(Int32)),
                      new DataColumn("OrderId",typeof(String)),
                      new DataColumn("Product",typeof(String)),
                      new DataColumn("UnitPrice",typeof(Decimal)),
                      new DataColumn("OrderQty",typeof(Int32)),
                      new DataColumn("LineTotal",typeof(Decimal),"UnitPrice*OrderQty")
                  };
orderDetailTable.Columns.AddRange(cols);

 

2. Create the relation and constraint on the tables.

Create the foreign key relation and constraint between the Order table and OrderDetail table.

C#
Edit|Remove
salesSet.Relations.Add("OrderOrderDetail",
    orderTable.Columns["OrderId"], orderDetailTable.Columns["OrderId"], true);

 

3. Insert the rows into the table.

a. Insert one row once.

C#
Edit|Remove
DataRow row1 = orderTable.NewRow();
row1["OrderId"] = "O0001";
row1["OrderDate"] = new DateTime(2013, 3, 1);
orderTable.Rows.Add(row1);


DataRow row2 = orderTable.NewRow();
row2["OrderId"] = "O0002";
row2["OrderDate"] = new DateTime(2013, 3, 12);
orderTable.Rows.Add(row2);


DataRow row3 = orderTable.NewRow();
row3["OrderId"] = "O0003";
row3["OrderDate"] = new DateTime(2013, 3, 20);
orderTable.Rows.Add(row3);

 

b. Insert all rows once.

C#
Edit|Remove
Object[] rows = {
                     new Object[]{1,"O0001","Mountain Bike",1419.5,36},
                     new Object[]{2,"O0001","Road Bike",1233.6,16},
                     new Object[]{3,"O0001","Touring Bike",1653.3,32},
                     new Object[]{4,"O0002","Mountain Bike",1419.5,24},
                     new Object[]{5,"O0002","Road Bike",1233.6,12},
                     new Object[]{6,"O0003","Mountain Bike",1419.5,48},
                     new Object[]{7,"O0003","Touring Bike",1653.3,8},
                 };


foreach (Object[] row in rows)
{
    orderDetailTable.Rows.Add(row);
}

 

4. Create the expression columns.

a. Use the Aggregate-Sum on the child table column to get the result.

C#
Edit|Remove
DataColumn colSub = new DataColumn("SubTotal", typeof(Decimal), "Sum(Child.LineTotal)");
orderTable.Columns.Add(colSub);

 

b. Compute the tax by referencing the SubTotal expression column.

C#
Edit|Remove
DataColumn colTax = new DataColumn("Tax", typeof(Decimal), "SubTotal*0.1");
orderTable.Columns.Add(colTax);

 

c. If the OrderId is 'Total', compute the due on all orders; or compute the due on this order.

 

C#
Edit|Remove
DataColumn colTotal = new DataColumn("TotalDue", typeof(Decimal), 
    "IIF(OrderId='Total',Sum(SubTotal)+Sum(Tax),SubTotal+Tax)");
orderTable.Columns.Add(colTotal);


DataRow row = orderTable.NewRow();
row["OrderId"] = "Total";
orderTable.Rows.Add(row);

 

More Information

DataColumn.Expression Property

Adding Columns to a DataTable (ADO.NET)

Creating a DataTable (ADO.NET)

Creating Expression Columns (ADO.NET)