Audit logs & DataSets tip

A couple of weeks ago, I was in the Netherland Antilles doing a training course on DeKlarit to a group of around 10 developers. They found a very good solution to a quite common problem which is how to maintain an audit trail of the changes in database tables.

What they did was to use the DataSet Diffgram as a way to get the changes in an entity, and store the diffgram in a database table. So, they just need to write:

StringWriter stringWriter =  new StringWriter();
dataSet.WriteXml(stringWriter , XmlWriteMode.DiffGram);

And then store stringWriter.ToString(); in a table column.

This approach has some of drawbacks:

- The XML string could be big, so you could need to compress it.

- You cannot (efficiently) answer the question 'who changed this column from Peter to John yesterday', because that would require parsing the XML in each row. You'll be able to answer it with Yukon.

And some big advantages:

- It is _very_ easy to write, in particular if you use the DataSets for your Business Entities.

 - You can display the changes in a row by just applying an XSLT to the diffgram.