(untagged)

A Practical Guide to .NET DataTables, DataSets and DataGrids - Part 2

Pete2004

0.00/5 (No votes)

23 Feb 2004

The purpose of this document is to provide a practical guide to using Microsoft’s .NET DataTables, DataSets and DataGrid

3 Tables

The architecture and capability of Tables should be understood since it carries over to understanding how a DataSet and DataGrid function. In the process of binding a DataGrid to a database the underlying code creates and associates collections of tables that are filled with data from the database. Also, a DataSet created from a database may contain tables with more information than needs to be displayed, columns may need to be added that are based upon complex formulas using information in other columns and data from multiple databases may need to be combined into a single tabular view. These operations are done by extracting information from these data sources and by filling a programmatically designed table that is unbound.

Fundamentally a table contains Columns and Rows collections, which means standard methods for accessing and manipulating collections can be used. The Columns collection contains, for each column, a name, a data type specification and maybe an assigned default value. Each table row in the Rows collection contains one cell for each column. The table class has an extensive set of methods for editing and managing versions of column and row data and for event notifications when changes occur. Figure 2 illustrates the overall architecture of a table.

Figure 2 DataTable Decomposed

3.1 Table creation

A table memory object that will be able to contain/manage columns, rows and events can be easily created from the DataTable class as follows:

// Create a table object by using the DataTable class:

DataTable dt = new DataTable();

// Name the table by assigning a data string containing 
// the name to the table’s
// TableName property:

dt.TableName = “Elements”;

// or use the DataTable(string TableName) constructor

DataTable dt = new DataTable(“Elements”);

3.2 Columns – Creating and Adding to Tables

A table contains a collection of column definitions that will be used to define how each cell within a row can be referenced and the type of data content. The following scenario shows how to define a column and add it to a table’s column collection.

a. Define a Table as described in the Tables section.

b. Create a column object to be added to the table by using the column class:

DataColumn dc = new DataColumn();

// Set the properties for the column:

// string name for the column that is used as an index 
// for columns collection
// and a cell within a row
dc.ColumnName = “AtomicNbr”;

// string name that is used for a column label or header 
// for display purposes
// if not set, then the default value is dc.ColumnName
dc.Caption = “Atomic Number”;

// one of the standard system data types using the
// GetType() method.
dc.DataType = System.Type.GetType(“System.Int32);

// or one could use the typeof operator
dc.DataType = typeof(System.Int32);

// a default value that is assigned each time 
// a new row is created
dc.DefaultValue = 0; 

// or use one of the other constructor’s such as
// DataColumn(string ColumnName, System.Type DataType)

DataColumn dc = new DataColumn(“AtomicNbr”, 
  System.Type.GetType(“System.Int32”));

c. Add the new column to the table Columns collection. The order in which the columns are added determines their zero-based index.

dt.Columns.Add(dc);

d. Repeat b and c for each column to be added to the table Columns collection.

dc = new DataColumn(“Element”, System.Type.GetType(“System.String”));
dc.DefaultValue = string.Empty;
dc.Caption = “Element”;
dt.Columns.Add(dc);

dc = new DataColumn(“Symbol”, System.Type.GetType(“System.String”) );
dc.DefaultValue = string.Empty;
dc.Caption = “Symbol”;
dt.Columns.Add(dc);

dc = new DataColumn(“AtomicMass”, System.Type.GetType(“System.Decimal”) );
dc.DefaultValue = 0.0;
dc.Caption = “Atomic Mass”;
dt.Columns.Add(dc);

Examples of data types supported in the .NET environment.

Data Type	.NET System Types
`Boolean`	`System.Boolean`
`Byte`	`System.Byte`
`Byte[] (Array)`	`System.Byte[]`
`Char (Chararacter)`	`System.Char`
`DateTime`	`System.DateTime`
`Decimal`	`System.Decimal`
`Double`	`System.Double`
`Integer`	`System.Int16, System.Int32, System.Int64`
`Single`	`System.Single`
`String`	`System.String`
`Unsigned Integer`	`System.UInt16, System.UInt32, System.UInt64`
`TimeSpan`	`System.TimeSpan`

