Using CLR With SQL Server 2012

For many years writing business logic has been technology and software specific, especially in terms of databases.

For example, assume you want to create a Stored Procedure or any other database object that requires complex SQL code. The only way out that had been known to programmers was to code such logic in a database and the calling code in the programming language.

This approach is still very popular, but there is an easy way that allows C# programmers to take control in their hands and code all the database oriented objects like Stored Procedures, functions, triggers and so on in the .NET language of their; choice for example, C#.

In this article, I'll cover the following:

• Introduction to SQL CLR
• Choosing Between T-SQL and SQL CLR
• Enabling SQL CLR Integration
• Creating a SQL CLR Stored Procedure
• Deploying a SQL CLR Stored Procedure into SQL Server
• Executing the SQL CLR Stored Procedure

Introduction to SQL CLR

SQL CLR is a tiny version of the .NET CLR that is integrated into the SQL Server 2005 and onwards. The existence of CLR in SQL Server allows the C# programmers and other .NET compliant language programmers to write database specific business logic in a programming language like C# instead of T-SQL. Let's understand what type of objects a programmer can create with SQL CLR integration.

The following are objects that can be created using SQL CLR:

• The following types of database objects can be created with SQL CLR Integration:
• Stored Procedures
• User defined aggregates
• Triggers
• User defined types

Choosing Between T-SQL and SQL CLR

It became obvious that we have two choices to implement the same functionality, but based on your scenario and requirements one can be advantageous over the other one. A few important points that will help you to decide what you may want to choose and in what situation are:

• T-SQL is best used to perform declarative, set-based operations (select, insert, update and delete).
• T-SQL also has a procedural capability, in other words can perform procedural operations like While and so on but when it comes to feature rich or more complex logic T-SQL is not the best choice. In such a scenario, SQL CLR with C# allows the programmer to have a better control on functionality.
• T-SQL is interpreted whereas SQL CLR is compiled. Hence; interpreted code is slower than compiled procedural code.
• Before SQL CLR code can be executed, it requires the CLR to be loaded by the SQL Server. Whereas T-SQL does not incur any such overhead.
• When any T-SQL code executes it shares the same stack frame in memory; whereas, every SQL CLR code requires its own stack frame hence result in larger memory allocation but better concurrent performance.
• T-SQL is composed of libraries full of data-centric functions and hence better suited to set-based operations. Whereas SQL CLR is better suited to recursive, mathematical, string manipulation types of operations.

Enabling SQL CLR Integration

After creating a database object using C#, you must enable the SQL CLR in your SQL Server 2012 where you would like to either use it or deploy it. By default, this feature is turned off (config_value is set to 0); to enable it (config_value is set to 1), use the following procedure:

1. Open SQL Server 2012 Management Studio; connect using Windows or SQL Authentication based on your setup type.
2. Once connected, click on the New Query button and it will open a query window. Enter the following text in the query window and notice the value of the "config_value" column is:
   
   
   Figure 1-1: Showing default behavior (disabled) of SQL CLR
   
   
3. Next, we need to enable the SQL CLR, to do so modify the code to appear as shown in Figure 1-2 and it will enable the SQL CLR integration.
   
   
   Figure 1-2: Showing SQL CLR enabled
   
   
4. Now your SQL Server is ready to execute the database objects that are built using the C# programming language unlike T-SQL.

Creating SQL CLR Stored Procedure

Microsoft Visual Studio 2012 provides the project template and class files for various SQL Server objects, like Stored Procedures, triggers, functions and so on that you can code in C# as an assembly, in other words Dynamic Link Library (DLL).

Try It Out: Creating a SQL CLR Stored Procedure using C#

1. In this exercise, you'll create a SQL Stored Procedure by adding a SQL CLR C# Stored Procedure item template into the SQL Server database project. The SQL CLR C# Stored Procedure we are creating will help you insert currency data into an AdventureWorks.Sales.Currency table.
   
   Create a new Windows Forms Application project. When Solution Explorer opens, save the solution as shown in Figure 1-3.
   
   
   Figure 1-3:Shwoing SQL Server Database Project template
    
2. This will load an empty project, in other words without any .cs class file in it as shown in Figure 1-4.
   
   
   Figure 1-4: Empty Project listed in Solution Explorer
    
