具有加密触发器的TooManyRowsAffectedException [英] TooManyRowsAffectedException with encrypted triggers
问题描述
我正在使用nHibernate更新具有3个加密触发器的表中的2列.触发器不归我所有，我无法对其进行更改，因此很遗憾，我无法在其中设置NOCOUNT ON.
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.
推荐答案
我们在第三方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:
- 在adonet.factory_class配置属性中提供IBatcherFactory实现的名称
- 创建一个实现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).
这篇关于具有加密触发器的TooManyRowsAffectedException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
