23.3 Collections Reference

ChildRelations

DataRelationCollection cdrc = DataTable.ParentRelations;

Accesses the child DataRelationCollection for the DataTable, providing access to the child DataRelation objects belonging to the DataTable. The ChildRelations property can be used to add, remove, and examine the child DataRelation objects in a DataTable.

Example

See the Examples for the Relations collection in Chapter 22.

Note

See the Notes for the Relations collection in Chapter 22.

Columns

DataColumnCollection dcc = DataTable.Columns;

Accesses the DataColumnCollection for the DataTable, providing access to the DataColumn objects belonging to the DataTable. The Columns property can be used to add, remove, and examine the DataColumn objects in a DataTable.

Examples

There are two methods that can add a column to a table. The Add( ) method optionally takes arguments that specify the name, type, and expression of the column to be added. An existing column can be added by passing a reference to an existing column. If no arguments are passed, the default names Column1, Column2, and so on, are assigned to the new column. The following examples show how to create a column using the Add( ) method:

// adding a column using a reference to an existing column
DataColumn col = new DataColumn("MyColumn", typeof(System.Int32));
dt.Columns.Add(col);
 
// adding and creating a column in the same statement
dt.Columns.Add("MyColumn", typeof(System.Int32));

The second method is AddRange( ), which allows more than one column stored in a DataColumn array to be added to the table in a single statement, as shown in the following example:

DataTable dt = new DataTable("MyTable");

// create and add two columns to the DataColumn array
DataColumn[] dca = new DataColumn[]
    {new DataColumn("Col1", typeof(System.Int32)),
    new DataColumn("Col2", typeof(System.Int32))};

// add the columns in the array to the table
dt.Columns.AddRange(dca);

There are several properties and methods that can interrogate the collection of columns within a table. The Count( ) method returns the number of columns in a table.

Int32 colCount = dt.Columns.Count;

The Contains( ) method returns a value indicating whether a column with a specified name exists in the collection. The method takes the column name as an argument.

bool colExists = dt.Columns.Contains("MyColumn");

The IndexOf( ) method returns the index of a column having a specified name within the collection. The method returns the zero-based index value for the column if it exists in the collection or the value of -1 if the column doesn't exist. The method takes a single argument containing the column name.

Int32 colIndex = dt.Columns.IndexOf("MyColumn");

Finally, there are three methods that remove columns from the collection. The Remove( ) method removes a column with a specified name from the collection. The method takes a single argument containing either the column name or a reference to a DataColumn object. If the column doesn't exist in the collection, an ArgumentException is raised.

// remove a column by specifying the name of the column
dt.Columns.Remove("MyColumn");

// remove a column by specifying a reference to the column
DataColumn col = new DataColumn("MyColumn");
dt.Columns.Add(col);

// ... do some work

dt.Columns.Remove(col);

The RemoveAt( ) method removes a column with a specified column index from the collection. The method takes a single argument containing the zero-based index of the column to be removed. If the column doesn't exist in the collection, an IndexOutOfRangeException is raised.

// remove the first column from the collection
dt.Columns.RemoveAt(0);

Finally, the Clear( ) method removes all columns from the column collection:

dt.Columns.Clear();

The DataColumnCollection raises a single event, CollectionChanged, when a column is either added or removed from the collection. The CollectionChangeEventArgs properties provide information about the nature of the change as described in Table 23-5.

Table 23-5. CollectionChangeEventArgs enumeration

Property

Description

Action

Describes how the collection changed. This is a value from the CollectionChangeActionenumeration as described in Table 23-6.

Element

Gets the column that was changed.

Table 23-6 describes the values of the CollectionChangeAction enumeration, one of which is assigned to the Action property of the CollectionChangeEventArgs argument passed to the event handling the change to the DataColumnCollection.

Table 23-6. CollectionChangeAction enumeration

Value

Description

Add

A column was added to the table.

Remove

A column was removed from the table.

Refresh

The column collection, as a whole, has changed. This is most commonly a result of adding or removing multiple columns or of removing all columns from the collection.

The following example demonstrates handling the ColumnChange event:

dt.Columns.CollectionChanged +=  new CollectionChangeEventHandler
    (dtColumns_CollectionChanged);

private void dtColumns_CollectionChanged(object sender, 
  CollectionChangeEventArgs e)
{
    MessageBox.Show("Action = " + e.Action + Environment.NewLine +
    "Element = " + ((DataColumn) e.Element).ColumnName);
}

Notes

The DataColumnCollection class derives its standard functionality from the InternalDataCollectionBase class from which it inherits.

The commonly used public properties of the DataColumnCollection are listed and described in Table 23-7.

Table 23-7. DataColumnCollection public properties

Property

Description

Count

Gets the number of items in the DataColumnCollection.

Item

Gets the specified DataColumn from the DataColumnCollection. In C#, the Item property is the indexer for the class.

The commonly used public methods of the DataColumnCollection are listed and described in Table 23-8.

Table 23-8. DataColumnCollection public methods

Method

Description

Add(  )

Adds a DataColumn object to the collection.

