Generic Solution Framework to Saving History of Records Using MSSQL Server 2005 Xml Column

• Download source files - 121.3 KB

Introduction

Developers always want to keep a history of records. It's a nice feature to see previous versions of records for almost every kind of application. End users want to see previous versions of records because of security (who changed this record), to remember something (what was customers previous city) etc. However, this is an expensive job to both develop and maintain.

Developers - if they have to - try to implement this feature in many ways: using triggers, generating a copy of a table, and storing history of records in this table, etc. I'm not insisting that the method about to be discussed is the best way to implement this feature. This may only be another way to do this; another idea.

New Offerings

This article offers: Create a generic table called HISTORY, save basic columns such as a related table, a related row id, an operation code (insert, update, delete), a transaction user, version number of record, operation timestamp in classic columns, and add an XML row to save a modified row's whole content as XML, called ROW_CONTENT. We'll use that XML column to retrieve history of records later. This way we can save many table's history in same table.

Background

The XML Data Type

You can use the XML data type like any other data type in SQL Server such as:

• As a column in a table
• As a variable in TSQL
• As a stored procedure or user-defined function parameter etc.

SELECT...FOR XML Enhancements

SELECT...FOR XML syntax provides and enhancement to TSQL for using XML document features and TSQL queries combined.

USE Northwind
SELECT TOP 1 CompanyName,ContactName
FROM Customers
ORDER BY CompanyName
FOR XML RAW
--result is : 
<row CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" />

And here another example to convert cells to XML first then get back as value:

USE Northwind
DECLARE @L_XML XML
DECLARE @L_COMPANY_NAME NVARCHAR(40)
SET @L_XML = (SELECT TOP 1 CompanyName,ContactName FROM Customers
                 ORDER BY CompanyName FOR XML RAW)
PRINT '@XML : ' + CAST (@L_XML as  NVARCHAR(100) )
SET @L_COMPANY_NAME = ( SELECT @L_XML.value('/row[1]/@CompanyName',_ 
    'NVARCHAR(40)') )
PRINT 'Company Name : ' + @L_COMPANY_NAME
--Output is : 
@XML : <row CompanyName="Alfreds Futterkiste" ContactName="Maria Anders"/>
Company Name : Alfreds Futterkiste

The main problem here is to get that column's name: /row[1]/@CompanyName. We'll use Syscolumns to generate a generic solution for solving this issue. There is a stored procedure to get column's xtype, precision, and scale called "STP_GET_COLDEF" in above source files. So we can convert values from XML rows to TSQL values by using that stored procedure regardless of which table we're interested in.

Using the code

Download source file above. There is a file named Database.sql which can be used to create a solution database executing this file on Microsoft SQL Server Management Studio. Then make the necessary connection string modifications at Program.cs if needed. By default connection the string is:

gs.ConnectionString = "server=(local);database=CONHIST;
    Integrated Security = SSPI";

Add some records for both Customers and Employees table. Modify them. See history records.

Special Thanks to

I appreciate to Gerd Klevesaatfor for his great article about "Customized display of collection data in a PropertyGrid". It was so helpful for me to display datarow's cell values in PropertyGrid. Here you can take a look at to this article if you wish: Customized display of collection data in a PropertyGrid by Gerd Klevesaat.

Things Left to Consider

As you see this is not a complete solution framework. This is a hint for implementing this feature.

You may need to consider many things about this article such as:

• Displaying lookup column's values in a user-friendly manner
• Displaying column titles in user-friendly manner
• Displaying master detail record's history in user-friendly manner

... and so on. But all of these things are easy to implement. They are simply not the subject of this article.

Conclusion

Using an XML column for saving history of records is an easy way of doing this job.