Introducing CLR Stored Procedures in SQL Server 2012

SQL Server,

Contents

 

Introduction

This guide demonstrates how to use SQL Server Common Language Runtime (CLR) integration with SQL Server 2012. In this guide, the following two assemblies will be created using C#:

  • MathAsm
  • StoredProceduresAsm

 

About Common Language Runtime (CLR)

The Common Language Runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Developers use compiled OO languages like C# or Visual Basic .NET to write code and to have the code executed as if it were a T-SQL procedure, function, or trigger. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

Managed code uses Code Access Security (CAS), code links, and application domains to prevent assemblies from performing certain operations. SQL Server 2005, SQL Server 2008, and SQL Server 2012 uses CAS to help secure the managed code and prevent compromise of the operating system or database server.

Enable CLR in SQL Server

In SQL Server 2005, SQL Server 2008, and SQL Server 2012, the Common Language Runtime (CLR) is off by default. In an effort to improve security, Microsoft has turned many features “off by default”. This is a big change from the old policy of turning every feature on so that developers weren’t discouraged from using the feature due to difficulties in getting the feature to work.

EXEC sp_configure 'show advanced options', '1'
Go

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Reconfigure
Go

Command(s) completed successfully.

EXEC sp_configure 'clr enabled', '1'
Go

Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

Reconfigure
Go

Command(s) completed successfully.

EXEC sp_configure 'show advanced options', '0'
Go

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

Reconfigure
Go

Command(s) completed successfully.

Compile and Build Assembly

In order to develop SQL CLR assemblies for SQL Server, you must have and utilize the .NET Framework 3.5 installed on your development computer. If you do not have the .NET Framework version 3.5 installed on your development computer, you must install it if you want to development SQL CLR assemblies for SQL Server. SQL Server 2005, SQL Server 2008, and SQL Server 2012 support only those assemblies that target the 2.0, 3.0, 3.5, or 4.0 version of the .NET Framework.

As mentioned in the Introduction, this guide will compile and build two assemblies using C# that will be integrated with SQL Server 2012. The source code for both assemblies is presented below.

   Math.cs

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server; 

public class Math {

    [SqlFunction]
    public static SqlDouble Factorial(SqlDouble x) {
        SqlDouble y = 1.0;
        while(x > 1.0) {
            y *= x;
            x -= 1;
        }
        return y;
    }
}

   StoredProcedures.cs

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class StoredProcedures {

    ///  /// Execute a command and send the resulting reader to the client /// 
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetVersion() {
        using (SqlConnection connection = new SqlConnection("context connection=true")) {
            connection.Open();
            SqlCommand command = new SqlCommand("select @@version", connection);
            SqlDataReader r = command.ExecuteReader();
            SqlContext.Pipe.Send(r);
        }
    }

    public const double SALES_TAX = .086;

    [SqlFunction()]
    public static SqlDouble addTax(SqlDouble originalAmount) {
        SqlDouble taxAmount = originalAmount * SALES_TAX;
        return originalAmount + taxAmount;
    }
}

Copy the source code files above to your source directory. For example:

C:\> copy C:\Users\SQLServerAdmin\Downloads\*.cs C:\Programming\c#\SQLCLRIntegrationExample\src\
C:\Users\SQLServerAdmin\Downloads\Math.cs C:\Users\SQLServerAdmin\Downloads\StoredProcedures.cs 2 file(s) copied.

There are two methods to compile the C# source code; Microsoft Visual Studio and the C# command-line compiler. This guide uses the C# command-line compiler.

To use the C# command-line compiler, navigate to the appropriate .NET Framework directory. For example:

C:\> cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319

From this directory, compile the two SQL CLR source code files into an assembly (dll).

SET SRC_PATH=C:\Programming\c#\SQLCLRIntegrationExample\src
SET DLL_PATH=C:\Programming\c#\SQLCLRIntegrationExample\assemblies

csc /target:library /out:%DLL_PATH%\Math.dll %SRC_PATH%\Math.cs

Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.

csc /target:library /out:%DLL_PATH%\StoredProcedures.dll %SRC_PATH%\StoredProcedures.cs

Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.

DIR %DLL_PATH%
 Volume in drive C has no label. Volume Serial Number is DC7C-B8D3 Directory of C:\Programming\c#\SQLCLRIntegrationExample\assemblies 03/26/2013 12:13 AM <DIR> . 03/26/2013 12:13 AM <DIR> .. 03/26/2013 12:13 AM 3,584 Math.dll 03/26/2013 12:13 AM 4,096 StoredProcedures.dll 2 File(s) 7,680 bytes 2 Dir(s) 51,610,902,528 bytes free

Grant Permissions for External Access

In order to create an assembly with EXTERNAL_ACCESS (or UNSAFE) permission set, you need extra permissions in the database. This can be achieved by setting the TRUSTWORTHY bit in the database (ALTER DATABASE[DevDB]SET TRUSTWORTHY ON); however, this is not a preferred option as it can cause other undesired side effects. Note that if your assembly does not require resources outside of the database (for example, writing to a file), you do not need to assign an EXTERNAL_ACCESS permission set to the assembly in order to execute the SQL CLR in the database. You only need to assign an EXTERNAL_ACCESS permission set to the assembly when accessing resources outside of the database server.

If your assembly will need access to resources outside of the database, use the following method as the preferred alternative to setting the TRUSTWORTHY bit in the database as explained above.

  1. Create a .NET strong name key file by using the sn.exe tool.
    C:\> SET SN_TOOL="C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\sn.exe"

C:\> SET KEY_PATH="C:\Programming\c#\SQLCLRIntegrationExample\keys"

