CLR Integration with MS SQL Server

Introduction

My friend told me that the CLR Integration is quite complex. I think that nothing is complex if we understand it properly. So in this article I am trying to discuss about MS SQL server CLR integration. I try to demonstrate it as simple as possible by an example that everyone can understand it properly.

Point in focus

1.    What is CLR

2.    Advantage of CLR

3.    How we can make the CLR Integration

What is CLR

The full form of CLR is Common Language Runtime. The CLR Integration of MS SQL Server starts from MS SQL Server version 2005 and later version.

The database objects such as Stored Procedure (SP), Function, Triggers can be coded in CLR. The main purpose of the CLR is to complete the tasks that are not possible or complex to make in T-SQL and it is faster than the T-SQL in many cases.

Now we look what MSDN tell about 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. 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 uses CAS to help secure the managed code and prevent compromise of the operating system or database server."

Advantage of CLR

    1.    The CLR Integration layer provides some facility that is not directly available from T-SQL.  

          It offers to access .NET framework libraries.

2. Provide the better result in complex logic for intense string operation or string manipulations, cryptography, accessing system resources and file management, etc.
3. CLR are managed codes so ensures type safety and memory management.
   
   
4. It is quite convenient for programmer as CLR Stored Procedures can be written in C#, VB or any other language that the .NET Framework supports.
   
   

How we can make the CLR Integration

In this example we are calling .NET CLR code from MS SQL Server. The demonstration example was implemented in MS Visual Studio 2010 and MS SQL Server 2008 and use .NET frame work 3.5.

I am going to demonstrate this simple example in to 2 steps.

1.    Using .NET Frame work for CLR Function creation

2.    Calling the CLR Function from MS SQL Server 2008

Using .NET Frame work for CLR Function creation

Step-1

Open the MS Visual Studio 2010 and then open the File menu. From File menu open the new projects.

Step-2

From New Projects dialog box, in the Installed Templates select

DatabaseàSQL ServeràVisual C# SQL CLR Database projects.

Step-3

From New Database References provide the SQL Server name in Server name and then choose the Authentication mode. Then choose the Database from Select or Enter database name.

  

Step-4

In Add new Item choose the User-Define Function

Step-5

In Function1.cs or class file I am just going to change the function name to fnDispalyName and change the code within the function. As it is a sample example I am not adding any code here just provide "Hello World".

return new SqlString("Hello World");

Sample code:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString fnDispalyName()

    {

        // Put your code here

        return new SqlString("Hello World");

    }

};

Step-6

Now Build the solutions. If any error came then rectify it and re-build the solution and then Deploy the solution from Build menu.

Step-7

Open the MS SQL Server management studio and type the connect to the TEST_DB and then provide the bellow SQL Script to test.

SELECT dbo.fnDispalyName()

Output:

Hello World

Hope you like it.

Posted by: MR. JOYDEEP DAS