AddRange(  )

Adds the objects in the array of DataColumn objects to the end of the collection.

CanRemove(  )

Returns a Boolean value indicating whether a specific item can be removed from the collection.

Clear(  )

Removes all items from the collection.

Contains(  )

Returns a Boolean value indicating whether a DataColumn with the specified name exists in the collection.

IndexOf(  )

Returns the index of the specified DataColumn. If the DataColumn doesn't exist in the collection, -1 is returned.

Remove(  )

Removes the specified DataColumn from the collection. An ArgumentException is generated if the specified DataColumn doesn't exist in the collection.

RemoveAt(  )

Removes the DataColumn at the specified index from the collection. An ArgumentException is raised if the collection doesn't have a DataColumn at the specified index.

Constraints

ConstraintCollection cc = DataTable.Constraints;

Accesses the ConstraintCollection for the DataTable, providing access to the Constraint objects belonging to the DataTable. The Constraints property can be used to add, remove, and examine the UniqueConstraint and ForeignKeyConstraint objects in a DataTable.

Examples

There are two methods that can add a constraint to a table. The Add( ) method takes an argument specifying a reference to an existing constraint or takes arguments specifying whether a unique constraint or foreign key constraint is being added. The following example demonstrates adding a constraint by specifying a reference to an existing constraint:

// add a unique constraint by reference
UniqueConstraint uc = new UniqueConstraint(dt.Columns["MyColumn"]);
dt.Constraints.Add(uc);

// add a foreign key constraint by reference
ForeignKeyConstraint fc = new ForeignKeyConstraint(
    dtParent.Columns["ParentColumn"],
    dtChild.Columns["ChildColumn"]);
dt.Constraints.Add(fc);

Two overloads of the Add( ) method create and add a UniqueConstraint in one statement. The methods take a constraint name, either a reference to a DataColumn or a DataColumn array, and an argument indicating whether the column or columns are a primary key.

// add a unique constraint that is also a primary key
dt.Constraints.Add("MyUniqueConstraint", dt.Columns["MyColumn"], true);

The other two overloads of the Add( ) method create and add a ForeignKeyConstraint in one statement. The methods take a constraint name, and either two DataColumn references or two DataColumn arrays.

// add a foreign key constraint based on two columns
dt.Constraints.Add("MyForeignKeyConstraint",
    dtParent.Columns["ParentCol1"],
    dtChild.Columns["ChildCol2"]);

The AddRange( ) method adds an array of Constraint objects to the end of the constraint collection:

Constraint c1, c2;

// ... code to define constraints c1 and c2

// add the constraints c1 and c2 to the ConstraintCollection for the table
dt.Constraints.AddRange(new Constraint[] {c1, c2});

Notes

The ConstraintCollection class derives its standard functionality from the InternalDataCollectionBase class from which it inherits.

The commonly used public properties of the ConstraintCollection are listed and described in Table 23-9.

Table 23-9. ConstraintCollection public properties

Property

Description

Count

Gets the number of items in the ConstraintCollection.

Item

Gets the specified Constraint from the ConstraintCollection. In C#, the Item property is the indexer for the class.

The commonly used public methods of the ConstraintCollection are listed and described in Table 23-10.

Table 23-10. ConstraintCollection public methods

Method

Description

Add(  )

Adds a Constraint object to the collection.

AddRange(  )

Adds the objects in the array of Constraint objects to the end of the collection.

CanRemove(  )

Returns a Boolean value indicating whether a specific item can be removed from the collection.

Clear(  )

Removes all items from the collection.

IndexOf(  )

Returns a Boolean value indicating whether a Constraint with the specified name exists in the collection.

Remove(  )

Returns the index of the specified Constraint. A value of -1 is returned if the Constraint doesn't exist in the collection.

RemoveAt(  )

Removes the specified Constraint from the collection. An ArgumentException is generated if the specified Constraint doesn't exist in the collection.

ExtendedProperties

PropertyCollection ep = DataTable.ExtendedProperties;

Accesses the PropertyCollection object of the DataTable that allows custom information related to the DataTable to be stored. Non-string properties aren't persisted when the DataTable is written as XML.

Example

The following example shows how to set and retrieve custom information for the DataTable using ExtendedProperties:

// set
dt.ExtendedProperties.Add("MyKey", "MyCustomData");

// get
String customData = dt.ExtendedProperties["MyKey"].ToString();

Note

See Notes for the ExtendedProperties collection section in Chapter 22.

ParentRelations

DataRelationCollection pdrc = DataTable.ParentRelations;

Accesses the parent DataRelationCollection for the DataTable, providing access to the parent DataRelation objects belonging to the DataTable. The ParentRelations property be used to add, remove, and examine the parent DataRelation object in a DataTable.

Example

See the Examples for the Relations collection in Chapter 22.

Note

See the Notes for the Relations collection in Chapter 22.

PrimaryKey

DataColumn[] pka = DataTable.PrimaryKey;
DataTable.PrimaryKey = pka;

Accesses the array of DataColumn objects that make up the primary key of the table.

Example

The primary key for a table can be set by specifying an array of DataColumn objects from the table. The following example shows how to create a primary key based on two columns:

// set the primary key based on two columns in the DataTable
DataTable dt = new DataTable("MyTable");
dt.Columns.Add("PK_Field1", typeof(System.Int32));
dt.Columns.Add("PK_Field2", typeof(System.Int32));
// add other table columns
dt.PrimaryKey = new DataColumn[] {dt.Columns["PK_Field1"],
    dt.Columns["PK_Field2"]};

To remove the primary key, simply set the primary key to null, as shown in the following example:

// remove the primary key
dt.PrimaryKey = null;

Note

The PrimaryKey property accesses the DataColumn object or objects that make up the primary key of DataTable. The primary key acts as a unique constraint for the table and also allows records to be located using the Find( ) method of the DataTable rows collection.

Rows

DataRowCollection drc = DataTable.Rows;

Accesses the DataRowCollection for the DataTable, providing access to the DataRow objects belonging to the DataTable. The Rows property can be used to add, remove, and examine the DataRow objects in a DataTable.

Examples

There are two methods that can add a row to a table. The Add( ) method takes either a DataRow argument or an object array of columns of the row to be added:

DataTable dt = new DataTable("MyTable");
dt.Columns.Add("Column1", typeof(System.Int32));
dt.Columns.Add("Column2", typeof(System.String));

DataRow newRow = dt.NewRow();
newRow["Column1"] = 1;
newRow["Column2"] = "DataRow 1";

// add a row using a reference to a DataRow
dt.Rows.Add(newRow);

// add and create a DataRow in one statement
dt.Rows.Add(new Object[] {2, "DataRow 2"});

A DataRow can also be inserted at a specific point in the DataRowCollection using the InsertAt( ) method, which in addition to a reference to a DataRow, takes an argument specifying the zero-based index at which to insert the row.

// create a new row
DataRow row = dt.NewRow();
row.ItemArray = new Object[] {1, "DataRow 1"};

// insert a new row as the first item of the collection
dt.Rows.InsertAt(row,0);

The Contains( ) method returns a value that indicates whether the primary key exists in the collection of rows. The method has two overloads taking an object or an array of objects allowing primary keys based on one or more columns to be examined.

// look for a primary key that is based on a single column
bool exists = dt.Rows.Contains("PK Value 1");

// look for a primary key that is based on multiple columns
bool exists = dt.Rows.Contains(new Object[] {"PK Field1 Value",
    "PK Field2 Value"});

The Find( ) method is the second method available to locate a row based on the primary key. The Find( ) method differs from the Contains( ) method in that it returns the matching row rather than just indicating if a matching row exists. Like the Contains( ) method, the Find( ) method has two overloads taking an object or an array of objects allowing rows with primary keys based on a single or multiple columns to be returned. A null reference is returned if a matching row isn't found.

// get the row for a primary key that is based on a single column
DataRow row = dt.Rows.Find("PK Value 1");

// get the row for a primary key that is based on multiple columns
DataRow row = dt.Rows.Find(new Object[] {"PK Field1 Value",
    "PK Field2 Value"});

The Remove( ) method removes a row specified by a DataRow argument from the collection:

// remove the row matching the primary key value, if found 
DataRow row = dt.Rows.Find("PK Value 1");
if(row != null)
    dt.Rows.Remove(row);

The RemoveAt( ) method removes the row specified by a zero-based index argument from the collection. If there no row at the index, an IndexOutOfRangeException is raised.

// remove the first row from the collection
dt.Rows.RemoveAt(0);

The Clear( ) method removes all rows from the collection:

// remove all rows from the table
dt.Rows.Clear();

Notes

The DataRowCollection class derives its standard functionality from the InternalDataCollectionBase class from which it inherits.

The commonly used public properties of the DataRowCollection are listed and described in Table 23-11.

Table 23-11. DataRowCollection public properties

Property

Description

Count

Gets the number of items in the DataRowCollection.

Item

Gets the specified DataRow from the DataRowCollection. In C#, the Item property is the indexer for the class.

The commonly used public methods of the DataRowCollection are listed and described in Table 23-12.

Table 23-12. DataRowCollection public methods

Method

Description

Add(  )

Adds a DataRow object to the collection.

Clear(  )

Removes all items from the collection.

Contains(  )

Returns a Boolean value indicating whether a DataRow with the specified name exists in the collection.

Find(  )

Returns a DataRow specified by the primary key value or values. If the primary key doesn't exist in the collection, a null reference is returned.

InsertAt(  )

Inserts the DataRow specified at the specified location in the collection. If the location specified is greater than the number of items in the collection, the DataRow is added to the end of the collection.

Remove(  )

Removes the specified DataRow from the collection. An ArgumentException is generated if the specified DataRow doesn't exist in the collection.

RemoveAt(  )

Removes the DataRow at the specified index from the collection. An ArgumentException is raised if the collection doesn't have a DataRow at the specified index.



    Part I: ADO.NET Tutorial
    Part II: ADO.NET Core Classes
    Part III: API Quick Reference