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

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

'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

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

Return to the tips page.