By Mesbah U. Ahmed
The primary objective of this article is to illustrate the major characteristics of the Dot Net object named DataTable. The DataTable has not received proper attentions in many books and articles. Often, the authors get involved in explaining the details of a more versatile and novel object named DataSet, thereby the DataTable object gets short-changed. The DataTable objects are the foundations of DataSets, and we will not be able to develop advanced database applications without understanding the principles of a DataTable.
A DataTable is an innovative addition to the Dot Net Framework. Often a new developer may confuse it with a database table. Typically, one or more DataTables are used in a DataSet to construct a detached mini-database in the client's cache. However, a DataTable may stand alone by its own right, and we may use it in many situations that do not necessarily warrant database connectivity or DataSets.
A DataTable is a relational database-like table in the memory. It has a structural definition (like database table definition), and constraints like Unique constraints. We may also develop database-like views. We may create hierarchical relationships among many DataTables dynamically in a DataSet.
It has many properties, methods, and events like Columns, Rows, Clear, NewRow, PrimaryKey, GetRowType, RowChanged, RowChanging, OnRemoveColumn, OnCloumnChanging, Select, etc. The major properties 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 of the DataTable object. Then we need to create a DataColumn object so that we can define the properties of our column. After we have defined the column, we need to add it to the Columns collection of our DataTable. We need to repeat this procedure for every column of the DataTable. We may also specify the primary keys, if any. The following code 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 the table. Now we will have to load data in it. To insert data, we will have to construct a DataRow object and fill it with data first. Then we can 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 can retrieve the rows one at a time from its Rows collection using foreach loop. 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 DataTable named "Departments". It will have three columns. We will load two rows of data in it, and then we will retrieve the data, one row at a time from its Rows collection and display the values. When we run the program, 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 provided an overview of the Dot Net DataTable object. The DataTable object is one of the major foundations of the DataSet object. A thorough understanding of the basic DataTable object is essential for being able to develop advanced applications in databases. More information on creating multiple DataTables with constraints and relationships are available 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
