Microsoft.NET

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

ADO.NET Core Classes:DataRow – Part 8

Posted by Ravi Varma Thumati on November 18, 2009

The DataRow class represents a single row of data in the DataTable. The DataRow class can retrieve, update, insert, and delete a row of data from the DataTable. Using the DataRow class, each column value for the row can be accessed.

The DataRow maintains the RowState property that is used by ADO.NET to track the changes that have been made to a DataRow. This property allows changed rows to be identified, and the appropriate update command to be used to update the data source with the changes.

Creating a DataRow

A DataRow is created by calling the NewRow( ) method of a DataTable, a method that takes no arguments. The DataTable supplies the schema, and the new DataRow is created with default or empty values for the fields:

// create a table with one column

DataTable dt = new DataTable();

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

// create a row with the same schema as DataTable dt

DataRow row = dt.NewRow();

row[“MyColumn”] = “Item 1”;

// add the row to the table

dt.Rows.Add(row);

Updating Rows

There are three ways to modify the contents of a row. First, you can simply replace the values of the column with a new value:

DataRow row;

// … code to retrieve data into the row

// access the column by its ordinal and update the value

row[0] = “New Value”;

// access the column by its name and update the value

row[“MyColumn”] = “New Value”;

You can also buffer the updates to a row by calling the BeginEdit( ) , EndEdit( ), and CancelEdit( ) methods. The BeginEdit( ) method turns off all constraints and suspends events used to enforce validation rules. If CancelEdit( ) is called, the changes in the buffer are discarded. When EndEdit( ) is called, the data is validated against the constraints, and the appropriate events are raised. BeginEdit( ) is called implicitly when a user changes the value of a data-bound control. EndEdit( ) is called implicitly when AcceptChanges( ) is called.

DataTable dt = new DataTable();

// … code to retrieve data into the DataTable object

DataRow row = dt.Rows[0];

row.BeginEdit();

foreach(DataColumn col in dt.Columns)

{

// …modify the column value

}

bool rowValid = true;

// …check the values in the row to make sure that they are valid

if(rowValid)

{

row.CancelEdit();

}

else

{

row.EndEdit();

}

Finally, a row can be updated by accessing the row through the ItemArray property. When this method is called, an attempt is made to locate the row matching the primary key. If the row is found, it is updated with the values in the ItemArray; otherwise, a new row is created. Any columns with an array element set to null are set to the default value for the column. The value for AutoIncrement columns should be set to null in the ItemArray.

// create a table with two columns

DataTable dt = new DataTable();

DataColumn colId =

new DataColumn(“ProductId”, typeof(System.Int32));

DataColumn colDesc =

new DataColumn(“Description”, typeof(System.String));

dt.Columns.AddRange(new DataColumn[] {colId, colDesc});

dt.Rows.Add(new object[] {1, “Widget”});

// get the data for the row using the ItemArray property

object[] row = dt.Rows[0].ItemArray;

// set the ProductId to be AutoIncrement

colId.AutoIncrement = true;

// pass null for the AutoIncrement value

dt.Rows.Add(new object[] {null, “Thing”});

// let the description be null

colDesc.AllowDBNull = true;

// add a row with a null description, and AutoIncrement Id

dt.Rows.Add(new object[] {null, null});

Deleting Rows

The Delete( ) method deletes rows from the DataTable. If the RowState is Added, the row is removed; otherwise the RowState of the existing DataRow is changed to Deleted. The row is permanently removed from the table only when AcceptChanges( ) is called on the row either explicitly or implicitly when the Update( ) method of the DataAdapter successfully updates the changes to the row back to the data source.

// delete the first row from the table

DataRow row = dt.Rows[0];

row.Delete();        // RowState changed to Deleted

row = dt.NewRow();

// … code to set the data for the row

// add the row to the table

dt.Rows.Add(row);

// delete the row

row.Delete();        // Newly inserted row is removed from the table

When you iterate through the rows of a DataTable, it is important to remember that rows are only marked for deletion and are still present in the collection of rows for the table. If you might be accessing Deleted rows in the DataTable, you need to explicitly check the state of the row and ignore it if it is Deleted. This is shown in the following example:

// Iterate over the results (and ignore deleted rows).

foreach (DataRow dr in ds.Tables[“Customers”].Rows)

{

if (dr.RowState != DataRowState.Deleted)

{

// … process the row

}

}

If you try to access the current value of a field in a row that has been deleted, you’ll receive the RowNotInTableException or DeleteRowInacessibleException.

Using Row State Information

The RowState property is used by ADO.NET to track the changes that have been made to a DataRow, which allows changes made to the data while disconnected to be updated back to the data source. The RowState property indicates whether the row belongs to a table, and if it does, whether it’s newly inserted, modified, deleted, or unchanged since it was loaded.

The value of the RowState property can’t be set directly. ADO.NET sets the row state in response to actions that affect the DataRow. The AcceptChanges( ) and RejectChanges( ) methods, whether explicitly or implicitly called, both reset the RowState value for the row to Unchanged. The following code illustrates this idea:

