Accessing Deleted Rows in a DataTable

Problem

When you delete rows from a DataSet they are really marked for deletion until changes are committed by calling AcceptChanges( ) either directly or indirectly. You want to access the rows that you have deleted from a DataTable .

Solution

Use either a DataView or the Select( ) method of the DataTable to access deleted rows.

The sample code contains three event handlers:

Form.Load

Sets up the sample by creating a DataTable containing Orders data from Northwind. A view containing the Current rows is bound to a data grid on the form.

Current Rows RadioButton.CheckedChanged

Sets the view of the Orders data to display only Current rows. The text box displaying information about deleted rows is cleared.

Deleted Rows RadioButton.CheckedChanged

Sets the view of the Orders data to display only Deleted rows. The DataTable for the DataView is retrieved and the Select( ) method is used to get the Deleted rows. The overloaded indexer in C#, or Item( ) property in VB.NET, is used to retrieve the OrderID from the Original version of these deleted rows. This information is displayed in the text box on the form.

The C# code is shown in Example 2-6.

Example 2-6. File: AccessDeletedRowsForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Text; using System.Data; using System.Data.SqlClient; private DataView dv; // . . . private void AccessDataSetDeletedRowsForm_Load(object sender, System.EventArgs e) { // Fill the Orders table. SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders", ConfigurationSettings.AppSettings["Sql_ConnectString"]); DataTable dt = new DataTable("Orders"); da.Fill(dt); // Define a view of just the Current rows. dv = new DataView(dt, null, null, DataViewRowState.CurrentRows); dataGrid.DataSource = dv; currentRadioButton.Checked = true; } private void currentRadioButton_CheckedChanged(object sender, System.EventArgs e) { // Filter to include only current rows. dv.RowStateFilter = DataViewRowState.CurrentRows; dataGrid.ReadOnly = false; deletedTextBox.Clear( ); } private void deletedRadioButton_CheckedChanged(object sender, System.EventArgs e) { // Filter the view to include only deleted rows. dv.RowStateFilter = DataViewRowState.Deleted; dataGrid.ReadOnly = true; // Get the DataTable from the DataView. DataTable dt = dv.Table; // Filter using the DataTable RowState. DataRow[] delRows = dt.Select(null, null, DataViewRowState.Deleted); StringBuilder sb = new StringBuilder("Deleted Records:" + Environment.NewLine); // Iterate over the collection of deleted rows. foreach(DataRow row in delRows) sb.Append("Order ID: " + row["OrderID", DataRowVersion.Original] + Environment.NewLine); deletedTextBox.Text = sb.ToString( ); }

Discussion

ADO.NET manages the state of the rows while they are being modified. Rows are assigned a state from the DataRowState enumeration described in Table 2-4.

Table 2-4. DataRowState enumeration

When AcceptChanges( ) is called on the DataSet , DataTable , or DataRow , either explicitly or implicitly by calling the Update( ) method of the DataAdapter , the following occurs:

• All rows with a row state of Deleted are removed.
• All other rows are assigned a row state of Unchanged and the Original row version values are overwritten with the Current version values.

When RejectChanges( ) is called on the DataSet , DataTable , or DataRow , the following occurs:

• All rows with a row state of Added are removed.
• All other rows are assigned a row state of Unchanged and the Current row version values are overwritten with the Original row version values.

Each DataRow has a RowState property that returns the current state of the row.

ADO.NET maintains several versions of the data in each row while it is being modified to allow the disconnected to be later reconciled with the data source. Table 2-5 describes the DataRowVersion enumeration values.

Table 2-5. DataRowVersion enumeration

The HasVersion( ) method of the DataRow object checks whether a particular row version exists.

The DataViewRowState enumeration is used to retrieve a particular version of data or to determine whether a version exists. It is used for this purpose by both the Select( ) method of the DataTable and by the RowStateFilter property of the DataView . You can retrieve more than one version by using a Boolean OR of DataViewRowState values. Table 2-6 describes the DataViewRowState enumeration values.

Table 2-6. DataViewRowState enumeration

The Current version of each row is retrieved by default when accessing rows in a DataTable or in a DataView . The solution demonstrates an approach for getting Deleted rows from both a DataTable and a DataView . Deleted rows include only those marked for deletion using the Delete( ) method of the DataRow or the DataView , not the Remove( ) or RemoveAt( ) method of the DataRowCollection , which instead immediately removes the specified DataRow from the collection.

The solution demonstrates two techniques for retrieving the deleted rows:

To get the Deleted rows from the DataTable , use an overload of the Select( ) method of the DataTable to return an array of deleted DataRow objects. The overload accepts an argument having a DataViewRowState enumeration value. To retrieve deleted rows, pass a value of Deleted as the argument.

To get the Deleted rows from the DataView , set the RowStateFilter property of the DataView to Deleted . Deleted rows are also visible, along with other rows, when you set the RowStateFilter property to ModifiedOriginal and OriginalRows .