3. Right-click the project, choose "Add New Item", and in the "Add New Item" dialog from the SQL CLR C# tab, select "SQL CLR Stored Procedure" and name it as shown in SQLCLRStoredProcedure in Figure 1-5. Click "Add".
   
   
   Figure 1-5: Adding a SQL CLR C# Stored Procedure as New Item to the project
    
4. Your Visual Studio environment will now look as shown in Figure 1-6.
   
   
   Figure 1-6: Showing Visual Studio after adding the SQL CLR C# Stored Procedure
    
5. Replace the code inside the StoredProcedure class with the code listed under Listing 1-1.
   
   Listing 1-1. SQLCLRStoredProcedure.cs
   
   [Microsoft.SqlServer.Server.SqlProcedure()]
   public static void InsertCurrency_CS(SqlString currencyCode, SqlString currencyName)
   {
   SqlConnection conn = null;
   
   try
   {
   conn = new SqlConnection(@"server = .\sql2012;integrated security = true;database = AdventureWorks");
   
   SqlCommand cmdInsertCurrency = new SqlCommand();
   cmdInsertCurrency.Connection = conn;
   
   SqlParameter parmCurrencyCode = new SqlParameter("@CCode", SqlDbType.NVarChar, 3);
   SqlParameter parmCurrencyName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
   
   parmCurrencyCode.Value = currencyCode;
   parmCurrencyName.Value = currencyName;
   
   cmdInsertCurrency.Parameters.Add(parmCurrencyCode);
   cmdInsertCurrency.Parameters.Add(parmCurrencyName);
   
   cmdInsertCurrency.CommandText =
   "INSERT Sales.Currency (CurrencyCode, CurrencyName, ModifiedCurrencyDate)" +
   " VALUES(@CCode, @Name, GetDate())";
   
   conn.Open();
   
   cmdInsertCurrency.ExecuteNonQuery();
   }
   
   catch (SqlException ex)
   {
   SqlContext.Pipe.Send("An error occured" + ex.Message + ex.StackTrace);
   }
   
   finally
   {
   conn.Close();
   }
   }
    
6. Save the project and build the solution. After successful build it will produce a .dll under the project location's \bin\debug folder.

How It Works

Since this is a Stored Procedure coded in C#, and would be inserting curreny data into the AdventureWorks.Sales.Currency table that has three columns out of which we will be passing values for two as input parameters.

[Microsoft.SqlServer.Server.SqlProcedure()]
public static void InsertCurrency_CS(SqlString currencyCode, SqlString currencyName)

The most important part of any database application is creating a connection and command:

SqlConnection conn = null;
conn = new SqlConnection(@"server = .\sql2012;integrated security = true;database = AdventureWorks");
SqlCommand cmdInsertCurrency = new SqlCommand();
cmdInsertCurrency.Connection = conn;

Once we have the connection and command objects we need to set the parameters that this Stored Procedure will be accepting:

SqlParameter parmCurrencyCode = new SqlParameter("@CCode", SqlDbType.NVarChar, 3);
SqlParameter parmCurrencyName = new SqlParameter("@Name", SqlDbType.NVarChar, 50);

parmCurrencyCode.Value = currencyCode;
parmCurrencyName.Value = currencyName;

cmdInsertCurrency.Parameters.Add(parmCurrencyCode);
cmdInsertCurrency.Parameters.Add(parmCurrencyName);

After having the parameters we will set the INSERT starement that will perform the actual task, but since we chose only two parameters for this Sales.Currency table, the third column, that is a date column, will be passed the GetDate() function.

cmdInsertCurrency.CommandText =
"INSERT Sales.Currency (CurrencyCode, CurrencyName, ModifiedCurrencyDate)" +
" VALUES(@CCode, @Name, GetDate())";

Next, we will open the connection and execute the command:

conn.Open();
cmdInsertCurrency.ExecuteNonQuery();

The most important point is to remember that this code will actually be invoked from inside SQL Server Management Studio, and so exception handling's catch block will need extra attention as in the following:

catch (SqlException ex)
{
SqlContext.Pipe.Send("An error occured" + ex.Message + ex.StackTrace);
}

The SqlContext class allows you to invoke a function to show an error in SQL Server's Error window.

Deploying SQL CLR Stored Procedure into SQL Server

• Once an assembly of SQL CLR C# type for a specific type of database object is created, it needs to be deployed in SQL Server. Once deployed SQL Server uses it like any other.

T-SQL based database object

