Terms in ADO.NET:
1) DataTable: DataSet is made up of a collection of tables, relationships, and constraints. In
ADO.NET, DataTable objects are used to represent the tables in a DataSet.
A DataTable represents one table of in-memory relational data. The data is local to the .NET-based application in which it resides, but can be populated from a data source such as Microsoft SQL Server using a DataAdapter.
The DataTable class is a member of the System.Data namespace.
You can create and use a DataTable independently or as a member of a DataSet, can also be used in conjunction with other .NET Framework objects, including the DataView.
The DataSet has Tables property which contains a collection of DataTable that it holds.
The schema or structure of a table is represented by columns and constraints. You define the schema of a DataTable using DataColumn objects as well as ForeignKeyConstraint and UniqueConstraint objects.
In addition to a schema, a DataTable must also have rows to contain and order data. The DataRow class represents the actual data contained in a table. You use the DataRow and its properties and methods to retrieve, evaluate, and manipulate the data in a table. As you access and change the data within a row, the DataRow object maintains both its current and original state.
You can create parent-child relationships between tables using one or more related columns in the tables. You create a relationship between DataTable objects using a DataRelation. DataRelation objects can then be used to return the related child or parent rows of a particular row.
Creating DataTable: If you are creating a DataTable programmatically, you must first define its schema by adding DataColumn objects to the DataColumnCollection.
DataTable workTable = new DataTable("Customers");
/*adding column to table*/
DataColumn workCol = workTable.Columns.Add("CustID", typeof(Int32));
workCol.AllowDBNull = false;
workCol.Unique = true;
workTable.Columns.Add("CustLName", typeof(String));
workTable.Columns.Add("CustFName", typeof(String));
workTable.Columns.Add("Purchases", typeof(Double));
In the example, notice that the properties for the CustID column are set to not allow DBNull values and to constrain values to be unique. However, if you define the CustID column as the primary key column of the table, the AllowDBNull property will automatically be set to false and the Unique property will automatically be set to true.
Defining Primary Keys:
A database table commonly has a column or group of columns that uniquely identifies each row in the table. This identifying column or group of columns is called the primary key.
When you identify a single DataColumn as the PrimaryKey for a DataTable, the table automatically sets the AllowDBNull property of the column to false and the Unique property to true. For multiple-column primary keys, only the AllowDBNull property is automatically set to false.
The PrimaryKey property of a DataTable receives as its value an array of one or more DataColumn objects, as shown in the following examples. The first example defines a single column as the primary key.
workTable.PrimaryKey = new DataColumn[] {workTable.Columns["CustID"]};
// Or
DataColumn[] columns = new DataColumn[1];
columns[0] = workTable.Columns["CustID"];
workTable.PrimaryKey = columns;
The following example defines two columns as a primary key.
workTable.PrimaryKey = new DataColumn[] {workTable.Columns["CustLName"],
workTable.Columns["CustFName"]};
// Or
DataColumn[] keyColumn = new DataColumn[2];
keyColumn[0] = workTable.Columns["CustLName"];
keyColumn[1] = workTable.Columns["CustFName"];
workTable.PrimaryKey = keyColumn;
Adding Data to a DataTable:
After you create a DataTable and define its structure using columns and constraints, you 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 you 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 = workTable.NewRow();
You then can manipulate the newly added row using an index or the column name, as shown in the following example.
workRow["CustLName"] = "Smith";
workRow[1] = "Smith";
After data is inserted into the new row, the Add method is used to add the row to the DataRowCollection, shown in the following code.
workTable.Rows.Add(workRow);
You can also call the Add method to add a new row by passing in an array of values, typed as Object, as shown in the following example.
workTable.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 10 rows to the newly created Customers table.
DataRow workRow;
for (int i = 0; i <= 9; i++)
{
workRow = workTable.NewRow();
workRow[0] = i;
workRow[1] = "CustName" + i.ToString();
workTable.Rows.Add(workRow);
}
DataTable Constraints:
You can use constraints to enforce restrictions on the data in a DataTable, in order to maintain the integrity of the data. A constraint is an automatic rule, applied to a column or related columns, that determines the course of action when the value of a row is somehow altered. Constraints are enforced when the System.Data.DataSet.EnforceConstraints property of the DataSet is true. For a code example that shows how to set the EnforceConstraints property, see the EnforceConstraints reference topic.
There are two kinds of constraints in ADO.NET: the ForeignKeyConstraint and the UniqueConstraint. By default, both constraints are created automatically when you create a relationship between two or more tables by adding a DataRelation to the DataSet. However, you can disable this behavior by specifying createConstraints = false when creating the relation.
ForeignKeyConstraint:
A ForeignKeyConstraint enforces rules about how updates and deletes to related tables are propagated. For example, if a value in a row of one table is updated or deleted, and that same value is also used in one or more related tables, a ForeignKeyConstraint determines what happens in the related tables.
The DeleteRule and UpdateRule properties of the ForeignKeyConstraint 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 the DeleteRule and UpdateRule properties of the ForeignKeyConstraint.
Rule setting Description
Cascade Delete or update related rows.
SetNull Set values in related rows to DBNull.
SetDefault Set values in related rows to the default value.
None Take no action on related rows. This is the default.
A ForeignKeyConstraint can restrict, as well as propagate, changes to related columns. Depending on the properties set for the ForeignKeyConstraint of a column, if the EnforceConstraints property of the DataSet is true, performing certain operations on the parent row will result in an exception. For example, if the DeleteRule property of the ForeignKeyConstraint is None, a parent row cannot be deleted if it has any child rows.
You can create a foreign key constraint between single columns or between an array of columns by using the ForeignKeyConstraint constructor. Pass the resulting ForeignKeyConstraint object to the Add method of the table's Constraints property, which is a ConstraintCollection. You can also pass constructor arguments to several overloads of the Add method of a ConstraintCollection to create a ForeignKeyConstraint.
When creating a ForeignKeyConstraint, you can pass the DeleteRule and UpdateRule values to the constructor as arguments, or you can set them as properties as in the following example (where the DeleteRule value is set to None)
ForeignKeyConstraint custOrderFK = new ForeignKeyConstraint("CustOrderFK",
custDS.Tables["CustTable"].Columns["CustomerID"],
custDS.Tables["OrdersTable"].Columns["CustomerID"]);
custOrderFK.DeleteRule = Rule.None;
// Cannot delete a customer value that has associated existing orders.
custDS.Tables["OrdersTable"].Constraints.Add(custOrderFK);
AcceptRejectRule:
Changes to rows can be accepted using the AcceptChanges method or canceled using the RejectChanges method of the DataSet, DataTable, or DataRow. When a DataSet contains ForeignKeyConstraints, invoking the AcceptChanges or RejectChanges methods enforces the AcceptRejectRule. The AcceptRejectRule property of the ForeignKeyConstraint determines which action will be taken on the child rows when AcceptChanges or RejectChanges is called on the parent row.
The following table lists the available settings for the AcceptRejectRule.
Rule setting Description
Cascade Accept or reject changes to child rows.
None Take no action on child rows. This is the default.
Example
The following example creates a ForeignKeyConstraint, sets several of its properties, including the AcceptRejectRule, and adds it to the ConstraintCollection of a DataTable object.
private void CreateConstraint(DataSet dataSet,
string table1, string table2,string column1, string column2)
{
// Declare parent column and child column variables.
DataColumn parentColumn;
DataColumn childColumn;
ForeignKeyConstraint foreignKeyConstraint;
// Set parent and child column variables.
parentColumn = dataSet.Tables[table1].Columns[column1];
childColumn = dataSet.Tables[table2].Columns[column2];
foreignKeyConstraint = new ForeignKeyConstraint
("SupplierForeignKeyConstraint", parentColumn, childColumn);
// Set null values when a value is deleted.
foreignKeyConstraint.DeleteRule = Rule.SetNull;
foreignKeyConstraint.UpdateRule = Rule.Cascade;
foreignKeyConstraint.AcceptRejectRule = AcceptRejectRule.None;
// Add the constraint, and set EnforceConstraints to true.
dataSet.Tables[table1].Constraints.Add(foreignKeyConstraint);
dataSet.EnforceConstraints = true;
}
UniqueConstraint:
The UniqueConstraint object, which can be assigned either to a single column or to an array of columns in a DataTable, ensures that all data in the specified column or columns is unique per row. You can create a unique constraint for a column or array of columns by using the UniqueConstraint constructor. Pass the resulting UniqueConstraint object to the Add method of the table's Constraints property, which is a ConstraintCollection. You can also pass constructor arguments to several overloads of the Add method of a ConstraintCollection to create a UniqueConstraint. When creating a UniqueConstraint for a column or columns, you can optionally specify whether the column or columns are a primary key.
You can also create a unique constraint for a column by setting the Unique property of the column to true. Alternatively, setting the Unique property of a single column to false removes any unique constraint that may exist. Defining a column or columns as the primary key for a table will automatically create a unique constraint for the specified column or columns. If you remove a column from the PrimaryKey property of a DataTable, the UniqueConstraint is removed.
The following example creates a UniqueConstraint for two columns of a DataTable.
DataTable custTable = custDS.Tables["Customers"];
UniqueConstraint custUnique = new UniqueConstraint(new DataColumn[]
{custTable.Columns["CustomerID"],
custTable.Columns["CompanyName"]});
custDS.Tables["Customers"].Constraints.Add(custUnique);
DataTable Events:
The DataTable object provides a series of events that can be processed by an application. The following table describes DataTable events.
Event Description
Initialized
Occurs after the EndInit method of a DataTable is called. This event is intended primarily to support design-time scenarios.
ColumnChanged
Occurs after a value has been successfully changed in a DataColumn.
ColumnChanging
Occurs when a value has been submitted for a DataColumn.
RowChanged
Occurs after a DataColumn value or the RowState of a DataRow in the DataTable has been changed successfully.
RowChanging
Occurs when a change has been submitted for a DataColumn value or the RowState of a DataRow in the DataTable.
RowDeleted
Occurs after a DataRow in the DataTable has been marked as Deleted.
RowDeleting
Occurs before a DataRow in the DataTable is marked as Deleted.
TableCleared
Occurs after a call to the Clear method of the DataTable has successfully cleared every DataRow.
TableClearing
Occurs after the Clear method is called but before the Clear operation begins.
TableNewRow
Occurs after a new DataRow is created by a call to the NewRow method of the DataTable.
Disposed
Occurs when the DataTable is Disposed. Inherited from MarshalByValueComponent.
Most operations that add or delete rows do not raise the ColumnChanged and ColumnChanging events. However, the ReadXml method does raise ColumnChanged and ColumnChanging events, unless the XmlReadMode is set to DiffGram or is set to Auto when the XML document being read is a DiffGram.
Additional Related Events
________________________________________
The Constraints property holds a ConstraintCollection instance. The ConstraintCollection class exposes a CollectionChanged event. This event fires when a constraint is added, modified, or removed from the ConstraintCollection.
The Columns property holds a DataColumnCollection instance. The DataColumnCollection class exposes a CollectionChanged event. This event fires when a DataColumn is added, modified, or removed from the DataColumnCollection. Modifications that cause the event to fire include changes to the name, type, expression or ordinal position of a column.
The Tables property of a DataSet holds a DataTableCollection instance. The DataTableCollection class exposes both a CollectionChanged and a CollectionChanging event. These events fire when a DataTable is added to or removed from the DataSet.
Changes to DataRows can also trigger events for an associated DataView. The DataView class exposes a ListChanged event that fires when a DataColumn value changes or when the composition or sort order of the view changes. The DataRowView class exposes a PropertyChanged event that fires when an associated DataColumn value changes.
Sequence of Operations:
Here is the sequence of operations that occur when a DataRow is added, modified, or deleted:
1. Create the proposed record and apply any changes.
2. Check constraints for non-expression columns.
3. Raise the RowChanging or RowDeleting events as applicable.
4. Set the proposed record to be the current record.
5. Update any associated indexes.
6. Raise ListChanged events for associated DataView objects and PropertyChanged events for associated DataRowView objects.
7. Evaluate all expression columns, but delay checking any constraints on these columns.
8. Raise ListChanged events for associated DataView objects and PropertyChanged events for associated DataRowView objects affected by the expression column evaluations.
9. Raise RowChanged or RowDeleted events as applicable.
10. Check constraints on expression columns.
Example
The following example demonstrates how to create event handlers for the RowChanged, RowChanging, RowDeleted, RowDeleting, ColumnChanged, ColumnChanging, TableNewRow, TableCleared, and TableClearing events. Each event handler displays output in the console window when it is fired.
static void DataTableEvents()
{
DataTable table = new DataTable("Customers");
// Add two columns, id and name.
table.Columns.Add("id", typeof(int));
table.Columns.Add("name", typeof(string));
// Set the primary key.
table.Columns["id"].Unique = true;
table.PrimaryKey = new DataColumn[] { table.Columns["id"] };
// Add a RowChanged event handler.
table.RowChanged += new DataRowChangeEventHandler(Row_Changed);
// Add a RowChanging event handler.
table.RowChanging += new DataRowChangeEventHandler(Row_Changing);
// Add a RowDeleted event handler.
table.RowDeleted += new DataRowChangeEventHandler(Row_Deleted);
// Add a RowDeleting event handler.
table.RowDeleting += new DataRowChangeEventHandler(Row_Deleting);
// Add a ColumnChanged event handler.
table.ColumnChanged += new
DataColumnChangeEventHandler(Column_Changed);
// Add a ColumnChanging event handler.
table.ColumnChanging += new
DataColumnChangeEventHandler(Column_Changing);
// Add a TableNewRow event handler.
table.TableNewRow += new
DataTableNewRowEventHandler(Table_NewRow);
// Add a TableCleared event handler.
table.TableCleared += new
DataTableClearEventHandler(Table_Cleared);
// Add a TableClearing event handler.
table.TableClearing += new
DataTableClearEventHandler(Table_Clearing);
// Add a customer.
DataRow row = table.NewRow();
row["id"] = 1;
row["name"] = "Customer1";
table.Rows.Add(row);
table.AcceptChanges();
// Change the customer name.
table.Rows[0]["name"] = "ChangedCustomer1";
// Delete the row.
table.Rows[0].Delete();
// Clear the table.
table.Clear();
}
private static void Row_Changed(object sender, DataRowChangeEventArgs e)
{
Console.WriteLine("Row_Changed Event: name={0}; action={1}",
e.Row["name"], e.Action);
}
private static void Row_Changing(object sender, DataRowChangeEventArgs e)
{
Console.WriteLine("Row_Changing Event: name={0}; action={1}",
e.Row["name"], e.Action);
}
private static void Row_Deleted(object sender, DataRowChangeEventArgs e)
{
Console.WriteLine("Row_Deleted Event: name={0}; action={1}",
e.Row["name", DataRowVersion.Original], e.Action);
}
private static void Row_Deleting(object sender,
DataRowChangeEventArgs e)
{
Console.WriteLine("Row_Deleting Event: name={0}; action={1}",
e.Row["name"], e.Action);
}
private static void Column_Changed(object sender, DataColumnChangeEventArgs e)
{
Console.WriteLine("Column_Changed Event: ColumnName={0}; RowState={1}",
e.Column.ColumnName, e.Row.RowState);
}
private static void Column_Changing(object sender, DataColumnChangeEventArgs e)
{
Console.WriteLine("Column_Changing Event: ColumnName={0}; RowState={1}",
e.Column.ColumnName, e.Row.RowState);
}
private static void Table_NewRow(object sender,
DataTableNewRowEventArgs e)
{
Console.WriteLine("Table_NewRow Event: RowState={0}",
e.Row.RowState.ToString());
}
private static void Table_Cleared(object sender, DataTableClearEventArgs e)
{
Console.WriteLine("Table_Cleared Event: TableName={0}; Rows={1}",
e.TableName, e.Table.Rows.Count.ToString());
}
private static void Table_Clearing(object sender, DataTableClearEventArgs e)
{
Console.WriteLine("Table_Clearing Event: TableName={0}; Rows={1}",
e.TableName, e.Table.Rows.Count.ToString());
}
Some Illustrations of working with Datatable:
The following example creates two DataTable objects and one DataRelation object, and adds the new objects to a DataSet. The tables are then displayed in a DataGridView control.
// Put the next line into the Declarations section.
private System.Data.DataSet dataSet;
private void MakeDataTables()
{
// Run all of the functions.
MakeParentTable();
MakeChildTable();
MakeDataRelation();
BindToDataGrid();
}
private void MakeParentTable()
{
// Create a new DataTable.
System.Data.DataTable table = new DataTable("ParentTable");
// Declare variables for DataColumn and DataRow objects.
DataColumn column;
DataRow row;
// Create new DataColumn, set DataType,
// ColumnName and add to DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "id";
column.ReadOnly = true;
column.Unique = true;
// Add the Column to the DataColumnCollection.
table.Columns.Add(column);
// Create second column.
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ParentItem";
column.AutoIncrement = false;
column.Caption = "ParentItem";
column.ReadOnly = false;
column.Unique = false;
// Add the column to the table.
table.Columns.Add(column);
// Make the ID column the primary key column.
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = table.Columns["id"];
table.PrimaryKey = PrimaryKeyColumns;
// Instantiate the DataSet variable.
dataSet = new DataSet();
// Add the new DataTable to the DataSet.
dataSet.Tables.Add(table);
// Create three new DataRow objects and add
// them to the DataTable
for (int i = 0; i<= 2; i++)
{
row = table.NewRow();
row["id"] = i;
row["ParentItem"] = "ParentItem " + i;
table.Rows.Add(row);
}
}
private void MakeChildTable()
{
// Create a new DataTable.
DataTable table = new DataTable("childTable");
DataColumn column;
DataRow row;
// Create first column and add to the DataTable.
column = new DataColumn();
column.DataType= System.Type.GetType("System.Int32");
column.ColumnName = "ChildID";
column.AutoIncrement = true;
column.Caption = "ID";
column.ReadOnly = true;
column.Unique = true;
// Add the column to the DataColumnCollection.
table.Columns.Add(column);
// Create second column.
column = new DataColumn();
column.DataType= System.Type.GetType("System.String");
column.ColumnName = "ChildItem";
column.AutoIncrement = false;
column.Caption = "ChildItem";
column.ReadOnly = false;
column.Unique = false;
table.Columns.Add(column);
// Create third column.
column = new DataColumn();
column.DataType= System.Type.GetType("System.Int32");
column.ColumnName = "ParentID";
column.AutoIncrement = false;
column.Caption = "ParentID";
column.ReadOnly = false;
column.Unique = false;
table.Columns.Add(column);
dataSet.Tables.Add(table);
// Create three sets of DataRow objects,
// five rows each, and add to DataTable.
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 0 ;
table.Rows.Add(row);
}
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i + 5;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 1 ;
table.Rows.Add(row);
}
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i + 10;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 2 ;
table.Rows.Add(row);
}
}
private void MakeDataRelation()
{
// DataRelation requires two DataColumn
// (parent and child) and a name.
DataColumn parentColumn =
dataSet.Tables["ParentTable"].Columns["id"];
DataColumn childColumn =
dataSet.Tables["ChildTable"].Columns["ParentID"];
DataRelation relation = new
DataRelation("parent2Child", parentColumn, childColumn);
dataSet.Tables["ChildTable"].ParentRelations.Add(relation);
}
private void BindToDataGrid()
{
// Instruct the DataGrid to bind to the DataSet, with the
// ParentTable as the topmost DataTable.
dataGrid1.SetDataBinding(dataSet,"ParentTable");
}
1) DataTable: DataSet is made up of a collection of tables, relationships, and constraints. In
ADO.NET, DataTable objects are used to represent the tables in a DataSet.
A DataTable represents one table of in-memory relational data. The data is local to the .NET-based application in which it resides, but can be populated from a data source such as Microsoft SQL Server using a DataAdapter.
The DataTable class is a member of the System.Data namespace.
You can create and use a DataTable independently or as a member of a DataSet, can also be used in conjunction with other .NET Framework objects, including the DataView.
The DataSet has Tables property which contains a collection of DataTable that it holds.
The schema or structure of a table is represented by columns and constraints. You define the schema of a DataTable using DataColumn objects as well as ForeignKeyConstraint and UniqueConstraint objects.
In addition to a schema, a DataTable must also have rows to contain and order data. The DataRow class represents the actual data contained in a table. You use the DataRow and its properties and methods to retrieve, evaluate, and manipulate the data in a table. As you access and change the data within a row, the DataRow object maintains both its current and original state.
You can create parent-child relationships between tables using one or more related columns in the tables. You create a relationship between DataTable objects using a DataRelation. DataRelation objects can then be used to return the related child or parent rows of a particular row.
Creating DataTable: If you are creating a DataTable programmatically, you must first define its schema by adding DataColumn objects to the DataColumnCollection.
DataTable workTable = new DataTable("Customers");
/*adding column to table*/
DataColumn workCol = workTable.Columns.Add("CustID", typeof(Int32));
workCol.AllowDBNull = false;
workCol.Unique = true;
workTable.Columns.Add("CustLName", typeof(String));
workTable.Columns.Add("CustFName", typeof(String));
workTable.Columns.Add("Purchases", typeof(Double));
In the example, notice that the properties for the CustID column are set to not allow DBNull values and to constrain values to be unique. However, if you define the CustID column as the primary key column of the table, the AllowDBNull property will automatically be set to false and the Unique property will automatically be set to true.
Defining Primary Keys:
A database table commonly has a column or group of columns that uniquely identifies each row in the table. This identifying column or group of columns is called the primary key.
When you identify a single DataColumn as the PrimaryKey for a DataTable, the table automatically sets the AllowDBNull property of the column to false and the Unique property to true. For multiple-column primary keys, only the AllowDBNull property is automatically set to false.
The PrimaryKey property of a DataTable receives as its value an array of one or more DataColumn objects, as shown in the following examples. The first example defines a single column as the primary key.
workTable.PrimaryKey = new DataColumn[] {workTable.Columns["CustID"]};
// Or
DataColumn[] columns = new DataColumn[1];
columns[0] = workTable.Columns["CustID"];
workTable.PrimaryKey = columns;
The following example defines two columns as a primary key.
workTable.PrimaryKey = new DataColumn[] {workTable.Columns["CustLName"],
workTable.Columns["CustFName"]};
// Or
DataColumn[] keyColumn = new DataColumn[2];
keyColumn[0] = workTable.Columns["CustLName"];
keyColumn[1] = workTable.Columns["CustFName"];
workTable.PrimaryKey = keyColumn;
Adding Data to a DataTable:
After you create a DataTable and define its structure using columns and constraints, you 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 you 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 = workTable.NewRow();
You then can manipulate the newly added row using an index or the column name, as shown in the following example.
workRow["CustLName"] = "Smith";
workRow[1] = "Smith";
After data is inserted into the new row, the Add method is used to add the row to the DataRowCollection, shown in the following code.
workTable.Rows.Add(workRow);
You can also call the Add method to add a new row by passing in an array of values, typed as Object, as shown in the following example.
workTable.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 10 rows to the newly created Customers table.
DataRow workRow;
for (int i = 0; i <= 9; i++)
{
workRow = workTable.NewRow();
workRow[0] = i;
workRow[1] = "CustName" + i.ToString();
workTable.Rows.Add(workRow);
}
DataTable Constraints:
You can use constraints to enforce restrictions on the data in a DataTable, in order to maintain the integrity of the data. A constraint is an automatic rule, applied to a column or related columns, that determines the course of action when the value of a row is somehow altered. Constraints are enforced when the System.Data.DataSet.EnforceConstraints property of the DataSet is true. For a code example that shows how to set the EnforceConstraints property, see the EnforceConstraints reference topic.
There are two kinds of constraints in ADO.NET: the ForeignKeyConstraint and the UniqueConstraint. By default, both constraints are created automatically when you create a relationship between two or more tables by adding a DataRelation to the DataSet. However, you can disable this behavior by specifying createConstraints = false when creating the relation.
ForeignKeyConstraint:
A ForeignKeyConstraint enforces rules about how updates and deletes to related tables are propagated. For example, if a value in a row of one table is updated or deleted, and that same value is also used in one or more related tables, a ForeignKeyConstraint determines what happens in the related tables.
The DeleteRule and UpdateRule properties of the ForeignKeyConstraint 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 the DeleteRule and UpdateRule properties of the ForeignKeyConstraint.
Rule setting Description
Cascade Delete or update related rows.
SetNull Set values in related rows to DBNull.
SetDefault Set values in related rows to the default value.
None Take no action on related rows. This is the default.
A ForeignKeyConstraint can restrict, as well as propagate, changes to related columns. Depending on the properties set for the ForeignKeyConstraint of a column, if the EnforceConstraints property of the DataSet is true, performing certain operations on the parent row will result in an exception. For example, if the DeleteRule property of the ForeignKeyConstraint is None, a parent row cannot be deleted if it has any child rows.
You can create a foreign key constraint between single columns or between an array of columns by using the ForeignKeyConstraint constructor. Pass the resulting ForeignKeyConstraint object to the Add method of the table's Constraints property, which is a ConstraintCollection. You can also pass constructor arguments to several overloads of the Add method of a ConstraintCollection to create a ForeignKeyConstraint.
When creating a ForeignKeyConstraint, you can pass the DeleteRule and UpdateRule values to the constructor as arguments, or you can set them as properties as in the following example (where the DeleteRule value is set to None)
ForeignKeyConstraint custOrderFK = new ForeignKeyConstraint("CustOrderFK",
custDS.Tables["CustTable"].Columns["CustomerID"],
custDS.Tables["OrdersTable"].Columns["CustomerID"]);
custOrderFK.DeleteRule = Rule.None;
// Cannot delete a customer value that has associated existing orders.
custDS.Tables["OrdersTable"].Constraints.Add(custOrderFK);
AcceptRejectRule:
Changes to rows can be accepted using the AcceptChanges method or canceled using the RejectChanges method of the DataSet, DataTable, or DataRow. When a DataSet contains ForeignKeyConstraints, invoking the AcceptChanges or RejectChanges methods enforces the AcceptRejectRule. The AcceptRejectRule property of the ForeignKeyConstraint determines which action will be taken on the child rows when AcceptChanges or RejectChanges is called on the parent row.
The following table lists the available settings for the AcceptRejectRule.
Rule setting Description
Cascade Accept or reject changes to child rows.
None Take no action on child rows. This is the default.
Example
The following example creates a ForeignKeyConstraint, sets several of its properties, including the AcceptRejectRule, and adds it to the ConstraintCollection of a DataTable object.
private void CreateConstraint(DataSet dataSet,
string table1, string table2,string column1, string column2)
{
// Declare parent column and child column variables.
DataColumn parentColumn;
DataColumn childColumn;
ForeignKeyConstraint foreignKeyConstraint;
// Set parent and child column variables.
parentColumn = dataSet.Tables[table1].Columns[column1];
childColumn = dataSet.Tables[table2].Columns[column2];
foreignKeyConstraint = new ForeignKeyConstraint
("SupplierForeignKeyConstraint", parentColumn, childColumn);
// Set null values when a value is deleted.
foreignKeyConstraint.DeleteRule = Rule.SetNull;
foreignKeyConstraint.UpdateRule = Rule.Cascade;
foreignKeyConstraint.AcceptRejectRule = AcceptRejectRule.None;
// Add the constraint, and set EnforceConstraints to true.
dataSet.Tables[table1].Constraints.Add(foreignKeyConstraint);
dataSet.EnforceConstraints = true;
}
UniqueConstraint:
The UniqueConstraint object, which can be assigned either to a single column or to an array of columns in a DataTable, ensures that all data in the specified column or columns is unique per row. You can create a unique constraint for a column or array of columns by using the UniqueConstraint constructor. Pass the resulting UniqueConstraint object to the Add method of the table's Constraints property, which is a ConstraintCollection. You can also pass constructor arguments to several overloads of the Add method of a ConstraintCollection to create a UniqueConstraint. When creating a UniqueConstraint for a column or columns, you can optionally specify whether the column or columns are a primary key.
You can also create a unique constraint for a column by setting the Unique property of the column to true. Alternatively, setting the Unique property of a single column to false removes any unique constraint that may exist. Defining a column or columns as the primary key for a table will automatically create a unique constraint for the specified column or columns. If you remove a column from the PrimaryKey property of a DataTable, the UniqueConstraint is removed.
The following example creates a UniqueConstraint for two columns of a DataTable.
DataTable custTable = custDS.Tables["Customers"];
UniqueConstraint custUnique = new UniqueConstraint(new DataColumn[]
{custTable.Columns["CustomerID"],
custTable.Columns["CompanyName"]});
custDS.Tables["Customers"].Constraints.Add(custUnique);
DataTable Events:
The DataTable object provides a series of events that can be processed by an application. The following table describes DataTable events.
Event Description
Initialized
Occurs after the EndInit method of a DataTable is called. This event is intended primarily to support design-time scenarios.
ColumnChanged
Occurs after a value has been successfully changed in a DataColumn.
ColumnChanging
Occurs when a value has been submitted for a DataColumn.
RowChanged
Occurs after a DataColumn value or the RowState of a DataRow in the DataTable has been changed successfully.
RowChanging
Occurs when a change has been submitted for a DataColumn value or the RowState of a DataRow in the DataTable.
RowDeleted
Occurs after a DataRow in the DataTable has been marked as Deleted.
RowDeleting
Occurs before a DataRow in the DataTable is marked as Deleted.
TableCleared
Occurs after a call to the Clear method of the DataTable has successfully cleared every DataRow.
TableClearing
Occurs after the Clear method is called but before the Clear operation begins.
TableNewRow
Occurs after a new DataRow is created by a call to the NewRow method of the DataTable.
Disposed
Occurs when the DataTable is Disposed. Inherited from MarshalByValueComponent.
Most operations that add or delete rows do not raise the ColumnChanged and ColumnChanging events. However, the ReadXml method does raise ColumnChanged and ColumnChanging events, unless the XmlReadMode is set to DiffGram or is set to Auto when the XML document being read is a DiffGram.
Additional Related Events
________________________________________
The Constraints property holds a ConstraintCollection instance. The ConstraintCollection class exposes a CollectionChanged event. This event fires when a constraint is added, modified, or removed from the ConstraintCollection.
The Columns property holds a DataColumnCollection instance. The DataColumnCollection class exposes a CollectionChanged event. This event fires when a DataColumn is added, modified, or removed from the DataColumnCollection. Modifications that cause the event to fire include changes to the name, type, expression or ordinal position of a column.
The Tables property of a DataSet holds a DataTableCollection instance. The DataTableCollection class exposes both a CollectionChanged and a CollectionChanging event. These events fire when a DataTable is added to or removed from the DataSet.
Changes to DataRows can also trigger events for an associated DataView. The DataView class exposes a ListChanged event that fires when a DataColumn value changes or when the composition or sort order of the view changes. The DataRowView class exposes a PropertyChanged event that fires when an associated DataColumn value changes.
Sequence of Operations:
Here is the sequence of operations that occur when a DataRow is added, modified, or deleted:
1. Create the proposed record and apply any changes.
2. Check constraints for non-expression columns.
3. Raise the RowChanging or RowDeleting events as applicable.
4. Set the proposed record to be the current record.
5. Update any associated indexes.
6. Raise ListChanged events for associated DataView objects and PropertyChanged events for associated DataRowView objects.
7. Evaluate all expression columns, but delay checking any constraints on these columns.
8. Raise ListChanged events for associated DataView objects and PropertyChanged events for associated DataRowView objects affected by the expression column evaluations.
9. Raise RowChanged or RowDeleted events as applicable.
10. Check constraints on expression columns.
Example
The following example demonstrates how to create event handlers for the RowChanged, RowChanging, RowDeleted, RowDeleting, ColumnChanged, ColumnChanging, TableNewRow, TableCleared, and TableClearing events. Each event handler displays output in the console window when it is fired.
static void DataTableEvents()
{
DataTable table = new DataTable("Customers");
// Add two columns, id and name.
table.Columns.Add("id", typeof(int));
table.Columns.Add("name", typeof(string));
// Set the primary key.
table.Columns["id"].Unique = true;
table.PrimaryKey = new DataColumn[] { table.Columns["id"] };
// Add a RowChanged event handler.
table.RowChanged += new DataRowChangeEventHandler(Row_Changed);
// Add a RowChanging event handler.
table.RowChanging += new DataRowChangeEventHandler(Row_Changing);
// Add a RowDeleted event handler.
table.RowDeleted += new DataRowChangeEventHandler(Row_Deleted);
// Add a RowDeleting event handler.
table.RowDeleting += new DataRowChangeEventHandler(Row_Deleting);
// Add a ColumnChanged event handler.
table.ColumnChanged += new
DataColumnChangeEventHandler(Column_Changed);
// Add a ColumnChanging event handler.
table.ColumnChanging += new
DataColumnChangeEventHandler(Column_Changing);
// Add a TableNewRow event handler.
table.TableNewRow += new
DataTableNewRowEventHandler(Table_NewRow);
// Add a TableCleared event handler.
table.TableCleared += new
DataTableClearEventHandler(Table_Cleared);
// Add a TableClearing event handler.
table.TableClearing += new
DataTableClearEventHandler(Table_Clearing);
// Add a customer.
DataRow row = table.NewRow();
row["id"] = 1;
row["name"] = "Customer1";
table.Rows.Add(row);
table.AcceptChanges();
// Change the customer name.
table.Rows[0]["name"] = "ChangedCustomer1";
// Delete the row.
table.Rows[0].Delete();
// Clear the table.
table.Clear();
}
private static void Row_Changed(object sender, DataRowChangeEventArgs e)
{
Console.WriteLine("Row_Changed Event: name={0}; action={1}",
e.Row["name"], e.Action);
}
private static void Row_Changing(object sender, DataRowChangeEventArgs e)
{
Console.WriteLine("Row_Changing Event: name={0}; action={1}",
e.Row["name"], e.Action);
}
private static void Row_Deleted(object sender, DataRowChangeEventArgs e)
{
Console.WriteLine("Row_Deleted Event: name={0}; action={1}",
e.Row["name", DataRowVersion.Original], e.Action);
}
private static void Row_Deleting(object sender,
DataRowChangeEventArgs e)
{
Console.WriteLine("Row_Deleting Event: name={0}; action={1}",
e.Row["name"], e.Action);
}
private static void Column_Changed(object sender, DataColumnChangeEventArgs e)
{
Console.WriteLine("Column_Changed Event: ColumnName={0}; RowState={1}",
e.Column.ColumnName, e.Row.RowState);
}
private static void Column_Changing(object sender, DataColumnChangeEventArgs e)
{
Console.WriteLine("Column_Changing Event: ColumnName={0}; RowState={1}",
e.Column.ColumnName, e.Row.RowState);
}
private static void Table_NewRow(object sender,
DataTableNewRowEventArgs e)
{
Console.WriteLine("Table_NewRow Event: RowState={0}",
e.Row.RowState.ToString());
}
private static void Table_Cleared(object sender, DataTableClearEventArgs e)
{
Console.WriteLine("Table_Cleared Event: TableName={0}; Rows={1}",
e.TableName, e.Table.Rows.Count.ToString());
}
private static void Table_Clearing(object sender, DataTableClearEventArgs e)
{
Console.WriteLine("Table_Clearing Event: TableName={0}; Rows={1}",
e.TableName, e.Table.Rows.Count.ToString());
}
Some Illustrations of working with Datatable:
The following example creates two DataTable objects and one DataRelation object, and adds the new objects to a DataSet. The tables are then displayed in a DataGridView control.
// Put the next line into the Declarations section.
private System.Data.DataSet dataSet;
private void MakeDataTables()
{
// Run all of the functions.
MakeParentTable();
MakeChildTable();
MakeDataRelation();
BindToDataGrid();
}
private void MakeParentTable()
{
// Create a new DataTable.
System.Data.DataTable table = new DataTable("ParentTable");
// Declare variables for DataColumn and DataRow objects.
DataColumn column;
DataRow row;
// Create new DataColumn, set DataType,
// ColumnName and add to DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "id";
column.ReadOnly = true;
column.Unique = true;
// Add the Column to the DataColumnCollection.
table.Columns.Add(column);
// Create second column.
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "ParentItem";
column.AutoIncrement = false;
column.Caption = "ParentItem";
column.ReadOnly = false;
column.Unique = false;
// Add the column to the table.
table.Columns.Add(column);
// Make the ID column the primary key column.
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = table.Columns["id"];
table.PrimaryKey = PrimaryKeyColumns;
// Instantiate the DataSet variable.
dataSet = new DataSet();
// Add the new DataTable to the DataSet.
dataSet.Tables.Add(table);
// Create three new DataRow objects and add
// them to the DataTable
for (int i = 0; i<= 2; i++)
{
row = table.NewRow();
row["id"] = i;
row["ParentItem"] = "ParentItem " + i;
table.Rows.Add(row);
}
}
private void MakeChildTable()
{
// Create a new DataTable.
DataTable table = new DataTable("childTable");
DataColumn column;
DataRow row;
// Create first column and add to the DataTable.
column = new DataColumn();
column.DataType= System.Type.GetType("System.Int32");
column.ColumnName = "ChildID";
column.AutoIncrement = true;
column.Caption = "ID";
column.ReadOnly = true;
column.Unique = true;
// Add the column to the DataColumnCollection.
table.Columns.Add(column);
// Create second column.
column = new DataColumn();
column.DataType= System.Type.GetType("System.String");
column.ColumnName = "ChildItem";
column.AutoIncrement = false;
column.Caption = "ChildItem";
column.ReadOnly = false;
column.Unique = false;
table.Columns.Add(column);
// Create third column.
column = new DataColumn();
column.DataType= System.Type.GetType("System.Int32");
column.ColumnName = "ParentID";
column.AutoIncrement = false;
column.Caption = "ParentID";
column.ReadOnly = false;
column.Unique = false;
table.Columns.Add(column);
dataSet.Tables.Add(table);
// Create three sets of DataRow objects,
// five rows each, and add to DataTable.
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 0 ;
table.Rows.Add(row);
}
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i + 5;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 1 ;
table.Rows.Add(row);
}
for(int i = 0; i <= 4; i ++)
{
row = table.NewRow();
row["childID"] = i + 10;
row["ChildItem"] = "Item " + i;
row["ParentID"] = 2 ;
table.Rows.Add(row);
}
}
private void MakeDataRelation()
{
// DataRelation requires two DataColumn
// (parent and child) and a name.
DataColumn parentColumn =
dataSet.Tables["ParentTable"].Columns["id"];
DataColumn childColumn =
dataSet.Tables["ChildTable"].Columns["ParentID"];
DataRelation relation = new
DataRelation("parent2Child", parentColumn, childColumn);
dataSet.Tables["ChildTable"].ParentRelations.Add(relation);
}
private void BindToDataGrid()
{
// Instruct the DataGrid to bind to the DataSet, with the
// ParentTable as the topmost DataTable.
dataGrid1.SetDataBinding(dataSet,"ParentTable");
}
No comments:
Post a Comment