Invested Development

Convert a collection of SQL parameters into a name:value string suitable for writing to a log.

/// <summary>
/// Serializes a collection of
/// <see cref="System.Data.SqlClient.SqlParameter"/>s.
/// </summary>
/// <param name="parameters">
/// The collection of parameters to serialize.
/// </param>
/// <returns>
/// A <see cref="System.String"/> that contains the parameter values.
/// </returns>
public string SerializeSqlParameterCollection(
    SqlParameterCollection parameters)
{
    StringBuilder sb = new StringBuilder();
    foreach (SqlParameter parameter in parameters)
        sb.Append(
            String.Format("{0}:{1} ",
                parameter.ParameterName, parameter.Value));
    return sb.ToString();
}

.NET Framework, Programming, Snippets .NET, Coding, csharp, sql

The .NET framework provides several mechanisms for extending the behaviors of web pages and other endpoints. One such technique is the http module. Modules are often either overlooked as a solution or are considered too heavyweight or scary for use. This is unfortunate as they are a powerful item in the ASP.NET development toolbox.

An http module is a single unit of code that is executed as part of the web request pipeline. When a web request is received, the list of registered modules are afforded the opportunity to inject themselves at several stages in the execution pipeline. Each module has the opportunity to inspect or modify parts of the request before or after it is processed. This makes them a powerful mechanism for attaching custom behaviors. Modules also have a flexible activation model as they can be attached or detached via configuration allowing them to be enabled or disabled at will without affecting a production site.

The full source code for this example can be found here.

How to Create an HttpModule
The ASP.NET Module type is in the Web category under the Add New Item dialog.

Remove the default sample code that is included in the template. The LogContext event handler is implemented in the template for demonstration purposes but is not used in this example. Your class should now look similar to the following:

using System;
using System.Web;

namespace ModuleWebApp
{

    ///

    /// Custom http module.
    /// 

    public class MyNewModule : IHttpModule
    {

        #region IHttpModule Members

        public void Dispose() {}

        ///

        /// Initializes the module.
        /// 

        /// <param name="context">The application context.</param>
        public void Init(HttpApplication context)
        {

        }

        #endregion

    }
}

Initializing the Module

Modules perform work by registering handlers for events in the request execution pipeline such as BeginRequest and EndRequest. It is within the event handlers that they perform their task. In the example below, the Init method is used to register handlers for the BeginRequest and EndRequest events.

///

/// Raised when the module is initialized.
/// 

/// <param name="context">The application context.</param>
public void Init(HttpApplication context)
{
    context.BeginRequest += new EventHandler(context_BeginRequest);
    context.EndRequest += new EventHandler(context_EndRequest);
}

Implementing the Event Handlers and Finishing the Module

Next provide implementations for the event handlers. This example module is going to calculate and display the time it takes to execute a page within the application. The code example below is for the completed module class. It contains the following changes:

• Declaration of a dictionary of stopwatches that stores the execution timers.
• Implementation of the BeginRequest and EndRequest handlers to start and stop the timers.
• Instantiation of the dictionary of timers in the Init method.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Web;

namespace ModuleWebApp
{

    /// <summary>
    /// A custom http module used to time request execution.
    /// </summary>
    public class RequestTimer : IHttpModule
    {

        #region IHttpModule Members

        public void Dispose() { }

        /// <summary>
        /// Raised when the module is initialized.
        /// </summary>
        /// <param name="context">The application context.</param>
        public void Init(HttpApplication context)
        {
            _timers = new Dictionary<Guid, Stopwatch>();

            context.BeginRequest += new EventHandler(context_BeginRequest);
            context.EndRequest += new EventHandler(context_EndRequest);
        }

        #endregion

        #region Event Handlers

