Microsoft.NET

……………………………………………….Expertise in .NET Technologies

ADO.NET Core Classes:DataTables – Part 6

Posted by Ravi Varma Thumati on November 18, 2009

The DataTable is an in-memory data store that contains DataColumn and Constraint objects that define the schema of the data. The actual data is stored as a collection of DataRow objects within the DataTable. Both the schema and the data can be created entirely programmatically, retrieved as the result of a query against a data source using a .NET managed data provider, or loaded from an XML document or stream through the DataSet to which it belongs.

The DataTable and the DataReader are similar because they both provide access to the results of a query that can then be exposed through collections of row and column objects. The primary difference is that the DataTable is a disconnected class that places little restriction on how the data within it is accessed and allows that data to be filtered, sorted, and modified. The DataReader is a connected class that provides little functionality beyond forward-only, read-only access to the results of the query.

Creating a DataTable

There are a number of ways a DataTable can be created. In the simplest case, a DataTable is created using the new keyword. The constructor accepts an optional argument that allows the TableName property to be set. If the TableName argument isn’t supplied, the default name of the table is Table; subsequent tables are named Table1, Table2, and so on. The following example demonstrates how to create a table named MyTable:

DataTable dt = new DataTable(“MyTable”);

A DataTable can also be created automatically in a DataSet when the Fill( ) or FillSchema( ) method of the DataAdapter is called specifying a table that doesn’t already exist in the DataSet.

DataTable objects can also be created from other DataTable objects. A new DataTable can be created from either the table schema alone, the schema and the data, or a subset of the DataTable based on if and how the data has been modified. Methods to do this are discussed later in this chapter.

Working with Columns

The schema for a table is defined by the columns in the table and the constraints on those columns. Columns belonging to the DataTable are stored as DataColumn objects in a DataColumnCollection object and are accessed through the Columns property of the DataTable. This section examines some methods and properties of the DataColumnCollection.

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, Column3, and so on are assigned to the new columns. The following examples show how to create columns within the table:

// 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 for adding columns is the AddRange( ) method, 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 interrogate the collection of columns within a table. The Count property 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 a String argument containing the column name:

Boolean colExists = dt.Columns.Exists(“MyColumn”);

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

Int32 colIndex = dt.Columns.IndexOf(“MyColumn”);

The Remove( ), RemoveAt( ), and Clear( ) methods remove columns from the DataSet. The Remove( ) method takes an argument that specifies either a column name or a reference to the column to be removed, as shown in the following example:

// 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 as shown in the following example:

// remove the first column from the collection

dt.Columns.RemoveAt(0);

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

dt.Columns.Clear();

Constraints

As mentioned earlier, schema for a table is defined by the columns in the table and the constraints on those columns. There are two types of constraints that can be placed on a table. Unique constraints define a column or group of columns for which the value in the column or columns must be unique in each data row. Foreign key constraints define and restrict the action performed when a value in a column or columns is updated or deleted. Constraints belonging to the DataTable are stored as either UniqueConstraint or ForeignKeyConstraint objects in a ConstraintCollection object and are accessed through the Constraints property of the DataTable. This section examines some methods and properties of the ConstraintCollection.

To add a constraint to a table, the Add( ) method takes an argument specifying a reference to an existing constraint or takes specific arguments if a unique or foreign-key constraint is added. The following example demonstrates adding both a unique and foreign-key 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 (wxh – test)

ForeignKeyConstraint fc = new ForeignKeyConstraint(

dtParent.Columns[“ParentColumn”],

dtChild.Columns[“ChildColumn”]);

dt.Constraints.Add(fc);

Two overloads of the Add( ) method create and add UniqueConstraint objects in one statement. The methods take a constraint name, a reference either 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, as shown in the following example:

// add a foreign key constraint based on two columns

dt.Constraints.Add(“MyForeignKeyConstraint”,

new DataColumn(dtParent.Columns[“ParentCol1”],

new dtParent.Columns[“parentCol2”]),

new DataColumn(dtChild.Columns[“ChildCol1”],

dtChild.Columns[“ChildCol2”]));