C:\> %SN_TOOL% -k %KEY_PATH%\extSQLKey.snk

Copyright (c) Microsoft Corporation. All rights reserved.

C:\> dir %KEY_PATH%
 Volume in drive C has no label. Volume Serial Number is DC7C-B8D3 Directory of C:\Programming\c#\SQLCLRIntegrationExample\keys 03/26/2013 12:29 AM <DIR> . 03/26/2013 12:29 AM <DIR> .. 03/26/2013 12:29 AM 596 extSQLKey.snk 1 File(s) 596 bytes 2 Dir(s) 51,610,288,128 bytes free
  2. In the master database, create a master key (if one does not already exist).
    USE [Master]
Go

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'some_secure_password';

Command(s) completed successfully.
  3. Still in master, create an asymmetric key.
    CREATE ASYMMETRIC KEY extSQLKey
FROM FILE = 'C:\Programming\c#\SQLCLRIntegrationExample\keys\extSQLKey.snk';

Command(s) completed successfully.
  4. Also in the master database, create a login from the asymmetric key.
    CREATE LOGIN extSQLLogin
FROM ASYMMETRIC KEY extSQLKey;

Command(s) completed successfully.
  5. Give the login just created EXTERNAL ACCESS ASSEMBLY permission.
    GRANT EXTERNAL ACCESS ASSEMBLY to extSQLLogin;

Command(s) completed successfully.
  6. Build your assembly like before but this time sign it with your strong name key.
    cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319

SET SRC_PATH=C:\Programming\c#\SQLCLRIntegrationExample\src
SET DLL_PATH=C:\Programming\c#\SQLCLRIntegrationExample\assemblies
SET KEY_PATH=C:\Programming\c#\SQLCLRIntegrationExample\keys

csc /target:library /keyfile:%KEY_PATH%\extSQLKey.snk /out:%DLL_PATH%\StoredProcedures.dll %SRC_PATH%\StoredProcedures.cs

Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.

csc /target:library /keyfile:%KEY_PATH%\extSQLKey.snk /out:%DLL_PATH%\Math.dll %SRC_PATH%\Math.cs

Microsoft (R) Visual C# Compiler version 4.0.30319.17929 for Microsoft (R) .NET Framework 4.5 Copyright (C) Microsoft Corporation. All rights reserved.

DIR %DLL_PATH%
 Volume in drive C has no label. Volume Serial Number is DC7C-B8D3 Directory of C:\Programming\c#\SQLCLRIntegrationExample\assemblies 03/26/2013 12:13 AM <DIR> . 03/26/2013 12:13 AM <DIR> .. 03/26/2013 01:00 AM 3,584 Math.dll 03/26/2013 01:00 AM 4,096 StoredProcedures.dll 2 File(s) 7,680 bytes 2 Dir(s) 51,609,612,288 bytes free
  7. The assembly is now ready to be deployed. The assembly is signed with a strong name key, and the strong name key has an asymmetric key created from it and there is a login created from that symmetric key with the necessary permission set.

 

Create Assemblies in SQL Server

With the assemblies built, the next step is to create the assembly in a SQL Server database along with the external procedure and/or function wrappers to run the assembly.

MathAsm

Use [DevDB]
Go

CREATE ASSEMBLY MathAsm FROM 'C:\Programming\c#\SQLCLRIntegrationExample\assemblies\Math.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

CREATE FUNCTION Factorial(@x FLOAT)
    RETURNS FLOAT
    EXTERNAL NAME MathAsm.Math.Factorial;
Go

Command(s) completed successfully.

StoredProceduresAsm

Use [DevDB]
Go

CREATE ASSEMBLY StoredProceduresAsm FROM 'C:\Programming\c#\SQLCLRIntegrationExample\assemblies\StoredProcedures.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
Go

CREATE PROCEDURE GetVersion AS EXTERNAL NAME StoredProceduresAsm.StoredProcedures.GetVersion;
Go

CREATE FUNCTION addTax(@originalAmount FLOAT)
    RETURNS FLOAT
    EXTERNAL NAME StoredProceduresAsm.StoredProcedures.addTax;
Go

Command(s) completed successfully.

Note that the external reference for creating the procedure and wrapper functions is in the form:

[EXTERNAL NAME] assembly_name.class_name.method_name

Run Example Assemblies in SQL Server

With the assemblies and procedures / functions wrappers created in SQL Server, run the following T-SQL to execute the assemblies.

SELECT [DevDB].[dbo].[Factorial](5) AS "Factorial"
Go

Factorial ----------------- 120

EXEC GetVersion
Go

(No column name) ----------------------------- Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Business Intelligence Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

SELECT [DevDB].[dbo].[addTax](10.00) AS "Original + Tax"
Go

Original + Tax ----------------------------- 10.86

Drop Assemblies from SQL Server

The following T-SQL can be used to drop the previously created objects in the example Class.

MathAsm

Use [DevDB]
Go

DROP FUNCTION [Factorial];
Go

DROP ASSEMBLY [MathAsm];
Go

Command(s) completed successfully.

StoredProceduresAsm

Use [DevDB]
Go

DROP PROCEDURE [GetVersion];
Go

DROP FUNCTION [addTax];
Go

DROP ASSEMBLY [StoredProceduresAsm];
Go

Command(s) completed successfully.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff’s other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor’s degree in Computer Science and Mathematics.

from:http://www.idevelopment.info/data/SQLServer/DBA_tips/Programming/PROG_20.shtml

发表评论

电子邮件地址不会被公开。 必填项已用*标注