get SQL query from NHibernate criteria, before the criteria executes

Question

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 has a '?' in place of that, just like mentioned over here and here). Also, at least one thing that does not work is criteria.setMaxResults(n).

I also tried NHibernate interceptors. However, the query which I get in the OnPrepareStatement(sql) also does not have the parameters. Is there any other way of getting the sql query from the criteria?

PS:- The criteria is created in the first place since its easier to work with them for complicated business needs. However, I need to do an export of data, which is awesomely slow through criteria. I am trying to get the query from the criteria, and then do a bcp export from that.

Firo · Answer 1 · 2012-03-15T11:41:45.427

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;

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

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

    protected override void Append(LoggingEvent loggingEvent)
    {
        queries.Add(loggingEvent.RenderedMessage);
    }
}

howto injecting a non executing connection

class FakeConnectionFactory : DriverConnectionProvider
{
    public override IDbConnection GetConnection()
    {
        return new FakeConnection(base.GetConnection());
    }
}

class FakeConnection : DbConnection
{
    private IDbConnection _connection;

    public FakeConnection(IDbConnection connection)
    {
        _connection = connection;
    }

    ...

    protected override DbCommand CreateDbCommand()
    {
        return new FakeCommand(_connection.CreateCommand());
    }
}

class FakeCommand : DbCommand
{
    private IDbCommand iDbCommand;

    public FakeCommand(IDbCommand iDbCommand)
    {
        this.iDbCommand = iDbCommand;
    }

    ...

    protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
    {
        return EmptyDataReader();
    }

    public override int ExecuteNonQuery()
    {
        return 0;
    }

    public override object ExecuteScalar()
    {
        return 0;
    }
}

Won't this give me the query after execution, since its coming from the logs? I need the query which the criteria is going to fire, so as to use it in some other way. — Gyanendra Singh, Mar 14 '12 at 16:23
thats not so easy because the parameter are added all over the place. you could implement a fake connection and command which does not execute — Firo, Mar 14 '12 at 17:45

score 1 · Accepted Answer · answered May 31 '12 at 09:37

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.

score 1 · Answer 3 · answered Nov 22 '18 at 12:10

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

  public static 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();
    }

get SQL query from NHibernate criteria, before the criteria executes

3 Answers3

Linked