sql-server – 具有加密触发器的TooManyRowsAffectedException

前端之家收集整理的这篇文章主要介绍了sql-server – 具有加密触发器的TooManyRowsAffectedException前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我正在使用nHibernate来更新一个表中有3个加密触发器的列.触发器不是我拥有的,我不能对它们进行更改,所以不幸的是我不能在其中设置NOCOUNT.

有没有另外一种方法解决在提交时抛出的TooManyRowsAffectedException?

更新1

到目前为止,我遇到的问题就是围绕.Save程序

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();@H_301_9@ 
 

感觉很脏,不容易延伸,但不会发生火山口.

解决方法

我们与第三方Sybase数据库有同样的问题.幸运的是,在一些挖掘NHibernate代码并与开发人员进行简要讨论之后,似乎有一个简单的解决方案,不需要更改NHibernate.该解决方案由Fabio Maulo于 this thread in the NHibernate developer group提供.

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

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);
    }
}@H_301_9@ 
 

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

public class NonBatchingBatcherWithoutVerification : sqlClientBatchingBatcher
{
    public NonBatchingBatcherWithoutVerification(ConnectionManager 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();
    }
}@H_301_9@ 
 

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

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

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

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

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

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

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>();
        }
    }
}@H_301_9@ 
 

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

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

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

猜你在找的MsSQL相关文章