使用 MSSQL Server 2005 XML 列保存记录历史记录的通用解决方案框架

• 下载源文件 - 121.3 KB

引言

开发者总是希望保存记录的历史。对于几乎所有类型的应用程序来说，查看记录的先前版本都是一个很好的功能。最终用户希望查看记录的先前版本，因为安全原因（谁更改了这个记录），为了记住某些事情（客户之前的城市是什么）等等。但是，开发和维护这项工作都非常昂贵。

开发者 - 如果他们必须这样做 - 尝试以多种方式实现此功能：使用触发器，生成表的副本，并将记录的历史记录存储在此表中等等。我并不是坚持即将讨论的方法是实现此功能的最佳方式。这可能只是另一种方法，另一种思路。

新的方案

本文提供：创建一个名为 HISTORY 的通用表，在经典列中保存基本列，例如相关表，相关行 ID，操作码（插入，更新，删除），事务用户，记录的版本号，操作时间戳，并添加一个 XML 行以将修改行的整个内容保存为 XML，称为 ROW_CONTENT。我们将使用该 XML 列来检索记录的历史记录。这样，我们可以在同一个表中保存许多表的历史记录。

背景

XML 数据类型

您可以像在 SQL Server 中使用任何其他数据类型一样使用 XML 数据类型，例如

• 作为表中的列
• 作为 TSQL 中的变量
• 作为存储过程或用户定义函数参数等。

SELECT...FOR XML 增强功能

SELECT...FOR XML 语法提供了一种增强 TSQL 的方法，用于组合使用 XML 文档功能和 TSQL 查询。

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

这里还有另一个例子，首先将单元格转换为 XML，然后再将其取回作为值

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

这里的主要问题是获取该列的名称：/row[1]/@CompanyName。我们将使用 Syscolumns 来生成一个通用解决方案来解决这个问题。在上面的源文件中有一个存储过程来获取列的 xtype、精度和比例，名为“STP_GET_COLDEF”。因此，无论我们对哪个表感兴趣，我们都可以使用该存储过程将 XML 行中的值转换为 TSQL 值。

使用代码

下载上面的源文件。有一个名为*Database.sql*的文件，可以用来在 Microsoft SQL Server Management Studio 上执行此文件来创建一个解决方案数据库。然后根据需要修改*Program.cs*中的必要连接字符串。默认情况下连接字符串是

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

为 Customers 和 Employees 表添加一些记录。修改它们。查看历史记录。

特别感谢

我感谢 Gerd Klevesaat 的精彩文章“PropertyGrid 中集合数据的自定义显示”。 它对我在 PropertyGrid 中显示 datarow 的单元格值非常有帮助。 如果您愿意，可以在这里查看本文：Gerd Klevesaat 的 PropertyGrid 中集合数据的自定义显示。

需要考虑的事项

正如您所见，这并不是一个完整的解决方案框架。 这只是实现此功能的一个提示。

您可能需要考虑本文的许多事情，例如

• 以用户友好的方式显示查找列的值
• 以用户友好的方式显示列标题
• 以用户友好的方式显示主从记录的历史记录

等等。但是所有这些事情都很容易实现。它们根本不是本文的主题。

结论

使用 XML 列保存记录的历史记录是完成这项工作的一种简单方法。