In many business systems, especially those in an enterprise context, it is at the very least good practice to implement some sort of audit trail. We need to keep track of who has performed what operations and when they occurred. In addition, we may require information not only on the underlying database table affected, but also on the individual field changes themselves. This article will look at implementing an audit table in an SQL Server database that automatically tracks the changes made to a dataset when its updates are sent back to the data store.

Trigger Happy
Triggers are most commonly used to log the audit trail of user activity. They can be powerful in this regard, but they are not completely free of issues. For one thing, triggers are database-specific. In addition, they are limited in the data to which they have access. For example, they have access to the database user who has performed the specific operation. However, in many instances, especially in a Web application, the application may employ the same account for multiple users (such as the ever-popular, super-secure "sa" user), often to make use of connection pooling. As a result, the user information that the trigger has access to is virtually useless. In addition, triggers have no information on the specific context under which the operation was executed. Finally, while triggers can be written using a tool, multiple triggers would still be required for each table, which leads to maintenance difficulties. This leads us to seek another solution.

Of Datasets and Diffgrams
The ADO.NET dataset is a rich class that functions as an in-memory relational database. In marked contrast to the ADO Recordset, it can store multiple related tables. One of the primary changes, however, is its disconnected nature. The ADO Recordset could be configured to be disconnected from its database, but this was not the default behavior. The ADO.NET dataset, however, has been specifically architectured to be disconnected. This means that it is able to store not only the data itself for each row in a table, but multiple versions of each row. For instance, if we update a specific row, the dataset stores the row in its current (i.e., adjusted) form as well as its original state. Figure 1 displays the underlying XML for a simple dataset showing a record from the region table in the Northwind database. When we change one of the rows, the XML now becomes a "diffgram" that reflects this change (see Figure 2).

The DataAdapter
The relationship between the diffgram and the dataset to which it relates is fundamental to the disconnected nature of the dataset. It means that the dataset can be passed around easily (e.g., via an XML Web Service) and interacted with freely, all without a connection to a specific database. In fact, datasets are data-source agnostic, in that they have no specific information about whether they have been populated from an SQL Server, Oracle, or flat file store. It is the DataAdapter object by which datasets are usually filled and by which the data is returned to the database, and this is the database-specific object. This is why the dataset resides in the System.Data namespace, while the xxxDataAdapter is found under the System.Data.SQLClient or System.Data.OleDB namespaces, for example. When the DataAdapter's update method is called, it iterates through the diffgram, analyzing and implementing the type of change required for each row. Using this information will allow us to implement our audit logger.

The Audit Table
The first step in our solution will be creating the actual audit table in the database. Obviously, this structure will vary depending on users' needs, but for our purposes, the simple schema shown in Table 1 will suffice. The source for all the SQL and .NET code can be downloaded from http://www.sys-con.com/magazine/ source.cfm?id=9. An important note here is that we will be storing the entire XML diffgram for a row in a single text field, rather than storing before-and-after versions as different fields. This can, of course, be done should the need exist, and various methods can be employed here. We could, for example, iterate through the rows retrieving the fields individually. To do this, we would get versions of a field as follows:

myDataSet.Tables(0).Rows(0).Item(0, DataRowVersion.Original).

Populating the Dataset
The dataset will ultimately be populated by the fill method of a DataAdapter, but we have a few options here. We can build and configure the DataAdapter in code, but there is a caveat here - the table name must be explicitly set or our audit table will contain a lot of information on changes made to Table1 and Table2, rather than customers and orders, for instance. This is easily done, either by specifying the table names in the fill method overload, or creating a TableMapping object to the DataAdapter before the fill. If the former option is used, the update overload must be similarly set. It is easier to simply use the visual designer in Visual Studio.NET or the FillDataset method of the Data Access Application Block (DAAB - see the workspace at Click Here ! for more).

Propagating the Changes Back to the Database
To propagate the dataset changes back to the database, we need to call the DataAdapter's update method. As discussed earlier, this method analyzes the change described in the diffgram to identify what type of operation to perform on each row (i.e., an insert, update, or delete) and executes the appropriate command. For this to work, it is essential that the command objects are set for the DataAdapter. These can be created automatically using the SqlCommandBuilder or the visual designer, but the latter approach is recommended, as it executes more quickly and we have more control over the commands generated. If we do elect to build the commands ourselves, it is important to match the command parameters to the correct version of the row in the diffgram. This is achieved by setting the SourceVersion property of each parameter, as can be seen in the sample code created by the visual designer.

In order to execute our auditing code after this propagation, we need to hook into the RowUpdated event of the DataAdapter. To do this, we create our delegate sub (daRowUpdated_Event in our case) and attach it to the DataAdapter as follows:

VB.NET : AddHandler SqlDataAdapter1.RowUpdated, AddressOf daRowUpdated_Event

C# : SqlDataAdapter1.RowUpdated += new EventHandler(daRowUpdated_Event);

This means that our method will be called just subsequent to the actual update of each row, and this is where we will do our auditing. The full code can be seen in Listing 1 (the code is online at www.sys-con.com/dotnet/sourcec.cfm). Our audit insert will use a stored procedure because it is more optimized. Similarly, the SQL command and its parameter collection will be created outside of the daRowUpdated_Event method and shall instead be placed in the Button2_Click method, as can be seen in Listing 1. More optimizations can be made, and these are highly recommended in this situation where a number of database calls will be made. These have been left out for illustrative purposes.

The first line of our method checks to see if the action actually occurred (i.e., no error was encountered). It looks at the RecordsAffected property that is returned from the database. For this to work correctly, the NOCOUNT setting must be set to OFF in SQL Server, at least for these action queries.

The subsequent lines in the code obtain the table name and type of the operation we are performing. After this, we create an empty dataset with a structure that is identical to our existing one. This is accomplished through the clone command, which copies only the dataset structure. We add our current row together with its diffgram and then place it all into a MemoryStream. Finally, we pass this to its SQL Command parameter and execute the command. The final result can be seen in Figure 3. A solution for separate before-and-after versions might use GetXML on the temporary dataset for the "current" view, call RejectChanges on the row, and then call GetXML again to get the original values.

Conclusion
There is, of course, much scope for enhancement, modification, and optimization here. For example, the temporary dataset need not be reconstructed completely on each call. In addition, the code could easily be adjusted to use the DAAB. Primary keys could be stored separately from the other fields, but this would need to be explicitly defined on the DataTable. However, this is a trivial task with typed datasets.

There remains a strong case for the use of triggers in auditing. Nonetheless, they do suffer from the drawbacks outlined above, and in those instances it is hoped that this article will be beneficial. The added bonus of this approach is that by storing both versions of the data, we can create an undo function by traversing the table and working with the XML..

• Figure 1
• Figure 2
• Table 1
• Source Code (zip file)