There are several properties and methods that interrogate the collection of constraints within a table. The Count property returns the number of constraints in a table:

Int32 constraintCount = dt.Constraints.Count;

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

Boolean colExists = dt.Columns.Exists(“MyColumn”);

The Remove( ), RemoveAt( ), and Clear( ) methods remove constraints from the DataTable. The Remove( ) method takes an argument that specifies either a constraint name or a reference to the constraint to be removed, as shown in the following example:

// remove a constraint by specifying the name of the constraint

dt.Constraints.Remove(“MyConstraint”);

// remove a constraint by specifying a reference to the constraint

DataConstraint constraint = new DataConstraint(“MyConstraint”);

dt.Constraints.Add(constraint);

// … do some work

dt.Constraints.Remove(constraint);

The RemoveAt( ) method removes a constraint with a specified index from the collection, as shown in this example:

// remove the first constraint from the collection

dt.Constraints.RemoveAt(0);

The Clear( ) method removes all constraints from the constraint collection:

dt.Constraints.Clear();

Primary Key

The primary key is a column or collection of columns that uniquely identify each row in the table. The PrimaryKey property accesses one or more DataColumn objects that define the primary key of the DataTable. The primary key acts both as a unique constraint for the table and allows records to be located using the Find( ) method of the DataTableRows collection, as discussed in the next section.

The primary key for a table is set by specifying an array of DataColumn objects from the table. The following example illustrates creating 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

// set the primary key

dt.PrimaryKey = new DataColumn[] {dt.Columns[“PK_Field1”],

dt.Columns[“PK_Field2”]};

To remove the primary key, simply set the primary key object to null.

// remove the primary key

dt.PrimaryKey = null;

Rows

A DataRow object represents a row of data in the table stored in a collection of columns. Rows belonging to the DataTable are stored as DataRow objects in a DataRowCollection object and are accessed through the Rows property of the DataTable. The Rows property accesses methods and properties that can add, remove, and examine the DataRow objects in a DataTable. This section examines some of the methods and properties of the DataRowCollection.

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”].Value = 1;

newrow[“Column2”].Value = “DataRow 1”;

// add a row using a reference to a DataRow

dt.Rows.Add(dr);

// add and create a DataRow in one statement

dt.Rows.Add(new Object[] {2, “DataRow 2”});

Additionally, a DataRow can 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);

There are two methods that can examine and locate rows within the DataRow collection. The Contains( ) method returns a value indicating whether the primary key exists in the collection of rows. The method has two overloads that take an object or an array of objects and allow primary keys based on one or more columns to be examined.

// look for a primary key that is based on a single column

Boolean exists = dt.Rows.Contains(“PK Value 1”);

// look for a primary key that is based on multiple columns

Boolean exists = dt.Rows.Contains(new Object[] {“PK Field1 Value”,

“PK Field2 Value”});

The Find( ) method is the second method that can 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 was found. Like the Contains( ) method, the Find( ) method has two overloads that take an object or an array of objects and allow primary keys based on one or more columns to be examined. 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( ), RemoveAt( ), and Clear( ) methods remove rows from the DataTable. The Remove( ) method takes a DataRow argument and removes that row 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 takes an argument that specifies the index within the collection at which to remove the row:

// remove the first row from the collection

dt.Rows.RemoveAt(0);

Finally, the Clear( ) method removes all rows from the table:

// remove all rows from the table

dt.Rows.Clear();

Loading Data

There are three methods that can add new rows to the DataTable. The NewRow( ) method creates a new empty DataRow with the same schema as the DataTable. After creating the row, it can be added to the DataTable using the Add( ) method of the DataRowCollection:

// create the target table

DataTable dt = new DataTable(“MyTable”);

dt.Columns.Add(“Column1”, typeof(System.Int32));

dt.Columns.Add(“Column2”, typeof(System.String));

// create and add a new row to the table

DataRow newrow = dt.NewRow();

newrow[“Column1”] = 1;

newrow[“Column2”] = “Row 1”;

dt.Rows.Add(newrow);

