SQL CLR Stored Procedure using Visual Studio 2010

In this post, we will see how to use SQL CLR functionality for defining Stored Procedure using C# in VS 2010. We will also see how to enable CLR under SQL Server.

T-SQL is designed for direct access of data and for manipulation of that data. But T-SQL does not contain arrays, classes, collections, For-Each loop functionalities. However using SQL CLR, we can achieve all of this. With the integration of CLR in SQL Server, we can write managed code to define –

1. Stored Procedures.
2. User Defined Functions.
3. Triggers.
4. User Defined Type.
5. User Defined Aggregate.

The decision to use CLR functionality under SQL server needs to be implemented when you are performing –

1. CPU intensive operations.
2. Procedures that perform complex logic.
3. When you want to use BCL (Base class libraries) of .NET framework.

By default the CLR is not enabled under SQL Server. To enable CLR under SQL Server, execute the following command –

SP_CONFIGURE 'clr enabled',1
RECONFIGURE

You will see the following result –



Once you enable the CLR, now let’s create a Stored Procedure using Visual Studio 2010 –

This will display a ‘New Database Reference’ window where we will have to provide a database connection. Let’s set the configuration as shown below –

If the database is not available, it will get created or you can choose an existing database if you want. After this step, this will ask you whether you want to enable SQL/CLR debugging. Click the ‘YES’ button.

Important Note – Right click your project and go to properties window. Make sure that you have selected the framework - .NET 3.5. Now observe the project under solution explorer. It contains a Test.sql file to test the procedure or function. It also contains PostDeployScript.sql file and PreDeployScript.sql file as shown below –

Post and Pre deployment scripts can be used for modifying, dropping or recreating and taking other actions before or after deployment. Now let’s add a Stored Procedure in our project. Right click the project and add Stored Procedure with the name ‘FileIOOperation’.

Now import a namespace for the I/O operation –

using System.IO;

Write code for creating a file and writing a message in the file as shown below –

Now let’s deploy the procedure. Right click the project and click ‘Deploy’ from the context menu. Once deployment succeeds, go to SQL Server Management Studio and confirm the deployment. Go to ‘Object Explorer’ > Expand the ‘SQLCLRSampleDB’ > under ‘Programmability’ > ‘Assemblies’ folder you will see our assembly and under ‘Stored Procedures’ folder you will see the stored procedure as shown below –

Let’s try to execute the stored procedure by writing the following code –



If you execute the stored procedure, you will get a security exception as shown below –



The reason for this exception is this stored procedure will only run under database scope and will not allow you to perform the IO operations. There are three permission sets which are available with CLR objects as explained below –

1) SAFE – this permission will allow you to perform computation and data access operations within the server. This is the default permission.
2) EXTERNAL_ACCESS – this permission will allow you to access the external resources like files, network, registry, environment variables etc.
3) UNSAFE – this permission will allow you to access restricted resources like WIN 32 APIs.
So for our demo, let’s set the permission to ‘EXTERNAL_ACCESS’. Right click our project and go to properties window. From the properties window, chose ‘Database’ option and set the ‘Permission Level’ to ‘External’ as shown below –

Now redeploy the project. The deployment will fail because our database is not set to ‘TRUSTWORTHY’ flag. So let’s set the ‘TRUSTWORTHY’ flag to ‘ON’ as shown below –

ALTER DATABASE SQLCLRSampleDB SET TRUSTWORTHY ON

Now again redeploy the project this time the deployment will be successful.

Now test our stored procedure by executing it. Check on your ‘C:\’ the file is created with the name ‘Test.txt’. Open it and see the message as shown below –



Download the source code