Click here to Skip to main content
15,395,257 members
Articles / Database Development / SQL Server
Posted 12 Jun 2012


13 bookmarked

SQL Server Assembly Creation

Rate me:
Please Sign up or sign in to vote.
4.93/5 (6 votes)
19 Jun 2012CPOL1 min read
How to create an assembly in SQL Server and store it in the database.


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.


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); 

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

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

Step 3--Create the stored procedure 

CREATE PROCEDURE GetCompanyDetail @compID int 
EXTERNAL NAME ClassLibrary1."CheckDatabase.Demo".GetCompanyDetail; 
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; 

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).


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer ITS
India India
No Biography provided

Comments and Discussions

QuestionIncorrect Namespace (CLR)? Pin
AspDotNetDev15-Jun-12 13:13
protectorAspDotNetDev15-Jun-12 13:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.