Getting access to the Deleted Rows

If you Delete a row in a DataTable it's not really gone. It's just in a private buffer thing that hides the data out of the traditional Rows Collection and that works good for databinding and the like. The deleted rows are kept so when you do a DataAdapter.Update() it knows which rows you wanted to delete in the persistent store.

So what if you are writing your own adapter or for other reasons just need to get at the deleted rows. The trick is to create a DataView with a RowState Filter of “Deleted“ and then you can look at the DataRowViews to get individual row/column data.

DataView deleteView = new DataView(MyDataSet.MyTable, "", "", System.Data.DataViewRowState.Deleted);

foreach (DataRowView drv in deleteView)

{

string someColumnValue = (string)drv["SomeColumn"];

}

Using a DataAdapter to create transaction history

In some mission critical applications, it's important create a log or audit trail against updates made against a specific table. Sometimes this could be done with triggers if you have all the information available, or perhaps your updates are encapsulated inside of stored procedures so you can add central code their. What you may not have known is that if you are using a DataAdapter to read & write to a table that needs to be audited, then you can accomplish the task with the DataAdapter.

The secret to this tip is that a SqlCommand object, like the Insert, Update and Delete command members of the DataAdapter can execute more than one SQL statement. In fact, DataAdapters use this technique by default to refresh a DataSet after an update or Insert. This is important to retrieve identity column values, other column defaults or perhaps values that have been modified by triggers.

To include additional SQL statements, separate them with a semi-colon. You have full access to the parameters collection as well. In the text below is an example update command text that has been modified to insert into a log table, that contains an columns: Action, OldCategory, NewCategory. This text can be assigned to the UpdateCommand's CommandText property inside the DataAdapter.

UPDATE    Categories
   set    CategoryName = @CategoryName,
          Description = @Description,
          Picture = @Picture
WHERE     (CategoryID = @Original_CategoryID) 
      AND (CategoryName = @Original_CategoryName);
SELECT    CategoryID, CategoryName, Description, Picture
 FROM     Categories
WHERE     (CategoryID = @CategoryID);
INSERT INTO CategoryLog
          (Action, OldCategory, NewCategory)
          VALUES ('Updated",@Original_CategoryName, @CategoryNam)

How can I improve the loading of my datasets?

Datasets maintain some internal indexes to improve performs for things like finds and selects.

When you are loading more than 1 row into a DataTable - with a DataAdapter.Fill or other technique, you can turn this index maintenance off by doing a

MyTable.BeginLoadData()

Where MyTable is the reference to your DataTable, which could be

MyDataSet.Tables[0].BeginLoadData()

When you are done loading the data, don't forget to turn the index maintenance back on with.

MyDataSet.Tables[0].EndLoadData()

When loading multiple tables, you should turn on/off 1 table at a time.

Another thing that Datasets do while having rows added to tables is to validate them. Things like primary key uniqueness, foreign key referential integrity and nulls in columns with AllowDBNull = false are some examples of things that must be checked. Again, you can save some cycles by turning this off during loading of a dataset and turning it back on afterward. This can be done with:

MyDataSet.EnforceConstraints = false

And of course when you are done loading, you can perform a:

MyDataSet.EnforceConstraints = true

Of course you may get a “ConstraintException” on this last line if there are problems with your data. Otherwise, you'd get this exception as soon as the offending row is loaded. On a related note, you can check DataSet.HasErrors and each DataTable.HasErrors for any errors. For each table you can call DataTable.GetErrors() to get a list of rows with errors. Each row has a RowError property that contains any error text related to the entire row, and also a GetColumnError() method that you can use to test each column for a column specific error message.