Creating Audit Functionality Using ASP.NET 2.0

• Download source - 23.1 KB

Introduction

This updated article describes how to create an audit tool that tracks and records the changes of any fields with their old and new values using an ASP.NET web page. To see the project in action, you can download the code. However, you must change your connection properties for the database interaction.

Background

When posed with this issue, I found various solutions that range from SQL Server Triggers to more custom solutions that require additional knowledge of web controls. However, my solution has the following benefits:

• Records and tracks only the changes made to the field or fields that are changed. One solution, for example, used a trigger that would populate the audit changes, but this included ALL fields that were in the UPDATE SQL statement. What this means it that it would populate the Audit table with fields that were also not changed, which filled my audit table exponentially.
• Plays on events already part of the .NET Framework. In this example, I use key event objects that allow us to record and compare data changes consistently, whether using GridView or DetailsView.
• Uses existing properties that allow audit tracking to work. There are event properties already nicely built into the .NET Framework that is part of the GridView and DetialsView control. This allows us to see the new values and old values via a returned collection object.

Using the Code

The pattern implementations are the same for both the GridView and DetailsView. It is just a matter of simply passing in the appropriate event object as a parameter. Here is an example of the overload methods for the GridView and DetailsView:

// GridView Method overload
public static void updateFieldAudits(GridViewUpdatedEventArgs e)
{
    foreach (DictionaryEntry newValues in e.NewValues)
    {
        int i = 0;

        string newKeyCol = newValues.Key.ToString();
        foreach (DictionaryEntry oldVals in e.OldValues)
        {
            string oldKeyCol = oldVals.Key.ToString();

            if (oldKeyCol == newKeyCol)
            {
                break;
            }
            i++;
        }
        string oldVal = (string)e.OldValues[i];

        if (newValues.Value != null)
        {
            string newVal = newValues.Value.ToString();

            if (oldVal != newVal)
            {
                MyDBDAL.insertAuditChanges(newKeyCol, oldVal, newVal);
            }
        }
        else
        {
            if (!String.IsNullOrEmpty(oldVal))
            {
                MyDBDAL.insertAuditChanges(newKeyCol, oldVal, "");
            }
        }
    }
}
// DetailsView Method overload
public static void updateFieldAudits(DetailsViewUpdatedEventArgs e)
{
    foreach (DictionaryEntry newValues in e.NewValues)
    {
        int i = 0;

        string newKeyCol = newValues.Key.ToString();
        foreach (DictionaryEntry oldVals in e.OldValues)
        {
            string oldKeyCol = oldVals.Key.ToString();
            if (oldKeyCol == newKeyCol)
            {
                break;
            }
                i++;
            }
            string oldVal = (string)e.OldValues[i];

            if (newValues.Value != null)
            {
                string newVal = newValues.Value.ToString();

                if (oldVal != newVal)
                {
                MyDBDAL.insertAuditChanges(newKeyCol, oldVal, newVal);
            }
        }
        else
        {
            if (!String.IsNullOrEmpty(oldVal))
            {
                MyDBDAL.insertAuditChanges(newKeyCol, oldVal, "");
            }
        }
    }
}

In order to record the changes, the event of interest on the GridView is the RowUpdated method. The example below traps this event and allows you to pass in the GridViewUpdatedEventArgs event object parameter to the updateFieldAudits method.

protected void GridView1_RowUpdated(object sender, 
    GridViewUpdatedEventArgs e)
{
    AuditFields.updateFieldAudits(e);
}

For the DetailsView code-behind, we capture the ItemUpdated event method:

protected void DetailsView1_ItemUpdated(object sender, 
    DetailsViewUpdatedEventArgs e)
{ 
    AuditFields.updateFieldAudits(e);
}

Finally, we have to wire up the web control events for the GridView and DetailsView with the following:

OnRowUpdated="GridView1_RowUpdated" 
OnItemUpdating="DetailsView1_ItemUpdating"

That's it! It is portable for web controls and you can add your own database update functions to record your audit data changes, or create a custom business object.

History

• 8 January, 2007 -- Original version posted
• 8 June, 2007 -- Article updated