How to Have Nhibernate Only Generate the SQL Without Executing It

How can I have NHibernate only generate the SQL without executing it?

You can get the generated sql queries without execution with the following methods:

For the NHibernate.Linq queries:

public String GetGeneratedSql(System.Linq.IQueryable queryable, ISession session)
{
    var sessionImp = (ISessionImplementor) session;
    var nhLinqExpression = new NhLinqExpression(queryable.Expression, sessionImp.Factory);
    var translatorFactory = new ASTQueryTranslatorFactory();
    var translators = translatorFactory.CreateQueryTranslators(nhLinqExpression, null, false, sessionImp.EnabledFilters, sessionImp.Factory);

    return translators[0].SQLString;
}

For Criteria queries:

public String GetGeneratedSql(ICriteria criteria)
{
    var criteriaImpl = (CriteriaImpl) criteria;
    var sessionImpl = (SessionImpl) criteriaImpl.Session;
    var factory = (SessionFactoryImpl) sessionImpl.SessionFactory;
    var implementors = factory.GetImplementors(criteriaImpl.EntityOrClassName);
    var loader = new CriteriaLoader((IOuterJoinLoadable) factory.GetEntityPersister(implementors[0]), factory, criteriaImpl, implementors[0], sessionImpl.EnabledFilters);

    return loader.SqlString.ToString();
}

For QueryOver queries:

public String GetGeneratedSql(IQueryOver queryOver)
{
    return GetGeneratedSql(queryOver.UnderlyingCriteria);
}

For Hql queries:

public String GetGeneratedSql(IQuery query, ISession session)
{
    var sessionImp = (ISessionImplementor)session;
    var translatorFactory = new ASTQueryTranslatorFactory();
    var translators = translatorFactory.CreateQueryTranslators(query.QueryString, null, false, sessionImp.EnabledFilters, sessionImp.Factory);

    return translators[0].SQLString;
}

How do I view the SQL that is generated by nHibernate?

You can put something like this in your app.config/web.config file :

in the configSections node :

<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net"/>

in the configuration node :

<log4net>
  <appender name="NHibernateFileLog" type="log4net.Appender.FileAppender">
    <file value="logs/nhibernate.txt" />
    <appendToFile value="false" />
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n"  />
    </layout>
  </appender>
  <logger name="NHibernate.SQL" additivity="false">
    <level value="DEBUG"/>
    <appender-ref ref="NHibernateFileLog"/>
  </logger>
</log4net>

And don't forget to call

log4net.Config.XmlConfigurator.Configure();

at the startup of your application, or to put

[assembly: log4net.Config.XmlConfigurator(Watch=true)]

in the assemblyinfo.cs

In the configuration settings, set the "show_sql" property to true.

How can i get NHibernate to give me the SQL it would generate for an insert / update instead of executing it?

This is not possible unfortunately.

Nhibernate generate plain sql query instead of execution statement

No, NHibernate issues commands to SQL Server using sp_executesql and this cannot be changed. However, you should be able to troubleshoot any slow queries to resolve the performance issues. The first thing that I would like at is to check that the parameters supplied with the sp_executesql call have the same data types as the columns they reference.

get SQL query from NHibernate criteria, before the criteria executes

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.

Get executed SQL from nHibernate

You can attach an IInterceptor to your NH ISession, then use the OnPrepareStatement() method to trap (even modify) the SQL.

How to obtain NHibernate generated SQL in code at runtime?

Here is an article describing how to get the underlying sql from hql or criteria in Hibernate; I'd imagine porting this to use NHibernate wouldn't be too tricky:

http://narcanti.keyboardsamurais.de/hibernate-criteria-to-sql-translation.html

how can I access (or Generate) the NHibernate Sql at Runtime

If you need to know the query executed just in the case there is an exception, you can use check the "Data" additional elements on the exception:

yourexception.Data["actual-sql-query"]

that contain the query causing the exception.
In ordinal operations you can use the logger named "NHibernate.SQL", that is the most reliable way to get the query text in order to me, because other "straight" method depends on the way you query NH ( ICriteria,Hql, QueryOver, LinqTONh, SOmeNewWayNotYetImplemented ...) so intercepting the logger is usually better.

---

Related Topics