At this point a table called “Elements” has been created with four columns “AtomicNbr”, “Element”, “Symbol” and “AtomicMass” with their respective data types and default values. The following three DisplayColumnInfo() method code examples show this by using different techniques for accessing members and displaying data from their collections. In the first example, a for-loop is used to illustrate accessing table column collections through an integer index while in the second example a foreach loop illustrates accessing the same collections using a column class type. The third example uses strings containing the column names as an index. These accessing data examples illustrate the natural syntax approaches for working with collections.

1. for-loop

private void DisplayColumnInfo(DataTable dt)
{lder ColInfo = new StringBuilder();
ColInfo.AppendFormat(“Column\tName\tDataType\n”);

// note that the total number of columns in the 
// collection is contained in the ‘Count’ property
for(int j=0; j<dt.Columns.Count; j++)
{
  ColInfo.AppendFormat(“ [{0}]\t{1}\t{2}\ t{3}\n”, j, 
    dt.Columns[j].ColumnName,
    dt.Columns[j].Caption, dt.Columns[j].DataType.ToString());
}

MessageBox.Show(ColInfo.ToString() , “Column Name”,
   MessageBoxButtons.OK,
MessageBoxIcon.Information);
}

2. foreach loop

private void DisplayColumnInfo(DataTable dt)
{

  StringBuilder ColInfo = new StringBuilder();
  ColInfo.AppendFormat(“Column\tName\tDataType\n”);
  int j = -1;

  foreach (DataColumn dc in dt.Columns)
  {
   ColInfo.AppendFormat(“ [{0}]\t{1}\t{2}\t{3}\n”, ++j, dc.ColumnName,
   dc.Caption, dc.DataType.ToString() );
  }

  MessageBox.Show(ColInfo.ToString(),
    “Column Name”, MessageBoxButtons.OK,
  MessageBoxIcon.Information);
}

3. Using known column names as indexes – not column captions!

private void DisplayColumnDataTypeInfo(DataTa}

3. Using known column names as indexes – not column captions!

private void DisplayColumnDataTypeInfo(DataTable dt)
{
  StringBuilder ColInfo = new StringBuilder();
  ColInfo.AppendFormat(“Column\tName\tDataType\n”);

  ColInfo.AppendFormat(“ [{0}]\t{1}\t{2}\n”,1, “AtomicNbr”,
  dt.Columns[“AtomicNbr”].DataType.ToString());

  ColInfo.AppendFormat(“ [{0}]\t{1}\t{2}\n”,1, “Element”,
  dt.Columns[“Element”].DataType.ToString());

  ColInfo.AppendFormat(“ [{0}]\t{1}\t{2}\n”,1, “Symbol”,
  dt.Columns[“Symbol”].DataType.ToString());

  ColInfo.AppendFormat(“ [{0}]\t{1}\t{2}\n”,1, “AtomicMass”,
  dt.Columns[“AtomicMass”].DataType.ToString());

  MessageBox.Show(ColInfo.ToString() , “Column Name”,
  MessageBoxButtons.OK,
  MessageBoxIcon.Information);
}

3.3 Deleting/Removing Columns

Once a table has been defined columns can be deleted or removed as follows:

