How do I get auto-generated SQL statements from NHibernate's operation?

Question

I got a tricky problem. I have to record specified database operations into my database log table. It is a custom table, not relevant to any system database table. It takes the form of SQL statements, but I am using NHibernate as my data access methods.

So how do I make it?

Answer 1

The only way to ensure that all commands are logged would be to setup a SQL Trace and have it log all commands to a SQL Server table.

Answer 2

Take a look at this article. It uses NHibernate + Log4Net

It did not work straight out of the box for me since I was new to Log4Net. I guess any other logging framework would do if you know how to set it up.

Answer 3

While I would have to try it out myself to be sure, you should be able to use a custom interceptor to do this. Check IInterceptor and inherit from EmptyInterceptor. Something like this should work:

public class LogSqlInterceptor : EmptyInterceptor {  
  public SqlString OnPrepareStatement(SqlString sql) {
 logger.Log(sql.ToString());
  }
}

logger is obviously whatever you are using to log the sql statements. It could just be File.WriteLine(). I am fairly certain this will do what you want but I can't test it right now.