Building an IIS7 SQL Logging Module with .NET

All the credit for the SQL logging module demo goes to CarlosAg.  He built it for an internal Microsoft conference, and I stole it for my MIX talk.  It is a great demo because it shows off something you can only do with IIS7 and the integrated pipeline.  I love it!

You'll want to prepare for this demo by doing the following:

Make sure you're running on Vista with IIS7 installed (including ASP.NET or at least .NET extensibility). 

To start with, let's build a simple module with hard coded connection string.  First, let's create the database.  Open SQL Server Management Studio, and run the following script as 'sa'.  (you'll want to enable the sa account first to make this work, and you may need to edit the physical path below to one that exists on your machine) 

USE [master]
GO
/****** Object:  Database [techready]    Script Date: 02/03/2007 12:16:42 ******/
CREATE DATABASE [MIX] ON  PRIMARY
( NAME = N'mix', FileName='x:\mix.mdf' )
GO
USE [MIX]
GO
/****** Object:  Table [dbo].[Log]    Script Date: 02/03/2007 12:18:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Log](
    [Date] [datetime],
    [Method] [varchar](50) NULL,
    [IPAddress] [varchar](50) NULL,
    [Url] [varchar](50) NULL,
    [UserName] [varchar](255) NULL,
    [UserAgent] [varchar](255) NULL,
    [ResponseCode] [varchar](50) NULL,
    [SiteName] [varchar](50) NULL,
    [ApplicationName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Once the database has been created, we can create a module to connect to it.  Create an App_Code directory in your Web site / application, and drop in your first sqlLoggingModule.cs code (you'll want to replace billsiis7demo with the name of your machine):

using System;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Data.SqlClient;
using System.Web.Hosting;

public class SqlLoggingModule : IHttpModule
{
    public SqlLoggingModule()
    {

    }

    public void Dispose()
    {
    }

    public void Init(HttpApplication context)
    {
        context.LogRequest += new EventHandler(context_LogRequest);
    }

    void context_LogRequest(object sender, EventArgs e)
    {
      HttpApplication application = (HttpApplication)sender;
      LogRequest(application.Context);
    }

    private void LogRequest(HttpContext httpContext)
    {
      string connectionString = "server=billsiis7demo\\sqlexpress;database=MIX;uid=sa;pwd=sa";

//on Beta 3 and beyond
//httpContext.Response.DisableKernelCache();

      using (SqlConnection connection = new SqlConnection(connectionString)) {

        SqlCommand cmd = connection.CreateCommand();
        cmd.CommandText =
            "insert into Log (Date, Method, IPAddress, Url, UserName, UserAgent, ResponseCode, SiteName, ApplicationName) values" +
                          "(@Date, @Method, @IPAddress, @Url, @UserName, @UserAgent, @ResponseCode, @SiteName, @ApplicationName)";

        cmd.Parameters.AddWithValue("@Date", DateTime.Now);
        cmd.Parameters.AddWithValue("@Method", httpContext.Request.HttpMethod);
        cmd.Parameters.AddWithValue("@IPAddress", httpContext.Request.UserHostAddress);
        cmd.Parameters.AddWithValue("@Url", httpContext.Request.Url.ToString());
        cmd.Parameters.AddWithValue("@UserName", httpContext.Request.ServerVariables["LOGON_USER"]);
        cmd.Parameters.AddWithValue("@UserAgent", httpContext.Request.UserAgent);
        cmd.Parameters.AddWithValue("@ResponseCode", httpContext.Response.StatusCode + "." + httpContext.Response.SubStatusCode);
        cmd.Parameters.AddWithValue("@SiteName", HostingEnvironment.SiteName);
        cmd.Parameters.AddWithValue("@ApplicationName", httpContext.Request.ApplicationPath);

        connection.Open();
        cmd.ExecuteNonQuery();
      } 
    }

}

Note: since this logging module runs in user mode, kernel cached responses will not be logged.  In Longhorn Server Beta 3 a new method DisableKernelCache() was added to allow modules to disable the kernel cache for these kind of scenarios.  I've added the code above, but commented it out.  Disabling the kernel cache may have a negative impact on performance.  You would test this scenario before ever putting it onto a real server, right? :)

Pretty simple, eh?  Before you can use it, you'll need to register it as a module with IIS. Go to the modules feature for this site / application, and click 'add managed module', or add the following configuration to your web.config file inside :

<modules>
    <add name="sqlLoggingModule" type="SqlLoggingModule" />
</modules>

Request a page from your site, and look in the table to see the request logged.  It should "just work" if all is well with the world.

Now let's add configuration extensibility.  To do this, you'll want to create a new sqlLogging_schema.xml file that looks like this and drop it in the \windows\system32\inetsrv\config\schema directory:

<configSchema>
  <sectionSchema name="system.webServer/sqlLogging">
    <attribute name="connectionString" type="string" />
  </sectionSchema>
</configSchema>

Once you've done that, you'll need to register it in \windows\system32\inetsrv\config\applicationHost.config file. Add the section here under sectionGroup name=system.webServer like so:

        <sectionGroup name="system.webServer">
                <section name="sqlLogging" overrideModeDefault="Allow" />

Now you'll want to make a few changes to your module. First, you need to remove that horrible hard coded connection string, and add it to your web.config. Simple edit web.config and add it like this inside , replacing billsiis7demo with your machine name:

<sqlLogging connectionString="server=billsiis7demo\sqlexpress;database=MIX;uid=sa;pwd=sa" />

Now it's time to fix your module. First, let's replace the hard coded connection string with a GetConnectionString(httpContext) method. It looks like this:

private string GetConnectionString(HttpContext httpContext)
{
  ConfigurationSection section = WebConfigurationManager.GetSection("system.webServer/sqlLogging");
  return (string)section["connectionString"];
}

Don't forget to add using Microsoft.Web.Administration; in your .cs file (you may need to add reference to \windows\system32\inetsrv\Microsoft.Web.Administration.dll first). Now you can not only use Microsoft.Web.Administration to read/write to your new configuration section - just like GetConnectionString(httpContext) does above, but you can use appcmd.exe (our command-line tool) and VB/JScript using COM. Here are some examples:

Using AppCmd:
==to get configuration:
C:\windows\system32\inetsrv\appcmd.exe list config "Default Web Site" /section:sqlLogging

==to set configuration:
C:\windows\system32\inetsrv\appcmd.exe set config "Default Web Site" /section:sqlLoggingconnectionString:"server=billsiis7demo\sqlexpress;database=MIX;uid=sa;pwd=sa"

==vbScript to get configuration:
Dim adminManager, section
Set adminManager = WScript.Createobject("Microsoft.ApplicationHost.AdminManager")
Set section = adminManager.GetAdminSection("system.webServer/sqlLogging", "MACHINE/WEBROOT/APPHOST/Default Web Site")
WScript.Echo(section.Properties.Item("connectionString").Value)


And there you have it! A sql logging module that was built using .NET with 66 lines of code and an IIS7 configuration section that is scriptable, programmatically accessible, and usable from the cmd-line. Hope this help!

No Comments