Microsoft Sync Framework clashes with Nhibernate TooManyRowsAffectedexception

Question

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

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.

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/

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).

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 in advance!

Answer 1

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 (http://216.121.112.228/browse/NH-1353).

KR,

Paul

    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;
    }
}