// create a table with one column

DataTable dt = new DataTable();

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

// create a new row

DataRow row = dt.NewRow();        // RowState = Detached

// add the row to the table

dt.Rows.Add(row);                 // RowState = Added

dt.AcceptChanges();               // RowState = Unchanged

// modify the row

row[“MyColumn”] = “MyFieldValue”; // RowState = Modified

// reject the changes

row.RejectChanges();              // RowState = Unchanged

// delete the row

row.Delete();                     // RowState = Deleted

row.AcceptChanges();              // row no longer exists

Using Row Version Information

ADO.NET maintains up to three versions of each DataRow object. The DataAdapter reconciles changes made since the data was loaded from the data source, thereby making changes to the disconnected data permanent. Two versions of each row are maintained to allow the DataAdapter to determine how to perform the reconciliation. The Original version contains the values that were loaded into the row. The Current version contains the latest version of the data, including the changes made since the data was originally loaded. The Original version isn’t available for newly created rows.

ADO.NET also allows a row to be put into edit mode which temporarily suspends events for the row and allows the user to make multiple changes to the row without triggering validation rules. The BeginEdit( ) method of the DataRow puts the row into edit mode, while the EndEdit( ) and CancelEdit( ) methods take the row out of edit mode. AcceptChanges( ) also takes the row out of edit mode because it implicitly calls EndEdit( ), as does RejectChanges( ), which implicitly calls CancelEdit( ).

A Proposed row version is made available while the row is in edit mode and contains the changes that have been made to the row while it was in edit mode. If the EndEdit( ) method is called, the changes made are copied from the Proposed row to the Current row. If the CancelEdit( ) method is called, the values in the Proposed version are simply discarded. In either case, once the editing is completed, the Proposed version of the row is no longer available.

Finally, a Default row version returns the Current version if the row isn’t being edited or the Proposed row if it is.

The HasVersion( ) method of the DataRow can determine whether a specific version of the row exists. If the version exists, column values for it can be retrieved using one of the three overloads of the DataRow indexer. The following example shows how to check for and retrieve the Proposed version of a row:

DataRow dr;

// … code to build, fill, and modify the row

object objColVal;

// check if the Proposed version of the row exists

if (dr.HasVersion(DataRowVersion.Proposed))

{

// retrieve the value for the first column in the Proposed row

objColVal = dr[0, DataRowVersion.Proposed];

}

Accepting or Rejecting Changes to Rows

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 both the rows and columns that have been changed.

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

Navigating Parent and Child Rows

A DataRow can have both parent and child rows if the DataTable that it belongs to has a DataRelation set up with another table. There are three methods that allow these relationships to be navigated. The GetParentRow( ) method returns the parent row as a DataRow object. The GetParentRows( ) method returns the parent rows as an array of DataRow objects. Both methods require either the name of the DataRelation or a reference to the DataRelation as an argument. An optional argument specifying the DataRowVersion allows control over the version of the rows returned.

The SetParentRow( ) method changes the parent row for the DataRow. This method simply takes a reference to the new parent DataRow and an optional DataRelation argument, if required.

Finally, the GetChildRows( ) method returns the child rows as an array of DataRow objects. The name of the DataRelation or a reference to the DataRelation is required as an argument. An optional argument specifying the DataRowVersion allows control over the version of rows returned.

Using Row Error Information

The HasErrors property returns a Boolean value indicating whether an error is set on any of the columns in the row. This value can determine whether error-handling code needs to be executed or set custom error information based on custom validation rules. Rather than iterating over the entire collection of rows to locate errors, the GetErrors( ) method of the DataTable can return the array of rows containing errors within the table. The HasErrors property of the DataTable indicates whether there are any rows with errors and should be checked to determine whether calling GetErrors( ) is necessary.

Error information for the row can be set for the entire row or for a specific column in the row. The RowError property sets and retrieves an error description that applies to the entire row. The GetColumnError( ) and SetColumnError( ) methods set and get the error description for a particular column specified with either a column name, column ordinal, or a reference to the column object.

Finally, the ClearErrors( ) method clears all error information for the row and for all columns in the row.

The following example demonstrates how to work with DataRow errors:

DataRow row;

// … code to set up the row

if(row.HasErrors)

{

String rowErrorText = row.RowError;

foreach(DataColumn colError in row.GetColumnsInError())

{

String colErrorColumnName = colError.ColumnName;

String colErrorText = row.GetColumnError(colError);

// … processing for column errors

}

// … processing for row error

// clear the errors, once processed

row.ClearErrors();

}

else

{

// no errors in the row

}

As mentioned, errors can also be set on columns, as shown in this example:

DataRow row;

// … code to set up the row

// using the column name

row.SetColumnError(“MyColumn”, “Custom error text, based on name.”);

// using the ordinal

row.SetColumnError(1, “Custom error text, based on ordinal.”);

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: