Using Clr Stored Procedures in Sql Server 2005

For this example you'll need adventure works database installed on your sql server instance AND you'll want a connection in your server pane of Visual Studio.(you can get adventure works database on msdn here: http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en )

First problem I ran into was that my server was not configured to allow Clr procedures.

This link (http://geekswithblogs.net/rasyadi/archive/2005/11/18/60459.aspx

) showed me how to enable it.

exec sp_configure 'clr enabled', 1

However, i got this error.

 ‘Ad hoc updates to system catalogs are not allowed.’

Researching that I found this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89594&SiteID=1

 

And used Zoulasc’s fix:

sp_configure 'allow updates', 1

reconfigure with override

Then i ran the sp_configure 'clr enabled',1 query and ran reconfigure again.

Now that I’m able to use clr on my sql server I started a new project:

Create a new sql project under C# projects.

Bind it to the Adventureworks database.

Now, just add a class to the project, I prefer to keep my classes separated out by what they’re for. I’m going to make one to work with the adventure works Product table.

 

You’ll need these three using (or “import”) statements added to the defaults.

 

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

 

Modify the class to be public (sql only accepts public classes for procedures, it won’t even deploy with procedures within internal/private classes)

 

Ok, so now I have this:

    public class ProductRepository

    {

        [Microsoft.SqlServer.Server.SqlProcedure]

        public static void sp_GetAllProducts()

        {

 

        }

    }

 

Now we can write our code to read all of the products:

First, let’s create a sqlconnection:

 

Most people would do this:

SqlConnection connection = newSqlConnection("Context Connection=true");

 

But my biggest pet-peave is when people don’t dispose of their connection or close it,(or the command or the reader) we can do this automagically with the using() statement. So here's the final look at the function.

        public static void sp_GetAllProducts()

        {

            using(SqlConnection connection =

new SqlConnection("Context Connection=true"))

            using (SqlCommand command =

new SqlCommand("SELECT * from Production.Product", connection))

            {

                connection.Open();

 

                using (

SqlDataReader reader = command.ExecuteReader())

                {

                    SqlPipe pipe = SqlContext.Pipe;

                    pipe.Send(reader);

                }

            }

          }

Parameters are as simple as putting params on the function itself, then within your code creating instances of SqlParameter... let's say we wanted to get products by an id number:

 

[Microsoft.SqlServer.Server.SqlProcedure]

publicstaticvoid sp_GetProductsById(int id)

{

using(SqlConnection connection = newSqlConnection("Context Connection=true"))

using (SqlCommand command = newSqlCommand("SELECT * from Production.Product WHERE ProductID = @ProductID ", connection))

{

connection.Open();

SqlParameter param = newSqlParameter("@ProductID", id);

param.Direction = ParameterDirection.Input;

param.DbType = DbType.Int32;

command.Parameters.Add(param);

using (SqlDataReader reader = command.ExecuteReader())

{

SqlPipe pipe = SqlContext.Pipe;

pipe.Send(reader);

}//Automatically closes and disposes the objects

}