Click here to Skip to main content
Click here to Skip to main content

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

, 2 Sep 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
Introduction to using CLR .NET assemblies in Microsoft SQL Server.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

MrTimmins

Canada Canada
No Biography provided

Comments and Discussions

 
Generaladded code PinmemberMrTimmins2-Sep-08 7:44 
QuestionHow to grant external access to the assembly PinmemberSam Rahimi18-Aug-08 11:25 
AnswerRe: How to grant external access to the assembly Pinmemberjosephsj7-Sep-08 17:35 
GeneralBasics of what Pinmembersucram17-Aug-08 23:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141030.1 | Last Updated 2 Sep 2008
Article Copyright 2008 by MrTimmins
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid