The schema of a DataTable represents its structure in terms of columns and constraints. You define the schema of a DataTable object using DataColumn objects as well as ForeignKeyConstraint and UniqueConstraint objects. This schema definition can either happen automatically when data is pulled from a data source, or manually by creating those objects programmatically and then attaching them to the DataTable object.
In general, the columns in a DataTable object can:
Recall from Case Sensitivity section in ADO.NET DataSets chapter, that references by name to table, columns, and constraints is case-sensitive. This means that two or more columns, relations, or constraints can exist in the same table that have the same name but differ in case. For example, because column names are case-sensitive, you can have two columns named ID and id. To refer to a specific column you must use the same exact letter-case.
Note that case-sensitivity does not apply if only one column (or relation of constraint) exist with a particular name. For example, if a DataTable object contains three columns named ID, Name, and Title, and title, then columns ID, and Name can be referenced using any case while Title and title must be referenced using the same exact case.
Also recall that DataSet.CaseSensitive property applies only to actual data in the data set and it allows you to control case sensitivity for filtering, searching, sorting, and enforcing constraints on the actual data. References by name to tables or relations in a data set are not affected by DataSet.CaseSensitive.
To programmatically add columns to a DataTable object, use the Columns collection to add individual DataColumn object:
private void CreateDataTable()
{
// Create a names DataTable
DataTable dt = new DataTable("Employees");
// First, add a primary key column. You can either use the DataTable.PrimaryKey or
// DataColumn.AllowDBNull and DataColumn.Unique
DataColumn dtPrimary = dt.Columns.Add( "ID", typeof(int) );
dtPrimary.AllowDBNull = false;
dtPrimary.Unique = true;
// dt.PrimaryKey = dtPrimary;
// Now start adding data columns
dt.Columns.Add( "Name", typeof(string) );
dt.Columns.Add( "Location", typeof(string) );
}
An expression column is a column whose values are calculated from other column values in the same row, or from column values of multiple rows. The following lists several possible expressions for use in an expression column:
private void CreateDataTable()
{
// Create a names DataTable
DataTable dt = new DataTable("Employees");
// Now start adding data columns
dt.Columns.Add( "Name", typeof(string) );
dt.Columns.Add( "Salary", typeof(string), "MinSalary * 10" );
// Expression column
}
Note that expressions can reference other columns, however, a circular reference in which two columns reference each other will generate an exception.
Creating an auto-incrementing columns is very easy:
// Create the column object
DataColumn dcInc = dtMyDataTable.Columns.Add( "ID", typeof(int));
// Then set the column as auto-increment column
dcInc.AutoIncrement = true;
dcInc.AutoIncrementSeed = 1;
// Starting value
dcInc.AutoIncrementStep =
1; //
Increment step
Recall that a primary key is one or more columns that uniquely identify a row. You can identify a DataColumn object as being a primary key by setting its PrimaryKey property. This will automatically set the AllowDBNull property of the data column to false and the Unique property to true. Recall that a DataColumn object can be designated as a primary key using either the DataColumn.PrimaryKey property or the combination of DataColumn.AllowDBNull and DataColumn.Unique properties.
The following shows how to create a primary key composed of two columns:
// Create an arrary of DataColumn
object to act as the primary key
DataColumn[] dtPrimaryKey = new DataColumn[2];
dtPrimaryKey[0] = dt.Columns["ID"];
dtPrimaryKey[1] = dt.Columns["Region"];
// Now set the primary key
dt.PrimaryKey = dtPrimaryKey;
Recall that a constraint is an automatic rule applied to a column or more that determines the course of action when the value is altered. In ADO.NET there are two kinds of constraints, ForeignKeyConstraint and UniqueConstraint. Both constraints are created automatically when a DataRelation is created between two DataTables (this action can be disabled by setting createConstraint parameter to false when creating the relation). Also note that a constraint is only enforced if DataSet.EnforceConstraints property is true.
A ForeignKeyConstraint object enforces rules about how updates and deletes to related tables are propagated. ForeignKeyConstraint.DeleteRule and ForeignKeyConstraint.UpdateRule properties define the action to be taken when the user attempts to delete or update a row in a related table. The following table describes the different settings available for both DeleteRule and UpdateRule properties:
| Constraint Rule | Description |
| Cascade | Deletes of updated related rows. This is the default. |
| SetNull | Sets values in related rows to DBNull. |
| SetDefault | Sets values in related rows the default value. |
| None | No action to be taken on related rows. |
Note that a ForeignKeyConstraint can restrict as well as propagate changes to related columns. For example, if ForeignKeyConstraint.DeleteRule is None, then a parent row cannot be deleted if it has child rows.
// Get a data set
DataSet ds = GetDataSetFromSomewhere();
// Create a foreign key constraint object
ForeignKeyConstraint EmpDeptFK = new ForeignKeyConstraint( "EmpDeptFK",
ds.Tables["Employee"].Columns["ID"],
ds.Tables["Dept"].Columns["ID"] );
// Set rules and then add to collection of constraints
EmpDeptFK.DeleteRule = Rule.None;
ds.Tables["Employee"].Constraints.Add( EmpDeptFK );
Recall that changes to rows can be committed/rolled using AcceptChanges/RejectChanges methods respectively. Both of these two methods can be called from a DataSet, DataTable, or DataRow objects. For example, DataSet.AcceptChanges commits changes to all rows in all tables, whereas DataTable.AcceptChanges commits changes to all rows in the given DataTable object, and DataRow.AcceptChanges commits changes only to the given DataRow.
When AcceptChanges is called, ForeignKeyConstraint.AcceptRejectRule property is called. AcceptRejectRule determines which action to take on child rows when AcceptChanges or RejectChanges methods are called. AcceptRejectRule can take the following values:
| AcceptRejectRule | Description |
| Cascade | Changes are cascaded across the relationship. In other words, accepts or rejects changes to child rows based on whether AcceptChanges or RejectChanges was called. |
| None | No action to be taken on related rows. In other words, calling AcceptChanges or RejectChanges has no effect on child rows. |
A unique constraint represents a constraint on a column or a set of columns in which all values must be unique. There are several ways to create a unique constraint on a column or set of columns:
After creating a DataTable, you can add, edit, view and delete data; you can monitor errors and events; and you can query data. When modifying a DataTable you can also verify whether changes are accurate and determine whether to programmatically accept or reject changes.
Note: this section discusses how to manually add data to a DataTable. Obviously, calling DataSet.Fill method creates and populates DataTable objects automatically.
After you create a DataTable and define its structure and constraints, you can add new rows to the table. To add a new row and populate it with data:
// Create and initialize a new row
with data
DataRow drNew = dtMyDataTable.NewRow();
drNew["FirstName"] = "Yazan";
drNew["LastName"] = "Diranieh"
// Now add the row to the data table
dtMyDataTable.Rows.Add( drNew );
// A quicker way of adding a new
row. The code below is equivalent to the code block above
dtMyDataTable.Rows.Add( new object[] {"Yazan", "Diranieh"}
);
Data in a DataTable can be accessed using DataTable.Rows and DataTable.Columns properties. You can also use DataTable.Select method to view a subset of data according to search criteria, sort order and filter.
// Accessing rows and columns
directly. Returns the original records
object obColValue = dt.Rows[nRowIndex][nColumnIndex, DataRowVersion.Original];
// Using DataTable.Select to return an array of DataRow
objects. Returns the original version of all modified records
DataRow[] aRows = dt.Select( null, null, DataViewRowState.ModifiedOriginal );
When changes are made to column values in a DataRow, the changes are immediately placed in the DataRow object and assigned a DataRowVersion of Current. The state of the DataRow is then assigned a DataRowState of Modified (DataRowVersion applies to a column values and DataRowState applies to a row status). These changes can then be accepted/rejected using AcceptChanges/RejectChanges.
You edit data in a DataTable by editing individual DataRow objects. The DataRow class provides BeginEdit, CancelEdit, and EndEdit to suspend the state of the row while you are editing it. When you begin an edit operation using BeginEdit, the DataRowVersion of the each column changes to Proprosed. During the editing process, you can supply validation logic to individual columns by evaluating the ProposedValue in the DataTable.ColumnChanged event. After you evaluate the proposed value, you can either modify it or cancel the edit. When the edit is ended, the row moves out of the Proposed state. However, the data set does not actually accept the changes unitl AcceptChanges have been called.
private void btnDataTableEdit_Click(object sender, System.EventArgs e)
{
// Get data
DataSet ds = GetDataSet("select EmployeeID, LastName, FirstName, Title from Employees");
DataTable dt = ds.Tables[0];
// Trap changes to columns
dt.ColumnChanged += new DataColumnChangeEventHandler( OnColChanged );
// Now add a new and initialized row
DataRow drNew = dt.NewRow();
drNew["EmployeeID"] = 1000;
drNew["LastName"] = "Diranieh";
drNew["FirstName"] = "Yazan";
drNew["Title"] = "";
// This column causes an error
// Because CancelEdit was invoked on drNew (Title
column is empty), the drNew is now in
// an invalid state and was not added to the parent table
dt.Rows.Add( drNew );
// drNew does not show up in the grid!
dataGrid1.SetDataBinding(dt, "" );
}
// Trap changes to all columns. This handler is invoked
for each column as column value is changed
private void OnColChanged( object oSender, System.Data.DataColumnChangeEventArgs e )
{
// If 'Title' column is empty, cancel
the row
if (e.Column.ColumnName == "Title" )
{
if (e.ProposedValue.Equals(""))
{
Trace.WriteLine("Title cannot be empty. Please resubmit");
e.Row.CancelEdit();
}
}
}
Row State: Used to indicate the status of the row as a whole. For example, has the row been deleted, added, modified, or is it unchanged? This information is maintained by the DataRow.RowState property.
Row Version: Used to hold multiple data versions of the same row. For example, the Original version returns the a DataRow whose values reflect the original values before the edit process, while the Proporsed version returns a DataRow whose values reflect the current edit values. A specific version can be returned by applying the appropriate DataRowVersion enum value when retrieving rows
In other words, row version applies to a column value and row state applies to a row status as a whole. For example, if you make a modification to a column in a row, the row state will be changed to Modified and there will be two versions of data available in the same row: Current which contains current values, and Original which contains row values before the column was modified.
private void btnVersionAndState_Click(object sender, System.EventArgs e)
{
try
{
// Get data
DataSet ds = GetDataSet("select EmployeeID, LastName, FirstName, Title from Employees");
DataTable dt = ds.Tables[0];
// Now change a column value in the first row
DataRow drFirst = dt.Rows[0];
drFirst[0] = 1000;
// Now let's examine row state and row vesion
// What is the state of the row
DataRowState state = drFirst.RowState;
Trace.WriteLine( "State: " + state.ToString() );
// Retireve various version
of the first column
object obCurrent = drFirst[0, DataRowVersion.Current ];
// 1000
object obDefault = drFirst[0, DataRowVersion.Default ];
// 1000
object obOriginal = drFirst[0, DataRowVersion.Original ];
// 1
object obProposed = drFirst[0, DataRowVersion.Proposed ];
// throws VersionNotFoundException exception
}
catch( Exception ex )
{
Trace.WriteLine( ex.Message );
}
}
Note the effects of calling AcceptChanges or RejectChanges on row states and versions:
There are two different ways to delete a row from a data table:
Using DataRow.Delete is preferable especially when updating the data source back from a data table. When the DataAdapter encounters a row marked as Deleted, the data adapter will execute its DeleteCommand to remove the data row from the data source. The row can then be removed using AccpetChanges. If you have used Rows.Remove method, the data row will be removed entirely from the table but the DataAdapter will not delete the row at the data source.
To avoid having to respond to error immediately each time a row error occurs while editing values in a DataTable, you can add error information to a row, to be used later. Error information can be added to a row using DataRow.RowError property, which will automatically set DataRow.HasErrors property to true. If DataRow.HasErrors is true and the DataRow is part of a DataTable, then DataTable.HasErrors will be true as well. The same applies to DataSet. In other words, HasErrors will cascade from DataRow to DataTable to DataSet.
To examine for errors, check the HasErrors property and if true, use DataTable.GetErrors to return rows in error:
private void btnDataRowErrors_Click(object sender, System.EventArgs e)
{
// Get data
DataSet ds = GetDataSet("select EmployeeID, LastName, FirstName, Title from Employees");
DataTable dt = ds.Tables[0];
// Examine the first row and if some criteria is not met, indicate that this row
has an error
dt.Rows[0].RowError = "This is a custom error";
// Check: HasErrors should now be true
bool bHasErr = dt.HasErrors;
// bHasErr is true
// Now process errors
DataRow[] adrErrorRows = dt.GetErrors();
for (int i = 0; i < adrErrorRows.Length; i++)
{
Trace.WriteLine( adrErrorRows[i].RowError );
}
}