How To Obtain Nhibernate Generated Sql In Code At Runtime?

I know you can view the NHibernate generated SQL by hooking it up to log4net or piping it out to the console ('show_sql' option), but is there any way to obtain the generated SQL i

Solution 1:

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

Solution 2:

With NHibernate 3.2, this seems to work to get the SQL from an HQL query:

privatestringGetSQL(string hql)
{
    using (var iSession = ...)
    {
        var session = (NHibernate.Engine.ISessionImplementor)iSession;
        var sf = (NHibernate.Engine.ISessionFactoryImplementor)iSession.SessionFactory;

        var sql = new NHibernate.Engine.Query.HQLStringQueryPlan(hql, true, session.EnabledFilters, sf);

        returnstring.Join(";", sql.SqlStrings);
    }
}

Solution 3:

In the past, I was able to view the code generated and sent to SQL by hibernate via the SQL Profiler tool. Depending on your goals, it may be able to provide what you need.