微软同步框架的冲突与NHibernate TooManyRowsAffectedexception [英] Microsoft Sync Framework clashes with Nhibernate TooManyRowsAffectedexception

问题描述

我们正在努力实现Microsoft同步框架到我们的应用程序，它仍然使用NHibernate的域。

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

一，我们遇到的问题是，在同步框架已经改变了初始的数据库结构（增加影子表和触发器）NHibernate的似乎生气抛出一个toomanyrowsaffectedexception当您尝试插入对象到数据库中。

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的生成和同步触发由同步框架自动生成的手动调节所有触发器不一个真正的选择。

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.

<一个href="http://www.$c$cwrecks.com/blog/index.php/2009/03/25/nhibernate-and-toomanyrowsaffectedexception/" rel="nofollow">http://www.$c$cwrecks.com/blog/index.php/2009/03/25/nhibernate-and-toomanyrowsaffectedexception/

我尝试设置SQL Server 2008的特性NOCOUNT作为在这一问题描述：<一href="http://stackoverflow.com/questions/458548/wheres-the-best-place-to-set-nocount">http://stackoverflow.com/questions/458548/wheres-the-best-place-to-set-nocount 但是这导致了StaleStateException（-1行受到影响，预计1）。

I tried setting the sql server 2008 property NOCOUNT on as described in this question: http://stackoverflow.com/questions/458548/wheres-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.

THX提前！

推荐答案

我觉得NOCOUNT方式是要走的路。你可以通过设置NOCOUNT所使用的同步框架中的所有表做到这一点。请参见下面的code。另一种方法是修补NHibernate和忽略使用UpdateCount见（ https://nhibernate.jira.com/browse / 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).

KR，

保

    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\n", " AS\nSET NOCOUNT ON\n") + "\nSET 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;
    }
}

这篇关于微软同步框架的冲突与NHibernate TooManyRowsAffectedexception的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！