SQL Server Assembly Creation

Introduction

This article gives you guidance to create an assembly in SQL Server and store it in the database. That will help us with the following:

1. Performance improvement.
2. No need to release code again and again.
3. Provides flexibility in both CLR's (.NET and SQL Server).
4. Limits deployment.
5. Secure code.

Background

As we know, we have two major building blocks in Microsoft platform: DLL and exe. One is useful to support execution and the other is executed on an individual level. Basically SQL Server also provides the facility to create assembly files and just as we can access methods in a DLL, we can access methods from a SQL Server assembly. Here we are creating an assembly (DLL) file and going to store it in the database and access it via a Stored Procedure.

Using the code

Step 1 - Create the class library

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Text; 
using System.Data.SqlClient; 
using System.Data.SqlTypes; 
using Microsoft.SqlServer.Server; 

namespace CheckDatabase
{ 
public class Demo 
{
    static public void GetCompanyDetail(SqlInt32 compID)
    { 
        using (SqlConnection conn = new SqlConnection("context connection=true")) 
        {
            SqlCommand cmd = conn.CreateCommand(); 
            cmd.CommandText = @"select * from master_comp where comp_id=@compID"; 
            cmd.Parameters.AddWithValue("@compID", compID); 
            conn.Open(); 
            SqlContext.Pipe.ExecuteAndSend(cmd);
        } 
    } 
} 
}

Step 2 - Create the assembly in SQL Server with a specified path

CREATE ASSEMBLY ClassLibrary1 
from 'D:\DotNetTeam\SQLServer\ClassLibrary1.dll' 

Step 3--Create the stored procedure 

CREATE PROCEDURE GetCompanyDetail @compID int 
as 
EXTERNAL NAME ClassLibrary1."CheckDatabase.Demo".GetCompanyDetail; 
go 
select assembly_id,assembly_class,assembly_method from sys.assembly_modules 
where object_id=object_id('GetCompanyDetail')

Step 4 - Enable and disable the SQL Server CLR configuration

exec sp_configure 'clr enabled',1; 
reconfigure;

Step 5 - Execute the Stored Procedure

exec GetCompanyDetail 1

Points of interest

1. Check performance.
2. No need to change or deploy the code again and again, you just have to change the assembly in the database.
3. Deal directly with SQL Server CLR objects (core part).