Friday, May 27, 2005

How to access deleted rows in .NET dataset or datatable

Scenario:


1) UserInterface.dll gets typed dataset from MiddleTier.dll.
2) UserInterface.dll modifies, inserts, and deletes several rows from the typed dataset.
3) UserInterface.dll passes modified typed dataset back to MiddleTier.dll.
4) MiddleTier.dll needs to figure out what rows were modified, inserted, and deleted, then MiddleTier.dll needs to read these rows and prepare SQL command for the database.

What can we easily do:


- We can get access to modified and inserted rows:
ds.GetChanges(DataRowState.Added);
ds.GetChanges(DataRowState.Modified);
- We can also get access to deleted rows:
tableDeleted = table.GetChanges(DataRowState.Deleted);

Where the problem is:


- As soon as we try to access deleted rows we are getting exception:
-----------
An unhandled exception of type 'System.Data.DeletedRowInaccessibleException' occurred in system.data.dll

Additional information: Deleted row information cannot be accessed through the row.
-----------

Solution:


We need to reject changes in tableDeleted:
tableDeleted.RejectChanges();


Full code:


TestDataSet ds = new TestDataSet();
TestDataSet.TestTableDataTable table = ds.TestTable;
table.Rows.Add(new object[] {1, "key1", "Test1"});
table.Rows.Add(new object[] {2, "key2", "Test2"});
table.AcceptChanges();
table.Rows[0].Delete();
TestDataSet.TestTableDataTable tableDeleted = (TestDataSet.TestTableDataTable)table.GetChanges(DataRowState.Deleted);
tableDeleted.RejectChanges();
TestDataSet.TestTableRow row = tableDeleted[0];
int deletedKey1 = row.Key1;
string deletedKey2 = row.Key2;
string deletedValue001 = row.Value001;

What helped to find the solution


Google helped me to find
this info:

By Ritesh Jain via DotNetMonster.com

There is one alternative that u can try.........

'copy all the Deleted Rows into another Tables
dim dtDeleted as Datatable

dtDeleted = dtDetail.GetChanges(DataRowState.Deleted)
dtDeleted .RejectChanges

With cmdDel
Dim dr as DataRow
For Each dr In dtDeleted.Rows '
.CommandText = "DELTE FROM Table1 WHERE Id = " & dr!Id
.ExecuteNonQuery()
Next
End With

Followers

About Me

My photo
Email me: blog@postjobfree.com