Creating a Basic CLR Stored Procedure Using C# in Visual Studio 2010

I was recently confronted with a problem managing a database of MAC addresses. Our company purchased a block of MACs and our team was tasked with the problem of creating the database and associated services for managing the MAC range.

The problem was that there did not seem to be any way to easily convert from decimal integer types to hexadecimal in T-SQL. One possible way to solve this problem is to write a simple C# CLR stored procedure as this type of conversion is pretty trivial in languages like C#.

This tutorial assumes you will be adding the CLR stored procedure to the AdventureWorks 2008 database and that you will be using SQL Server 2008 R2.

Step 1 – Enabling CLR in SQL Server

Run sp_configure to enable CLR on your test SQL Server Instance.

EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE GO

Step 2 - Creating the VS 2010 Project

First, you must change the version of the.NET Framework used to 3.5 at the top of this screen or your project will not deploy. Then change the project name to sp_GetHexValue.

Add a reference to the AdventureWorks database by clicking Add New Reference.

Fill out the form and click ok.

Select AdventureWorks and click ok. Then right click the solution in Solution Explorer and select New -> Stored Procedure

Change the name to sp_GetHexValue.

Change the code to look like this:

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {    [Microsoft.SqlServer.Server.SqlProcedure]    public static void sp_GetHexValue(int number)    {          string hex = number.ToString("x");          SqlContext.Pipe.Send(hex);     } };

Step 3 – Deploy the Solution

Click Build -> Deploy Solution and you should receive the following output:

------ Build started: Project: sp_GetHexValue, Configuration: Debug Any CPU ------ sp_GetHexValue -> c:\users\rkaucher\documents\visual studio 2010\Projects\sp_GetHexValue\sp_GetHexValue\bin\Debug\sp_GetHexValue.dll ------ Deploy started: Project: sp_GetHexValue, Configuration: Debug Any CPU ------ Build started 8/29/2010 12:54:41 PM. SqlClrDeploy: Beginning deployment of assembly sp_GetHexValue.dll to server WIN7PC : AdventureWorks The following error might appear if you deploy a SQL CLR project that was built for a version of the .NET Framework that is incompatible with the target instance of SQL Server: "Deploy error SQL01268: CREATE ASSEMBLY for assembly failed because assembly failed verification". To resolve this issue, open the properties for the project, and change the .NET Framework version. Deployment script generated to: c:\users\rkaucher\documents\visual studio 2010\Projects\sp_GetHexValue\sp_GetHexValue\bin\Debug\sp_GetHexValue.sql Creating [sp_GetHexValue]... Adding files to assembly [sp_GetHexValue] Creating [dbo].[sp_GetHexValue]... Creating [sp_GetHexValue].[SqlAssemblyProjectRoot]... Creating [dbo].[sp_GetHexValue].[SqlAssemblyFile]... Creating [dbo].[sp_GetHexValue].[SqlAssemblyFileLine]... The transacted portion of the database update succeeded. Deployment completed Build succeeded. Time Elapsed 00:00:16.99 ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Step 4 – Testing

In SQL Server Management Studio open a new query and type the code shown and you will see that Intellisense in aware of the new stored procedure.

USE AdventureWorks GO EXEC sp_GetHexValue 1634289

And you will receive the output 18eff1. Four simple steps to writing and deploying a simple CLR procedure.