        /// <summary>
        /// Raised when the request begins.
        /// </summary>
        /// <param name="sender">The source of the event; the <see cref="System.Web.HttpApplication" />.</param>
        /// <param name="e">An <see cref="System.EventsArgs"/> that contains event data.</param>
        protected void context_BeginRequest(object sender, EventArgs e)
        {
            Guid timerId = Guid.NewGuid();
            Stopwatch timer = new Stopwatch();
            _timers.Add(timerId, timer);

            HttpContext.Current.Items.Add(EXECUTION_TIMER_KEY, timerId.ToString());

            timer.Start();
        }

        /// <summary>
        /// Raised when the request ends.
        /// </summary>
        /// <param name="sender">The source of the event; the <see cref="System.Web.HttpApplication" />.</param>
        /// <param name="e">An <see cref="System.EventsArgs"/> that contains event data.</param>
        protected void context_EndRequest(object sender, EventArgs e)
        {
            string timerId = HttpContext.Current.Items[EXECUTION_TIMER_KEY] as string;

            if (String.IsNullOrEmpty(timerId))
                return;

            Stopwatch timer = _timers[new Guid(timerId)];
            if (timer == null)
                return;

            timer.Stop();

            double elapsedSeconds = (timer.ElapsedMilliseconds) / (double)1000;

            HttpContext.Current.Response.Write(
                String.Format(
                    "Request executed in {0} seconds ({1} ticks).",
                    elapsedSeconds.ToString(),
                    timer.ElapsedTicks.ToString()));
        }

        #endregion

        #region Fields

        /// <summary>
        /// The collection of execution timers.
        /// </summary>
        private Dictionary<Guid, Stopwatch> _timers;

        #endregion

        #region Constants

        private const string EXECUTION_TIMER_KEY = "__ExecutionTimer_Id";

        #endregion

    }

}

Some of the concepts used here are beyond the scope of this discussion. Research the Stopwatch class for more information on how to use timers. See the HttpContext class to learn more about request and response objects.

Registering the Module

The final step is to register the module so that it is instantiated and executed on every request. This is done via the web.config file. The <system.web> section contains an <httpModules> section. The registration configuration line for the module should be added to the <httpModules> section as shown below:

That’s it! Running the web application should display the default.aspx page, which now shows the execution time in elapsed seconds (and ticks). This information is displayed at the end of every web request so it works on every page in the system. If you inspect the context_EndRequest handler you can see the following lines:

HttpContext.Current.Response.Write(
	String.Format(
		"Request executed in {0} seconds ({1} ticks).",
		elapsedSeconds.ToString(),
		timer.ElapsedTicks.ToString()));

The HttpContext class exposes the current Request and Response. These are the http request and response that are received from and sent to the client respectively. The execution time was displayed on the page because this handler wrote to the response object after the request had finished executing. The timer was started when the request started and stopped when the request ended. The time taken for the page to performing its processing and the request to finish is then displayed in the response. It is in this way that modules interact with all requests in the system and provide global level behaviors that are attached via configuration.

.NET Framework, ASP.NET, Programming .NET, ASP.NET, HttpModule

Of recent interest to me has been the ability to deploy user-defined managed functions, stored procedures, and other objects to SQL from within Visual Studio 2005. With the .NET CLR in SQL Server 2005, these managed functions can be written and deployed from within Visual Studio, even as part of an automated build process. In wanting to investigate this further, I’ve decided to write a few articles on how this technique is used and on how it may be useful to you. When I first heard about the ability to (essentially) deploy .NET assemblies into SQL Server and then have SQL Server make managed calls to them for use in queries I was skeptical, thinking this was yet another lost solution embarking upon a life quest for a problem to solve. However, several examples have since been drawn to my attention regarding the usefulness of this solution in exposing features of the richer .NET languages for use in advanced queries.

The first of these areas is that of regular expressions. Earlier this year, I came across an article describing how calls to the .NET 2.0 RegEx classes could be wrapped in a managed function for use in the where clause of a SQL query. My ears (eyes) perked up at this, for the idea of using regular expressions to match data in queries could add a massive amount of power to my SQL toolbox. Even better, because the call would be to the .NET classes, we already know that I’d be able to pre-compile the expressions if needed as that is a feature of that implementation. The rest of this article walks through a simple example for creating a C# Database Project in Visual Studio 2005, deploying a managed function developed in that project, and then writing a query to use that function for RegEx matching within the WHERE clause. For those interested in more information, the original article can be found here: http://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx.

