Get Sql Query From Nhibernate Criteria, Before The Criteria Executes

I have a NHibernate criteria, from which I need to get the SQL query. I tried the various methods from here. However, the query which I get does not have the parameters in it(it ha

Solution 1:

using the logger, configured before executing the code

var sqlLogger = (Logger)LogManager.GetRepository().GetLogger("NHibernate.SQL");
_sqlappender = new NhSqlAppender();
sqlLogger.AddAppender(_sqlappender);
if (!sqlLogger.IsEnabledFor(Level.Debug))
    sqlLogger.Level = Level.Debug;

classNhSqlAppender : AppenderSkeleton
{
    private List<string> queries = new List<string>(1000);

    public IList<string> Queries
    {
        get { return queries; }
    }

    protectedoverridevoidAppend(LoggingEvent loggingEvent)
    {
        queries.Add(loggingEvent.RenderedMessage);
    }
}

howto injecting a non executing connection

classFakeConnectionFactory : DriverConnectionProvider
{
    publicoverride IDbConnection GetConnection()
    {
        returnnew FakeConnection(base.GetConnection());
    }
}

classFakeConnection : DbConnection
{
    private IDbConnection _connection;

    publicFakeConnection(IDbConnection connection)
    {
        _connection = connection;
    }

    ...

    protectedoverride DbCommand CreateDbCommand()
    {
        returnnew FakeCommand(_connection.CreateCommand());
    }
}

classFakeCommand : DbCommand
{
    private IDbCommand iDbCommand;

    publicFakeCommand(IDbCommand iDbCommand)
    {
        this.iDbCommand = iDbCommand;
    }

    ...

    protectedoverride DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
    {
        return EmptyDataReader();
    }

    publicoverrideintExecuteNonQuery()
    {
        return0;
    }

    publicoverrideobjectExecuteScalar()
    {
        return0;
    }
}

Solution 2:

To answer the question myself, I think its not possible to get the complete query with all the parameters, as the parameters are added all over the place. Also, there are other problems also with a few techniques, like in the case of using criteria join walker, setMaxResults does not work, and is subject to breaking changes in nhibernate.

Solution 3:

I think this extension method will do what you are looking for

publicstatic String ToSql(this ICriteria criteria)
    {
        var criteriaImpl = criteria as CriteriaImpl;
        var sessionImpl = criteriaImpl.Session;
        var factory = sessionImpl.Factory;
        var implementors = factory.GetImplementors(criteriaImpl.EntityOrClassName);
        var loader = new CriteriaLoader(factory.GetEntityPersister(implementors[0]) as IOuterJoinLoadable, factory, criteriaImpl, implementors[0], sessionImpl.EnabledFilters);

        return loader.SqlString.ToString();
    }