writing audit records of pre and post values

Question

I'm currently using an SqlDataSource in ASP.NET/C# to let users insert, delete and update entries in a table/gridview. Every event needs to be written to an audit table.

I have easily implemented inserting and deleting - when inserting, the main info audited is just the parameter values of the insert query (e.Command.Parameters[0].Value.ToString() etc), and deleting is pretty much the same (just getting the ID in the delete query).

But with updating, I need to log which fields were changed and also their old values. How would I do this? As an example, here is the code for the inserting:

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
    string fields = e.Command.Parameters[0].Value.ToString() + "," + e.Command.Parameters[1].Value.ToString() + "," + e.Command.Parameters[2].Value.ToString() + "," + e.Command.Parameters[3].Value.ToString() + "," + e.Command.Parameters[4].Value.ToString();
    System.Security.Principal.WindowsPrincipal p = System.Threading.Thread.CurrentPrincipal as System.Security.Principal.WindowsPrincipal;
    string[] namearray = p.Identity.Name.Split('\\');
    string name = namearray[1];
    string queryString = "INSERT INTO Audit (source, action, item, userid, timestamp) VALUES (@source, @action, @item, @userid, @timestamp)";
    using (SqlConnection connection = new SqlConnection("constring - deleted for privacy "))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Parameters.AddWithValue("@source", "Nominal");
        command.Parameters.AddWithValue("@action", "Insert");
        command.Parameters.AddWithValue("@item", fields);
        command.Parameters.AddWithValue("@userid", name);
        command.Parameters.AddWithValue("@timestamp", DateTime.Now);
        connection.Open();
        try
        {
            command.ExecuteNonQuery();
        }
        catch (Exception x)
        {
            Response.Write(x);
        }
        finally
        {
            connection.Close();
        }
    }

}

How can this be done?

Answer 1

Why not use SQL Triggers?

Answer 2

You can save yourself a lot of headache (but not DB space) if you track the current state in your audit log insead of the previous values. When you insert, throw the original values in your audit table. Then when you update, throw the newly updated values (whether they've changed or not) in your audit log. You can roll back to any previous version from here, and you don't have to add any comparison logic for auditing.