Click here to Skip to main content
Click here to Skip to main content

SQL Server Assembly Creation

, 19 Jun 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
How to create an assembly in SQL Server and store it in the database.

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

License

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

Share

About the Author

deepakaitr12345
Software Developer ITS
India India
No Biography provided

Comments and Discussions

 
QuestionIncorrect Namespace (CLR)? PinprotectorAspDotNetDev15-Jun-12 14:13 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 19 Jun 2012
Article Copyright 2012 by deepakaitr12345
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid