Click here to Skip to main content
13,257,383 members (48,669 online)
Click here to Skip to main content
Add your own
alternative version


10 bookmarked
Posted 12 Jun 2012

SQL Server Assembly Creation

, 19 Jun 2012
Rate this:
Please Sign up or sign in to vote.
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

You may also be interested in...


Comments and Discussions

QuestionIncorrect Namespace (CLR)? Pin
AspDotNetDev15-Jun-12 14:13
protectorAspDotNetDev15-Jun-12 14: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171114.1 | Last Updated 19 Jun 2012
Article Copyright 2012 by deepakaitr12345
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid