SQL Server supports the Common Language Runtime (CLR), which allows you to develop different database objects (stored procedures, user-defined functions, triggers, user-defined aggregates, and user-defined types) using C# and Visual Basic. CLR also allows you to execute these database objects using the common run-time system.
Example 8.12 shows how you can enable the use of CLR with the sp_configure system procedure.
use sample;
EXEC sp_configure 'clr_enabled',1
RECONFIGURE
Code language: PHP (php)
To implement, compile, and store procedures using CLR, you have to execute the following four steps in the given order:
- Implement a stored procedure using C# (or Visual Basic) and compile the program, using the corresponding compiler.
- Use the CREATE ASSEMBLY statement to create the corresponding executable file.
- Store the procedure as a server object using the CREATE PROCEDURE statement.
- Execute the procedure using the EXECUTE statement.
Figure 8-1 shows how CLR works. You use a development environment such as Visual Studio to implement your program. After the implementation, start the C# or Visual Basic compiler to generate the object code. This code will be stored in a .dll file, which is the source for the CREATE ASSEMBLY statement. After the execution of this statement, you get the intermediate code. In the next step you use the CREATE PROCEDURE statement to store the executable as a database object. Finally, the stored procedure can be executed using the already-introduced EXECUTE statement.

Examples 8.13 through 8.17 demonstrate the whole process just described. Example 8.13 shows the C# program that will be used to demonstrate how you apply CLR to implement and deploy stored procedures.
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public partial class StoredProcedures
{ [SqlProcedure]
public static int GetEmployeeCount()
{
int iRows;
SqlConnection conn = new SqlConnection(“Context Connection=true”);
conn.Open();
SqlCommand sqlCmd = conn.CreateCommand();
sqlCmd.CommandText = "select count(*) as 'Employee Count' " + "from employee";
iRows = (int)sqlCmd.ExecuteScalar();
conn.Close();
return iRows;
}
};
Code language: PHP (php)
This program uses a query to calculate the number of rows in the employee table. The using directives at the beginning of the program specify namespaces, such as System.Data. These directives allow you to specify class names in the source program without referencing the corresponding namespace. The StoredProcedures class is then defined, which is written with a [SqlProcedure] attribute. This attribute tells the compiler that the class is a stored procedure. Inside that class is defined a method called GetEmployeeCount(). The connection to the database system is established using the conn instance of the SQLConnection class. The Open() method is applied to that instance to open the connection. The CreateCommand() method, applied to conn, allows you to access the SqlCommand instance called sqlCmd.
The following lines of code
sqlCmd.CommandText =
"select count(*) as 'Employee Count' " + "from employee";
iRows = (int)sqlCmd.ExecuteScalar();
Code language: JavaScript (javascript)
use the SELECT statement to find the number of rows in the employee table and to display the result. The command text is specified by setting the CommandText property of the SqlCmd instance returned by the call to the CreateCommand() method. Next, the ExecuteScalar() method of the SqlCommand instance is called. This returns a scalar value, which is finally converted to the int data type and assigned to the iRows variable.
Example 8.14 shows the first step in deploying stored procedures using CLR.
csc /target:library GetEmployeeCount.cs
/reference:"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
MSSQL\Binn\sqlaccess.dll"
Code language: JavaScript (javascript)
Example 8.14 demonstrates how to compile the C# method called GetEmployeeCount() (Example 8.13). (Actually, this command can be used generally to compile any C# program, if you set the appropriate name for the source program.) csc is the command that is used to invoke the C# compiler. You invoke the csc command at the Windows command line. Before starting the command, you have to specify the location of the compiler using the PATH environment variable. At the time of writing this tutorial, the C# compiler (the csc.exe file) can be found in the C:\WINDOWS\Microsoft.NET\Framework directory. (You should select the appropriate version of the compiler.)
The /target option specifies the name of the C# program, while the /reference option defines the .dll file, which is necessary for the compilation process.
Example 8.15 shows the next step in creating the stored procedure. (Before you execute this example, copy the existing .dll file to the root of the C: drive.)
USE sample;
GO
CREATE ASSEMBLY GetEmployeeCount
FROM 'C:\GetEmployeeCount.dll' WITH PERMISSION_SET = SAFE
Code language: PHP (php)
The CREATE ASSEMBLY statement uses the managed code as the source to create the corresponding object, against which CLR stored procedures, UDFs, and triggers can be created. This statement has the following syntax:
CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ]
FROM { dll_file}
[WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE }]
assembly_name is the name of the assembly. The optional AUTHORIZATION clause specifies the name of a role as owner of the assembly. The FROM clause specifies the path where the assembly being uploaded is located. (Example 8.15 copies the .dll file generated from the source program from the Framework directory to the root of the C: drive.)
The WITH PERMISSION_SET clause is a very important clause of the CREATE ASSEMBLY statement and should always be set. It specifies a set of code access permissions granted to the assembly. SAFE is the most restrictive permission set. Code executed by an assembly with this permission cannot access external system resources, such as files. EXTERNAL_ACCESS allows assemblies to access certain external system resources, while UNSAFE allows unrestricted access to resources, both within and outside the database system.
The Database Engine also supports the ALTER ASSEMBLY and DROP ASSEMBLY statements. You can use the ALTER ASSEMBLY statement to refresh the system catalog to the latest copy of .NET modules holding its implementation. This statement also adds or removes files associated with the corresponding assembly. The DROP ASSEMBLY statement removes the specified assembly and all its associated files from the current database.
Example 8.16 creates the stored procedures based on the managed code implemented in Example 8.13.
USE sample;
GO
CREATE PROCEDURE GetEmployeeCount
AS EXTERNAL NAME GetEmployeeCount.StoredProcedures.GetEmployeeCount
Code language: PHP (php)
The CREATE PROCEDURE statement in Example 8.16 is different from the same statement used in Examples 8.6 and 8.8, because it contains the EXTERNAL NAME option. This option specifies that the code is generated using CLR. The name in this clause is a three-part name:
assembly_name.class_name.method_name
- assembly_name is the name of the assembly (see Example 8.15).
- class_name is the name of the public class (see Example 8.13).
- method_name, which is optional, is the name of the method, which is specified inside the class.
Example 8.17 is used to execute the GetEmployeeCount procedure.
USE sample;
DECLARE @ret INT
EXECUTE @ret=GetEmployeeCount
PRINT @ret
Code language: PHP (php)
The PRINT statement returns the current number of the rows in the employee table.