Microsoft Sync Framework encountered Nhibernate TooManyRowsAffectedexception

Question

We are trying to implement Microsoft Sync Framework in our application, which saves the domain using NHibernate.

One of the problems we encountered is that after the Sync Framework changed your original database structure (adding shadow tables and triggers), NHibernate seems upset that when trying to insert objects into the database, it throws exception toomanyrowsaffected.

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

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

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

Do you guys know if there is a way to configure the synchronization structure to automatically set these NOCOUNT instructions in it? Or maybe there is a way to tell NHibernate to expect more / less rows to be changed? Or, perhaps any of you have an automated script to add these NOCOUNT statements to triggers in the synchronization structure.

thanks in advance!

Answer 1

, NOCOUNT - . , NOCOUNT , . . . - NHibernate updatecount (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\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;
    }
}