The LoadDataRow( ) method takes an array of values and attempts to find a row with a matching primary key. If the primary key is found, the values replace the existing data for the row; otherwise a new row is added. The LoadDataRow( ) method takes a Boolean AcceptChanges argument. If the AcceptChanges value is true, AcceptChanges is called to accept all changes for both inserted and modified rows. If AcceptChanges is false, the DataRowState fields of newly added rows are marked as insertions, while changes to existing rows are marked as modifications.

The BeginLoadData( ) method turns off all constraints, notifications, and index maintenance for the DataTable while data is loaded; the EndLoadData( ) method turns them back on. Calling BeginLoadData( ) and EndLoadData( ) methods might result in performance improvements when adding a series of DataRows to the DataTable using the LoadDataRow( ) method. If there are constraint violations when the EndLoadData( ) method is called, a ConstraintException is raised. The following example illustrates these methods:

// create the target table

DataTable dt = new DataTable(“MyTable”);

dt.Columns.Add(“Column1”, typeof(System.Int32));

dt.Columns.Add(“Column2”, typeof(System.String));

// add two rows to the DataTable dt

dt.BeginLoadData();

dt.LoadDataRow(new Object[]{1,”Row 1″}, false);

dt.LoadDataRow(new Object[]{2,”Row 2″}, false);

dt.EndLoadData();

Finally, the ImportRow( ) method accepts a DataRow object argument and either adds the row to the table or updates an existing row with a matching primary key in the table, using the existing schema and preserving the existing DataRowState of the row:

// create the target table

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”] = “Row 1”;

dt.Rows.ImportRow(newrow);

Committing and Discarding Changes

When a DataRow is modified, ADO.NET marks the row as having a pending change and sets the RowState of the DataRow to Added, Modified, or Deleted as appropriate. ADO.NET also maintains version information by tracking both Original and Current versions of each row. Together, this information allows ADO.NET to identify the rows and columns that have been changed.

The DataTable has three methods—GetChanges( ) , AcceptChanges( ), and RejectChanges( )—that can commit or discard the changes made to a DataTable since it was last loaded or since AcceptChanges( ) was last called. These methods function identically to the same methods for the DataSet as they are described in previous article. Also, when these methods are called on a DataSet, they are implicitly called on all tables contained within the DataSet.

Cloning the Schema of the Table

The Clone( ) method creates a new DataTable with the same schema as the original, but it contains none of the data in the original DataTable. The following example uses the Clone( ) method to create a new DataTable:

// create a DataTable object variable to receive the clone

DataTable cloneDt;

cloneDt = dt.Clone();

Copying the Table

The Copy( ) method creates a new DataTable with the same structure and data as the original. The following example uses the Copy( ) method to create a new DataTable:

// create a DataTable object variable to receive the copy

DataTable copyDt;

copyDt = dt.Copy();

Selecting a Subset of Rows

The Select( ) method returns a subset of rows from the DataTable and returns the result as a DataRow array. The four overloads take optional arguments specifying the filter criteria, sort order, and DataViewRowState of the rows to be returned. The following example illustrates this concept:

// all rows with order amount > 100, sorted on the order date descending

DataRow[] dra = dt.Select(“OrderAmount>100.00”, “OrderDate DESC”);

The following example returns all modified rows in the table:

DataRow[] dra = dt.Select(null, null, DataViewRowState.ModifiedCurrent);

Performing Aggregate Calculations

The Compute( ) method computes the result of an aggregate query on the rows in the table that meet the filter criteria. Here’s an example that illustrates this:

DataTable dt = new Table();

dt.Columns.Add(“OrderId”, typeof(System.Int32));

dt.Columns.Add(“OrderAmount”, typeof(System.Decimal));

// … add some rows

// computes the sum of order amounts for all orders with Id less than 10

Decimal totalOrderAmount = dt.Compute(“SUM(OrderAmount)”, “OrderId<10”)

Removing All Data

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

dt.Clear();

Resetting the Table

The Reset( ) method returns the DataTable to its original state. This method can discard the existing DataTable and start working with a new DataTable rather than instantiating a new DataTable object.

dt.Reset();

Identifying Errors in the Table

