Working with Datatable Rowstates

Provided by Jim Ferguson, Senior Application Architect

The ADO.NET DataTable object contains a lot of useful information that you might not be aware of. For each row in the DataTable you can determine the row’s state (is it original, new, deleted, modified etc.) and you can have access to the "before" values of modified rows as well as the values from rows that have been deleted. These examples show some of the things you can do to test and work with this additional information.

First we will create a test DataTable and add some data to it. Note that all rows will have the rowstate of "Added" since they did not come from an external data store by means of the ADO "Fill" command:

Dim dt As New DataTable
Dim nr As DataRow

dt.Columns.Add(New DataColumn("ID", GetType(Integer)))
dt.Columns.Add(New DataColumn("Name", GetType(String)))
nr = dt.NewRow
nr.Item("ID") = 1
nr.Item("Name") = "Bob"
dt.Rows.Add(nr)
nr = dt.NewRow
nr.Item("ID") = 2
nr.Item("Name") = "Sue"
dt.Rows.Add(nr)
nr = dt.NewRow
nr.Item("ID") = 3
nr.Item("Name") = "Dave"
dt.Rows.Add(nr)
Debug.WriteLine("All rows, rowstate is added")

For Each dr As DataRow In dt.Rows
  Debug.WriteLine("ID: " & dr.Item("ID").ToString & _
  ", Name: " & dr.Item("Name").ToString & _
  ", Rowstate: " & dr.RowState.ToString)
Next

---

Now we will delete a row. Since the row we delete had the row state of "Added", deleting it simply removes it from the collection. There is no way to access the values from this deleted row:

' Delete a row

dt.Rows(1).Delete()

Debug.WriteLine("Only two rows in collection. No way to access the deleted row")

For Each dr As DataRow In dt.Rows
  Debug.WriteLine("ID: " & dr.Item("ID").ToString & _
  ", Name: " & dr.Item("Name").ToString & _
  ", Rowstate: " & dr.RowState.ToString)
Next

---

In order to change the rowstate of the added rows to "Unchanged", you use the AcceptChanges method. Now see what happens to the RowState property of the remaining rows:

' Cause all rows to be set to unchanged
dt.AcceptChanges()

' Modify a row
dt.Rows(1).Item("Name") = "Molly"

Debug.WriteLine("Now see the unchanged and modified rowstate")

For Each dr As DataRow In dt.Rows
  Debug.WriteLine("ID: " & dr.Item("ID").ToString & _
  ", Name: " & dr.Item("Name").ToString & _
  ", Rowstate: " & dr.RowState.ToString)
Next

---

Now when we delete a row, it remains in the collection. If you attempt to access the values of the deleted row, you will generate a run-time error. In order to avoid this, you can check the row’s RowState property, and skip rows that are marked as Deleted:

' Delete another row
dt.Rows(1).Delete()

Debug.WriteLine("Now deleted row is included with the correct rowstate")

' Must check the rowstate.
' Attempt to access the deleted row would fail

For Each dr As DataRow In dt.Rows
  If dr.RowState = DataRowState.Unchanged Then
    Debug.WriteLine("ID: " & dr.Item("ID").ToString & _
    ", Name: " & dr.Item("Name").ToString & _
    ", Rowstate: " & dr.RowState.ToString)
  End If
Next

---

Finally, what if you need to be able to see the values of the deleted row (perhaps you are logging the "before" state of a row to an audit table)? The way to do this is to create a filtered DataView from the DataTable. One of the arguments to the DataView constructor is the DataViewRowState of the rows you wish to access. Here is an example of how to do this:

' In order to access the values from the deleted row
' you can create a filtered dataview

Debug.WriteLine("Retrieve the values of deleted rows")

Dim dv As New DataView(dt, "", "", DataViewRowState.Deleted)

For Each drv As DataRowView In dv
  Debug.WriteLine("ID: " & drv.Item("ID").ToString & _
  ", Name: " & drv.Item("Name").ToString)
Next