Writing a C# CLR Stored Procedure in Visual Studio 2010 and deploying to SQL Server 2008 R2

Visual Studio provides a nice environment for writing CLR stored procedures with managed code.  Writing a CLR stored procedure in C# and deploying to SQL Server 2008 R2 is mostly intuitive, with a couple of caveats.

This example shows how to write a stored procedure that will convert a string into a SHA-512 hash. (Note there is a useful existing function called HashBytes you can use, though the only SHA hash it supports is SHA1. Update 12/29/10: HashBytes supports SHA2 512 & 256 in SQL11 – Denali)

1. Prepare your SQL Server instance to enable assembly execution. E.g. run the following script:

sp_configure ‘show advanced options’, ‘1’
RECONFIGURE
exec sp_configure ‘clr enabled’, ‘1’
RECONFIGURE
GO

2. Start Visual Studio 2010 and create a new Visual C# SQL CLR Database Project:

Warning: Visual Studio 2010 builds with .NET Framework 4.0 by default, and SQL Server 2008 R2 requires 3.5. Make sure you change this setting to .NET Framework 3.5 when you are creating the project.

If you’re using SQL Server Denali (SQL11) then you’re fine to use .NET Framework 4.0.

3. Next you can choose an existing database reference if present, or create a new one. I’ll use the AdventureWorks database:

4. In the Solution Explorer for your project right-click and select Add->Stored Procedure

5. Now you will see some skeleton C# code for a stored procedure in Visual Studio

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 StoredProcedure1()
{
// Put your code here
}
};

You can add your own code in at this point. Let’s create some code to create and return a SHA-512 hash of a string..

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Security.Cryptography;

[Microsoft.SqlServer.Server.SqlProcedure]
public static void SHA512(String plaintext)
{
SqlPipe sp;
sp = SqlContext.Pipe;

    // convert the passPhrase string into a byte array
ASCIIEncoding AE = new ASCIIEncoding();
byte[] passBuff = AE.GetBytes(plaintext);

    SHA512Managed hashVal = new SHA512Managed();
byte[] passHash = hashVal.ComputeHash(passBuff);
string hashedString = System.Text.ASCIIEncoding.ASCII.GetString(passHash);

    sp.Send(hashedString);
}

6. To build the solution select Build->Build Solution in Visual Studio. Then select Build-> Deploy Solution to deploy it to the database.

Select View->Output in order to see build and deployment messages and confirm your stored procedure built and deployed sucessfully.

7. Once deployed you can call the new stored procedure in a new SQL Server Management Studio query. E.g.:

USE AdventureWorks2008R2;
GO
exec dbo.SHA512 @plaintext = “This is a test”;
GO

 

Notes

For a more comprehensive way of generating hashes in a stored procedure check out Michael Cole’s blog entry Let’s hash a blob.

Advertisements
This entry was posted in Databases. Bookmark the permalink.

2 Responses to Writing a C# CLR Stored Procedure in Visual Studio 2010 and deploying to SQL Server 2008 R2

  1. Pingback: Manually Deploying a CLR Stored Procedure | MSFT Stack

  2. msuworld says:

    Nice Post ..I am looking for some more examples of CLR functions and Stored Procedures, can you please refer anything ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s