In the previous article, ADO.NET DataSet, we saw how to create and use a DataSet in C#. A DataSet may contain one or more DataTable objects. The C# DataTable class represents a data table. A DataTable can be used via the DataSet object and independently.
A DataTable consists of Columns, Rows, and Constraints collection.
A DataColumn defines the column name and datatype. We can create a new DataColumn using the DataColumn constructor or by invoking the Add method of the DataTable.Columns collection property. The DataColumn is the fundamental building block for creating the schema of a DataTable. We build the schema by adding one or more DataColumn objects to the DataColumnCollection. For more information, see Adding Columns to a Table. Each DataColumn has a DataType property that determines the kind of data the DataColumn contains. For example, we can restrict the data type to integers, strings, or decimals. Because data contained by the DataTable is usually merged back into its original data source, we must match the data types to those in the data source.
DataColumn FirstColumn == new DataColumn("ISBN", Type.GetType("System.Int32");
Properties such as AllowDBNull, Unique, and ReadOnly place restrictions on the entry and updating of data, thereby helping to ensure data integrity. We can also use the AutoIncrement, AutoIncrementSeed, and AutoIncrementStep properties to control automatic data generation. For more information about AutoIncrement columns, see Creating AutoIncrement Columns. For more information, see Defining a Primary Key for a Table. Finally, we can also ensure that values in a DataColumn are unique by creating a UniqueConstraint and adding it to the ConstraintCollection of the DataTable to which the DataColumn belongs. For more information, see Adding Constraints to a Table.
To create a relation between DataColumn objects, create a DataRelation object, and add it to the DataRelationCollection of a DataSet. We can use the Expression property of the DataColumn object to calculate the values in a column or create an aggregate column.
private void MakeTable(DataTable firstTable) {
// Create a DataTable. DataTable firstTable = new DataTable("firstTable");
// Create a DataColumn and set various properties.
DataColumn firstColumn = new DataColumn();
firstColumn.DataType = System.Type.GetType("System.Decimal");
firstColumn.AllowDBNull = false;
firstColumn.Caption = "Price";
firstColumn.ColumnName = "Price";
firstColumn.DefaultValue = 25;
// Add the column to the table.
firstTable.Columns.Add(firstColumn);
// Add 10 rows and set values.
DataRow firstRow;
for (int i = 0; i < 10; i++) {
firstRow = firstTable.NewRow();
firstRow["Price"] = i + 1;
// Be sure to add the new row to the DataRowCollection.
firstTable.Rows.Add(firstRow);
}
}
The DataRow and DataColumn objects are primary components of a DataTable. Use the DataRow object and its properties and methods to retrieve, evaluate, insert, delete, and update the values in the DataTable. The DataRowCollection represents the actual DataRow objects in the DataTable, and the DataColumnCollection contains the DataColumn objects that describe the schema of the DataTable. Use the overloaded Item property to return or sets the value of a DataColumn. Use the HasVersion and IsNull properties to determine the status of a particular row value and the RowState property to determine the state of the row in relation to its parent DataTable.
To create a new DataRow, use the NewRow method of the DataTable object. After creating a new DataRow, use the Add method to add the new DataRow to the DataRowCollection. Finally, call the AcceptChanges method of the DataTable object to confirm the addition. For more information about adding data to a DataTable, see Adding Data to a Table. We 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 removes the row from the collection. In contrast, Delete marks the DataRow for removal. The actual removal occurs when we call the AcceptChanges method. By calling Delete, we can programmatically check which rows are marked for removal before actually deleting them.
private void CreateNewDataRow() {
// Use the MakeTable function below to create a new table.
DataTable firstTable;
firstTable = MakeNamesTable();
// Once a table has been created, use the NewRow to create a DataRow.
DataRow firstRow;
firstRow = firstTable.NewRow();
// Then add the new row to the collection.
firstRow["FirstName"] = "John";
firstRow["LastName"] = "Smith";
firstTable.Rows.Add(firstRow);
foreach(DataColumn dc in firstTable.Columns)
Console.WriteLine(dc.ColumnName);
dataGrid1.DataSource = firstTable;
}
private DataTable MakeNamesTable() {
// Create a new DataTable titled 'Names.'
DataTable namesTable = new DataTable("Names");
// Add three column objects to the table.
DataColumn idColumn = new DataColumn();
idColumn.DataType = System.Type.GetType("System.Int32");
idColumn.ColumnName = "id";
idColumn.AutoIncrement = true;
namesTable.Columns.Add(idColumn);
DataColumn FirstNameColumn = new DataColumn();
FirstNameColumn.DataType = System.Type.GetType("System.String");
FirstNameColumn.ColumnName = "FirstName";
FirstNameColumn.DefaultValue = "FirstName";
namesTable.Columns.Add(FirstNameColumn);
DataColumn LastNameColumn = new DataColumn();
LastNameColumn.DataType = System.Type.GetType("System.String");
LastNameColumn.ColumnName = "LastName";
namesTable.Columns.Add(LastNameColumn);
// Create an array for DataColumn objects.
DataColumn[] keys = new DataColumn[1];
keys[0] = idColumn;
namesTable.PrimaryKey = keys;
// Return the new DataTable.
return namesTable;
}
Relational databases enforce data integrity with constraints or rules applied to a column that defines what action to take when data in a related column or row is changed. In ADO.NET there are two types of constraints; ForeignKeyConstraint and UniqueConstraint. A constraint is a rule used to maintain the integrity of the data in the DataTable. For example, when we delete a value used in one or more related tables, a ForeignKeyConstraint determines whether the values in the related tables are also deleted, set to null values, set to default values, or whether no action occurs. A UniqueConstraint, on the other hand, ensures that all values within a particular table are unique. A base Constraint constructor is not used. Primary or unique key constraints are created using the UniqueConstraint constructor, and foreign key constraints are created using the ForeignKeyConstraint constructor.
private void GetConstraints(DataTable firstTable) {
Console.WriteLine();
// Print the table's name.
Console.WriteLine("TableName: " + firstTable.TableName);
// Iterate through the collection and print each name and type value.
foreach(Constraint cs in firstTable.Constraints) {
Console.WriteLine("Constraint Name: " + cs.ConstraintName);
Console.WriteLine("Type: " + cs.GetType().ToString());
// If the constraint is a UniqueConstraint, print its properties using
// a function below.
if (cs is UniqueConstraint) {
PrintUniqueConstraintProperties(cs);
}
// If the constraint is a ForeignKeyConstraint, print its properties
// using a function below.
if (cs is ForeignKeyConstraint) {
PrintForeigKeyConstraintProperties(cs);
}
}
}
private void PrintUniqueConstraintProperties(Constraint cs) {
UniqueConstraint uCS;
uCS = (UniqueConstraint) cs;
// Get the Columns as an array.
DataColumn[] colArray;
colArray = uCS.Columns;
// Print each column's name.
for (int i = 0; i < colArray.Length; i++) {
Console.WriteLine("Column Name: " + colArray[i].ColumnName);
}
}
private void PrintForeigKeyConstraintProperties(Constraint cs) {
ForeignKeyConstraint fkeyCS;
fkeyCS = (ForeignKeyConstraint) cs;
// Get the Columns as an array.
DataColumn[] colArray;
colArray = fkeyCS.Columns;
// Print each column's name.
for (int i = 0; i < colArray.Length; i++) {
Console.WriteLine("Column Name: " + colArray[i].ColumnName);
}
Console.WriteLine();
// Get the related columns and print each column name.
colArray = fkeyCS.RelatedColumns;
for (int i = 0; i < colArray.Length; i++) {
Console.WriteLine("Related Column Name: " + colArray[i].ColumnName);
}
Console.WriteLine();
}
Here is one more detailed article: ADO.NET DataTable Tutorial.
Next >> Constructing an ADO.NET DataTable Dynamically