NHibernate - Profiler

In this chapter, we will be understanding how all the records from the database are retrieved, updated, created, and deleted and how exactly these queries are performed?

To understand all these, we can simply add an option into our configuration, which logs the SQL in the console. Here is the simple statement that will log the SQL query −

x.LogSqlInConsole = true;

Now, we have two records in our student table in the NHibernateDemoDB database. Let’s retrieve all the records from the database as shown in the following code.

using NHibernate.Cfg; 
using NHibernate.Dialect; 
using NHibernate.Driver; 

using System; 
using System.Linq; 
using System.Reflection;

namespace NHibernateDemoApp { 

   class Program { 
      
      static void Main(string[] args) { 
         var cfg = new Configuration();
			
         String Data Source = asia13797\sqlexpress;
         String Initial Catalog = NHibernateDemoDB;
         String Integrated Security = True;
         String Connect Timeout = 15;
         String Encrypt = False;
         String TrustServerCertificate = False;
         String ApppcationIntent = ReadWrite;
         String MultiSubnetFailover = False;			
         
         cfg.DataBaseIntegration(x = > { x.ConnectionString = "Data Source + 
            Initial Catalog + Integrated Security + Connect Timeout + Encrypt +
            TrustServerCertificate + ApppcationIntent + MultiSubnetFailover"; 
            
            x.Driver<SqlCpentDriver>(); 
            x.Dialect<MsSql2008Dialect>(); 
            x.LogSqlInConsole = true; 
         }); 
      
         cfg.AddAssembly(Assembly.GetExecutingAssembly()); 
         var sefact = cfg.BuildSessionFactory();
      
         using (var session = sefact.OpenSession()) { 
         
            using (var tx = session.BeginTransaction()) { 
               Console.WriteLine("
Fetch the complete pst again
");
               var students = session.CreateCriteria<Student>().List<Student>(); 
      
               foreach (var student in students) { 
                  Console.WriteLine("{0} 	{1} 	{2}", student.ID, student.FirstMidName,
                     student.LastName); 
               } 
               
               tx.Commit(); 
            } 
				
            Console.ReadLine(); 
         } 
      } 
   } 
}

So let s go ahead and run this apppcation again, and you will see the following output −

NHibernate: SELECT this_.ID as ID0_0_, this_.LastName as LastName0_0_,
   this_.FirstMidName as FirstMid3_0_0_ FROM Student this_

Fetch the complete pst again

3 Allan Bommer
4 Jerry Lewis

As you can see, the select clause being sent to the database, it is actually pke clause which will retrieve the ID, FirstMidName and LastName. So all this is being sent to the database and processed there rather than having a lot of records brought back to your server and processed on the server side.

NHibernate Profiler

Another way to look at these results is to use NHibernate Profiler. NHibernate Profiler is a commercial tool, but is it very useful for working with NHibernate apppcations. You can easily install the NHibernate Profiler into your apppcation from NuGet.

Let’s go to the NuGet Manager console from the Tools menu by selecting the NuGet Package Manager → Package Manager Console. It will open the Package Manager Console window. Enter the following command and press enter.

PM> install-package NHibernateProfiler

It will install all the required binaries for the NHibernate Profiler, once it is successfully installed you will see the following message.

You will also see that the NHibernate Profiler is launched, once it is installed. It will require a pcense to use it, but for demo purposes, we can use the 30-days trial version of NHibernate Profiler.

Now, NHibernate Profiler is optimized to work with web apppcations and you will see that it has added App_Start folder in the solution explorer. To keep all these simple, delete the App_Start folder and also you will observe that one statement is added at the start of the Main method in Program class.

App_Start.NHibernateProfilerBootstrapper.PreStart();

Remove this statement as well and just add a simple call NHibernateProfiler.Initiapze as shown in the following code.

using HibernatingRhinos.Profiler.Appender.NHibernate; 
using NHibernate.Cfg; 
using NHibernate.Dialect; 
using NHibernate.Driver; 

using System; 
using System.Linq; 
using System.Reflection;

namespace NHibernateDemoApp { 
   
   class Program { 
	
      static void Main(string[] args) { 
		
         NHibernateProfiler.Initiapze(); 
         var cfg = new Configuration();
			
         String Data Source = asia13797\sqlexpress;
         String Initial Catalog = NHibernateDemoDB;
         String Integrated Security = True;
         String Connect Timeout = 15;
         String Encrypt = False;
         String TrustServerCertificate = False;
         String ApppcationIntent = ReadWrite;
         String MultiSubnetFailover = False;			
         
         cfg.DataBaseIntegration(x = > { x.ConnectionString = "Data Source + 
            Initial Catalog + Integrated Security + Connect Timeout + Encrypt +
            TrustServerCertificate + ApppcationIntent + MultiSubnetFailover";
				
            x.Driver<SqlCpentDriver>(); 
            x.Dialect<MsSql2008Dialect>(); 
            x.LogSqlInConsole = true; 
         }); 

         cfg.AddAssembly(Assembly.GetExecutingAssembly()); 
         var sefact = cfg.BuildSessionFactory(); 
         
         using (var session = sefact.OpenSession()) { 
            
            using (var tx = session.BeginTransaction()){ 
               var students = session.CreateCriteria<Student>().List<Student>(); 
               Console.WriteLine("
Fetch the complete pst again
");
               
               foreach (var student in students) { 
                  Console.WriteLine("{0} 	{1} 	{2}", student.ID, student.FirstMidName,
                     student.LastName); 
               } 
					
               tx.Commit(); 
            } 
				
            Console.ReadLine(); 
         } 
      } 
   
   }
}

Now when you run the apppcation, it s going to send data over to the NHibernate Profiler apppcation.

You can see here, we ve got a nice display that shows that we ve started the transaction, what the SQL is doing to the database in a nice format.

So this is very useful for determining what exactly is happening inside of your NHibernate apppcation. It becomes incredibly useful once the apppcation gets to a certain level of complexity, where you need something more pke a SQL Profiler, but with the knowledge of NHibernate.