Microsoft Sync Framework 与 Nhibernate TooManyRowsAffectedexception 发生冲突 [英] Microsoft Sync Framework clashes with Nhibernate TooManyRowsAffectedexception

问题描述

我们正在尝试将 Microsoft Sync Framework 实施到我们的应用程序中，该应用程序使用 NHibernate 保留其域.

We are trying to implement the Microsoft Sync Framework into our application that persists it's domain using NHibernate.

我们遇到的一个问题是，在 Sync Framework 更改了您的初始数据库结构(添加影子表和触发器)之后，NHibernate 似乎会在您尝试将对象插入数据库时​​抛出 tomanyrowsaffectedexception 感到不安.

One of the problems we encountered is that after the Sync Framework has altered your initial database structure (adding shadow tables and triggers) NHibernate seems to get upset by throwing an toomanyrowsaffectedexception when you try to insert objects into the database.

我发现这篇文章有在每个更新语句周围添加SET NOCOUNT ON和OFF的解决方案，但是由于表结构是由nhibernate自动生成的，同步触发器是由Sync Framework自动生成的，手动调整所有触发器不是真的是一个选择.

I found this article that has the solution of adding SET NOCOUNT ON and OFF around each update statement, but since the table structure is automatically generated by nhibernate and the sync triggers are automatically generated by the Sync Framework adjusting all triggers manually is not really an option.

http://www.codewrecks.com/blog/index.php/2009/03/25/nhibernate-and-toomanyrowsaffectedexception/

我尝试按照这个问题中的描述设置 sql server 2008 属性 NOCOUNT:设置 NOCOUNT 的最佳地点在哪里?但这导致了 StaleStateException(-1 行受影响，预期为 1).

I tried setting the sql server 2008 property NOCOUNT on as described in this question: Where's the best place to SET NOCOUNT? but this resulted in a StaleStateException (-1 rows affected, expected 1).

你们知道是否有办法配置同步框架以在它的触发器中自动设置这些 NOCOUNT 语句?或者有没有办法告诉 NHibernate 期望更多/更少的行被更改?或者，也许你们中的任何人都有一个自动脚本来将这些 NOCOUNT 语句添加到同步框架的触发器中.

Do you guys know if there is a way to configure the sync framework to automatically set these NOCOUNT statements in it's triggers? Or maybe is there a way to tell NHibernate to expect more/less rows to have been changed? Or maybe any of you have an automated script to add these NOCOUNT statements to the sync framework's triggers.

提前谢谢！

推荐答案

我认为 NOCOUNT 方法是要走的路.您可以通过为同步框架使用的所有表设置 NOCOUNT 来做到这一点.请参阅下面的代码.另一种方法是修补 NHibernate 并忽略更新计数(https://nhibernate.jira.com/浏览/NH-1353).

I think the NOCOUNT way is the way to go. You could do this by setting the NOCOUNT for all tables used by the sync framework. See the code below. Another way is to patch NHibernate and ignore the updatecount see (https://nhibernate.jira.com/browse/NH-1353).

韩国，

保罗

    class SqlSyncTriggerHelper
{
    private const string triggerSql = @"select sys.triggers.name from sys.triggers, sys.objects
        where sys.objects.name='{0}' and sys.objects.type = 'U' and sys.triggers.parent_id = sys.objects.object_id";

    private DbSyncScopeDescription syncScopeDescription;

    public SqlSyncTriggerHelper(DbSyncScopeDescription syncScopeDescription)
    {
        this.syncScopeDescription = syncScopeDescription;
    }

    public void Apply(SqlConnection conn)
    {
        SqlTransaction transaction = null;
        try
        {
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
            transaction = conn.BeginTransaction();
            foreach (var table in syncScopeDescription.Tables)
            {
                foreach (string trigger in GetTriggers(table.UnquotedLocalName, conn, transaction))
                {
                    AlterTrigger(trigger, conn, transaction);
                }
            }
            transaction.Commit();
        }
        catch
        {
            if (transaction != null)
            {
                transaction.Rollback();
            }
            throw;
        }
        finally
        {
            if (transaction != null)
            {
                transaction.Dispose();
            }
            conn.Close();
        }
    }

    private void AlterTrigger(string trigger, SqlConnection conn, SqlTransaction transaction)
    {
        SqlCommand newCmd = new SqlCommand(string.Format("exec sp_helptext '{0}'", trigger), conn, transaction);
        var triggerStringBuilder = new StringBuilder();
        using (var reader = newCmd.ExecuteReader())
        {
            while (reader.Read())
            {
                triggerStringBuilder.Append(reader.GetValue(0) as string);
            }
        }
        var triggerString = triggerStringBuilder.ToString();
        triggerString = triggerString.Replace("CREATE TRIGGER", "ALTER TRIGGER").Replace(" AS
", " AS
SET NOCOUNT ON
") + "
SET NOCOUNT OFF";
        var alterTriggerCommand = new SqlCommand(triggerString, conn, transaction);
        alterTriggerCommand.ExecuteNonQuery();
    }

    private IEnumerable<string> GetTriggers(string tableName, SqlConnection conn, SqlTransaction transaction)
    {
        var resultList = new List<string>();
        var command = new SqlCommand(string.Format(triggerSql, tableName), conn, transaction);
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                resultList.Add(reader.GetString(0));
            }
        }
        return resultList;
    }
}

这篇关于Microsoft Sync Framework 与 Nhibernate TooManyRowsAffectedexception 发生冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！