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 ApplicationIntent = ReadWrite;
         String MultiSubnetFailover = False;			
         
         cfg.DataBaseIntegration(x = > { x.ConnectionString = "Data Source + 
            Initial Catalog + Integrated Security + Connect Timeout + Encrypt +
            TrustServerCertificate + ApplicationIntent + MultiSubnetFailover"; 
            
            x.Driver<SqlClientDriver>(); 
            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("nFetch the complete list againn");
               var students = session.CreateCriteria<Student>().List<Student>(); 
      
               foreach (var student in students) { 
                  Console.WriteLine("{0} t{1} t{2}", student.ID, student.FirstMidName,
                     student.LastName); 
               } 
               
               tx.Commit(); 
            } 
				
            Console.ReadLine(); 
         } 
      } 
   } 
}

So let”s go ahead and run this application 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 list again

3 Allan Bommer
4 Jerry Lewis

As you can see, the select clause being sent to the database, it is actually like 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 applications. You can easily install the NHibernate Profiler into your application 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 license 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 applications 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.Initialize 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.Initialize(); 
         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 ApplicationIntent = ReadWrite;
         String MultiSubnetFailover = False;			
         
         cfg.DataBaseIntegration(x = > { x.ConnectionString = "Data Source + 
            Initial Catalog + Integrated Security + Connect Timeout + Encrypt +
            TrustServerCertificate + ApplicationIntent + MultiSubnetFailover";
				
            x.Driver<SqlClientDriver>(); 
            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("nFetch the complete list againn");
               
               foreach (var student in students) { 
                  Console.WriteLine("{0} t{1} t{2}", student.ID, student.FirstMidName,
                     student.LastName); 
               } 
					
               tx.Commit(); 
            } 
				
            Console.ReadLine(); 
         } 
      } 
   
   }
}

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

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 application. It becomes incredibly useful once the application gets to a certain level of complexity, where you need something more like a SQL Profiler, but with the knowledge of NHibernate.