By following this tutorial you can enable your ASP.NET MVC application to output the NHibernate’s SQL-queries into the web browser, in real-time. If you have an existing application, no coding is required, this is all about configuration.

Background

When developing any application which uses NHibernate, at some point you will probably want to see the SQL-queries executed by the ORM. When that need arises, you have multiple options. You can:

1. trace at the database level,
2. use NHProf or
3. use log4net to output the queries into a text file.

To just name a few options. Or, as this tutorial shows, it is possible to see the executed SQL-queries with a web browser, in real-time, by just using your ASP.NET MVC application. This can be done by combining NHibernate with log4net and Glimpse.

Glimpse

Glimpse can be described as the “Firebug for the server-side”. It’s an application which shows interesting details about your ASP.NET MVC applications, like the routes, executed methods and requested views. After configuration, there’s nothing else to do: Just use your website and watch the Glimpse’s UI to see how your application behaves. The Glimpse’s UI is divided between different tabs so it is easy to use and navigate.

One of the tabs inside the Glimpse is called Trace. This contains all the trace outputs which were written by your application when a page was requested. If you call Trace.WriteLine (“Hello”) in your controller, it will be shown on this tab. This tab is one of the easiest extension points of the Glimpse.

For more information on Glimpse check out the links in the end of this article.

Implementation

Getting NHibernate’s SQL-queries into the Glimpse is done by configuring the log4net to write all the NHibernate’s logs using the TraceAppender.Nothing else is required. If something is written to the Trace, Glimpse will show it.

If you have an existing ASP.NET MVC 3 application which already uses NHibernate and log4net, configuring the Glimpse to show the SQL-queries is just a matter of couple copy-pastes.

1. Installing Glimpse

You need to have Glimpse installed and configured. Installation is easily done with Nuget: Install_package Glimpse

2. Configuring log4net

The web.config’s log4net-section must be changed so that NHibernate’s logs are written with a TraceAppender:

    <appender name="TraceAppender" type="log4net.Appender.TraceAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
      </layout>
    </appender>

    <logger name="NHibernate.Loader">
      <level value="ALL" />
      <appender-ref ref="TraceAppender" />
    </logger>

3. Enabling the Glimpse

Point your browser to http://yourapp:port/Glimpse/Config and click the “Turn Glimpse On”. Now when you browse back into the homepage, you should see the Glimpse’s icon on the bottom right corner and clicking it will expand the UI.

And you’re all set.

It’s now just matter of using your application and watching the Trace-tab to see all the SQL-queries executed by the NHibernate.

If you don’t have an existing ASP.NET MVC 3 application, you can test this out by downloading a sample project from the GitHub https://github.com/mikoskinen/blog. The test project has log4net, (Fluent) NHibernate and Glimpse all configured and ready and it doesn’t require a database to run. All you have to do is to visit the http://site:port/glimpse/config to enable the Glimpse.

Tweaking the output

In my example I’m using NHibernate.Loader-logger. This results in a quite verbose output:

I like it because it provides me information on the performance of the SQL queries. If you want to include just the executed SQL-queries, you can make this happen by changing the NHibernate.Loader to NHibernate.SQL in your web.config:

    <logger name="NHibernate.SQL">
      <level value="ALL" />
      <appender-ref ref="TraceAppender" />
    </logger>

Or, if you want more information, changing the NHibernate.SQL to NHibernate will log everything:

Links

• Demo project with everything configured
• Glimpse Home Page
• NuGet Package of the Week #5 – Debugging ASP.NET MVC applications with Glimpse
• ASP.NET Tracing

The original idea was provided by Sam Saffron’s blog post “A day in the life of a slow page at Stack Overflow”.

Background

ORM mapping happens mostly between a database table/view and a class. But there are situations where the mapping should be between a stored procedure and a class. I encountered one of those situations when a basic database view was lacking in performance and I had to replace it with a stored procedure. This tutorial will show you how to use the Fluent NHibernate to map a class to a Oracle Stored Procedure.

Note:This tutorial only focuses on the query side. You will not be able to modify data with the following code.

The stored procedure

The easiest way to work with selective stored procedures in Oracle is to create a procedure which returns a SYS_REFCURSOR. In this tutorial we will use a simple procedure called SP_WEB_ACCOUNTS which takes a one parameter as an input and then returns the SYS_REFCURSOR (“record set”).

create or replace PROCEDURE SP_WEB_ACCOUNTS
(
    p_cursor OUT SYS_REFCURSOR,
    p_Customer Varchar2
)
IS

Begin
Open P_cursor for

  SELECT     AccountId     AS Id
    CustomerId   AS Customer,
    AccountDescription   AS AccountDESCRIPTION
  FROM Accounts
  Where AccountId=p_customer;

End SP_WEB_ACCOUNTS;

P_Customer is the input parameter for our procedure and it returns the p_cursor of type SYS_REFCURSOR.

The Fluent NHibernate mapping

At the moment the Fluent NHibernate does not support mapping into the SELECT procedures but that doesn’t mean  that we couldn’t use it to create the basic mapping between the class and the columns which the stored procedure returns.

In our case we have a simple class called Account.

    public class Account
    {
        public virtual int Id { get; set; }
        public virtual string Customer { get; set; }
        public virtual string AccountDescription { get; set; }
    }

We also have a AccountClassMap, defined like every other Fluent NHibernate mapping class:

    public class AccountMap : ClassMap<Account>
    {
        public AccountMap()
        {
            Id(x => x.Id);
            Map(x => x.Customer);
            Map(x => x.AccountDescription);
        }
    }

The mapping lacks one thing which is usually included:The name of the database table or view. We can’t use Fluent NHibernate to map the Account to our previously created stored procedure, so we have to resort to the native NHibernate way: We need a XML-file.

Note:Fluent NHibernate does support the mapping of INSERT, UPDATE AND DELETE stored procedures.

The XML-mapping

Creating the required XML-mapping is straightforward: We just add a new XML-file into the project and change its Build Action to “Embedded”.

Inside the XML-file we add a named SQL-query which works as the mapping between our stored procedure and our Account-class.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">

  <sql-query name="GetAccountsForCustomer">
    <return class="our.project.Account, our.assembly"  />
    { call SP_WEB_ACCOUNTS(:customer) }
  </sql-query>

</hibernate-mapping>

This XML-configuration tells fours things to the NHibernate:

1. Our stored procedure is known by the NHibernate with the name “GetAccountsForCustomer”.
2. Our stored procedure returns objects of type Account.
3. Our stored procedure is executed by “call SP_WEB_ACCOUNTS” –statement.
4. And that our stored procedure expects a parameter which NHibernate knows by name “customer”.

Now we have the required ClassMap (created with the help of Fluent NHibernate) and the required XML-configuration file. Then we just need combine these two in Fluent NHibernate configuration.

Configuring the Fluent NHibernate

When we previously had only the ClassMaps to use, our Fluent NHibernate configuration code looked like this:

            return Fluently.Configure(normalConfig)
                .Mappings(m =>m.FluentMappings.AddFromAssemblyOf<UserMap>())
                .BuildSessionFactory();

Making the Fluent NHibernate to take advantage of the XML-configuration file requires only a small modification:

            return Fluently.Configure(normalConfig)
                .Mappings(m =>m.FluentMappings.AddFromAssemblyOf<UserMap>())
                .Mappings(m => m.HbmMappings.AddFromAssemblyOf<UserMap>())
                .BuildSessionFactory();

This assumes that the newly added XML-files resides in the same assembly as the UserMap-class. Fluent NHibernate is smart enough to scan the assembly for the NHibernate mapping files.

Now everything is ready and we can start using our new mapping.

Querying the database with NHibernate

Using the mapping is very simple. We ask NHibernate to execute the query called GetAccountsForCustomer by calling session.GetNamedQuery-method and provide the required parameter for it:

var accounts = session.GetNamedQuery("GetAccountsForCustomer")
                .SetParameter("customer", User.Identity.Name)
                .List<Account>();

This reaches out to the database using the stored procedure and provides the list of Account-objects.

Further reading

Had a long evening trying to install the ASP.NET MVC 3 Tools Update. Web PI installer stated that everything was installed successfully, even though the Visual Studio and the Add/Remove Programs stated otherwise. Downloading and running the MSI-package gave more hints about what was wrong because the installer would start the rollback as soon as it tried to install ASP.NET Web Pages. The installer’s log mentioned that the installation failed because of an error 0x80070643.

Thankfully I wasn’t the only one with this particular problem and Googling brought up a good blog about the problem: http://blog.williamhilsum.com/2011/03/error-0x80070643-installing-aspnet-mvc.html

Following the advice on the last update of the post to modify the Windows’s Registry helped to solve all the problems.

Until recently I’ve used GitHub only through its SSH-connections. They have always worked fine and when my one and only previous attempt to use HTTP ended up with some seemingly random error, I haven’t seen any reason to make the change.

But lately I’ve had to work with many different computers and playing around with the SSH-keys gets little cumbersome. So this time I decided to give the HTTP-connections a second chance but, similar to the previous attempt, I was presented with the following cryptic error:

error: error setting certificate verify locations:
  CAfile: /bin/curl-ca-bundle.crt
  CApath: none
while accessing https://mikoskinen@github.com/mikoskinen/myproject.git/info/refs

fatal: HTTP request failed

Fortunately for me, I wasn’t the only one with the problem. Googling the error message brought up many different advices but the one which worked for me was the following command:

git config --system http.sslcainfo bin/curl-ca-bundle.crt

The syntax looks little funny around the “bin” but it works. As an added bonus to the not having to deal with the SSH-keys anymore, the HTTP connection seems to work with GitHub little faster than the SSH so it is my preferred option nowadays.

Service Pack 1 for the Visual Studio 2010 was released last night. It's now available for the MSDN Subscripters and in few days it should be available for everyone. Grap it while it's hot.

Here's a good summary of the new features and bugfixes.

Personally I'm most interested in the following new features:

• Support for IIS Express
• XAML Editor changes
• Support for intrinsic functions