Resumé

SQL 2005 and Compiled CLR Procedures

Cristian Merighi () 0.00

Wit hthis article I intend to list the steps to follow in order to exploit the new SQL Server 2005 features and executing compiled procedures, written in C# or VB.Net, directly from the DataBase Server.
This article is obsolete. Some functionalities might not work anymore. Comments are disabled.

I need to write this article to help myself keepin' in mind the various step we have to make in order to use CLR in SQL 2005!
I eagerly tried this new feature as soon as I got my brand new developer version accompaning my Visual Studio 2005 copy, fortunately I kept a log about every single operation done... here is the trace...

This is going to be a cut'n'paste code article which hasn't the intention to give solutions for a particular problem, it just will show what can be done with a compiled stored procedure and how to exploit this feature.

First of all we have to enable the SQL Server ability to execute CLR code - which is off by default - and we can do it executing the following TSQL script...

EXEC sp_configure 'show advanced options' , '1';
GO
reconfigure;
GO
EXEC sp_configure 'clr enabled' , '1'
GO
reconfigure;
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '1';
GO

...or using the Surface Area Configuration Tool as shown in the following screenshot images:

first step
last step

Now let's see the commented code (C#) that will be executed:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace SpareSQLCLR
{
	// class must be public
    public class SpareSQL
    {
        // from the reference remarks section:
        // SqlProcedureAttribute is used only by Microsoft Visual Studio to automatically register the specified method as a stored procedure. It is not used by SQL Server.
        [Microsoft.SqlServer.Server.SqlProcedure()]
        public static void GetFamilyBudgetAmount(SqlGuid guid, SqlDateTime refDate, out SqlMoney amount)
        {
            // please note: using the contextual connection
            using (SqlConnection connection = new SqlConnection("context connection=true"))
            {
                amount = new SqlMoney(0D);
                connection.Open();
                SqlCommand command = new SqlCommand(string.Format("SELECT ID FROM tblUsers WHERE idFamily = '{0}'", guid), connection);
                SqlDataReader reader = command.ExecuteReader();

                string nested = string.Empty;
                using (reader)
                {
                    while (reader.Read())
                    {
                        nested += string.Format(@" SELECT  TOP 1 tblBudget.dblAmount AS Amount, tblBudget.ID, 
                                    tblBudget.dteReference AS Date, tblBudget.idUser AS MemberID
                                    FROM tblBudget WHERE idUser = '{0}' AND dteReference <= @Date ORDER BY Date DESC; ", 
                                    reader[0]);
                    }
                }
                command.Parameters.AddWithValue("@Date", refDate);
                foreach (string sql in nested.Split(';'))
                {
                    command.CommandText = sql;
                    object oAmount = command.ExecuteScalar();                    
                    try
                    {
                        if (!oAmount.Equals(DBNull.Value)) amount = SqlMoney.Add(amount, new SqlMoney(Convert.ToDouble(oAmount)));
                    }
                    catch (Exception e) { 
                        // sending custom error message back to the client 
                        SqlContext.Pipe.Send(string.Format("[amount:{0}]\n({1})", oAmount, e)); 
                    }
                }
            }
        }
    }
}

Once that code got compiled there's nothing more to do than adding the assembly to SQL Server by scripting...

CREATE ASSEMBLY SpareSQLCLR 
FROM 'F:\Cristian\Webs\ToolSpare\SQLCLR\SpareSQLCLR.dll'
-- SAFE is recommended in order to just compute data
-- without needs to access external resources outside the SQL Server instance
WITH PERMISSION_SET = SAFE

...or using the GUI as shown in the following screenshots (SAFE attribute can be selected in a dropdown list)...

assembly assembly

...and then create the stored procedure that will execute the GetFamilyBudgetAmount method:

CREATE PROCEDURE [dbo].[sp_GetFamilyBudgetAmount]
        @guid [uniqueidentifier],
        @refDate [datetime],       
        @amount [money] OUTPUT
WITH EXECUTE AS CALLER
AS
-- please note the form [assembly name].[full class name].[sqlprocedure method]
EXTERNAL NAME [SpareSQLCLR].[SpareSQLCLR.SpareSQL].[GetFamilyBudgetAmount]
GO

Take care. Bye.

feedback
 

Syndicate

Author

Cristian Merighi facebook twitter google+ youtube

Latest articles

Top rated

Archive

Where am I?

Author

Cristian Merighi facebook twitter google+ youtube

I'm now reading

Feeds