Click here to Skip to main content
13,555,878 members
Click here to Skip to main content
Add your own
alternative version


22 bookmarked
Posted 7 Aug 2008
Licenced CPOL

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

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


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

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

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.

FROM 'C:\\CLRSQLExample.dll' 

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
CREATE Function fnExtractAreaCode(@matchString nvarchar(max)) returns nvarchar(max)
CREATE Function fnSampleTableFunction(@str nvarchar(max)) returns table( chars nchar)

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.


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


About the Author

Canada Canada
No Biography provided

You may also be interested in...

Comments and Discussions

Generaladded code Pin
MrTimmins2-Sep-08 7:44
memberMrTimmins2-Sep-08 7:44 
QuestionHow to grant external access to the assembly Pin
Sam Rahimi18-Aug-08 11:25
memberSam Rahimi18-Aug-08 11:25 
AnswerRe: How to grant external access to the assembly Pin
josephsj7-Sep-08 17:35
memberjosephsj7-Sep-08 17:35 
GeneralBasics of what Pin
sucram17-Aug-08 23:18
membersucram17-Aug-08 23:18 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04-2016 | 2.8.180515.1 | Last Updated 2 Sep 2008
Article Copyright 2008 by MrTimmins
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid