How can I access (or generate) NHibernate Sql in Runtime

Question

I think the name is pretty clear, but give a little context.

I read this question, but it doesn't quite match my use case

Intercept-sql-statements-containing-parameter-values-generated-by-nhibernate

The problem I am facing is that I want to exit the generated SQL only when an exception occurs (or the time period expires). This will allow us to eliminate some lengthy requests in a very complex application. I do not want to intercept and record all requests, since, in my opinion, this will be a hit on performance.

The moment I want to register it, I have an ICriteria object that uses NHibernate, so what I'm looking for is a kind of “dummy” NHibernate object with which I can run this ICriteria and return an SQL string (or even SQL with placeholders for parameters and a list of parameters).

So, there is something that does this (I think it is either incredibly simple and I missed something, or it is very difficult and no one needs it).

If this does not exist, then the best way to do something that achieves the goal, without manually creating SQL (I want as close to executable SQL as possible). Is there a way to go straight to the NHibernate generator and get a string?

Add to this to make it more understandable.

I want to do this "After the fact", and not intercept every request (due to performance issues). I also do not want to do this with Log4Net, since I need it to register against the session.

I think there should be a way to create a second NHibernate object that can use a different driver (one that does not go to the database), so I can implement a way to intercept this object and not the main object that is stored in the database

Answer 1

If you need to know a query that is executed only if there is an exception, you can use additional Data elements to exclude:

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

which contain a request that throws an exception. In ordinal operations, you can use a logger named "NHibernate.SQL", this is the most reliable way to get the query text for me, because the other "direct" method depends on how you query NH (ICriteria, Hql, QueryOver, LinqTONh, SOmeNewWayNotYetImplemented ...), so intercepting a registrar is usually better.

Answer 2

By default, NHibernate will log the request if an exception occurs by including the following log4net configuration in app / web config

 <log4net> <appender name="rollingFile" type="log4net.Appender.RollingFileAppender, log4net"> <param name="File" value="Log\NHibernate\log.txt"/> <param name="AppendToFile" value="true"/> <param name="RollingStyle" value="Date"/> <param name="DatePattern" value="yyyy.MM.dd"/> <param name="StaticLogFileName" value="true"/> <layout type="log4net.Layout.PatternLayout, log4net"> <param name="ConversionPattern" value="%d [%t] %-5p %c [%x] &lt;%X{auth}&gt; - %m%n"/> </layout> </appender> <root> <priority value="ERROR"/> <appender-ref ref="rollingFile"/> </root> </log4net> 

And if you want to get the assignment of a global variable from the interceptor or set the property in the interceptor and register it, if the exception is as follows

 public class SessionManagerSQLInterceptor : EmptyInterceptor, IInterceptor { //public string your property{ get; set; } NHibernate.SqlCommand.SqlString IInterceptor.OnPrepareStatement(NHibernate.SqlCommand.SqlString sql) { NHSessionManager.Instance.NHibernateSQL = sql.ToString(); //property = sql.ToString(); //Or //AsignSqlToSomeGlobalVariable(sql.ToString()); return sql; } } 

Answer 3

If you use log4net, you can use BufferingForwardingAppender . This will allow you to log a custom number of log events that occurred before the error (including the sql statement).

This example shows how to execute only significant events. LevelEvaluator is set with a WARN threshold. This means that events will be delivered only when a message with a WARN level or a higher level is logged. Up to 512 (BufferSize) previous messages of any level will also be provided to provide contextual information. Sent messages will be discarded.

 <appender name="BufferingForwardingAppender" type="log4net.Appender.BufferingForwardingAppender" > <bufferSize value="512" /> <lossy value="true" /> <evaluator type="log4net.Core.LevelEvaluator"> <threshold value="WARN"/> </evaluator> <appender-ref ref="ConsoleAppender" /> </appender> 

There is also a blog entry here:

http://www.beefycode.com/post/Log4Net-Tutorial-pt-8-Lossy-Logging.aspx