Uso de perfiles de bases de datos de MiniProfiler con NHibernate

mvc-mini-profiler

Question

¿Cuál es la forma más sencilla de utilizar perfiles de bases de datos de MiniProfiler con NHibernate? Para que el generador de perfiles funcione, necesito ajustar el DbConnection que NHibernate usa en una DbConnection ProfiledDbConnection .

No estoy muy familiarizado con los aspectos internos de NHibernate, por lo que no sé dónde están todos los puntos de extensibilidad. (Noté que NHibernate ISession tiene una propiedad de Connection , pero es de solo lectura).

Answer 1

Intente implementar NHibernate.Connection.IConnectionProvider (podría heredar DriverConnectionProvider ), en GetConnection() ajustar el IDbConnection como lo necesite.

Conecte su proveedor de conexión utilizando la clave Environment.ConnectionProvider en sus propiedades de configuración.

Answer 2

[ACTUALIZACIÓN] Consulte los siguientes enlaces para una versión de que usa RealProxy para proxy de SqlCommand - ahora se admite el procesamiento por lotes

• blog http://blog.fearofaflatplanet.me.uk/mvcminiprofiler-and-nhibernate-take-2
• esencia https://gist.github.com/1110153

He dejado la respuesta original sin modificaciones, ya que fue aceptada. [/ACTUALIZAR]

Me las he arreglado para hacer que esto funcione parcialmente al implementar un controlador de cliente perfilado (ejemplo para Sql Server 2008 abajo) - esto funciona para ejemplos simples, sin embargo, aún no he encontrado una solución para el procesamiento por lotes NH (que intenta lanzar el comando volver a SqlCommand)

public class ProfiledSql2008ClientDriver : Sql2008ClientDriver
{
    public override IDbCommand CreateCommand()
    {
        return new ProfiledDbCommand(
            base.CreateCommand() as DbCommand, 
            null,
            MiniProfiler.Current);
    }

    public override IDbConnection CreateConnection()
    {
        return ProfiledDbConnection.Get(
            base.CreateConnection() as DbConnection, 
            MiniProfiler.Current);
    }
}

Answer 3

Si alguien está interesado, he hecho una integración usando un apilador Log4net personalizado en su lugar. De esta forma me siento seguro de no meterme con el objeto Connection.

El bosquejo aproximado es algo así: NHibernate emite las cadenas sqls como instrucciones de depuración y el appender configurado en las llamadas log4net.xml inicia y descarta en el MiniProfiler.

Answer 4

Extendí la respuesta de Roberts de arriba para trabajar con NHibernate por lotes. Aquí hay muchos códigos, por lo que es posible que se acorten, algunos de ellos basados ​​en la fuente nHibernate para el controlador del cliente.

<property name="connection.driver_class">YoureOnTime.Data.ProfiledSqlClientDriver, YoureOnTime.Common</property>


public class ProfiledSqlClientDriver : DriverBase, IEmbeddedBatcherFactoryProvider
{
    public override IDbConnection CreateConnection()
    {
        return new ProfiledSqlDbConnection(
            new SqlConnection(), 
            MiniProfiler.Current);
    }

    public override IDbCommand CreateCommand()
    {
        return new ProfiledSqlDbCommand(
            new SqlCommand(),
            null,
            MiniProfiler.Current);
    }

    public override bool UseNamedPrefixInSql
    {
        get { return true; }
    }

    public override bool UseNamedPrefixInParameter
    {
        get { return true; }
    }

    public override string NamedPrefix
    {
        get { return "@"; }
    }

    public override bool SupportsMultipleOpenReaders
    {
        get { return false; }
    }

    public static void SetParameterSizes(IDataParameterCollection parameters, SqlType[] parameterTypes)
    {
        for (int i = 0; i < parameters.Count; i++)
        {
            SetVariableLengthParameterSize((IDbDataParameter)parameters[i], parameterTypes[i]);
        }
    }

    private const int MaxAnsiStringSize = 8000;
    private const int MaxBinarySize = MaxAnsiStringSize;
    private const int MaxStringSize = MaxAnsiStringSize / 2;
    private const int MaxBinaryBlobSize = int.MaxValue;
    private const int MaxStringClobSize = MaxBinaryBlobSize / 2;
    private const byte MaxPrecision = 28;
    private const byte MaxScale = 5;
    private const byte MaxDateTime2 = 8;
    private const byte MaxDateTimeOffset = 10;

