ADO.NET 3.5 Cookbook, 2nd Edition

4.7. 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 one of the four techniques demonstrated in this solution.

The solution fills a DataSet with the TOP 3 records from the Person.Contact table in the AdventureWorks database. The second row is deleted. The deleted row is accessed and written to the console using the following techniques:

• Iterating over the records in the DataTable, identifying the deleted rows as having their RowState property being set to DataRowState.Deleted, and accessing the original values for the deleted row using the field accessor of the DataRow that takes a second argument and specifying it as DataRowVersion.Original.

• Creating a DataView of only deleted records in the table using an overload of the DataView constructor with the row state argument set to DataViewRowState.Deleted.

• Creating a DataView based on the table and filtering only deleted records by setting the RowStateFilter property of the DataView to DataViewRowState.Deleted.

• Using the Select() method with the row state set to DataViewRowState.Deleted to get a DataRow array of deleted records from the DataTable and accessing the values for the deleted rows through their original values by specifying a row state of DataRowVersion.Original in the DataRow field accessor.

The C# code in Program.cs in the project AccessDeletedDataRows is shown in Example 4-8.

Example 4-8. File: Program.cs for AccessDeletedDataRows solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace AccessDeletedDataRows
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = "SELECT TOP 3 ContactID, FirstName, LastName " +
                "FROM Person.Contact";

            // Fill the DataSet with the results of the batch query.
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable();
            da.Fill(dt);

            // Output the rows in the DataTable
            Console.WriteLine("---ORIGINAL DATATABLE---");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("{0}, {1}, {2}", row[0], row[1], row[2]);
            Console.WriteLine();

            // Delete row 2
            dt.Rows[1].Delete();

            // Output the rows in the DataTable
            Console.WriteLine("---AFTER ROW 2 DELETED---");
            foreach (DataRow row in dt.Rows)
            {
                if (row.RowState == DataRowState.Deleted)
                {
                    Console.WriteLine("{0}:\t {1}, {2}, {3}",
                        row.RowState, row[0, DataRowVersion.Original],
                        row[1, DataRowVersion.Original], row[2, DataRowVersion.Original]);
                }
                else
                {
                    Console.WriteLine("{0}:\t {1}, {2}, {3}", row.RowState,
                        row[0], row[1], row[2]);
                }
            }
            Console.WriteLine();

            Console.WriteLine("---USING DATAVIEW CONSTRUCTOR TO ACCESS DELETED ROW---");
            DataView dv = new DataView(dt, null, null, DataViewRowState.Deleted);
            foreach (DataRowView row in dv)
                Console.WriteLine("{0}, {1}, {2}", row[0], row[1], row[2]);

            Console.WriteLine("\n---USING DATAVIEW FILTER TO ACCESS DELETED ROWS---"); 
            dv = new DataView(dt);
            dv.RowStateFilter = DataViewRowState.Deleted;
            foreach (DataRowView row in dv)
                Console.WriteLine("{0}, {1}, {2}", row[0], row[1], row[2]);

            Console.WriteLine("\n---USING DATATABLE.SELECT()---");
            // Get the DataRow array of deleted items
            DataRow[] dra = dt.Select(null, null, DataViewRowState.Deleted);
            // Iterate over the array and display the original version of each
            // deleted row
            for (int i = 0; i < dra.Length; i++)
            {
                Console.WriteLine("{0}, {1}, {2}", dra[i][0, DataRowVersion.Original],
                    dra[i][1, DataRowVersion.Original], 
                    dra[i][2, DataRowVersion.Original]);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

The output is shown in Figure 4-8.

Figure 4-8. Output for AccessDeletedDataRows solution

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 4-2.

Table 4-2. DataRowState enumeration

Value	Description
Added	The row has been added to the collection of rows in the table but AcceptChanges() has not been called.
Deleted	The row has been deleted from the collection of rows in the table but AcceptChanges() has not been called.
Detached	The row does not belong to the collection of rows in a DataTable.
Modified	The data in the row has been changed but AcceptChanges() has not been called.
Unchanged	The data in the row has not been changed since it was loaded or since AcceptChanges() was last called.

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 4-3 describes the DataRowVersion enumeration values.

Table 4-3. DataRowVersion enumeration

Value	Description
Current	Current value. This version does not exist for rows with a state of Deleted.
Default	Default value as determined by the DataRowState: The Current version for rows with Added, Modified, or Unchanged state The Original version for rows with Deleted state The Proposed value for rows with Detached state
Original	Original value. This version does not exist for rows with a state of Added.
Proposed	Proposed value. This value exists during a row edit operation started either implicitly or explicitly with the BeginEdit() method and for Detached rows.

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 4-4 describes the DataViewRowState enumeration values.

Table 4-4. DataViewRowState enumeration

Value	Description
Added	The Current version of all Added rows.
CurrentRows	The Current version of all Unchanged, Added, and Modified rows. This is the default value.
Deleted	The Original version of all Deleted rows.
ModifiedCurrent	The Current version of all Modified rows.
ModifiedOriginal	The Original version of all Modified rows.
None	No rows.
OriginalRows	The Original version of Unchanged, Modified, and Deleted rows.
Unchanged	The Current version of all Unchanged rows.

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() methods of the DataRowCollection, which instead immediately remove 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 access their values, use the original version of the row by specifying DataRowVersion.Original in the overloaded field accessor of the DataRow. You cannot access the values of a deleted row directly.

To get the Deleted rows from the DataView, set the RowStateFilter property of the DataView to Deleted—you can do this using the fourth argument in the DataView constructor.