Creating CLR-based Stored Procedures in C# and VS 2005

Creating CLR-based Stored Procedures in C# and VS 2005

 

rickieleemail@yahoo.com on Aug. 30, 2005

http://rickie.cnblogs.com

 

The integration of the .NET CLR with SQL Server enables the development of stored procedures, user-defined functions, triggers, aggregates, and user-defined types using any of the .NET languages.

 

So when will we use the Managed code such as VB.NET and C# or T-SQL to create stored procedures in SQL Server 2005?  Generally, Managed code is better suited for the complicated business logic and calculation, and string handling and regular expressions. On the contrary, T-SQL is simpler in situations where the code is just used to perform data access with little or no procedural logic.

 

The following steps illustrate how to created CRL-based stored procedures. The managed code will be used in the example, although T-SQL is more suitable over there.

 

1. Create a “SQL Server Project” in C# by using VS 2005

Project Language: C#

Project Type: Database

Template: SQL Server Project

VS 2005 will ask you to create a database reference or use an existing one. In this example, the AdventureWorks database will be referenced.

 

2. Create a demo stored procedure in the AdventureWorks database

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void GetEmployee()

    {

        SqlPipe sp = SqlContext.Pipe;

        using(SqlConnection conn = new SqlConnection("context connection=true"))

        {

            conn.Open();

            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.Text;

            cmd.Connection = conn;

            cmd.CommandText = "Select * From HumanResources.Employee (nolock)";

            SqlDataReader reader = cmd.ExecuteReader();

            sp.Send(reader);

        }

    }

***

[Microsoft.SqlServer.Server.SqlProcedure]

This attribute tells Visual Studio that this is a stored procedure for SQL Server.

 

Next we need to build and deploy the stored procedure by right-clicking above SQL Server project. This will compile the DLL, copy it into SQL Server and create a stored procedure called GetEmployee in the AdventureWorks database.

 

3. Call the stored procedure

 

USE [AdventureWorks]

GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[GetEmployee]

SELECT      'Return Value' = @return_value

GO

 

All employee records will be retrieved in the query results pane. If there is an error message about execution the above code. Please enable execution CLR by using the following script.

 

-- To enable execution of user code in the .NET Framework.

Exec sp_configure 'clr enabled', 1

Reconfigure with override