Basics of Using a .NET Assembly in MS SQLServer - User Functions

• Download sample - 3.97 KB

Introduction

SQL Server 2005 provides a new feature which allows .NET assemblies to be accessed from User-defined procedures, functions, triggers, new types, and aggregates. CLR functions can be called from T-SQL queries, and CLR procedures can be called from a T-SQL batch as if they were T-SQL procedures. This article illustrates CLR integration for Functions by going step by step through the validation example on MSDN, located here.

Using the Code

The Common Language Rruntime (CLR) integration feature is off by default in Microsoft SQL Server. To enable CLR integration, use the clr enabled option of the sp_configure Stored Procedure.

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Step 1: Build the Assembly

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

public class Validation
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static bool RegExMatch(string pattern, string matchString)
    {
        Regex r1 = new Regex(pattern.TrimEnd(null));
        return r1.Match(matchString.TrimEnd(null)).Success;
    }

    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString ExtractAreaCode(string matchString)
    {

        Regex r1 = new Regex("\\((?<ac>[1-9][0-9][0-9])\\)");
        Match m = r1.Match(matchString);
        if (m.Success)
            return m.Value.Substring(1, 3);
        else return SqlString.Null;
    }
    [SqlFunction(TableDefinition = "letter nchar(1)")]
    public static IEnumerable SampleTableFunction(SqlString s)
    {
         
        return new ArrayList(s.ToString().ToCharArray());
    }
    [SqlFunction(FillRowMethodName = "FillRow", 
      TableDefinition = "letter nchar(1)")]
    public static IEnumerable SampleTableFunction(SqlString s)
    {
        return new ArrayList(s.ToString().ToCharArray());
    }
    private static void FillRow(object obj, out char col1)
    {
        char row = (char)obj;
        col1 = row; 
   }
}

The SqlFunction attribute indicates the function will be used as a user-defined function, which returns either a scalar value or a table.

The TableDefinition property indicates the function will return a table. The property is the SQL representation of the definition of the returned table.

The method name specified in FillRowMethodName is implicitly called by the SQL/CLR framework each time the MoveNext() method on the returned IEnumerable object (or type that implements the IEnumerable interface) is called. The FillRow method must have a signature that looks like this:

private static void FillRow(Object obj, out <col1_type> <col1>, out <col2_type> <col2>, ... )

where the first parameter (Object obj) is an object array that contains the values of one output row. The subsequent function parameters (out <col1_type> <col1> etc.) are out parameters that contain the values that will appear in the columns for the row that is being constructed (from http://blogs.msdn.com/stuartpa/archive/2005/07/21/441468.aspx).

Step 2: Create the Asssembly in SQL Server

To do this, you use the CREATE ASSEMBLY (Transact-SQL) statement. CREATE ASSEMBLY uploads an assembly that was previously compiled as a .dll file from managed code for use inside an instance of SQL Server.

CREATE ASSEMBLY CLRSQLExample
FROM 'C:\\CLRSQLExample.dll' 
WITH PERMISSION_SET = SAFE

The local path or network location of the assembly should be replaced with a path to your DLL. SQL Server looks for any dependent assemblies in the same location. The assembly name must be unique within the database.

SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the Registry. Other options are EXTERNAL_ACCES and UNSAFE.

Step 3: Use the Assembly Methods in a User Defined Function

This example only deals with User Defined Functions.

CREATE Function fnRegExMatch(@pattern nvarchar(max), @matchstring nvarchar(max)) returns bit
AS EXTERNAL NAME
CLRSQLExample.Validation.RegExMatch 
GO
CREATE Function fnExtractAreaCode(@matchString nvarchar(max)) returns nvarchar(max)
AS EXTERNAL NAME
CLRSQLExample.Validation.ExtractAreaCode 
GO
CREATE Function fnSampleTableFunction(@str nvarchar(max)) returns table( chars nchar)
AS EXTERNAL NAME
CLRSQLExample.Validation.SampleTableFunction

To identify the correct class and method to use from the assembly, EXTERNAL NAME uses:

Assembly Name.ClassName.MethodName

The registered assembly is named CLRSQLExample. The class within the assembly is Validation, and the methods within that class that will be executed are ExtractAreaCode, SampleTableFunction, and RegExMatch.