Some Utility Functions using SQL Server 2005 CLR Hosting

• Download source - 12.46 KB

Introduction

CLR Hosting is one of the new cool features of SQL Server 2005, and if used properly can greatly enhance our daily database needs.

For a better understanding of CLR Hosting in Microsoft SQL Server 2005, please visit this link.

Background

In the early days of SQL Server (before Microsoft SQL Server 2005), writing User Defined Functions for various utility subroutines was nothing but a nightmare to developers, without having a very sound knowledge of TSQL. Now with the introduction of CLR Hosting feature in Microsoft SQL Server 2005, we can harness these types of subroutine with the help of reach .NET Base Class Library. We can create feature rich assemblies and subroutines which we can deploy seamlessly in any SQL Server 2005 database around our enterprise and can leverage the strength of these subroutines to solve our day to day TSQL programming challenges.

In this article, I've created a custom library with some UDF, which can be easily used by TSQL programmers and developers to do some very common TSQL string related tasks. The following is the task list which can be done by using this library:

1. Validating a string against a string pattern (say for example an email address in the former foo@bar.com)
2. Validating a string against some predefined string patterns (which I've supplied for day to day work, so you didn't need to reinvent the patterns), like EMAILFORMAT, FRENCHPHONENO, USPHONENO, USZIPCODE......
3. Split a string with a supplied delimiter and return a custom table with RowNo and Data for easy iteration.

These are some of the very basic and straight forward aspects of this library, but if wisely used can really ease our life a lot.

Using the Code

The first and easiest way to make a jump start is to download the code and to deploy it in a test database in a Microsoft SQL Server 2005 instance. Here I'm not going into the tit bits of hosting assembly in SQL Server 2005, rather I feel better to dig down the code a bit. (The first link will provide you with a lot of resources for deployment and security issues.)

Now, going to the first method and the most easy one:

1.[Microsoft.SqlServer.Server.SqlFunction]
2.public static bool IsValidStringFormat_Custom(string InputString, string Pattern)
3.{
4.    Regex expression = new Regex(Pattern);
5.    return expression.IsMatch(InputString);
6.}   

The first line of the code is an attribute which clearly dictates that the following method is nothing but a SQL function. This is really cool and really quite self explanatory. If we want any method which needs to be exposed as User Defined Function, we just need to tag it with the attribute.

In this function, we are using the .NET Framework class System.Text.RegularExpressions.Regx for making a match with the custom provided pattern.

This utility method returns bit when invoked from SQL Server 2005, so it becomes quite easy to make a decision whether a string matches with a given pattern. It can be easily used with check constraints or Stored Procedures or inside any other User Defined Function.

The next one is the function with predefined patterns (something like our old regular expression validator with some predefined set of patterns).

[Microsoft.SqlServer.Server.SqlFunction]
public static bool IsValidFormat(string InputString, string PatternType)
{
    bool flag = false;
    switch (PatternType)
    { 
        case "EMAILFORMAT":
            flag = IsValidStringFormat_Custom(InputString, EmaiFormat);
        break;
        case "FRENCHPHONENO":
            flag = IsValidStringFormat_Custom(InputString, FrenchPhoneNo);
        break;
        case "FRENCHPOSTALCODE":
            flag = IsValidStringFormat_Custom(InputString, FrenchPostalCode);
        break;
        case "GERMANPHONENO":
            flag = IsValidStringFormat_Custom(InputString, GermanPhoneNo);
        break;
        case "GERMANPOSTALCODE":
            flag = IsValidStringFormat_Custom(InputString, GermanPostalCode);
        break;
        case "INTERNETURL":
            flag = IsValidStringFormat_Custom(InputString, InternetURL);
        break;
        case "JAPANESEPHONENO":
            flag = IsValidStringFormat_Custom(InputString, JapanesePhoneNo);
        break;
        case "JAPANESEPOSTALCODE":
            flag = IsValidStringFormat_Custom(InputString, JapanesePostalCode);
        break;
        case "PRCPHONENO":
            flag = IsValidStringFormat_Custom(InputString, PRCPhoneNo);
        break;
        case "PRCPOSTALCODE":
            flag = IsValidStringFormat_Custom(InputString, PRCPostalCode);
        break;
        case "PRCSOCIALSECURITYNO":
            flag = IsValidStringFormat_Custom(InputString, PRCSocialSecurityNo);
        break;
        case "USPHONENO":
            flag = IsValidStringFormat_Custom(InputString, USPhoneNo);
        break;
        case "USSOCIALSECURITYNO":
            flag = IsValidStringFormat_Custom(InputString, USSocialSecurityNo);
        break;
        case "USZIPCODE":
            flag = IsValidStringFormat_Custom(InputString, USZipCode);
        break;
    }

return flag;

}

There is nothing fancy about this code. I've just used some constant string pattern, and depending upon the supplied PatternType (say for example 'USZIPCODE') matching the pattern using my old IsValidStringFormat_Custom function.

Now going to the last function (which is a bit untidy now). This function splits a string by a given deliminator and returns a table.

[SqlFunction(FillRowMethodName = "FillRow", 
	TableDefinition = "Data NVARCHAR(MAX), RowNo int")]
public static System.Collections.IEnumerable Split
		(SqlString InputString, string deliminator)
{
        string[] strArray = InputString.Value.Split(deliminator.ToCharArray());
        for (int foo = 0; foo < strArray.Length; foo++)
        {
            strArray[foo] = strArray[foo] + "~" + foo.ToString();
        }
        return strArray;
}
public static void FillRow(object row, out string str, out int RowNo)
{
        str = (((string)row).Split('~'))[0];
        RowNo = int.Parse((((string)row).Split('~'))[1]);
}

The most interesting feature is that this User Defined Function returns a table rather than a bit. To accomplish this, we have use two routines, the first one Split is the subroutine which returns IEnumerable (which makes it enumerable just like any other data structure in System.Collection) and the second function actually fills the table.

In the attribute declaration point, we can also see some very interesting parameters, FillMethodName and TableDefinition.

The FillMethodName points to the method which actually fills the table which needs to be returned from the User Defined Datatype.

The TableDefinition defines the actual table structure which is returned by the UDF.

Points of Interest

With the integration of CLR Hosting in SQL Server 2005, Microsoft has shown a promising integration of BCL and SQL Server DataBase which was really missing till date. If properly utilized, this can really help our day to day tough TSQL queries to run and manage in a very efficient way.

Like any other library, the most interesting part of this library is that it can be deployed and used in any SQL Server 2005 database without dependency, and there is a single point of deployment and maintenance.

History

• 3^rd August, 2009: Initial post