    private static void SetDefaultParameterSize(IDbDataParameter dbParam, SqlType sqlType)
    {
        switch (dbParam.DbType)
        {
            case DbType.AnsiString:
            case DbType.AnsiStringFixedLength:
                dbParam.Size = MaxAnsiStringSize;
                break;

            case DbType.Binary:
                if (sqlType is BinaryBlobSqlType)
                {
                    dbParam.Size = MaxBinaryBlobSize;
                }
                else
                {
                    dbParam.Size = MaxBinarySize;
                }
                break;
            case DbType.Decimal:
                dbParam.Precision = MaxPrecision;
                dbParam.Scale = MaxScale;
                break;
            case DbType.String:
            case DbType.StringFixedLength:
                dbParam.Size = IsText(dbParam, sqlType) ? MaxStringClobSize : MaxStringSize;
                break;
            case DbType.DateTime2:
                dbParam.Size = MaxDateTime2;
                break;
            case DbType.DateTimeOffset:
                dbParam.Size = MaxDateTimeOffset;
                break;
        }
    }

    private static bool IsText(IDbDataParameter dbParam, SqlType sqlType)
    {
        return (sqlType is StringClobSqlType) || (sqlType.LengthDefined && sqlType.Length > MsSql2000Dialect.MaxSizeForLengthLimitedStrings &&
            (DbType.String == dbParam.DbType || DbType.StringFixedLength == dbParam.DbType));
    }

    private static void SetVariableLengthParameterSize(IDbDataParameter dbParam, SqlType sqlType)
    {
        SetDefaultParameterSize(dbParam, sqlType);

        // Override the defaults using data from SqlType.
        if (sqlType.LengthDefined && !IsText(dbParam, sqlType))
        {
            dbParam.Size = sqlType.Length;
        }

        if (sqlType.PrecisionDefined)
        {
            dbParam.Precision = sqlType.Precision;
            dbParam.Scale = sqlType.Scale;
        }
    }

    public override IDbCommand GenerateCommand(CommandType type, SqlString sqlString, SqlType[] parameterTypes)
    {
        IDbCommand command = base.GenerateCommand(type, sqlString, parameterTypes);
        //if (IsPrepareSqlEnabled)
        {
            SetParameterSizes(command.Parameters, parameterTypes);
        }
        return command;
    }

    public override bool SupportsMultipleQueries
    {
        get { return true; }
    }

    #region IEmbeddedBatcherFactoryProvider Members

    System.Type IEmbeddedBatcherFactoryProvider.BatcherFactoryClass
    {
        get { return typeof(ProfiledSqlClientBatchingBatcherFactory); }
    }

    #endregion
}


public class ProfiledSqlClientBatchingBatcher : AbstractBatcher
{
    private int batchSize;
    private int totalExpectedRowsAffected;
    private SqlClientSqlCommandSet currentBatch;
    private StringBuilder currentBatchCommandsLog;
    private readonly int defaultTimeout;

    public ProfiledSqlClientBatchingBatcher(ConnectionManager connectionManager, IInterceptor interceptor)
        : base(connectionManager, interceptor)
    {
        batchSize = Factory.Settings.AdoBatchSize;
        defaultTimeout = PropertiesHelper.GetInt32(NHibernate.Cfg.Environment.CommandTimeout, NHibernate.Cfg.Environment.Properties, -1);

        currentBatch = CreateConfiguredBatch();
        //we always create this, because we need to deal with a scenario in which
        //the user change the logging configuration at runtime. Trying to put this
        //behind an if(log.IsDebugEnabled) will cause a null reference exception 
        //at that point.
        currentBatchCommandsLog = new StringBuilder().AppendLine("Batch commands:");
    }

    public override int BatchSize
    {
        get { return batchSize; }
        set { batchSize = value; }
    }

    protected override int CountOfStatementsInCurrentBatch
    {
        get { return currentBatch.CountOfCommands; }
    }

