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:
- Performance improvement.
- No need to release code again and again.
- Provides flexibility in both CLR's (.NET and SQL Server).
- Limits deployment.
- 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
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";
Step 2 - Create the assembly in SQL Server with a specified path
CREATE ASSEMBLY ClassLibrary1
CREATE PROCEDURE GetCompanyDetail @compID int
EXTERNAL NAME ClassLibrary1."CheckDatabase.Demo".GetCompanyDetail;
select assembly_id,assembly_class,assembly_method from sys.assembly_modules
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
- Check performance.
- No need to change or deploy the code again and again, you just have to change the assembly in the database.
- Deal directly with SQL Server CLR objects (core part).