What is CLR?

The Common Language Runtime (CLR) is the engine behind the .NET Framework that handles the execution of all managed application code. It plays an intermediation role between the operating system and .NET applications. Applications developed in the .NET platform don’t directly interact with the operating system. Rather, they talk to the CLR and the CLR manages/handles the execution of the applications.

When we write a program using any of the .NET languages like C#, VB or C++, the source code doesn’t get compiled to machine code. Each language specific compiler (VB compiler, C# compiler etc) converts the source code to an intermediate language code which is known as IL code. The IL code then is converted to machine specific code by the CLR engine at runtime.

The CLR is responsible for managing memory allocation, starting and terminating threads and processes and enforcing security policy at runtime.

Figure 1: Common Language runtime (CLR) architecture

CLR Integration in SQL Server 2005

We all are familiar with T-SQL to write database objects such as stored procedures, triggers, functions, cursors etc. It has always been difficult to write database objects with more complex logic like array operations, complex math calculations in traditional T-SQL. On the other hand, it becomes an easy task to attain the same functionality in any server side language like C#, or VB.NET. If we could write the database objects in any of our server side languages and run them on SQL server then that would definitely solve the problem of writing database objects with complex logic. To accomplish this, SQL Server 2005 has integrated the Common Language Runtime (CLR), which is hosted within the SQL Server database Engine. It provides the ability to create database objects using .NET languages like C#, VB.NET etc. These objects include stored procedures, triggers, and functions and support logic and features that are not available in native T-SQL.

The integration of the CLR with SQL Server doesn’t replace T-SQL at all. It extends the capability of SQL Server in several important ways. While T-SQL, the existing data access and manipulation language, is appropriate to accomplish set-oriented data access operations, the integration of the CLR with SQL Server 2005 brings with it the ability to create database objects using modern object-oriented languages with the existing class support from the .NET framework. This helps to create database objects with more complex logic, better computation capabilities, OOP’s concepts, structured error handling mechanisms, thus facilitating code reuse.

CLR integration in SQL server 2005 supports the following object types:

• Stored procedures
• User-defined functions (UDF)
• Triggers
• User-defined (data) types (UDT)
• User-defined aggregates (UDA)

Advantages of CLR Integration in SQL Server 2005

• The advantages of CLR integration in SQL server 2005 are:
• Rich programming model: .NET languages are very productive and useful since they provide powerful features such as object-oriented programming, structured exception handling and advanced conditional constructs which are not available in Transact-SQL.
• Common and Enhanced Development Environment: Developers can use an enhanced development environment to code and debug the database objects and scripts, rather than the traditional approaches provided by SQL Server.
• Access to the .NET Framework library: Access to the .NET Framework’s base class library enables us to perform tasks such as encryption, string manipulation, calls to Web services, and to work with the file system and many other tasks that are difficult or impossible with Transact-SQL.
• Improved safety and security: Managed code runs in a CLR environment hosted by the SQL Server database engine. Managed code uses Code Access Security (CAS) code links, and application domains to prevent assemblies from performing certain operations. SQL Server 2005 uses CAS to help secure the managed code and prevent compromises of the operating system. This provides a safer and more secure system than with Transact-SQL.
• Object lifetime management: CLR imposes automatic garbage collection, destructors, and lease policies simplify management.
• Performance and scalability: Since CLR code is managed, in some cases this delivers improved performance over Transact-SQL.

Enabling CLR Integration in SQL Server

By default CLR integration is disabled in SQL server2005. To be able to run the CLR database objects on SQL server we have to enable the CLR integration feature.

Open up a query window in SQL Server Management Studio and execute the following code:

To disable the CLR on SQL server execute the following in the query window of SQL Server Management Studio:

Supported .NET Framework Libraries for CLR Integration

SQL Server has a list of supported .NET Framework libraries. These supported libraries do not need to be explicitly registered on the server. In fact, SQL Server loads them directly from the Global Assembly Cache (GAC) when required.

The libraries/namespaces supported by CLR integration in SQL Server are:

