带有加密触发器的 TooManyRowsAffectedException

2022-01-01 00:00:00 sql-server triggers nhibernate

我正在使用 nHibernate 更新一个表中的 2 列，该表上有 3 个加密触发器.触发器不归我所有，我无法对其进行更改，因此很遗憾我无法在其中设置 NOCOUNT.

I'm using nHibernate to update 2 columns in a table that has 3 encrypted triggers on it. The triggers are not owned by me and I can not make changes to them, so unfortunately I can't SET NOCOUNT ON inside of them.

是否有另一种方法可以解决提交时抛出的 TooManyRowsAffectedException?

Is there another way to get around the TooManyRowsAffectedException that is thrown on commit?

到目前为止，我解决这个问题的唯一方法是使用

So far only way I've gotten around the issue is to step around the .Save routine with 

var query = session.CreateSQLQuery("update Orders set Notes = :Notes, Status = :Status where OrderId = :Order");
query.SetString("Notes", orderHeader.Notes);
query.SetString("Status", orderHeader.OrderStatus);
query.SetInt32("Order", orderHeader.OrderHeaderId);
query.ExecuteUpdate();

感觉很脏，不容易延伸，但不会弹坑.

It feels dirty and is not easily to extend, but it doesn't crater.

推荐答案

我们在使用第 3 方 Sybase 数据库时遇到了同样的问题.幸运的是，在深入研究 NHibernate 代码并与开发人员进行简短讨论后，似乎有一个简单的解决方案，不需要更改 NHibernate.解决方案由 Fabio Maulo 在 NHibernate 开发人员组中的此线程中提供.

We had the same problem with a 3rd party Sybase database. Fortunately, after some digging into the NHibernate code and brief discussion with the developers, it seems that there is a straightforward solution that doesn't require changes to NHibernate. The solution is given by Fabio Maulo in this thread in the NHibernate developer group.

为了为 Sybase 实现这一点，我们创建了我们自己的 IBatcherFactory 实现，它继承自 NonBatchingBatcher 并覆盖 AddToBatch() 方法以删除对提供的 IExpectation 对象上的 VerifyOutcomeNonBatched() 调用:

To implement this for Sybase we created our own implementation of IBatcherFactory, inherited from NonBatchingBatcher and overrode the AddToBatch() method to remove the call to VerifyOutcomeNonBatched() on the provided IExpectation object:

public class NonVerifyingBatcherFactory : IBatcherFactory
{
    public virtual IBatcher CreateBatcher(ConnectionManager connectionManager, IInterceptor interceptor)
    {
        return new NonBatchingBatcherWithoutVerification(connectionManager, interceptor);
    }
}

public class NonBatchingBatcherWithoutVerification : NonBatchingBatcher
{
    public NonBatchingBatcherWithoutVerification(ConnectionManager connectionManager, IInterceptor interceptor) : base(connectionManager, interceptor)
    {}

    public override void AddToBatch(IExpectation expectation)
    {
        IDbCommand cmd = CurrentCommand;
        ExecuteNonQuery(cmd);
        // Removed the following line
        //expectation.VerifyOutcomeNonBatched(rowCount, cmd);
    }
}

要对 SQL Server 执行相同操作，您需要从 SqlClientBatchingBatcher 继承，覆盖 DoExectuteBatch() 并从 Expectations 对象中删除对 VerifyOutcomeBatched() 的调用:

To do the same for SQL Server you would need to inherit from SqlClientBatchingBatcher, override DoExectuteBatch() and remove the call to VerifyOutcomeBatched() from the Expectations object:

public class NonBatchingBatcherWithoutVerification : SqlClientBatchingBatcher
{
    public NonBatchingBatcherWithoutVerification(ConnectionManager connectionManager, IInterceptor interceptor) : base(connectionManager, interceptor)
    {}

    protected override void DoExecuteBatch(IDbCommand ps)
    {
        log.DebugFormat("Executing batch");
        CheckReaders();
        Prepare(currentBatch.BatchCommand);
        if (Factory.Settings.SqlStatementLogger.IsDebugEnabled)
        {
            Factory.Settings.SqlStatementLogger.LogBatchCommand(currentBatchCommandsLog.ToString());
            currentBatchCommandsLog = new StringBuilder().AppendLine("Batch commands:");
        }

        int rowsAffected = currentBatch.ExecuteNonQuery();

        // Removed the following line
        //Expectations.VerifyOutcomeBatched(totalExpectedRowsAffected, rowsAffected);

        currentBatch.Dispose();
        totalExpectedRowsAffected = 0;
        currentBatch = new SqlClientSqlCommandSet();
    }
}

现在你需要将你的新类注入到 NHibernate 中.我知道有两种方法可以做到这一点:

Now you need to inject your new classes into NHibernate. There are at two ways to do this that I am aware of:

  1. 在 adonet.factory_class 配置属性中提供您的 IBatcherFactory 实现的名称
  2. 创建实现 IEmbeddedBatcherFactoryProvider 接口的自定义驱动程序

鉴于我们的项目中已经有一个自定义驱动程序来解决 Sybase 12 ANSI 字符串问题，实现接口的简单更改如下:

Given that we already had a custom driver in our project to work around Sybase 12 ANSI string problems it was a straightforward change to implement the interface as follows:

public class DriverWithCustomBatcherFactory : SybaseAdoNet12ClientDriver, IEmbeddedBatcherFactoryProvider
{
    public Type BatcherFactoryClass
    {
        get { return typeof(NonVerifyingBatcherFactory); }
    }

    //...other driver code for our project...
}

可以通过使用 connection.driver_class 配置属性提供驱动程序名称来配置驱动程序.我们想使用 Fluent NHibernate，它可以使用 Fluent 来完成，如下所示:

The driver can be configured by providing the driver name using the connection.driver_class configuration property. We wanted to use Fluent NHibernate and it can be done using Fluent as follows:

public class SybaseConfiguration : PersistenceConfiguration<SybaseConfiguration, SybaseConnectionStringBuilder>
{
    SybaseConfiguration()
    {
        Driver<DriverWithCustomBatcherFactory>();
        AdoNetBatchSize(1); // This is required to use our new batcher
    }

    /// <summary>
    /// The dialect to use
    /// </summary>
    public static SybaseConfiguration SybaseDialect
    {
        get
        {
            return new SybaseConfiguration()
                .Dialect<SybaseAdoNet12Dialect>();
        }
    }
}

在创建会话工厂时，我们使用这个新类如下:

and when creating the session factory we use this new class as follows:

var sf = Fluently.Configure()
    .Database(SybaseConfiguration.SybaseDialect.ConnectionString(_connectionString))
    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<MyEntity>())
    .BuildSessionFactory();

最后，您需要将 adonet.batch_size 属性设置为 1，以确保使用新的批处理程序类.在 Fluent NHibernate 中，这是使用继承自 PersistenceConfiguration 的类中的 AdoNetBatchSize() 方法完成的(有关此示例，请参见上面的 SybaseConfiguration 类构造函数).

Finally you need to set the adonet.batch_size property to 1 to ensure that your new batcher class is used. In Fluent NHibernate this is done using the AdoNetBatchSize() method in a class that inherits from PersistenceConfiguration (see the SybaseConfiguration class constructor above for an example of this).

相关文章