SQL.2005.CLR.Integration

I've been looking into the programmable aspects of SQL Server 2005 for the past days and one of the things that interest me most is the CLR Integration. Developers can now use managed-code to develop stored procedures that can be accessed by SQL Server 2005.

Here's a simple example I made:

Step 1 - Creating the assembly.
1. Create a Library project using VS.NET 2005
2. Enter the following code:

using System;
using System.Data;
using System.Data.SqlServer;

public class TestClass
{
public static void RunTest()
{
SqlCommand cm = SqlContext.GetCommand();
cm.CommandText = "SELECT * FROM Production.Product";
SqlDataReader dr = cm.ExecuteReader();
SqlContext.GetPipe().Send(dr);
}
}

3. Compile the assembly.

Noticed the namespace System.Data.SqlServer and the new class SqlContext? These are new stuffs. The GetPipe() method actually returns a SqlPipe object that allows you to send results to SQL Server 2005.

Step 2 - Loading the assembly in SQL Server 2005
1. From SQL Server Management Studio, Create a New Query.
2. Type in the following and run it:

CREATE ASSEMBLY Test FROM 'D:\test\SQLCLRTest.dll'
WITH PERMISSION_SET=UNSAFE

The path following the FROM statement should point to where your assembly is located. If there are no errors, you have successfully loaded the assembly.

3. Next, type and run this:

CREATE PROCEDURE RunTest
AS EXTERNAL NAME Test.TestClass.RunTest

This will create a Stored Procedure for you. You can even see it in your Stored Procedures list.

4. Finally, to run your procedure, type and run the following:

EXEC RunTest

You should get some results.

Step 3 - To remove the procedure and unload the assembly after you are fed up with it:
1) Run DROP PROCEDURE RunTest
2) Then run DROP ASSEMBLY Test

Have Fun!