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 8:44 
QuestionHow to grant external access to the assembly PinmemberSam Rahimi18-Aug-08 12:25 
If you want your assembly to have access to files, networking, etc., you have to do the following things:
 
1. ON BUILD: select "sign with strong naming" in build options, choose create new key file, and leave the password empty.
 
2. IN MANAGEMENT STUDIO: after enabling for CLR like is talked about in the article, register the assembly in the following manner
 
use master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
go
create asymmetric key extKey from file='key file generated by visual studio'
go
create login extLogin from asymmetric key extKey
go
GRANT EXTERNAL ACCESS ASSEMBLY to extLogin
go
 
use [your_database_name]
 
create user extLogin for login extLogin
go
create assembly asmSupernovaLib
authorization extLogin
from 'path_of_your_dll'
with permission_set = external_access
go
 

It took me half a day to figure this out, so I figured I'd put it here in one place to save the rest of you the trouble.
 
Cheers,
 
Sam
AnswerRe: How to grant external access to the assembly Pinmemberjosephsj7-Sep-08 18:35 
GeneralBasics of what Pinmembersucram18-Aug-08 0: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
Web04 | 2.8.141220.1 | Last Updated 2 Sep 2008
Article Copyright 2008 by MrTimmins
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid