CLR Integration with SQL Server 2005

Let’s begin with integrating sql server 2005 with CLR. It is good for database programmers to work on T-SQL, but for .Net programmer some times it is a night mare to write complex stored procedures. With the introduction of SQL Server 2005 and .Net 2.0 we can now write the database code like stored procedures, functions, triggers and user defined types in our favorite .Net languages like VB.Net and C#.Net.

Let’s give a simple beginning,
· Start visual studio 2005’
· Create Project, and Select the preferred language (from Visual Basic, Visual C#) > select Database > SQL Server Project as shown below, and click OK.

· You will be asked for the Database reference, if you haven’t created it earlier create one by providing the server name and the database name, and click Test Connection button to test the connection, if the test succeeds click ok.


· If you have already added a Database Reference following Dialog will be shown, from this you can either select the available reference or add a new one and repeat the above step.


· The template solution is loaded in the VS 2005 IDE, lets check the contents of the default template of the Sql Server Project.
> References has System, System.Data and System.XML already added.
> Test.sql contains some sample TSQL statements.
· Now we are done with Visual Studio ready for developing SQL Server Project.

Creating a Stored Procedure:
· Right click on the project node in the solution explorer, choose Add > Stored Procedure > and click Add, as shown below. However you can change the method name in the partial class named StoredProcedures.

· By default the method is static, so that it can be accessible when we deploy it, and we are now ready to write our .Net code inside the stored procedure.
· We can write our regular .Net code for the stored procedure too, but there is point to be taken care of. The connection string, you can give your own connection string for initializing the connection object, but let’s start by using “context connection = true”, which says that the connection will be made by using the existing connection by which the Stored Procedure has been invoked.
· Now use the following code in the stored procedure, which selects all the records from the Categories table of the Northwind Database.


Now build the application and if you do not find any errors publish the stored procedure. It will be deployed to the database server you have configured.

Now connect to the SQL Server Management Studio, and try to execute the staored procedure you have deployed.

exec CLRUSP_RetrieveCategories
you should get the following error..

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

To enable .Net code to execute in sql server, you need to run the follwing script.

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

To Disable it you can run the above script just by changing 1 to 0

EXEC sp_configure 'clr enabled', 0;
RECONFIGURE WITH OVERRIDE;
GO

Now run the stored procedure again

exec CLRUSP_RetrieveCategories

this will execute and will give you the output..