How should I serialize some simple auditing data for storing in a SQL table?

Question

I have an audit table in SQL server. It is to record an entry for each high-level user action, e.g. update a record, add a new record, delete a record etc.

I have a mechanism for detecting and recording all the changes made (in .NET, not as a trigger in the database) and have a collection of objects that record a field name, previous value and new value. I'm wanting to store the field changes in the same table (not in a separate table, i.e. I don't want a full-blown normalised relational design for this), so I have a blob field (or it could be a character field) that I want to record the field-level audit data in.

I'm thinking I just want to take my object graph (basically just a list of these field change objects) and serialize it and store the serialized version in the table.

Later, when the user wants to view the changes I can deserialize the field and reconstruct the collection of field changes.

So, what would be the best framework/serialization format in .NET 3.5 to use? I don't much mind about the size, and it doesn't have to be human-readable.

Answer 1

To serialize an object, you need to either mark the object class with the [Serializable] attribute or implement the ISerializable interface i.e.

 [Serializable]
 public class MySerializableClass
 {
   ...
 }

or

using System.Runtime.Serialization;

public class MySerializableClass : ISerializable
{
    SerializationInfo info;
    StreamingContext context;

    public void GetObjectData(SerializationInfo info, StreamingContext context) 
    {
        this.info = info;
        this.context = context;

        // implementation code goes here
    }

}

Also have a look at IFormatter where you can chose what data type you want to serialize your object to.

Answer 2

I would probably use xml even though binary would be best for size/performance.

With xml you can still use SQL to query the data and you won't have to worry too much about versioning your objects. If you use binary, you will have to consider versioning in case your objects change.

Answer 3

Avoid BinaryFormatter for anything that you store long-term (for example in a database); because it contains type/assembly metadata you can easily find that you can't deserialize the data later. Plus it is .NET-specific! So you're a bit scuppered if you want to read it from any other platform.

JSON (via Json.NET) would make a simple, pretty readable format that doesn't take much space. Xml via XmlSerializer or DataContractSerializer would be fine but isn't as readable. If space is your biggest concern, perhaps something like "protocol buffers" (protobuf-net and others) - virtually impossible to read without the supporting utility dll, but very fast and efficient.

I'd be tempted to use JSON, personally. It means I can read the audit in SSMS...

Answer 4

I am sorry I don't have an answer, but it's really a weird feeling when someone is trying to do something that is exactly the same as you. For the past three days I have been trying to come up with solutions for the same exact problem. Craig I would very much be interested in knowing how did you manage to capture the property, the previous value and the current value.

I have sort of got that by implementing INotifyPropertyChanged and capturing just the name of the property. I then store that in a dictionary and pass it along with the object that gets serialized. On the server side then I look at the name and figure out what is it that changed. However I still haven't got to a clean solution where I can somehow serialize just the changes.

If you don't mind would you care sharing your design to capture the changes?

Thanks ~KD