Try It Out: Deploying SQL CLR C# Stored Procedure in SQL Server

In this exercise, you'll deploy the created assembly into the SQL2012Db database, and upon execution this will insert currency into the AdventureWorks.Sales.Currency table.

1. Open SQL Server 2012 Management Studio and connect to the SQL Server.
2. Select the SQL2012 databbase (if you don't have this database then you can use any database of your choice) and click on "New Query", it will open a new blank query window.
3. In the opened query window put the code as shown in Listing 1-2.
   
   Listing 1-2. Deploying the assembly into SQL Server
   
   Create Assembly SQLCLR_StoredProcedure
   From
   --change this path to reflect your database assebmly location
   'C:\VidyaVrat\ SQLCLRArticle.dll'
   WITH PERMISSION_SET = UNSAFE
   GO
   
   CREATE PROCEDURE dbo.InsertCurrency_CS
   (
   @currCode nvarchar(3),
   @currName nvarchar(50)
   )
   AS EXTERNAL NAME SQLCLR_StoredProcedure.StoredProcedures.InsertCurrency_CS;
    
4. Once the code is added, click "Execute" or press F5. It should execute the command successfully. Then go to the Object Browser, select your deSQL2012DB, right-click and choose "Refresh". It will show the objects under Programmability and Assemblies in the Object Browser as shown in Figure 1-7.

Figure 1-7: Deploying assembly and showing objects in Object Browser in the SQL Server

How It Works

This entire deployment process is a two-step process. First we need to register an assembly (that we created in C#) with our own given name inside SQL Server.

Create Assembly SQLCLR_StoredProcedure
from
'C:\VidyaVrat\SQLCLRArticle.dll'
WITH PERMISSION_SET = UNSAFE
GO

The Permission Set property allows the user to execute assemblies with specific code access permissions. UNSAFE enables this assembly to have unrestricted access within SQL Server.

Second, we need to create the Stored Procedure that will basically invoke the Stored Procedure we have created of C# assembly.

CREATE PROCEDURE dbo.InsertCurrency_CS
(
  @currCode nvarchar(3),
  @currName nvarchar(50)
)
AS EXTERNAL NAME SQLCLR_StoredProcedure.StoredProcedures.InsertCurrency_CS;
GO

The name used in the Create Procedure is the name (InsertCurrency_CS), we gave to the function in the C# class (refer to Listing 1-1). Next we set the input parameters that were being passed to the C# function (refer to Listing 1-1).

The External Name is actually in the syntax of <SQL registered assembly>.<CS class name>.<CS function name> and so it turns out to be:

SQLCLR_StoredProcedure.StoredProcedures.InsertCurrency_CS

Refer to Listing 1-1 and 1-2 for the class name, and assembly name and so on that are in use here.

Executing the SQL CLR Stored Procedure

After deploying the assembly and creating a Stored Procedure, we are ready to execute this procedure from SQL 2012 and insert currency into the AdventureWorks.Sales.Currency table.

Try It Out: Executing the SQL CLR Stored Procesure

In this exercise, you will execute the InsertCurrency_CS Stored Procedure.

1. Open SQL Server Management Studio (if not already open), select SQL2012db and click "New Query" window.
2. In the query window add the code as shown in Listing 1-3 to execute the procedure and add a currency.

Listing 1-3.Executing Stored Procedure to Insert Currency

Exec dbo.InsertCurrency_CS 'ABC','United States of America'

Note: we need to specify a unique value for the currency code, for example I used "ABC". I know there is no such curreny for the United States. But If you try to enter USD or a duplicate value then you will receive a System. Adding a duplicate value to the Sales.Currency table for CurrencyCode column. You will get an exception as shown in Figure 1-8.


Figure 1-8: Showing Argument Exception in the event of duplicate entry

How It Works

As coded in the C# under Listing 1-1, we have an Insert statement that accepts two input parameters and GetDate() for the ModifiedCurrencyDate column will be automatically passed on each execution.

cmdInsertCurrency.CommandText =
"INSERT Sales.Currency (CurrencyCode, CurrencyName, ModifiedCurrencyDate)" +
" VALUES(@CCode, @Name, GetDate())";

Hence, the Stored Procedure execution statement will be as shown in Listing 1-3, that is passing values for both the input parameters CurrencyCode and CurrencyName.

Exec dbo.InsertCurrency_CS 'ABC','United States of America'