• System
• System.Data
• System.Xml
• System.Security
• Microsoft.Visualbasic
• System.Web.Services
• System.Configuration
• System.Deployment
• System.Transactions
• mscorlib

Unsupported libraries can still be called from your CLR database objects. The only thing you have to do is register the unsupported library in the SQL Server database, using the CREATE ASSEMBLY statement, so that it can be used in your code.

Develop and deploy CLR objects using Visual Studio 2005

1. Open Visual studio and create a new project, Select Project Type as Database and the SQL Server Project as template, give it a name (for example, MyCLRDatabaseObject), solution name and location. If you want to create it in any specific location then you can set that in the location field, otherwise it will create the project in the default location.

Figure 2: New database project creation

2. A dialog will pop up to set the new database references. Select the server name from the list and set the database authentication. Once you select the server name from the list then the database connect section will be enabled. Select a database name from the list where you want to deploy your object. Also you can test the connection by clicking on the Test Connection button.

Figure 3: Setting Database Reference

3. Once you are done with adding database references, the program will prompt you to enable debugging for SQLCLR for this connection. Choose Yes.

Figure 4: Enable CLR Debugging Pop up window

4. In the Solution Explorer pane right click on Project, and then click on Add; you will be shown a list of objects in the menu. Select the desired object from the menu list. Alternatively you can select Existing Item and then the desired object template from the template window. We selected StoredProcedure from the Template list for our sample application.

Figure 5: Add Database Templates

5. Upon clicking the object name from the menu list, the following template window will be opened. Select the template type and rename it as per your requirements; then click on the Add button. Here we have selected the Stored Procedure template and renamed it to MyFirstCLRstoredProcedure.vb

Figure 6: Choose Template

6. When we click on the Add button the program will generate the following code snippet in the page.

Listing 1: Code generated by visual studio

The class contains a single static method that will become a CLR stored procedure. The class is defined as public to denote that other classes can instantiate objects of this type. In the above code snippet MyFirstCLRStoredProcedure is the function which is going to be a CLR stored procedure. Methods that will become database objects must be declared as public static in C#, or Public Shared in Visual Basic. The keyword public means that they are accessible from outside and static (or shared) means that they behave like procedures (not like methods of objects that have state).

We have added following lines of code.

Listing 2: Sample class file

In the above code snipet we have opened a connection of type context connection. It is possible to have user credentials passed, but such a connection would not be part of the caller transaction. In fact, it would not be able to access to temporary tables created on the caller. Also, it would be slower because of the additional layer the commands and results would have to pass through. So it is advisable to have a context connection in a CLR object.

You can open the context connection by simply using “Context Connection = true” instead of the regular connection string.

I have written a simple SQL query in the Command Text section. This should return all the rows present in the Employee table. We retrieve the data from the Table into a Data Reader and then return those data rows from the Reader through SqlPipe using the Send method.

7. We will now create the stored procedure assembly. Select Build >> Build MyCLRDatabaseObject from the main menu. If there is no error during compilation, then Visual Studio will create a MyCLRDatabaseObject.dll assembly in the Bin folder.

8. Deploy the stored procedures by selecting Build >>Deploy MyCLRDatabaseObject from the menu. If you get a Deploy Succeeded message in the bottom section then that means the CLR database object has been successfully deployed to SQL server.

9. Open the Query window in SQL Server Management Studio and execute the Database object against the respective database.

We can check our CLR stored procedure MyFirstCLRStoredProcedure in the management studio by executing it.

Figure 7: Stored procedure execution

Here we get the result from the database table. We can have a number of database objects inside a single class and then deploy the assembly to the server at once. This would definitely save a good amount of time over deploying each one independently.

10. You can create the table and check the stored procedure at your system.

Run the following scripts to create the tables in your SQL server.

Listing 3: Employee Table Creation using T-SQL code

To insert some dummy data in to the Employee table, execute the following script:

Listing 4: Insert sample data in to employee table using the Query

Develop and Deploy CLR objects without Visual Studio 2005

We can also develop and deploy the CLR Database objects without the help of Visual Studio. We only require the .NET framework which contains a compiler that can be used to deploy the CLR object.