The GetErrors( ) method returns an array of the rows that contain errors, whether they are constraint violations or failed update attempts. The RowError property or the SetColumnError( ) method of the DataRow object can be used to set an error, or an error can be set by the DataAdapter in response to errors that occur while data is being reconciled with the data source. The HasErrors( ) method should be called prior to calling GetErrors( ) to determine whether the call to GetErrors( ) is necessary. The following example shows how to use these methods:

DataRow[] errorRow;

if(dt.HasErrors())

{

errorRows = dt.GetErrors();

for(Int32 i = 0; i<errorRows.Length; i++)

{

// … resolve the error for the row

// clear the error for resubmitting

errorRows[i].ClearErrors();

}

}

DataTable Events

The following section describes DataTable events.

ColumnChanged and ColumnChanging

The ColumnChanged and ColumnChanging events can be handled to validate data or control user interface elements. The ColumnChanging event is raised when a value is being changed in a specified column; the ColumnChanged event is raised after the value in the column has been changed. Both events pass a DataColumnChangeEventArgs argument to the event handler that provide information specific to the event.

The following code demonstrates handling the ColumnChanging and ColumnChanged events to perform data validation and logging:

DataTable dt = new DataTable();

dt.ColumnChanged += new DataColumnChangeEventHandler(dt_ColumnChanged);

dt.ColumnChanging += new DataColumnChangeEventHandler(dt_ColumnChanging);

private static void dt_ColumnChanging(object sender,

DataColumnChangeEventArgs e);

{

if (e.Column.ColumnName == “MyColumn”)

{

if(e.ProposedValue.Equals(“Invalid Data”)

{

e.Row.RowError = “Invalid data.”;

e.Row.SetColumnError(e.Column, “Column value ” +

“cannot be ” e.ProposedValue.ToString());

}

}

}

private static void ds_ColumnChanged(object sender,

DataColumnChangeEventArgs e);

{

System.IO.TextWriter tw = System.IO.File.AppendText(“colchange.log”);

tw.WriteLine(“ColumnChanging: Name = {0}; ProposedValue = {1}; ” +

“Row Id = {2}”, e.ColumnName, e.ProposedValue.ToString(),

e.Row[“Id”].ToString());

tw.Close();

}

RowChanged, RowChanging, RowDeleted, and RowDeleting

The DataTable raises four events in response to actions performed on rows. These events are RowChanging and RowChanged, which are raised, respectively, before and after a row is edited; RowDeleting and RowDeleted are raised, respectively, before and after a row is marked for deletion. These events can support custom validation logic similar to the ColumnChanging and ColumnChanged events described earlier. All four events pass a DataRowChangeEventArgs argument to the event handler providing information specific to the event.

The following code demonstrates handling the RowChanged, RowChanging, RowDeleted, and RowDeleting events:

DataTable dt = new DataTable();

dt.RowChanged+= new DataRowChangeEventHandler(dt_RowChanged);

dt.RowChanging+= new DataRowChangeEventHandler(dt_RowChanging);

dt.RowDeleted+= new DataRowChangeEventHandler(dt_RowDeleted);

dt.RowDeleting+= new DataRowChangeEventHandler(dt_RowDeleting);

private void dt_RowChanged(object sender, DataRowChangeEventArgs e);

{

MessageBox.Show(“RowChanged: Action  =  ” + e.Action + “; ” +

“Row Id  =  ” + e.Row[“Id”].ToString());

}

private void dt_RowChanging(object sender, DataRowChangeEventArgs e);

{

MessageBox.Show(“RowChanging: Action  =  ” + e.Action + “; ” +

“Row Id  =  ” + e.Row[“Id”].ToString());

}

private void dt_RowDeleted(object sender, DataRowChangeEventArgs e);

{

MessageBox.Show(“RowDeleted: Action  =  ” + e.Action + “; ” +

“Row Id  =  ” + e.Row[“Id”].ToString());

}

private void dt_RowDeleting(object sender, DataRowChangeEventArgs e);

{

MessageBox.Show(“RowDeleting: Action  =  ” + e.Action + “; ” +

“Row Id  =  ” + e.Row[“Id”].ToString());

}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: