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)