1. Open Notepad or any other editor and copy the following code. Then save the file as MyFirstCLRStoredProcedure.vb.

Listing 5: MyFirstCLRStoredProcedure.vb class file

2. The .NET Framework ships with the C# (csc.exe) and the Visual Basic .NET (vbc.exe) command line compilers. You can find these compilers in the folder where the .NET Framework is installed. These compilers can be found in C:\WINDOWS\Microsoft.NET\Framework\Version Name.

We can compile our class file MyFirstCLRStoredProcedure.vb using .NET framework SDK. It should be located at:

Start>>All programs>>Microsoft .NET Framework SDK v2.0>>SDK Command Prompt.

Write the following command in the SDK command prompt and hit enter.

Here setting the /target switch to library notifies the compiler to create a .dll file. E:\CLRObject\MyFirstCLRStoredProcedure.vb is the physical location of the class file.

Figure 8: MyFirstCLRStoredProcedure.vb Class compilation using .NET Framework SDK

Since the source code is in Visual Basic we have used the above command. If the source code is written in C# then you have to use the following command:

Upon running successfully , the above command will generate a dll in the same folder where your source code is present.

Figure 9: Dll creation in same folder

3. To use the assembly we need to register it in SQL Server using the CREATE ASSEMBLY statement. Open the Query window in Management Studio in SQL server and run the following T-SQL code against the respective database.

Here we have set MyCLRObject as the name of the assembly and E:\CLRObject\MyFirstCLRStoredProcedure.dll as the physical location of the dll file. The WITH PERMISSION_SET clause controls the type of access to external resources from the assembly.

There are three options for PERMISSION_SET

• SAFE: This is the default option. If we set this option the assembly will not be able to access external resources. It should be used for assemblies that perform only local operations such as data calculations or management.
• EXTERNAL_RESOUCES: This option will allow assembly code to access some external resources like files, environment variables, registry, and network.
• UNSAFE: This option will allow code access to any resource both on the server and outside the server. This option is required to make calls to unmanaged libraries.

Figure 10: Assembly MyCLRObject creation

SQL Server does not allow registration of more than one version of an assembly with the same name, so you must assign a unique name to your assembly. If you have an old version assembly running in SQL Server then you must drop that assembly and then create a new one.

4. Once our assembly is registered on SQL server, then we can create database objects. Run the following T-SQL code on the Query window in Management Studio in SQL server against the respective database to create a stored procedure.

Here MyFirstCLRStoredProcedure is the stored procedure name and the EXTERNAL NAME contains a reference to the specific Assembly, Class and Method name in the format as Assembly_Name.Class_Name.Method_Name.

Since we have registered the assembly as MyCLRObject, and in the class file we have Public Class as StoredProcedures and the Shared Method as MyFirstCLRStoredProcedure, so our EXTERNAL NAME referenced to MyCLRObject.StoredProcedures.MyFirstCLRStoredProcedure.

5. When we execute the stored procedure in SQL Server we get the following results.

Figure 11: Stored proedure Execution

Summary

This article described the methodology by which we can develop a CLR database object with the help of Visual Studio2005 or without it. It is not advisable to create CLR Objects for each and every task; it would be beneficial to use CLR database objects where we require complex programmatic logic, or require the .NET Framework's base class library to accomplish a task. If we are working on any relational data managing or any set-based operations then T-SQL is the best.

About Rupesh kumar Nayak

Rupesh is working in a leading IT Company in India as a software engineer, he started his journey from scripting language PHP and MySQL and later on moved to Microsoft's .Net technology.His area of expertise including PHP, ASP,VB.net, C#.Net, XML, HTML, JavaScript, AJAX, SQLReporting2002, SQLRe... This author has published 2 articles on DotNetSlackers. View other articles or the complete profile here.

---

Discussion

Subject	Author	Date
Good One	Soumya Patnaik	9/3/2008 7:49 AM
Useful and easy to understand .... Keep on writing such articles Reply | Permanent link

---

Please login to rate or to leave a comment.