Creating a C# Database Project in Visual Studio

First, open Visual Studio, then select File->New->Project. In the New Project dialog, expand the Visual C# node and select Database, then choose SQL Server Project from the list of templates. After the project is created, a dialog named Add Database Reference will ask for the specification of a database connection to work with for this project. This connection is used to deploy the project to SQL Server. Select a connection from the dialog or create a new one by clicking Add New Reference. I’ll assume for the brevity of this article that you either have a SQL database or can create a new one and that you can establish a database connection to it. Next, right-click the new SQL Server Project and select Add->New Item. Choose User-Defined Function from the Add New Item dialog and name it whatever you like; I used the class name RegexUtilities.cs in the test project I wrote for this article. This will add a snippet of code in the class that is a template for the function. Replace the entire contents of the code window with the following:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions {

[Microsoft.SqlServer.Server.SqlFunction]
public static bool Match(SqlChars searchString, SqlString regexPattern) {
Regex regex = new Regex(regexPattern.Value, RegexOptions.Compiled | RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline);
return regex.IsMatch(new string(searchString.Value));
}

}

The code here is a very simple wrapper around the Regex.IsMatch method and is adapted from the source code supplied by David Banister is his original article. I’ve cut a couple of things down for simplicity. Two parameters are passed to the method named Match: the string to search and the regex pattern to use in the match. The first line of the method creates a new compiled Regex object from the supplied pattern. The second line of codes returns a Boolean that indicates whether or not the supplied searchString was matched. First build the project with Ctrl+Shift+B and then right-click over the project in the Solution Explorer pane and select Deploy. This deploys the managed function to the SQL server you specified in the database connection for the project.

Now it’s time to test the function out in a query. First, let’s take the Social Security Number example and check that everything made it across OK. Open SQL Server Management Studio and connect to your database, then open a New Query window. Now type the following into the new query window:

select dbo.Match( N’123-45-6789′, N‘^\d{3}-\d{2}-\d{4}$’ )

You should receive the following error:

Msg 6263, Level 16, State 1, Line 1

Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.

This is because we have not yet enabled a state within SQL server that is disabled by default for security reasons. CLR code obviously wields great power and, as such, the ability to execute that code within the SQL process gives any would be malicious code a very nice process under which to run, and in an authenticated context. To run our example then, we must first instruct SQL server to allow CLR code to execute. This is achieved by running the following SQL within a query window:

sp_configure ‘clr enabled’, 1
go

reconfigure
go

A confirmation message should indicate that the ‘clr enabled’ option was changed from 0 to 1. It indicates that RECONFIGURE should be run to install the change, hence the second SQL statement in the snippet above. Now we are ready to execute our Social Security Number example again. This time you should receive a single result for (No column name) with a value of 1. This indicates a match and was reported as a literal with no column name because of the structure of the statement we ran. Change part of the statement’s left parameter to be an invalid social security number and re-run the example to see that a 0 is received (indicating false) this time instead.

Now the real purpose of this article was the use of Regex in a WHERE clause and for this pick a table that contains a string column. Now construct a SQL statement similar to the following:

select dbo.Match(C.[First Name], N‘^\w{8}$’), C.[First Name]
from C Customer

The statement above displays two columns of data, the first containing the result of the regular expression call, the second containing the customer first name. The regular expression in this example has been simplified to match words of eight characters in length (an oversimplification of the actual syntax, I know, but sufficient for the purposes of this article). Any customers with a first name of eight characters in length will be accompanied by a 1 in the left-hand column, the others with a 0.

That’s it for this article. A quick demonstration of how managed functions in SQL Server 2005 can open the power of the .NET class library for use within SQL.

.NET Framework, SQL Server CLR, SQL Server 2005