YES, We can call .Dll from SQL Server by the following ways…
- We can use ” sp_addextendedproc” to register the .Dll in SQL Server and You can call the same from SQL Server.
sp_addextendedproc ” FunctionName ” , ” Dll Path ”
Using Extended Stored procedure is a “Deprecated Feature“, So please don’t follow this way…
- 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.
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.
- 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
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
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.
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.
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
· 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.