The common language runtime (CLR) is a crucial component of the Microsoft .NET Framework. It serves as the execution environment for all .NET Framework code, including managed code. The CLR provides various functions and services required for program execution, such as just-in-time (JIT) compilation, memory management, type safety enforcement, exception handling, thread management, and security.

For SQL Server users and application developers, CLR integration allows the creation of stored procedures, triggers, user-defined types, and user-defined functions using any .NET Framework language, such as Microsoft Visual Basic .NET and Microsoft Visual C#. This integration provides benefits like cross-language integration, code access security, object lifetime management, and debugging and profiling support.

Creating a SQLCLR Project

To create a SQLCLR project, you need both Visual Studio and SQL Server installed on your system. Open Visual Studio and go to File -> New -> Project. In the New Project dialog box, select Visual C# -> Database -> SQL Server. Provide a project name, location, and solution name, and click OK.

In the dialog box that appears, select the server name and database name, and click the Test Connection button. Once the test connection is successful, click OK. If a database connection is already added by another project, you may need to select one of the existing database connections or add a new one as a reference to this project.

Writing a Simple CLR Stored Procedure

Once the project is created, right-click on the project in the Solution Explorer and click Add -> New -> Stored Procedure. Give your stored procedure a name and click OK. Write the code for your stored procedure, which can be a simple text message or a more complex database query.

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. The method uses the SqlContext and SqlPipe classes to output a simple text message and execute a database query. This method can be declared as a stored procedure in SQL Server and run in the same manner as a Transact-SQL stored procedure.

Compiling and Running the Stored Procedure

To compile the “Hello World” stored procedure, first, build the project and correct any compilation errors. Then, create an assembly file that can be loaded into SQL Server. SQL Server installs the necessary .NET Framework redistribution files by default, including the command-line compiler for Visual C# programs (csc.exe).

To compile the stored procedure, you need to modify your path variable to point to the directory containing csc.exe. The path will look something like: C:\Windows\Microsoft.NET\Framework\(version). Once you have added the .NET Framework directory to your path, you can compile the stored procedure with the following command:

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

After compiling the stored procedure, you can test it in SQL Server. Open SQL Server Management Studio and create a new query, connecting to a suitable test database. Copy the HelloWorld.dll file created in the previous step to the C drive (or provide the full path to your DLL file in the following command).

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

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

Once the assembly has been created, you can access the HelloWorld method by using the CREATE PROCEDURE statement. Call your stored procedure “hello”:

CREATE PROCEDURE hello
AS
EXTERNAL NAME HelloWorld.FirstCLRProc.HelloWorld

After creating the procedure, you can run it like any other stored procedure in SQL Server:

EXEC hello

The result of your SQL query will be displayed in SQL Server Management Studio, and any text messages sent using the SqlContext.Pipe.Send method will be displayed in the message window.

Writing a Simple CLR User-Defined Function

In addition to stored procedures, you can also write CLR user-defined functions in SQL Server. These functions can return a single value from the database. To create a CLR user-defined function, right-click on the project in the Solution Explorer and click Add -> New -> User-Defined Function.

Give your user-defined function a name and write the code for it. Here’s an example of a scalar function that returns the maximum SalesOrderId from the Sales.SalesOrderHeader table:

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();
        }
    }
}

Build the project and correct any compilation errors. Compile the user-defined function with the following command:

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

Copy the FirstUDScalarFunction.dll file from your project directory to the C drive (or provide the full path to your DLL file in the following command). Add the following Transact-SQL statement to your query:

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

Once the assembly has been created, you can access the FirstUDScalarFunction function by using the CREATE FUNCTION statement. Call your function CountSalesOrderHeader:

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

After creating the function, you can run it like any other user-defined function in SQL Server:

SELECT dbo.CountSalesOrderHeader()

The result of your SQL query will be displayed in SQL Server Management Studio.

Conclusion

CLR integration in SQL Server allows developers to leverage the power of the .NET Framework and write stored procedures, triggers, user-defined types, and user-defined functions using any .NET Framework language. This integration provides flexibility, cross-language integration, and access to additional functionality not available in Transact-SQL alone.

By combining the strengths of SQL Server and the .NET Framework, developers can create powerful and efficient database solutions that meet their specific needs.