Counting the number of DB queries

mcooke24 · **Joined:** Thu Oct 26, 2006 4:02 pm **Posts:** 12

Hi all,

At DevTeach in Vancouver I heard someone say that there is a way to restrict, or at least count the number of DB calls that NHibernate generates within a session. Does anyone know where I can find this functionality?

Thanks,
Matt

karlchu · **Posted:** Sun Dec 09, 2007 2:37 pm

I am not sure about restricting the number of DB calls. However, NHibernate.Statistics is implemented in the current trunk. You may use that to count the DB calls. With NHibernate 1.2 and before, you pretty much have to tap into the logging stream to do that. In some of the NHibernate tests, they use the following class to achieve this; adapt for your own need:

Code:

using System;
using System.Text;
using log4net;
using log4net.Appender;
using log4net.Core;
using log4net.Repository.Hierarchy;

namespace NHibernate.Test
{
   /// <summary>
   /// A disposible object that taps into the "NHibernate.SQL" logger and
   /// collection the log entries being logged.  This class should be used
   /// with a C# using-statement
   /// </summary>
   public class SqlLogSpy : IDisposable
   {
      private Logger sqlLogger;
      Level prevLogLevel;
      private MemoryAppender appender;

      public MemoryAppender Appender
      {
         get { return appender; }
      }

      public SqlLogSpy()
      {
         ILog log = LogManager.GetLogger("NHibernate.SQL");
         sqlLogger = log.Logger as Logger;
         if (sqlLogger == null)
            throw new Exception("Unable to get the SQL logger");

         // Change the log level to DEBUG and temporarily save the previous log level
         prevLogLevel = sqlLogger.Level;
         sqlLogger.Level = Level.Debug;

         // Add a new MemoryAppender to the logger.
         appender = new MemoryAppender();
         sqlLogger.AddAppender(appender);
      }

      public void Dispose()
      {
         // Restore the previous log level of the SQL logger and remove the MemoryAppender
         sqlLogger.Level = prevLogLevel;
         sqlLogger.RemoveAppender(appender);
      }
   }
}

Example usage of the SqlLogSpy class:

Code:

            using (SqlLogSpy spy = new SqlLogSpy())
            {
               Employee emp1 = sess.Get<Employee>(employeeId);

               Assert.AreEqual(1, spy.Appender.GetEvents().Length,
                               "Only one SQL statement should have been issued.");
            }