如何在两个SQL Server表之间检索审计跟踪，并将它们按正确的顺序放回

Question

背景上下文：

---

我有一个名为Project的数据库表和一个名为主ProjectHistory且具有相同架构的表。我正在使用nhibernate，因此我还有一个名为Project的域对象和一个称为ProjectHistory(源自Project(的域对象。

目标

---

我的目标是检索这些表中的数据，并将它们按发生的事件的顺序放回原处，并让C#代码输出最后n次更改的典型审计跟踪屏幕

详细信息

---

两个表都有以下字段和数据类型

• Id-(int(-项目中的主键，注意：历史表中没有PK
• 上次更新(日期时间(
• 名称(varchar(
• 描述(varchar(
• 时间戳(时间戳(

Projecthistory表的目标是，每当我更新Project表格时，Projecthistory

我通过使用这个触发器来实现这一点：

 ALTER TRIGGER ProjectTrigger
 ON Project
 AFTER UPDATE
 AS
    SET NOCOUNT ON
    insert into ProjectHistory
    select * from deleted

我的问题是，我现在需要一些C#代码来组合一个审计历史，其中用户选择了最近n个事件。

示例：

---

以下是事件示例：

1. 1月10日-创建项目1(历史上还没有(
2. 1月11日-创建项目2
3. 1月15日-编辑项目2(将把条目放在项目2的历史表中，最后更新日期为1月11日(
4. 1月25日-编辑项目1(将在项目1的历史表中输入日期为1月10日(

因此，正如您所看到的，如果我在历史表中按lastupdated字段降序排序，它将事件4的结果放在事件3的结果之前，这是不正常的

代码

---

我已经尝试了以下代码来尝试将它们组合在一起，但我将在下面解释缺陷：

   public IEnumerable<Project> GetHistory<Project, ProjectHistory>(int numberOfChanges)
    {
        IEnumerable<Project> current;
        IEnumerable<Project> history;
            current = Session.Query<Project>()
                .OrderByDescending(r => r.LastUpdated)
                .Take(numberOfChanges);
             history = Session.Query<ProjectHistory>()
            .OrderByDescending(r => r.LastUpdated).Cast<Project>();
            IEnumerable<Project> all = current.Concat(history);
            return all.OrderByDescending(r => r.Id).ThenByDescending(r => r.LastUpdated);
        }
    } 

我对上述代码的主要问题是：

1. 历史记录上的LastUpdated时间将反映上一次项目更新的LastUpdated时间，因此我无法在历史表中对该字段进行desc排序，并假设我将获得正确的事件降序。

   1. 我可以使用时间戳字段进行排序(因为这是输入记录的实际时间(，但似乎无法使用C#对该字段进行排序，因为该字段只是一个二进制(8(，因此它转换为C#中的一个字节[]，不支持IComparable

2. 我可以使用Project表上的lastUpdated字段，因为按lastupdate域排序可以按顺序获得最近的事件。我曾想过只遍历项目表，然后对每个项目进行单独的查询，以获取最新的历史项目，但这不支持对同一项目id进行多次更改的情况。问题是我想不出按正确顺序获取数据的方法。

我的主要观点是，我想完成我认为非常普通的事情，必须已经解决，所以我一定只是错过了这里的基本模式。

我已经有代码可以将一个项目对象与另一个项目象进行比较，并返回字段diffs(这非常有效(，但我只需要一个建议，说明如何以正确的顺序将这些表中的数据从数据库中取出(从我的查询或事后代码中(，这样我就可以显示"最后n个更改"，然后使用我的逻辑显示每个更改的字段diff。

Answer 1

这是我针对一个类似项目的解决方案。创建视图以合并项目和项目历史记录表。让SQL服务器来做繁重的工作。

这里有一个例子：

CREATE view [dbo].[VwProjectHistory] as select
    OtherFieldName,
    LastUpdated
from
    [dbo].[Project]
union all
select
    OtherFieldName,
    LastUpdated
from
    [dbo].[ProjectHistory]

Answer 2

如果我正确理解了你的问题，你可以简单地根据转换为long的时间戳进行排序。

// The last row in your question, 0 means start from the start of byte[]
return all.OrderByDescending(r => BitConverter.ToInt64(r.Timestamp,0));

Answer 3

选项1：这里有一个链接，允许您在order by子句中使用时间戳。我自己没有用过，所以使用的风险自负。

选项2：在ProjectHistory中添加一个自动递增字段，然后可以根据项目的id和自动递增id对结果进行排序。我不太使用强制转换操作，所以我不确定这些含义，但我相信你会在映射方面做更多的工作。

选项3：您是否调查了NHibernate Envers，这是一个支持和审计跟踪的框架？这将是一条新的学习曲线，但从长远来看，它可能会为你节省很多工作。