// For example to delete a column “AtomicMass”
dt.Columns.Remove(“AtomicMass”);
// or using a zero-based index – “AtomicMass” is the
// 4th column with index 3
dt.Columns.RemoveAt(3);
// To make sure that a column can be removed,
// for example, first determine
// whether the column exists, belongs to the table,
// or is involved in a constraint
// or relation.
if (dt.Columns.Contains("AtomicMass"))
  if (dt.Columns.CanRemove(dt.Columns["AtomicMass"])
  {
   dt.Columns.Remove(“AtomicMass”);
  }

3.4 Modifying Column Properties

Modifying a column property is simply accessing the property and setting its new value. For example:

dt.Columns[“AtomicNbr”].ColumnName = “AtomicNumber”;
dt.Columns[“AtomicMass”].DataType = typeof(System.float);

3.5 Clearing Column Collection

The entire table column collection can be cleared by simply using the Clear() method.

dt.Columns.Clear();

3.6 Cloning a Table

Once a table has been defined it can be used to create an identical table with the same column collection or it can be used as a basis for a new table where columns will be deleted, added or modified. The original table’s Clone() method is used to create the new table with the same structure including schemas and constraints; however, it does not copy the content contained in the rows.

DataTable dt1 = dt.Clone();

Now dt1 can be changed, for example:

// delete a column
dt1.Columns.Remove(“AtomicMass”);
// add a new column to the table
dc = new DataColumn(“IsotopeNbr”,
System.Type.GetType(“System.Int32”));
dc.DefaultValue = 0;
dt1.Columns.Add(dc);
// modify the name and caption of an existing column
dt1.Columns[“AtomicNbr”].ColumnName = “AtomicNumber”;
dt1.Columns[“AtomicNbr”].Caption = “Atomic Number”;

3.7 Rows – creating and adding to a table.

This section will show how to add rows and assign values to rows in the table rows collection using four equivalent methods for accessing individual cells within a row. The choice of method really depends upon the type of task such as the source of the data being used to fill the rows or simply extracting data from the rows.

1. Define a Table with Columns as described in the Tables and Columns section

2. The following scenario is the fundamental procedure for creating a row, filling the cells in the row and then adding the row to the table. This section also illustrates equivalent ways to index a cell, which provides the developer with much flexibility.

// First create a DataRow variable
DataRow dr;

// Next create a new row and assign it to the DataRow
// object dr using DataTable’s
// NewRow() method.
dr = dt.NewRow();
// dr now contains a cell for each column defined in the
// Columns collection

Four equivalent methods used to assign values to individual cells within a row.

Method 1

// fill each cell using a zero-based cell integer column indexes
dr[0] = 1;
dr[1] = “Hydrogen”;
dr[2] = “H”;
dr[3] = 1.0078;

Method 2

// fill each cell using the column name as the string column index
dr[“AtomicNbr”] = 1;
dr[“Element”] = “Hydrogen”;
dr[“Symbol”] = “H”;
dr[“AtomicMass”] = 1.0078;

Method 3

// fill each cell using DataColumn dc -- this is more applicable
// when using a DataColumn foreach loop
// e.g. foreach (DataColumn dc in dt.Rows) …
DataColumn dc;
dc = dt.Columns[“AtomicNbr”];
dr[dc] = 1;

dc = dt.Columns[“Element”];
dr[dc] = “Hydrogen”;

dc = dt.Columns[“Symbol”];
dr[dc] = “H”;

dc = dt.Columns[“AtomicMass”];
dr[dc] = 1.0078;

Method 4

// fill each cell using DataColumn dc and its ColumnName property
// which is identical to Method 3 but is
// included here for completeness
// Again, more applicable when using a foreach loop
DataColumn dc;
dc = dt.Columns[“AtomicNbr”];
dr[dc.ColumnName] = 1;

dc = dt.Columns[“Element”];
dr[dc.ColumnName] = “Hydrogen”;

dc = dt.Columns[“Symbol”];
dr[dc.ColumnName] = “H”;

dc = dt.Columns[“AtomicMass”];
dr[dc.ColumnName] = 1.0078;



// add the row to the table’s row collection
dt.Rows.Add(dr);

3. This scenario can be easily extended to a more general procedure to add n rows to the table. For example suppose a two dimensional object array ‘ElementData’ with n rows and dt.Columns.Count columns contains data to be added to the table. It could be loaded as follows:

DataRow dr;

int j;
for (int i=0; i < n; i++)
{
  j = -1;
  dr = dt.NewRow();
  foreach (DataColumn dc in dt.Columns)
  {
   j++;
   // fill each cell, using the column dc as the index, from the
   // previously defined two dimensional object array à ElementData
   if (dc.DataType == typeof(System.String))
    dr[dc] = (System.String)ElementData[i][j];
   else
    if (dc.DataType == typeof(System.Int32))
     dr[dc] = (System.Int32)ElementData[i][j];
    else
     if (dc.DataType == typeof(System.Decimal))
      dr[dc] = (System.Decimal)ElementData[i][j];
  }
  dt.Rows.Add(dr);
}

3.8 Modifying data within an existing table row

There are a number of ways to modify data in a table with the following illustrating the basic mechanism. Other techniques will be presented in the following sections.

// First create a DataRow variable
DataRow dr;
// Next assign the row in the Rows collection
// to be modified to dr, for example select row
with index = 0
dr = dt.Rows[0];
// Select the
// column within the row to be modified by specifying
// a column index and assign
the new value
dr[“AtomicNbr”] = 1.00781;

Equivalent alternative coding methods are as follows:

dt.Rows[0][“AtomicNbr”] = 1.00781;

dt.Rows[0][0] = 1.00781;

3.9 Fill Table using `LoadDataRow()` method

The LoadElementDataRow() code example method in this section illustrates loading data into a table using the DataTable’s LoadDataRow method that takes an object containing data for each cell within a row. The LoadDataRow method is bracketed by BeginLoadData() and EndLoadData() methods that turn off and on event notifications and other properties related to linked tables. Using these methods can prevent unnecessary processing by event handlers that would otherwise be triggered that are discussed in the Event Handler section. Also, the LoadDataRow method will modify an existing row if primary keys match or add the row to the Rows collection. Refer to the section on Row Versions that discusses the different versions of rows managed by the table’s class for sample code illustrating the different behaviors of the LoadDataRow method when a table has a primary key and when it does not.

private DataRow LoadElementDataRow(DataTable dt,
   int AtomicNbr, string Element,
string Symbol, double AtomicMass)
{
  // Turns off event notifications,
  // index maintenance, and constraints
  // while loading data
  dt.BeginLoadData();
  // Add the row values to the rows collection and
  // return the DataRow. If the second
  // argument is set to true, then dt.AcceptChanges() is called
  //otherwise new rows are
  // marked as additions and changes to existing rows are marked as
  //modifications.
  DataRow dr = dt.LoadDataRow(new object[]
   {AtomicNbr, Element, Symbol, AtomicMass}
   , false);
  // Turns on event notifications, index maintenance, and constraints
  // that were turned off
  // with the BeginLoadData() method
  dt.EndLoadData();
  return dr; // returns the DataRow filled
  // with the new values
}

3.10Retrieving Table Content

The GetTableData() example method retrieves the column labels and row data from an input table and formats them into a string that can be used for printing, copying to the clipboard and exporting to a tab delimited text file.

private string GetTableData(DataTable dt)
{

  StringBuilder TableData = new StringBuilder();
  // retrieve header row column labels
  TableData.AppendFormat(“Row”);
  foreach (DataColumn dc in dt.Columns)
  TableData.AppendFormat(“\t{0}”, dc.ColumnName);
  TableData.AppendFormat(“\n”);
  
  // retrieve rows
  int j = -1;
  foreach (DataRow dr in dt.Rows)
  {
    TableData.AppendFormat(“[{0}]”,++j);
    foreach (DataColumn dc in dt.Columns)
    {
     TableData.AppendFormat(“\t{0}”, dr[dc] );
    }
   TableData.AppendFormat(“\n”);
  }

  return TableData.ToString();

}

The output string for our element table with one row would look like the following when it is displayed in a grid format using an Excel spreadsheet or the DataGrid.

Row	AtomicNbr	Element	Symbol	AtomicMass
[0]	1	Hydrogen	H	1.0078

3.11Row Versions and Accepting/Rejecting Changes

3.11.1 Methods and Enumerations

This is an important section to understand because the Table class maintains different states and versions of rows that can be used to provide rollback, undo and transaction logging capability. That is, this state and version information provides very powerful programmatic control over table data and UI strategies.

Before discussing row states and versions there are Table and Row methods that need to be defined:

Table Method	Description
`AcceptChanges()`	Accepts all row changes to the table. Changes can be accepted to individual rows when the DataRow `AcceptChanges()` method is called.
`RejectChanges()`	Rejects all row changes to the table that have taken place since the last call to the Table or DataRow `AcceptChanges()`.
`GetChanges()`	Returns a table containing all rows that have been modified. This is particular useful when building transaction logs to satisfy government and corporate regulations, such as CFR21-11. Note: If the Table `AcceptChanges` is called prior to `GetChanges` then there will be no changes and the return value is null.

Rows Method	Description
`Add()`	Adds a row to the rows collection
`InsertAt()`	Inserts a row at a specific position in the rows collection
`RemoveAt()`	Removes a row at an index from the rows collection
`AcceptChanges()`	Accepts all changes to the row including changes to the individual cells including adding and deleting the row to and from the table respectively.
`RejectChanges()`	Rejects changes to the row restoring the original values
`BeginEdit()`	Begins a row editing session
`CancelEdit()`	Cancels a row editing session and restores all previous values
`EndEdit()`	Ends a row editing session

There are four different versions of Row Collections that are automatically maintained by the Table’s object that provides extensive programmatic control over edits, deletes and inserts.

DataRowVersion	Description
`Current`	This version contains the current set of all values contained in each table row. The current set and the default set are identical during modifying a row without calling `BeginEdit()` after `AcceptChanges()` is called.
`Default`	The Default rows collection contains all of the changes. Each time a new row is created the new row is initialized to the default column values. Each time a cell value within a row is modified, the modification will be reflected in this table.
`Proposed`	This version as its name implies contains only rows that have proposed changes where they are only present during a call to `BeginEdit()`. When `EndEdit()` is called the proposed changes are reflected in the Current `DataRow`, in the Original DataRow and proposed DataRow is deleted. When the `CancelEdit` is called, the proposed DataRow is deleted and the Default DataRow is changed back to the Current `DataRow` values.
`Original`	The Original Rows collection is updated each time `AcceptChanges()` is called. These values are used when `RejectChanges()` and `CancelEdit()` are called to return the values back to the state before any changes occurred since the last call to `AcceptChanges()`.

RowState	Description
`Added`	The row is marked as Added when a row is added or inserted to the Rows Collection and before an `AcceptChanges()` method is called.
`Deleted`	After `AcceptChanges()` is called the row is marked as Deleted when any of the following is performed: `dr.Delete()` `dt.Rows.RemoveAt(index)` `dt.Rows.Remove(dr)`
`Detached`	Before `AcceptChanges()` is called the row is marked as Detached when any of the following is performed: `dr.Delete()` `dt.Rows.RemoveAt(index)` `dt.Rows.Remove(dr)`
`Modified`	After `AcceptChanges()` is called any row cell value that is changed causes the row to be marked as Modified.
`Unchanged`	After `AcceptChanges()` is called the row or all rows are marked as Unchanged depending upon whether it is a DataRow `AcceptChanges()` call or Table `AcceptChanges()` call.

DataTable dt = new DataTable("Elements");
DataColumn AtomicNbr = new DataColumn("AtomicNbr",
System.Type.GetType("System.Int32"));
AtomicNbr.DefaultValue=0;
dt.Columns.Add(AtomicNbr);
DataColumn Element = new DataColumn("Element",
System.Type.GetType("System.String"));
Element.DefaultValue= "Element";
dt.Columns.Add(Element);
DataRow dr;

3.11.2 Sample 1 – Row States

This section shows the different row states and the conditions for them.

dr = dt.NewRow();
dr[Element]="Hydrogen";
dr[AtomicNbr]= 1;
// NewRow Before Add: RowState=Detached
dt.Rows.Add(dr);
// NewRow After Add: RowState=Added
dt.Rows[0].AcceptChanges();
//NewRow After AcceptChanges: RowState=Unchanged
dt.Rows.RemoveAt(0);
// note that the row is marked as Detached when
// RemoveAt() or Remove() is used
//NewRow After RemoveAt: RowState=Detached
// Add the row back, accept the changes and then delete the row
// note that the row state is now marked as deleted when Delete()
// if Delete() is called prior to the row being
// added then the row is marked as Detached.
dt.Rows.Add(dr);
dt.AcceptChanges();
dr.Delete();
// NewRow After Delete: <RowState=Deleted>

The following code examples will illustrate the above method functionality. After each section of code will be four tables, one each for each type of DataRowVersion, that will show whether the version contains a row and if so their respective values.

NOTE

Rows that do not exist (designated with a ‘No’ value under column ‘Has Versions’) for a particular RowState version are added or included for readability and clarity. That is, if all the rows in a version table were listed, these would not be in the Table Rows collection.

The presentation schema is from code Sample 2 to Sample N where each successive code Sample uses the results from the previous Sample. All changes that occur to the version tables for Sample code section are designated in bold red.

3.11.3 Sample 2 – Initial Loading of Table

dr = dt.NewRow();
dr[Element]="Hydrogen";
dr[AtomicNbr]= 1;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[Element]="Helium";
dr[AtomicNbr]= 2;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[Element]="Lithium";
dr[AtomicNbr]= 3;
dt.Rows.Add(dr);
dr = dt.NewRow();
// this row contains default values
dt.Rows.Add(dr);

Row 0 has only an Original Version and it is marked as Deleted. The Current and Default versions are identical with the four new rows being marked as Added. The Proposed version table does not contain any values.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	Yes	Added	1	Hydrogen
[2]	Yes	Added	2	Helium
[3]	Yes	Added	3	Lithium
[4]	Yes	Added	0	Element

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	Yes	Added	1	Hydrogen
[2]	Yes	Added	2	Helium
[3]	Yes	Added	3	Lithium
[4]	Yes	Added	0	Element

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Deleted	0	Element
[1]	No
[2]	No
[3]	No
[4]	No

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	No
[2]	No
[3]	No
[4]	No

3.11.4 Sample 3 – DataRow AcceptChanges

dt.Rows[0].AcceptChanges();
dt.Rows[1].AcceptChanges();

After the Rows[0].AcceptChanges() was called, the row that was marked deleted, row 0 in the above version tables, has been deleted from all versions and all other rows have new indices. The next AcceptChanges() command references row 1 in the newly ordered Rows Collection. In this case, the Row State is marked as Unchanged in versions Current, Default and Original. In the Original version there is only one row and it corresponds to Row 1 that was accepted. The Proposed version table does not contain any values.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Added	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Added	3	Lithium
[3]	Yes	Added	0	Element

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Added	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Added	3	Lithium
[3]	Yes	Added	0	Element

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	Yes	Unchanged	2	Helium
[2]	No
[3]	No

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	No
[2]	No
[3]	No

3.11.5 Sample 4 – Table AcceptChanges

dt.AcceptChanges();

After the Table.AcceptChanges() is called, all remaining rows are marked Unchanged in the Current, Default and Original Version tables and the Original version table is identical to the Current and Default version tables. The Proposed version table does not contain any rows.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	No
[2]	No
[3]	No

3.11.6 Sample 5 – DataRow BeginEdit

dt.Rows[1].BeginEdit();
dt.Rows[1]["Element"]= "Helium";
dt.Rows[1]["AtomicNbr"]= 222;

The above code begins an editing session on row 1 and the new values are reflected in the Default version table and values now appear in the Proposed version table. All other table version entries remain unchanged.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	222	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	Yes	Unchanged	222	Helium
[2]	No
[3]	No

3.11.7 Sample 6 – DataRow CancelEdit

dt.Rows[1].CancelEdit();

The CancelEdit() command returns the default values back to the Original state and clears out the Proposed values from row 1 in the Proposed version table.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	No
[2]	No
[3]	No

3.11.8 Sample 7 – DataRow BeginEdit – Example 2

dt.Rows[3].BeginEdit();

The BeginEdit() method initializes the Proposed row 3 with default values.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	No
[2]	No
[3]	Yes	Unchanged	0	Element

3.11.9 Sample 8 – DataRow Change values – Example 2

dt.Rows[3]["Element"]="Carbon";
dt.Rows[3]["AtomicNbr"]= 12;

The Default and Proposed row 3 values have been changed to reflect Carbon and 12. All other rows in all versions remain unchanged.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	0	Element

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	No
[2]	No
[3]	Yes	Unchanged	12	Carbon

3.11.10 Sample 9 – DataRow EndEdit – Modified Rows

dt.Rows[3].EndEdit();
dt.Rows[0]["Element"] = "Oxygen";
dt.Rows[0]["AtomicNbr"] = 8;
// Add a new row.
dr = dt.NewRow();
dt.Rows.Add(dr);

After EndEdit() is called, the Current and Default versions for row 3 are updated and marked as Modified. The Original version for row 3 still retains the original values before the changes and is marked as Modified.

The next two lines assign “Oxygen” and its atomic number to row 0 and these values are reflected in the Current and Default versions, which are also marked as Modified. The Original version for row 0 remains unchanged.

The next two lines adds a new row to the Current and Default version tables initialized with default values and marked as Added. Note that the new row does not appear in the Original version table.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Modified	8	Oxygen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Modified	12	Carbon
[4]	Yes	Added	0	Element

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Modified	8	Oxygen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Modified	12	Carbon
[4]	Yes	Added	0	Element

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Modified	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Modified	0	Element
[4]	No

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	No
[2]	No
[3]	No
[4]	No

3.11.11 Sample 10 – DataRow AcceptChanges of Modified Rows

dt.Rows[3].AcceptChanges();

The DataRow AcceptChanges() for row 3 causes the corresponding row in the Current and Default version tables to be marked as Unchanged and the Original version table now contains the same row values.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Modified	8	Oxygen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	Yes	Added	0	Element

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Modified	8	Oxygen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	Yes	Added	0	Element

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Modified	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	No

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	No
[2]	No
[3]	No
[4]	No

3.11.12 Sample 11 – DataRow RejectChanges

dt.Rows[0].RejectChanges();

Calling the DataRow RejectChanges() method for row 0 causes the corresponding row values for the Current and Default tables to revert to the Original version values and three tables, Current, Default and Original, have the Row State for row 0 marked as unchanged.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	Yes	Added	0	Element

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	Yes	Added	0	Element

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	No

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	No
[2]	No
[3]	No
[4]	No

3.11.13 Sample 12 – LoadDataRow without table having primary key

dt.BeginLoadData();
dt.LoadDataRow(new object[]{1,"Deuterium"}, false);
dt.EndLoadData();

If a table does not have a primary key then the LoadDataRow method will create a new row and fill it with values in the object array. Looking at rows 0 and 5 in the Current and Default version tables, they both have the same atomic number, but different element names. Also, Row 5 is marked as being Added.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	Yes	Added	0	Element
[5]	Yes	Added	1	Deuterium

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	Yes	Added	0	Element
[5]	Yes	Added	1	Deuterium

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Unchanged	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	No
[5]	No

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	No
[2]	No
[3]	No
[4]	No
[5]	No

3.11.14 Sample 13 – LoadDataRow with table having primary key

// must delete the row with a duplicate
// AtomicNbr in order to create
// a primary key.
dt.Rows.RemoveAt(5);
dt.AcceptChanges();
// Create a primary key and load the new object array data.
dt.PrimaryKey = new DataColumn[] {dt.Columns["AtomicNbr"]};
dt.BeginLoadData();
dt.LoadDataRow(new object[]{1,"Deuterium"}, false);
dt.EndLoadData();

If a table has a primary key then the LoadDataRow method will modify the data in the row if the primary keys match or else it will append the row to the table. Looking at row 0 in the Current and Default version tables, the element name has been changed from Hydrogen to Deuterium. In the three version tables Current, Default and Original row 0 is now marked as being Modified.

Current Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Modified	1	Deuterium
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	Yes	Unchanged	0	Element

Default Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Modified	1	Deuterium
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	Yes	Unchanged	0	Element

Original Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	Yes	Modified	1	Hydrogen
[1]	Yes	Unchanged	2	Helium
[2]	Yes	Unchanged	3	Lithium
[3]	Yes	Unchanged	12	Carbon
[4]	Yes	Unchanged	0	Element

Proposed Version

Row	Has Versions	Row State	AtomicNbr	Element
[0]	No
[1]	No
[2]	No
[3]	No
[4]	No

3.11.15 Sample Code for Obtaining Version and State Information

The above tables were generated using the following procedure.

static void PrintRowVersions(DataTable dt)
{
  DataRowVersion[] rowVer = new DataRowVersion[4];
  rowVer[0] = DataRowVersion.Current;
  rowVer[1] = DataRowVersion.Default;
  rowVer[2] = DataRowVersion.Original;
  rowVer[3] = DataRowVersion.Proposed;
  StringBuilder TableData = new StringBuilder();
  for(int i=0; i<rowVer.Length; i++)
  {
    // Print the value of each column in each row.
    TableData.AppendFormat("{0} Version\n", rowVer[i].ToString());
    // retrieve header row column labels
    TableData.AppendFormat("Row\tHas Versions\tRow State");
    foreach (DataColumn dc in dt.Columns)
      TableData.AppendFormat("\t{0}", dc.ColumnName);
    TableData.AppendFormat("\n");
    int n=-1;
    foreach(DataRow row in dt.Rows )
    {
      n++;
      if (row.HasVersion(rowVer[i]) )
      {
        // Print the specified version of the row's value.
        TableData.AppendFormat("[{0}]\tYes\t{1}",
        n.ToString(), row.RowState.ToString());
        foreach (DataColumn dc in dt.Columns)
        {
           TableData.AppendFormat("\t{0}", row[dc,rowVer[i]]);
        } 
        TableData.AppendFormat("\n");
     }
     else
     {
        TableData.AppendFormat("[{0}]\tNo\t", n.ToString());
        for(int j=0; j<dt.Columns.Count; j++)
         TableData.AppendFormat("\t ");
        TableData.AppendFormat("\n");
     }
   }
   TableData.AppendFormat("\n");
  }
  // output string data to a text file using a StreamWriter
  // StreamWriter sw = new StreamWriter("c:\RowVersions.txt");
  sw.Write(TableData.ToString());
  // sw.Close();
}

3.12Handling DataTable Errors

A DataTable can be checked to determine if it contains any rows with errors by examining the Table’s HasErrors property value. The following code illustrates how to isolate the rows and their columns with errors.

if (dt.HasErrors)
{ // Errors have occurred in rows in table dt
  foreach (DataRow dr in dt.Rows)
  {
   if(dr.HasErrors)
   {
    // Row has errors
    // GetColumnsInError() returns an array of
    // DataColumns that contain errors
    foreach(DataColumn dc in dr.GetColumnsInError())
    {
      // GetColumnError returns a description of the Column error
      MessageBox.Show(dr.GetColumnError(dc.Ordinal));
    }
   }
  }
}

3.13DataTable Events

The following code provides an example of adding a DataTable column changed event handler and the code within the handler illustrates some techniques for processing the new column values.

dt.ColumnChanged += new DataColumnChangeEventHandler
  (this.SampleForm_ColumnChanged);
private void SampleForm_ColumnChanged(object sender,
  System.Data.DataColumnChangeEventArgs e)
{
if(e.Column.Ordinal <op> …)
 {
   // could perform validation checks such as range values
   // or formatting or other types of 
   //processing on the changed column.
 }
if(e.Row.HasErrors)
 {
   // clear the error
   e.Row.SetColumnError(e.Column, string.Empty);
   // check to see if row has any more errors
   DataColumn [] dcErrors = e.Row.GetColumnsInError();
   // if there are no more errors then clear the row error flag.
   if(dcErrors.Length == 0)
   e.Row.ClearErrors();
 }
}

Next...

DataSets

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here