CLR Integration

CLR integration

Up to sql server 2000, there is concept called External Stored sub programs, that allows you to create stored sub program in c,c++ and then integrate to sql server. This feature is replaced with CLR integration in sql server 2005.

CLR Integration allows you to create stored sub programs in Dot Net languages like C#.NET, or VB.Net and then integrate to sql server. Main purpose of either external stored sub programs or CLR Integration is to allow the users to use the features like accessing files in operating system.

Using stored sub programs which are not possible with TSQL programming

CLR Integration feature is by default disable in sql server and you must enable it and for this purpose use

sp_configure stored procedure as follows

sp_configure ‘clr enabled’, reconfigure

Within visual studio.net create a project of type sqlserver database project.

Create required stored procedures, user defined functions and trigger within that project using C#.NET syntax and compile the project to generate a dll file.

While creating stored procedure , user defined function, and triggers within the sql server database project you have to follow the following rules

The method that are to be integrated as stored procedures must be specified with [Sql Procedure] attribute method to be integrated as user defined functions  must be specified with [sql function] attribute and methods to be integrated as triggers  must be specified with [sql trigger]

Methods to be integrated  into sql server must be declare with public and static

Methods to be integrated to sql server as stored procedures or triggers must have the return type void.

Methods to be integrated to sql server as triggers must  not have any parameters

Create an assembly  in sql server from ddl generated for sql server DB project using create assembly command that has the following syntax

Create assembly <assemblyname>

From <dllpath>

[with permission_set=safe| unsafe| external_access]

Create stored procedures, user defined functions and triggers with in sql server from the methods created in sql server database project by using “external name” option within create procedure , create function, and create  trigger command

Example

The following example creates one stored procedure and one user defined functions with in c#.net and integrate them with sql server

File->New Project

Add new item

And write the following code

build the solution, then .dll file is created.