Working With the DataTable Class in C#

A DataTable object represents tabular data as an in-memory, tabular cache of rows, columns, and constraints. You typically use the DataTable class to perform any disconnected data access. The DataTable is a central object in the ADO.NET library. Other objects that use the DataTable include the DataSet and the DataView.

DataTable objects are conditionally case-sensitive. For example, if you are using one DataTable named "ObjDataTable" and another one named "objDataTable", a string used to search for one of the tables is regarded as case-sensitive. However, if "ObjDataTable" exists and "objDataTable" does not, the search string is regarded as case-insensitive.

For creating a DataTable you first create an instance of the DataTable class, and then add DataColumn objects that define the type of data to be held and insert DataRow objects that contain the data. The following code demonstrates the creation of a data table:

//Create the DataTable named "Order"
DataTable order = new DataTable ("Order");

Using this code you are creating an empty data table for which the TableName property is set to Order. You can use this property to access this data table from a DataTableCollection.

Adding DataColumn Objects and Create a Schema

A DataTable contains a collection of DataColumn objects referenced by the Columns property of the table. This collection of columns, along with any constraints, defines the schema, or structure, of the table. The DataTable object is not useful until it has a schema, which is created by adding DataColumn objects and setting the constraints of each column. Constraints help maintain data integrity by limiting the data that can be placed in the column.

The following example adds DataColumn objects to an order DataTable object.

//Add the DataColumn using all properties
DataColumn ordId = new DataColumn("OrderID");
ordId.DataType = typeof(int);
ordId.Unique = true;
ordId.AllowDBNull = false;
ordId.Caption = "ORDER ID";
order.Columns.Add(ordId);

//Add the DataColumn using defaults
DataColumn quant = new DataColumn("Quantity");
quant.MaxLength = 35;
quant.AllowDBNull = false;
order.Columns.Add(quant);

DataColumn deDate = new DataColumn("DeliveryDate", typeof(DateTime));
deDate.AllowDBNull = false;
order.Columns.Add(deDate);
 
//Derived column using expression
DataColumn yearMake = new DataColumn("Year and Make");
yearMake.DataType = typeof(string);
yearMake.MaxLength = 70;
yearMake.Expression = "Year + ' ' + Make";
order.Columns.Add(yearMake);

DataColumn Properties
 

Name	Description
AllowDBNull	Gets or sets a value that indicates whether null values are allowed in this column for rows that belong to the table.
Autoincrement	Gets or sets a value that indicates whether the column automatically increments the value of the column for new rows added to the table.
AutoincrementSeed	Gets or sets the starting value for a column that has its Autoincrement property set to true.
AutoincrementStep	Gets or sets the increment used by a column with its Autoincrement property set to true.
Caption	Gets or sets the caption for the column.
ColumnName	Gets or sets the name of the column in the DataColumnCollection .
Container	Gets the container for the component.
DataType	Gets or sets the type of data stored in the column.
Expression	Gets or sets the expression used to filter rows, calculate the values in a column, or create an aggregate column.
MaxLength	Gets or sets the maximum length of a text column.
Unique	Gets or sets a value that indicates whether the values in each row of the column must be unique.

Creating Primary Key Columns

The primary key of a DataTable object consists of a column or columns that make up a unique identity for each data row. The following code shows how to set the PrimaryKey property for the order DataTable object:

//Set the Primary Key
order.PrimaryKey = new DataColumn[] { OrderID };

Automatic Numbering for the Primary Key Column

You can set up an auto-increment column, by setting the AutoIncrement property of your data column to true. After that, you set AutoIncrementSeed to the value of the first number you want and set AutoIncrementStep to the value you want to increment each time a new row is added.

The following example shows how to set the AutoIncrement, AutoIncrementSeed, and AutoIncrementStep properties.

private void AddAutoIncrementColumn()
{
    DataColumn column = new DataColumn();
    column.DataType = System.Type.GetType("System.Int32");
    column.AutoIncrement = true;
    column.AutoIncrementSeed = 1000;
    column.AutoIncrementStep = 10;

    // Add the column to a new DataTable.
    DataTable table = new DataTable("table");
    table.Columns.Add(column);
}

Creating DataRow Objects

After the DataTable object is created and contains DataColumn objects, you can populate the DataTable object by adding DataRow objects. Use the DataRow object and it's properties and methods to retrieve and evaluate; and insert, delete, and update the values in the DataTable. The DataRowCollection represents the actual DataRow objects in the DataTable. DataRowCollection has an Add method that accepts a DataRow object. The Add method is also overloaded to accept an array of objects instead of a DataRow object.

The following sample demonstrates how to create and add data into the order DataTable object:

//Add New DataRow by creating the DataRow first
DataRow newOrder = order.NewRow();
newOrder ["OrderID "] = "O12345";
newOrder ["Quantity"] = 5;
newOrder ["Year and Make"] = 2002;
order.Rows.Add(newOrder);

//Add New DataRow by simply adding the values
order.Rows.Add("O9876", 10 , 2001);

Note : nothing has been permanently stored to a database.

Deleting the Data Row

You can delete a DataRow from the DataRowCollection by calling the Remove method of the DataRowCollection, or by calling the Delete method of the DataRow object. The Remove method removes the row from the collection. In contrast, Delete marks the DataRow for removal. The actual removal occurs when you call AcceptChanges method. The DataRow object doesn't have an Undelete method. However, in some situations, you can use the RejectChanges method to roll back to a previous state when the deleted row was still there. Be aware that executing the RejectChanges method copies the Original data row version to the Current data row version.

Enumerating the Data Table

You can loop through the rows and columns of the data table by using a foreach statement. The following example shows how to enumerate through the rows and columns of a data table.

public string EnumerateTable(DataTable orders)
{
    System.Text.StringBuilder objSB = new System.Text.StringBuilder();
    foreach (DataColumn objDc in orders.Columns)
    {
        objSB.AppendFormat("{0,15} ", objDc.ColumnName);
    }
    objSB.Append("\r\n");
    foreach (DataRow objDr in orders.Rows)
    {
        if (objDr.RowState == DataRowState.Deleted)
        {
            objSB.Append("Deleted Row");
        }
        else
        {
            foreach (DataColumn objDc in orders.Columns)
            {
                objSB.AppendFormat("{0,15} ", objDr[objDc]);
            }
        }
        objSB.Append("\r\n");
    }
    return objSB.ToString();
}

Copying and Cloning the Data Table

If you want to create a full copy of a data table you can do this by using the DataTable object's Copy method, which copies the DataTable object's schema and data.


The following example shows how to invoke the Copy method.

private void CopyDataTable(DataTable myDataTable)
{
    // Create an object variable for the copy.
    DataTable copyDataTable;
    copyDataTable = myDataTable.Copy();
    // Insert code to work with the copy.
}

In some cases you need a copy of the DataTable schema without data. To copy just the schema without data, you can invoke the DataTable object's Clone method.

The following example shows how to invoke the Clone method.

private void GetClone(DataTable myDataTable)
{
    // Get a clone of the original DataTable.
    DataTable cloneTable;
    cloneTable = myDataTable.Clone();
    // Insert code to work with clone of the DataTable.
}

Importing DataRow Objects into a Data Table

DataTable contains an ImportRow method, which you can use to copy a data row from a data table that has the same schema. The ImportRow method is useful when the Current and Original data row version must be maintained.

Example :

DataTable clone = order.Clone();
clone.ImportRow(order.Rows[0]);