Introduction:

The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support. For SQL Server users and application developers, CLR integration means that you can now write stored procedures, triggers, user-defined types, and user-defined functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.

Creating SQLCLR Project:

You need both Visual Studio 2005 and SQL Server 2005 (or SQL Server Express 2005) installed in system.

Note: SQLCLR works fine with SQL Server 2005 Express as well. You need to install SQL Express Manager to create and drop functions, procedures, triggers, assemblies etc

Open Visual Studio 2005. Go to File -> New -> Project to open New Project dialog box.

In New Project dialog box, Select Visual C# -> Database -> SQL Server. Provide project name, location and solution name and click Ok.

It will display the dialog box to connect to database. Select the server name and database name and click Test Connection button. Click OK, once test connection is successful.

If database connection is already added by another project previously then it will display to message box to select one of the existing database connection as shown below:

Click Add New Reference button to create a new database connection and add as a reference to this project.

Writing A Simple CLR Stored Procedure:

Once the project is created, right click the project in Solution Explorer and click Add -> New -> Stored Procedure. Give ‘MyFirstUDProc.cs’ name to your stored procedure and click OK.. Add the following code your stored procedure.

using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class FirstCLRProc
{
public static void HelloWorld()
{
SqlContext.Pipe.Send("Hello world! It's now " +
                             System.DateTime.Now.ToString() + "\n");
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT AverageLeadTime FROM Purchasing.ProductVendor", conn);
SqlDataReader rdr = cmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);
}
}
}

This simple program contains a single static method on a public class. This method uses two new classes used for creating managed database objects - the SqlContext and SqlPipe class - to output a simple text message. This method can be declared as a stored procedure in SQL Server, and then run in the same manner as a Transact-SQL stored procedure.

Note: Change the SQLCommand in above code based on table and columns in your database.

We will now compile this program as a library, load it into SQL Server, and run it as a stored procedure.

Compiling the "Hello World" Stored Procedure:

First Build the project and correct the compilation errors. Then we need to create an assembly file which can be loaded in SQL Server.

SQL Server installs the Microsoft .NET Framework redistribution files by default. These files include csc.exe, a command-line compiler for Visual C# programs. In order to compile our sample, you will need to modify your path variable to point to the directory containing csc.exe. For the default installation of SQL Server, this path will look like:

C:\Windows\Microsoft.NET\Framework\(version)

Version contains the version number of the installed .NET Framework redistributable.

For example:

C:\Windows\Microsoft.NET\Framework\v2.0.50215

To add the Path variable, right click My Computer and select Advanced tab. Click Environment Variables button. Select Path variable, click Edit and add C:\WINNT\Microsoft.NET\Framework\v2.0.50215. Here your version may vary based on installation.

Once you have added the .NET Framework directory to your path, you can compile the sample stored procedure with the following command:

csc.exe /t:library /out:HelloWorld.dll HelloWorld.cs

First navigate to directory of your project in command prompt before executing above command.

This command launches the Visual C# compiler, using the /t option to specify building a library DLL. /out option specifies the output file name.

Loading and Running the "Hello World" Stored Procedure in SQL Server:

Once the sample procedure has successfully compiled, you can test it in SQL Server. To do this, open SQL Server Management Studio (or SQL Express Manager) and create a new query, connecting to a suitable test database.

We will need to create the assembly so we can access the stored procedure.

Copy the HelloWorld.dll created in above step to C drive. Alternately you can give the full path to your dll file in following command.

Add the following Transact-SQL statement to your query. Make sure you are connected to same server and database, as you specified while creating new database project in Visual Studio 2005.

CREATE ASSEMBLY HelloWorld from 'c:\ HelloWorld.dll' WITH PERMISSION_SET = SAFE

Once the assembly has been created, we can now access our HelloWorld method by using the create procedure statement. We will call our stored procedure "hello":

CREATE PROCEDURE hello
AS
EXTERNAL NAME HelloWorld.FirstCLRProc.HelloWorld

In the above command, first HelloWorld is the name of assembly, FirstCLRProc is the class name and HelloWorld is the method name. Once the procedure has been created, it can be run just like a normal stored procedure written in Transact-SQL. Execute the following command:

EXEC hello

It displays result of your SQLCommand query in SQL Server Management Studio and displays following in message window:

Hello world! It's now 8/4/2005 3:28:32 PM

(406 row(s) affected)

In message window, it should display the number of rows affected by your query.

Removing the "Hello World" Stored Procedure Sample:

When you are finished running the sample stored procedure, you can remove the procedure and the assembly from your test database.

First, remove the procedure using the drop procedure command:

drop procedure hello

Once the procedure has been dropped, you can remove the assembly containing your sample code:

drop assembly HelloWorld

Writing A Simple CLR User-Defined Function:

You can write CLR user-defined function similar to CLR stored procedure created above. We will create a scalar function, which will return a single value from database.

Right click the project in Solution Explorer and click Add -> New -> User-Defined Function. Give ‘FirstUDScalarFunction.cs’ name to your User-Defined Function. Add the following code in your User-Defined Function.

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
public class FirstCLRFn
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static int ReturnOrderCount()
    {
using(SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT Max(SalesOrderId) FROM Sales.SalesOrderHeader", conn);
return (int) cmd.ExecuteScalar();
            }
    }
}

Note: Change the SQLCommand in above code based on table and columns in your database.

First Build the project and correct the compilation errors.

Run the following command from command prompt, after navigating to your project directory in command prompt.

csc.exe /t:library /out:FirstUDScalarFunction.dll FirstUDScalarFunction.cs

Copy the FirstUDScalarFunction.dll from your project directory to C drive. Alternately you can give the full path to your dll file in following command.

Add the following Transact-SQL statement to your query. Make sure you are connected to same server and database, as you specified while creating new database project in Visual Studio 2005.

CREATE ASSEMBLY FirstUDScalarFunction FROM 'C:\FirstUDScalarFunction.dll' WITH PERMISSION_SET = SAFE

Once the assembly has been created, we can now access our FirstUDScalarFunction function by using the create function statement. We will call our function CountSalesOrderHeader().

CREATE FUNCTION CountSalesOrderHeader() RETURNS INT
AS EXTERNAL NAME FirstUDScalarFunction.FirstCLRFn.ReturnOrderCount

Once the function has been created, it can be run just like a normal user defined function written in Transact-SQL. Execute the following command:

SELECT dbo.CountSalesOrderHeader()

It displays result of your SQLCommand query in SQL Server Management Studio.

You can verify the result by running the SQLCommand query separately in SQL Server Management Studio.

Removing the " CountSalesOrderHeader" User Defined function:

When you are finished running the sample user defined function, you can remove the function and the assembly from your test database.

First, remove the function using the drop function command:

drop function CountSalesOrderHeader

Once the function has been dropped, you can remove the assembly containing your sample code:

drop assembly FirstUDScalarFunction

This shows how you can write your own CLR stored procedures. Similarly, you can create CLR user defined types, CLR triggers, CLR Table-Valued functions and CLR user-defined aggregate functions.