Tag Archives: CLR

CLR in SQL Server

Posted on by
Reply

According to Microsoft, Use Transact-SQL when the code will mostly perform data access with little or no procedural logic. Use managed code for CPU-intensive functions and procedures that feature complex logic, or when you want to make use of the BCL of the .NET Framework.

1. Create SQL Stored Procedure in visual stuido
Project name: “FirstSqlClass.cs”; class name:”FirstSqlClr” and function name:”ListEmployees”

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;   
 
 
public partial class FirstSqlClr
{
    [Microsoft.SqlServer.Server.SqlProcedure]
 
    public static void ListEmployees(SqlString fLetter, SqlString orderBy, out SqlString outputParameter)
    {
        string sqlCmd = "";
        sqlCmd += " select c.LastName, c.FirstName, c.EmailAddress ";
        sqlCmd += " from Person.Contact as c inner join HumanResources.Employee as e on c.ContactID = e.ContactID ";
        sqlCmd += " where left(c.LastName, 1) = @fLetter";
        sqlCmd += " order by " + orderBy.ToString();        
 
        SqlContext.Pipe.Send("Stored Procedure started at: " + DateTime.Now.ToString() + "\n");
        using (SqlConnection conn = new SqlConnection("context connection = true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sqlCmd, conn);
            cmd.Parameters.Add(new SqlParameter ("@fLetter", SqlDbType.NVarChar, 1));
            cmd.Parameters[0].Value = fLetter;
 
            SqlDataReader rdr = cmd.ExecuteReader();
            SqlContext.Pipe.Send(rdr);
        }
        outputParameter = "after some manipulation that has to be done by a procedure language.";
        SqlContext.Pipe.Send("Stored Procedure ended at: " + DateTime.Now.ToString() + "\n");
    }    
};

2. Set Target Framework to “.Net 2.0” SQL 2008 only support .Net 2.0. Comply to DLL.
3. Enable Clr in Sql Server:

sp_configure 'clr enabled', 1
go
reconfigure
go

4. Register the assembly in SQL Server Locate the Dll file and set Permission to “SAFE”.

use AdventureWorks
go
create assembly FirstSqlClrClass from
'C:\Users\kwu\Documents\visual studio 2010\projects\FirstSqlClrClass\FirstSqlClrClass\bin\Debug\FirstSqlClrClass.dll'
go

5. Create Stored Procedure

use AdventureWorks
go
create procedure FirstListEmployees(@fLetter Nvarchar(1), @OrderBy NVarchar(20), @outputParameter NVarchar(100) OUT) as
external name FirstSqlClrClass.FirstSqlClr.ListEmployees
go

The the SP can be used as regular T-Sql stored procedure.

use AdventureWorks
go
DECLARE @return_value int,
@outputParameter nvarchar(100)

EXEC @return_value = [dbo].[FirstListEmployees]
@fLetter = 'B',
@OrderBy = 'EmailAddress',
@outputParameter = @outputParameter OUTPUT

SELECT @outputParameter as N'@outputParameter'