    public override void AddToBatch(IExpectation expectation)
    {
        totalExpectedRowsAffected += expectation.ExpectedRowCount;
        IDbCommand batchUpdate = CurrentCommand;

        string lineWithParameters = null;
        var sqlStatementLogger = Factory.Settings.SqlStatementLogger;
        if (sqlStatementLogger.IsDebugEnabled || log.IsDebugEnabled)
        {
            lineWithParameters = sqlStatementLogger.GetCommandLineWithParameters(batchUpdate);
            var formatStyle = sqlStatementLogger.DetermineActualStyle(FormatStyle.Basic);
            lineWithParameters = formatStyle.Formatter.Format(lineWithParameters);
            currentBatchCommandsLog.Append("command ")
                .Append(currentBatch.CountOfCommands)
                .Append(":")
                .AppendLine(lineWithParameters);
        }
        if (log.IsDebugEnabled)
        {
            log.Debug("Adding to batch:" + lineWithParameters);
        }
        currentBatch.Append(((ProfiledSqlDbCommand)batchUpdate).Command);

        if (currentBatch.CountOfCommands >= batchSize)
        {
            ExecuteBatchWithTiming(batchUpdate);
        }
    }

    protected void ProfiledPrepare(IDbCommand cmd)
    {
        try
        {
            IDbConnection sessionConnection = ConnectionManager.GetConnection();

            if (cmd.Connection != null)
            {
                // make sure the commands connection is the same as the Sessions connection
                // these can be different when the session is disconnected and then reconnected
                if (cmd.Connection != sessionConnection)
                {
                    cmd.Connection = sessionConnection;
                }
            }
            else
            {
                cmd.Connection = (sessionConnection as ProfiledSqlDbConnection).Connection;
            }

            ProfiledSqlDbTransaction trans = (ProfiledSqlDbTransaction)typeof(NHibernate.Transaction.AdoTransaction).InvokeMember("trans", System.Reflection.BindingFlags.GetField | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance, null, ConnectionManager.Transaction, null);
            if (trans != null)
                cmd.Transaction = trans.Transaction;
            Factory.ConnectionProvider.Driver.PrepareCommand(cmd);
        }
        catch (InvalidOperationException ioe)
        {
            throw new ADOException("While preparing " + cmd.CommandText + " an error occurred", ioe);
        }
    }

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

        int rowsAffected;
        try
        {
            rowsAffected = currentBatch.ExecuteNonQuery();
        }
        catch (DbException e)
        {
            throw ADOExceptionHelper.Convert(Factory.SQLExceptionConverter, e, "could not execute batch command.");
        }

        Expectations.VerifyOutcomeBatched(totalExpectedRowsAffected, rowsAffected);

        currentBatch.Dispose();
        totalExpectedRowsAffected = 0;
        currentBatch = CreateConfiguredBatch();
    }

    private SqlClientSqlCommandSet CreateConfiguredBatch()
    {
        var result = new SqlClientSqlCommandSet();
        if (defaultTimeout > 0)
        {
            try
            {
                result.CommandTimeout = defaultTimeout;
            }
            catch (Exception e)
            {
                if (log.IsWarnEnabled)
                {
                    log.Warn(e.ToString());
                }
            }
        }

        return result;
    }
}


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


public class ProfiledSqlDbCommand : ProfiledDbCommand
{
    public ProfiledSqlDbCommand(SqlCommand cmd, SqlConnection conn, MiniProfiler profiler)
        : base(cmd, conn, profiler)
    {
        Command = cmd;
    }

    public SqlCommand Command { get; set; }

    private DbTransaction _trans;

    protected override DbTransaction DbTransaction
    {
        get { return _trans; }
        set
        {
            this._trans = value;
            ProfiledSqlDbTransaction awesomeTran = value as ProfiledSqlDbTransaction;
            Command.Transaction = awesomeTran == null ? (SqlTransaction)value : awesomeTran.Transaction;
        }
    }
}



public class ProfiledSqlDbConnection : ProfiledDbConnection
{
    public ProfiledSqlDbConnection(SqlConnection connection, MiniProfiler profiler)
        : base(connection, profiler)
    {
        Connection = connection;
    }

    public SqlConnection Connection { get; set; }

    protected override DbTransaction BeginDbTransaction(System.Data.IsolationLevel isolationLevel)
    {
        return new ProfiledSqlDbTransaction(Connection.BeginTransaction(isolationLevel), this);
    }       

}


public class ProfiledSqlDbTransaction : ProfiledDbTransaction
{
    public ProfiledSqlDbTransaction(SqlTransaction transaction, ProfiledDbConnection connection)
        : base(transaction, connection)
    {
        Transaction = transaction;
    }

    public SqlTransaction Transaction { get; set; }
}