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 as well as independently it's own.
A DataTable consists of Columns, Rows, and a 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, or 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 are 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. 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) {
-
-
- DataColumn firstColumn = new DataColumn();
- firstColumn.DataType = System.Type.GetType("System.Decimal");
- firstColumn.AllowDBNull = false;
- firstColumn.Caption = "Price";
- firstColumn.ColumnName = "Price";
- firstColumn.DefaultValue = 25;
-
- firstTable.Columns.Add(firstColumn);
-
- DataRow firstRow;
- for (int i = 0; i < 10; i++) {
- firstRow = firstTable.NewRow();
- firstRow["Price"] = i + 1;
-
- 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 and evaluate; and 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 AcceptChanges method. By calling Delete, we can programmatically check which rows are marked for removal before actually deleting them.
- private void CreateNewDataRow() {
-
- DataTable firstTable;
- firstTable = MakeNamesTable();
-
- DataRow firstRow;
- firstRow = firstTable.NewRow();
-
- 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() {
-
- DataTable namesTable = new DataTable("Names");
-
- 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);
-
- DataColumn[] keys = new DataColumn[1];
- keys[0] = idColumn;
- namesTable.PrimaryKey = keys;
-
- 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 that is 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, simply 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();
-
- Console.WriteLine("TableName: " + firstTable.TableName);
-
- foreach(Constraint cs in firstTable.Constraints) {
- Console.WriteLine("Constraint Name: " + cs.ConstraintName);
- Console.WriteLine("Type: " + cs.GetType().ToString());
-
-
- if (cs is UniqueConstraint) {
- PrintUniqueConstraintProperties(cs);
- }
-
-
- if (cs is ForeignKeyConstraint) {
- PrintForeigKeyConstraintProperties(cs);
- }
- }
- }
- private void PrintUniqueConstraintProperties(Constraint cs) {
- UniqueConstraint uCS;
- uCS = (UniqueConstraint) cs;
-
- DataColumn[] colArray;
- colArray = uCS.Columns;
-
- 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;
-
- DataColumn[] colArray;
- colArray = fkeyCS.Columns;
-
- for (int i = 0; i < colArray.Length; i++) {
- Console.WriteLine("Column Name: " + colArray[i].ColumnName);
- }
- Console.WriteLine();
-
- 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.