Can we call .DLL from SQL server ?

Leave a comment (0) Go to comments

YES, We can call .Dll from SQL Server by the following ways…

  1. We can use ” sp_addextendedproc” to register the .Dll in SQL Server and You can call the same from SQL Server.

    use master

    Go

    sp_addextendedproc ” FunctionName ” , ” Dll Path ”


    Caution:
    Using Extended Stored procedure is a “Deprecated Feature“, So please don’t follow this way…

  2. You can create CLR Objects in .Net (Stored Procedure, Functions,….) and Register in on SQL Server as a ASSEMBLY and then you can uses the same.
    Conclusion:
    Using CLR Assembly is the only recommended way to access the .Dll(s). So, Using the Extended Stored Procedure is the “Deprecated Feature“, So please don’t follow First way.

Let us go through with an example for detailed understanding.

[Update 30-Nov-2010]  I just uploaded a Video Tutorial on this topic, Check that out too.

Perquisites

  • SQL Server 2008 with .NET Framework version 2.0 + installed in our Server with Visual Studio.

Steps for building a CLR stored procedure in SQL Server 2008

  • Enabling CLR integration in SQL Server 2008
  • Creating a CLR stored procedure Assembly
  • Deploying the Assembly in SQL Server 2008
  • Creating & executing the CLR stored procedure in SQL Server 2008

Step 1 – Enabling CLR integration in SQL Server 2008

To enable CLR integration we use the clr enabled option of the sp_configure stored procedure and set the value as “1″ as shown below.

sp_configure ‘clr enable’, 1

GO

RECONFIGURE

GO

Step 2 – Creating a CLR stored procedure Assembly

1. Open the text editor then copy and paste the following code in that.  Save it as a Class file to MyCLRAssembly.vb

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes   Public Class MyFirstCLRProcs     <Microsoft.SqlServer.Server.SqlProcedure()> _     Public Shared Sub CLRProcs(ByVal iActionID As Integer)                  SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString())         Using oConnection As New SqlConnection("context connection=true")             Try                 oConnection.Open()        Dim oCommand As New SqlCommand( _  "select top 10 EventID ,StatusKey, ProcessTime, UserType, ActionID" & _   " from  EventTable where ActionID = " & _
iActionID, oConnection)                 Dim oReader As SqlDataReader                 oReader = oCommand.ExecuteReader()                 SqlContext.Pipe.Send(oReader)             Catch ex As Exception                 SqlContext.Pipe.Send(ex.ToString())             End Try         End Using     End Sub
End Class           

2. Required Namespaces and Classes:

  • Here Microsoft.SqlServer.Server is the main namespace responsible for creating CLR integrated stored procedures, functions, triggers, etc.
  • SqlContext and SqlPipe are two classes responsible for creating objects to output the results and text messages.  Here in the result part we send the SqlDataReader to clientside and print a message as “HelloWorld !” with the Server time.
  • Here we use Context Connection, which allows us to execute Transact-SQL statements in the same context that our code was invoked in the first place, i.e. connect using a connection string that points to local server.

3. Compile the class file  “MyCLRAssembly.vb” using the following command in SDK command prompt:

  • Go to : start>>All programs>>Microsoft .NET Framework SDK v2.o>>SDK Command Prompt
  • Write the following codes there and press enter

vbc  /t:library  c:\SQLAssembly\MyCLRAssembly.vb

Here “t” stands for target and “c:\SQLAssembly\” is the physical path of that Class file.  Then we get the MyCLRAssembly.dll assembly file at that folder.

Step 3 – Deploying the Assembly in SQL Server 2008

Execute the following T-SQL code against the SQL Server in Query analyzer of SQL Server 2008 Management Studio.


CREATE ASSEMBLY MyCLRAssembly from 'c:\SQLAssembly\MyCLRAssembly.dll'   WITH PERMISSION_SET = SAFE  Go


Here we have to give the exact path of our assembly file (.dll file) for creating assembly.  Again the important factor here is the PERMISSION_SET for accessing security permissions.  There are three modes: SAFE, UNSAFE, EXTERNAL_ACCESS; through which SQL Server allows users to deploy code in database.  SAFE mode is for all users and UNSAFE is for more reliable user like Database Administrator.

So every user can create Assemblies using SAFE mode. However, there are some restrictions and limited number of Namespaces available to use under this mode.

Step 4 – Creating & executing the CLR stored procedure in SQL Server 2008

Execute the following code in query analyzer of SQL Server 2008 Management Studio against the database.



CREATE PROC sp_MyCLRProcs(@iActionID as int)
AS
EXTERNAL NAME MyCLRAssembly.MyFirstCLRProcs.CLRProcs
Go



Here we have to give [Procedure Name] and must specify the exact path of assembly method as [Assembly Name].[Class Name].[CLR function Name].  As our CLR method, which having the prefix as <Microsoft.SqlServer.Server.SqlProcedure()>, has an input parameter so we have to define the parameter at creation of the stored procedure with the identical datatypes exact variable name. (Here it is “Integer” which is equivalent ot ‘ int’  in T-SQL)

· Executing the CLR stored procedure:

As any other stored procedure the CLR stored procedure is executed using EXEC  T-SQL command as follows :

EXEC sp_MyCLRProcs 216268

End, This Is How we can Call a .dll inside a SQL Server, TSQL statements. Lets look at advantages of using CLR procedures

Advantages of CLR procedure over T-SQL procedure

· Releases the tension to become Master of T-SQL for doing complex database operations.

· Through this we can move our Business layer into database, getting closer to Data for increasing performances by avoiding unnecessary server trips.

· Here .NET Framework Base Class Libraries (BCL) are available to use, through which we can solve our complex logic operations like string manipulations, threading operations, file management, cryptography, etc.

· It is known as Managed Procedure that means it deals with automatic garbage collection, memory management, exception handling, etc.

· As a developer in C#.NET or VB.NET we can use our OOP’s concepts for more structured operations.

· This feature provides the ability to leverage the features of .NET Code Access Security (CAS) to prevent assemblies from performing certain operations.

EOF - Can we call .DLL from SQL server ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.