Creating a Glimpse plugin to log SQL generated by NHibernate

Glimpse has generated a lot of buzz since it was unveiled earlier this year during the “Open Source Fest” at the Mix 2011 conference. If you’re not already using it to debug your ASP.NET Web Forms or MVC application, you should be. Glimpse allows you to inspect several server-side diagnostic and request related information with a UI just like Firebug. Here’s some resources to get you started if you need an intro:

• GetGlimpse.com
• NuGet Package of the Week – Glimpse

There are several built-in plugins for Web Forms and MVC that will serve many needs, and you can even create your own. This tutorial will demonstrate how to create a plugin that will display the SQL generated by NHibernate.

Glimpse Plugins

Plugins are implemented by a simple class that implements the IGlimpsePlugin interface. There are a couple of great “hello world” example plugins on the Glimpse creating plugins page. Besides setting the name and some optional start-up code, a plugin’s main job is just to return some set of data that will be rendered to the Glimpse UI. What that data is and how we get it is the real magic, so read on below.

The actual code for this NHibernate Glimpse plugin is fairly simple. We create a header column, add a row indicating the number of SQL statements that were executed and then add all the actual SQL to the plugin data. The SqlLogger class referenced here is just a static class that acts as a wrapper for the HttpContext.Items collection, which is where we’re logging the SQL itself. You can find a full gist including this class and all of the following code snippets, with comments, here.

[Glimpse.Core.Extensibility.GlimpsePluginAttribute()]
public class NHGlimpsePlugin : IGlimpsePlugin
{
	public object GetData(HttpContextBase context)
	{
		var data = new List<object[]> { new[] { "Key", "Value" } };

		data.Add(new object[] { "*count*", SqlLogger.Count });

		foreach (var sqlString in SqlLogger.All())
		{
			data.Add(new object[] { "*sql*", sqlString });
		}

		return data;
	}

	public void SetupInit() { }

	public string Name { get { return "NHibernate"; } }
}

Logging the SQL generated by NHibernate

The easiest way to log the SQL generated by NHibernate so it can be pushed up to Glimpse is to create a custom Log4Net appender and configure it in the web.config. It’s as simple as creating a class that extends the AppenderSkeleton base class and implement the Append method, like so:

public class NHibernateQueryAppender : AppenderSkeleton
{
	protected override void Append(LoggingEvent loggingEvent)
	{
		SqlLogger.AddSql(loggingEvent.RenderedMessage);
	}
}

Wiring up the web.config:

<log4net>
	<appender name="GlimpseAppender" type="Headspring.Data.NHibernateQueryAppender, Headspring" />
	<logger name="NHibernate.SQL">
		<level value="DEBUG"/>
		<appender-ref ref="GlimpseAppender" />
	</logger>
</log4net>

The hard part

There is no hard part! We’re done! Drop this into an application that’s using NHibernate, enable Glimpse, click over to the new NHibernate tab, and wonder how you ever lived without this.

The complete source code with comments is available here.