Introduction to DataTables: Part I

The primary objective of this article is toillustrate the major characteristics of the Dot Net object namedDataTable. The DataTable has not received proper attentions in manybooks and articles. Often, the authors get involved in explaining thedetails of a more versatile and novel object named DataSet, thereby theDataTable object gets short-changed. The DataTable objects are thefoundations of DataSets, and we will not be able to develop advanceddatabase applications without understanding the principles of aDataTable.

A DataTable is an innovative addition to theDot Net Framework. Often a new developer may confuse it with a databasetable. Typically, one or more DataTables are used in a DataSet toconstruct a detached mini-database in the client's cache. However, aDataTable may stand alone by its own right, and we may use it in manysituations that do not necessarily warrant database connectivity orDataSets.

A DataTable is a relational database-liketable in the memory. It has a structural definition (like databasetable definition), and constraints like Unique constraints. We may alsodevelop database-like views. We may create hierarchical relationshipsamong many DataTables dynamically in a DataSet.

It has many properties, methods, and eventslike Columns, Rows, Clear, NewRow, PrimaryKey, GetRowType, RowChanged,RowChanging, OnRemoveColumn, OnCloumnChanging, Select, etc. The majorproperties and collection objects involved in a DataTable are:
?DataRow : stored in a collection named Rows.
?DataColumn: stored in a collection named Columns
?PrimaryKey: references to a specified DataColumn object of the DataTable, or to a group of DataColumn objects (in case of concatenated key)
?Constraint: stored in a collection named Constraints
?DefaultView: Gets a customized view of the table which may include a filtered or sorted view
?DataRelation: Stored either in ParentRelations collection, or ChildRelations collection (in the context of a DataSet).

If we want to create a DataTable programmatically, we may use the following steps:

1. At first we need to create an instance ofthe DataTable object. Then we need to create a DataColumn object sothat we can define the properties of our column. After we have definedthe column, we need to add it to the Columns collection of ourDataTable. We need to repeat this procedure for every column of theDataTable. We may also specify the primary keys, if any. The followingcode accomplishes these tasks. It defines a column named DeptNumber,and specifies it to be the primary key:

// Get a Column object
myDataColumn = new DataColumn();
myDataColumn.DataType = System.Type.GetType("System.Int32");
myDataColumn.ColumnName = "DeptNumber";
myDataColumn.ReadOnly=true;
myDataColumn.Unique= true;

// Now add the column to the column collection
myDataTable.Columns.Add(myDataColumn);

// Specify the column as the primary key, if needed.
DataColumn[] myPrimaryKeyColumns = new DataColumn[1];
myPrimaryKeyColumns[0] = myDataTable.Columns["DeptNumber"];
myDataTable.PrimaryKey = myPrimaryKeyColumns;

2. In the above step, we have defined thetable. Now we will have to load data in it. To insert data, we willhave to construct a DataRow object and fill it with data first. Then wecan add the row in the Rows collection of our DataTable as follows:

// Instantiate a DataRow object and add it to the Rows collection
myDataRow = myDataTable.NewRow();
myDataRow["DeptNumber"] = 100;
myDataRow["DeptName"] = "Accounting";
myDataRow["DeptBudget"] = 222222.22;
myDataTable.Rows.Add(myDataRow);

Obviously, we will have to repeat the process for every row we want to load into the DataTable.

3. Once the DataTable is loaded, we canretrieve the rows one at a time from its Rows collection using foreachloop. A field in a given row can be retrieved as DataRow["columnName"].

foreach (DataRow r in myDataTable.Rows)
{ Console.WriteLine("DeptNumber : {0} ", r["DeptNumber"]);
Console.WriteLine("Deptname : {0} ", r["DeptName"]);
Console.WriteLine("DeptBudget : {0} ", r["DeptBudget"]);
}

A Complete Example:

In this example, we will create a DataTablenamed "Departments". It will have three columns. We will load two rowsof data in it, and then we will retrieve the data, one row at a timefrom its Rows collection and display the values. When we run theprogram, it will display the following output:

The complete code:
// MyCSharp\DataTables\DataTable1.cs
// Author: Mesbah U. Ahmed
using System;
using System.Data;
public class CreateDataTable
{ public static void Main()
{ // Instantiate a DataTable object. Set its name property as Departments
DataTable myDataTable = new DataTable("Departments");

// Get a pair of DataColumn and DataRow reference
DataColumn myDataColumn;
DataRow myDataRow;

// create the first column
myDataColumn = new DataColumn();
myDataColumn.DataType = System.Type.GetType("System.Int32");
myDataColumn.ColumnName = "DeptNumber";
myDataColumn.ReadOnly=true;
myDataColumn.Unique= true;

// Now add the column to the column collection
myDataTable.Columns.Add(myDataColumn);

// Create and add the second column
myDataColumn= new DataColumn();
myDataColumn.DataType = System.Type.GetType("System.String");
myDataColumn.ColumnName = "DeptName";
myDataColumn.ReadOnly= false;
myDataTable.Columns.Add(myDataColumn);

// Create and add the third column
myDataColumn= new DataColumn();
myDataColumn.DataType = System.Type.GetType("System.Decimal");
myDataColumn.ColumnName = "DeptBudget";
myDataColumn.ReadOnly= false;
myDataTable.Columns.Add(myDataColumn);

// Set the DeptNumber as the primary key
DataColumn[] myPrimaryKeyColumns = new DataColumn[1];
myPrimaryKeyColumns[0] = myDataTable.Columns["DeptNumber"];
myDataTable.PrimaryKey = myPrimaryKeyColumns;

// Now instantiate two DataRow objects and add them to Rows collection
myDataRow = myDataTable.NewRow();
myDataRow["DeptNumber"] = 100;
myDataRow["DeptName"] = "Accounting";
myDataRow["DeptBudget"] = 222222.22;
myDataTable.Rows.Add(myDataRow);

myDataRow = myDataTable.NewRow();
myDataRow["DeptNumber"] = 200;
myDataRow["DeptName"] = "Information Technology";
myDataRow["DeptBudget"] = 333333.33;
myDataTable.Rows.Add(myDataRow);

// Display the data
Console.WriteLine("The Department DataTable contains the following data: ");
foreach (DataRow r in myDataTable.Rows)
{ Console.WriteLine("DeptNumber : {0} ", r["DeptNumber"]);
Console.WriteLine("Deptname : {0} ", r["DeptName"]);
Console.WriteLine("DeptBudget : {0} ", r["DeptBudget"]);
Console.WriteLine();
}
}
}

Compilation: In Beta 1, you may compile the code as follows:

csc /r:System.Data.dll,System.dll DataTable1.cs

Conclusion: In this article we have providedan overview of the Dot Net DataTable object. The DataTable object isone of the major foundations of the DataSet object. A thoroughunderstanding of the basic DataTable object is essential for being ableto develop advanced applications in databases. More information oncreating multiple DataTables with constraints and relationships areavailable in the Microsoft Dot Net Framework SDK documentation.

References:
1. Albahari, B., et al; "C# Essentials", O'Reilly, 2001
2. Harvey, B., et al; "C# Programming With the Public Beta", Wrox Press Ltd., 2000
3. Hollis B., and Lhotka, R.' "VB.Net Programming with Public Beta", Wrox Press Ltd., 2001
4. Conrad, J., et al; "Introducing .Net", Wrox Press Ltd., 2000