Programmatically Constructing a DataTable using C#
A DataTable, which represents one table of in-memory relational data, can be created and used independently, or can be used by other .NET Framework objects, most commonly as a member of a DataSet.
We can create a DataTable object by using the DataTable constructor, or by passing constructor arguments to the Add method of the Tables property of the DataSet, which is a DataTableCollection.
We can also create DataTable objects within a DataSet by using the Fill or FillSchema methods of the DataAdapter object, or from a pre-defined or inferred XML schema using the ReadXml, ReadXmlSchema, or InferXmlSchema methods of the DataSet. Note that after we have added a DataTable as a member of the Tables collection of one DataSet, we cannot add it to the collection of tables of any other DataSet.
When we first create a DataTable, it does not have a schema (a structure). To define the schema of the table, we must create and add DataColumn objects to the Columns collection of the table. We can also define a primary key column for the table, and create and add Constraint objects to the Constraints collection of the table. After we have defined the schema for a DataTable, we can add rows of data to the table by adding DataRow objects to the Rows collection of the table.
We are not required to supply a value for the TableName property when we create a DataTable; we can specify the property at another time, or we can leave it empty. However, when we add a table without a TableName value to a DataSet, the table will be given an incremental default name of TableN, starting with "Table" for Table0.
Note It is recommended that we avoid the naming convention of "Table" or "TableN" when we supply a TableName value, because the name we supply may conflict with an existing default table name in the DataSet. If the supplied name already exists, an exception will be thrown.
The following example creates an instance of a DataTable object and assigns it the name "Customers".
DataTable currentTable = new DataTable("Customers");
The following example creates an instance of a DataTable by adding it to the Tables collection of a DataSet.
DataSet customerDS = new DataSet();
DataTable custTable = customerDS.Tables.Add("CustTable");
In the following code example, we programmatically create a DataTable, set the primary key, and the AutoIncrement and ReadOnly properties of the DataColumn. This example code demonstrates how we can programmatically create DataTables.
// Create the Table
DataTable OrdersTable = new DataTable("Orders");
// Build the Orders schema
ordersTable.Columns.Add("ID" Type.GetType("System.Int32"));
ordersTable.Columns.Add("Name" Type.GetType("System.String"));
ordersTable.Columns.Add("Amount" Type.GetType("System.Int32"));
// Set up the ID column as the PrimaryKey
DataColumn[] prmk = new DataColumn[1];
prmk[0] = ordersTable.Columns["ID"];
ordersTable.PrimaryKey = prmk;
ordersTable.Columns["ID"].AutoIncrement = true;
ordersTable.Columns["ID"].AutoIncrementSeed = 1;
ordersTable.Columns["ID"].ReadOnly = true;
As we can see in this example, we create a new DataTable and define its schema by adding DataColumns. We use the DataTable constructor to create an empty DataTable named Orders. We saw Increment, IncrementSeed and ReadOnly property that we set them programmatically.
ADO.NET enables us to create DataTable objects and add them to an existing DataSet. We can set constraint information for a DataTable by using the PrimaryKey and Unique properties of DataColumn objects, which are added to the Columns collection of the DataTable. The following example constructs a DataSet, adds a new DataTable object to the DataSet, and then adds three DataColumn objects to the table. Finally, the code sets one column as the primary key column.
DataSet customerDS = new DataSet("CustomerOrders");
DataTable ordersTable = customerDS.Tables.Add("Orders");
DataColumn pkCol = ordersTable.Columns.Add("OrderID", typeof(Int32));
ordersTable.Columns.Add("OrderQuantity", typeof(Int32));
ordersTable.Columns.Add("CompanyName", typeof(string));
ordersTable.PrimaryKey = new DataColumn[] {pkCol};
References by name to tables and relations in a DataSet are case-sensitive. Two or more tables or relations can exist in a DataSet that have the same name, but that differ in case. For example we can have Table1 and table1. In this situation, a reference to one of the tables by name must match the case of the table name exactly, otherwise an exception is thrown. For example, if the DataSet SampleDS contains tables Table1 and table1, we would reference Table1 by name as SampleDS.Tables ["Table1"], and table1 as SampleDS.Tables ["table1"]. Attempting to reference either of the tables as SampleDS.Tables ["TABLE1"] would generate an exception.
The case-sensitivity rule does not apply if only one table or relation exists with a particular name. That is, if no other table or relation object in the DataSet matches the name of that particular table or relation object, even by a difference in case, we can reference the object by name using any case and no exception is thrown. For example, if the DataSet has only Table1, we can reference it using SampleDS.Tables ["TABLE1"].
The CaseSensitive property of the DataSet does not affect this behavior. The CaseSensitive property applies to the data in the DataSet and affects sorting, searching, filtering, enforcing constraints, and so on. References to the tables or relations in a DataSet are not affected by the CaseSensitive property.
After we create a DataTable and define its structure using columns and constraints, we can add new rows of data to the table. To add a new row, declare a new variable as type DataRow. A new DataRow object is returned when we call the NewRow method. The DataTable then creates the DataRow object based on the structure of the table, as defined by the DataColumnCollection.
The following example demonstrates how to create a new row by calling the NewRow method.
DataRow workRow = currentTable.NewRow();
workRow["CustLastName"] = "Smith";
workRow[1] = "Smith";
currentTable.Rows.Add(workRow);
currentTable.Rows.Add(new Object[] {1, "Smith"});
Passing an array of values, typed as Object, to the Add method creates a new row inside the table and sets its column values to the values in the object array. Note that values in the array are matched sequentially to the columns, based on the order in which they appear in the table. The following example adds ten rows to the newly created Customers table.
DataRow workRow;
for (int i = 0; i <= 9; i++)
{
workRow = currentTable.NewRow();
workRow[0] = i;
workRow[1] = "CustName" + i.ToString();
currentTable.Rows.Add(workRow);
}
We can access the contents of a DataTable using the Rows and Columns collections of the DataTable. We can also use the DataTable.Select method to return subsets of the data in a DataTable according to certain criteria including search criteria, sort order, and row state. Additionally, we can use the Find method of the DataRowCollection when searching for a particular row using a primary key value. The Select method of the DataTable object returns a set of DataRow objects that match the specified criteria. Select takes optional arguments of a filter expression, sort expression, and DataViewRowState. The filter expression identifies which rows to return based on DataColumn values, such as LastName = 'Smith'. The sort expression follows standard SQL conventions for ordering columns, for example LastName ASC, FirstName ASC. For rules about writing expressions, see the Expression property of the DataColumn class.
Tip: If we will be performing a number of calls to the Select method of a DataTable, we can increase performance by first creating a DataView for the DataTable. Creating the DataView will index the rows of the table. The Select method will then use that index, significantly reducing the time to generate the query result. For information about creating a DataView for a DataTable, see Creating and Using DataViews.
The Select method determines which version of the rows to view or manipulate based on a DataViewRowState. The following table describes the possible DataViewRowState enumeration values.
Member name Description
- CurrentRows Current rows including unchanged, added, and modified rows.
- Deleted A deleted row.
- ModifiedCurrent A current version, which is a modified version of original data. (See ModifiedOriginal.)
- ModifiedOriginal The original version of all modified rows. The current version is available using ModifiedCurrent.
- Added A new row.
- None None.
- OriginalRows Original rows, including unchanged and deleted rows.
- Unchanged An unchanged row.
In the following example, the DataSet object is filtered so that we are only working with rows whose DataViewRowState is set to CurrentRows.
DataRow[] currRows = currentTable.Select(null, null, DataViewRowState.CurrentRows);
if (currRows.Length < 1 )
Console.WriteLine("No Current Rows Found");
else
{
foreach (DataColumn myCol in currentTable.Columns)
Console.Write("\t{0}", myCol.ColumnName);
Console.WriteLine("\tRowState");
foreach (DataRow myRow in currRows)
{
foreach (DataColumn myCol in currentTable.Columns)
Console.Write("\t{0}", myRow[myCol]);
Console.WriteLine("\t" + myRow.RowState);
}
}
The Select method can be used to return rows with differing RowState values or field values. The following example returns a DataRow array that references all rows that have been deleted, and returns another DataRow array that references all rows, ordered by CustLastName, where the CustID column is greater than 5.
// Retrieve all deleted rows.
DataRow[] delRows = currentTable.Select(null, null, DataViewRowState.Deleted);
// Retrieve rows where CustID > 5, and order by CustLastName.
DataRow[] custRows = currentTable.Select("CustID > 5", "CustLastName ASC");
continue article