Working with CLR Objects in SQL Server 2005 or Higher: Part I

• Download source code [C#] - 4.51 KB
• Download source code [VB.NET] - 5.72 KB

Introduction

This article is part 1 of a two parts series. If you want to read the the second part, click here.

I have never thought of writing an article on database. I am very thankful to Abhijit Jana[^], a CodeProject MVP and my office colleague, who has always encouraged me to write articles on new topics. I am dedicating this article to all of those who like my articles.

Also I am not a DBA, so please let me know when I do mistakes so that I could update the article and enrich it further in future.

This is a series of articles, as CLR is a huge topic. Also, I am trying to provide sample applications in both C# and VB.NET format so that everyone gets the benefits from this article.

Table of Contents

• Overview
• Background
• Advantages
• Types of Objects Supported
• Creating your First SQL Server Project
• Deploy your Project Automatically
• Deploy your Project Manually
• Dropping Objects Permanently
• Testing the SQL Server Project
• Conclusion
• References
• History

Overview

SQL Server 2005 introduces a new way of writing database objects for .NET developers. In addition to Extended Stored Procedures, we may now use CLR Stored Procedures to fetch or store data, write Triggers, User Defined Functions etc., and get the full functionality of the powerful .NET framework through SQL Server.

For example, suppose you want to create an XML parser which parses data and gives the output. Let us think about the options we will be having if we want to do this in the database.

1. You can write a normal Stored Procedure to handle this complex logic of creating an XML output.
2. Use OpenXML, to read the XML; open cursor to read only the data within the output table from OpenXML.
3. Use manual string parsing techniques.
4. Use the XML data type introduced in SQL Server 2005.

From the above few techniques available, the best way is to use the XML data type. If you have used XML data type ever, you might have already got the flavour of CLR types. XML is a serialisable data type which you can use in SQL Server 2005.

In this article, my intension is to make you understand how you can build your own data types, objects etc., in SQL server.

Background

I think database is the ideal place to store business logic. We can create Stored Procedures, functions etc., to create business logic so that we can use these interfaces from our application and get data stored without creating insert/update statements. We can even check user privileges if session, auth tokens, and everything is stored inside the database, just before running a query.

Thus, if we can write complex database logic using .NET classes, our task will be the easiest, and also we could get all the benefits available to .NET classes within our Stored Procedures.

Advantages

Extended Stored Procedures have already been there with earlier versions of databases. These objects can do anything in the system like normal executables. The main advantage of CLR over an extended Stored Procedure is:

• CLR Stored Procedures are intended to work within the Managed Environment. So all the benefits of the Managed Environment (like Garbage Collection) are there with those objects.
• We can utilize the advantages of the huge classes available with the .NET library.
• Both of these use database memory, so no new process will be created to run your code.

When we call these objects, it first goes to the assembly registered to the database and find the class associated with the calling object. Then, it calls that object with the context of the database connection.

From the above diagram, you can see the main assembly holds all the object within itself. When an external world calls (which in this case is the application object) those objects, SQL Server gets the definition of the object in the database. Then, it reads the assembly associated with the current object and calls the method automatically.

Each object in the SQL database including the .NET CLR objects share the memory of SQL Server database engine. So, if we call a CLR object directly, it will not relogin to the database, rather it will go on using the existing login connection. We will discuss about this later on.

Types of Objects Supported

The CLR of SQL Server 2005 supports five types of objects:

• Stored Procedures
• User Defined Functions
• User Defined Aggregate Functions
• Triggers
• User Defined Data Types

We can use each of them when required and import them to the database engine. Before we discuss each of them, first, let us create a sample application in Visual Studio.

Creating your First SQL Server Project

Start your Visual Studio, and choose New Project. You will be provided with the New Project dialog box. Choose DataBase from the left hand side tree, and select SQL Server Project. Choose your desired location and click OK.

A series of message boxes will appear. First appears:

Here, you can choose the database connection. You can also choose to add a new reference to add a new database connection. This connection will be used by Visual Studio to deploy your application. After that, two warning message boxes appear:

Just be affirmative to these message boxes if you are using a test database connection.

After you finish these steps, a new project will be created with a folder named Test and a SQL file within it. We will come to this later on.

In Solution Explorer, you will find a Stored Procedure in file is already created. You can delete the file and select a new object based on your requirements.

Let us choose the Stored Procedure first.

You will be prompted with a new dialog box to name the Stored Procedure. Name it whatever you like. In my sample, I have created a class with the name MyFirstCLRSP. Let's write the code for it below:

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void MyFirstCLRSP()
    {
        SqlPipe p;    // Pipe object to send data to Database Engine
        p = SqlContext.Pipe;
        p.Send("Not Implemented!!");

    }
};

After you compile this, you can run this in the database either manually or automatically.

Deploy your Project Automatically

If you want to do this automatically, just right click on Solution Explorer - > Deploy Solution. It will be deployed automatically to the database.

Deploy your Project Manually

To do this manually to the database, you have to follow these steps:

1. First you need to build the application to create a DLL.
2. Register your assembly to SQL Server using the code below:
SQL

CREATE ASSEMBLY MyFirstCLRSP 
FROM 'C:\CLR\CLRproject\CLR\bin\Debug\SqlClassLibrary.dll' 
-- REPLACE this with your path
WITH PERMISSION_SET = SAFE;   -- DEFAULT as most restrictive

3. Create a new Stored Procedure that is present in the assembly to access it directly using the code below:
SQL

CREATE PROCEDURE ClrDemo
AS
EXTERNAL NAME MyFirstCLRSP.StoredProcedures.MyFirstCLRSP

After you create the Stored Procedure, just run it normally.

EXEC ClrDemo

You will see a text printed as: Not Implemented!

Now, let us talk about the PERMISSION_SET option while creating assemblies:

• PERMISSION_SET: While registering the assembly within the database, you can use PERMISSION_SET=SAFE; it is the default option of the permission. This is the most restrictive option. If we set it as SAFE, which I did in the above code, then the code within the whole assembly will run with Safe permission, i.e., the code cannot access external system resources such as files, the network, environment variables, or the Registry.
• EXTERNAL_ACCESS: It allows assemblies to access external files, network, environment variables, Registry etc. Only SQL Server logins with EXTERNAL_ACCESS permissions can create EXTERNAL_ACCESS assemblies.
• UNSAFE: This permission allows unrestrictive access to all the resources to the system, both from inside and outside of SQL Server. Code from UNSAFE assemblies can even call unmanaged code.

Note: You must have a sysadmin role to create UNSAFE Assemblies.

Dropping Objects Permanently

To drop an object, just execute:

DROP ASSEMBLY MyFirstCLRSP

This will drop the assembly with all the files that it has created within SQL Server. Dropping an assembly removes an assembly and all its associated files, such as source code and debug files, from the database.

We can also specify WITH NO DEPENDENTS which will delete only the assembly, but not any dependent files associated with the assembly.

Note: To drop an assembly, you need to have ownership of the assembly, or CONTROL permission on it.

To Test a SQL Server Project

First of all, you must remember that SQL Server CLR debugging is not possible for Visual Studio Express or Professional Editions. Only Team System Visual Studio can debug SQL Server CLR Stored Procedures.

To start with debugging, you must enable SQL Server debugging for the current connection.

1. Open Server Explorer.
2. In Server Explorer, right-click on the connection you want to debug and choose Allow SQL CLR Debugging.
3. A message box will appear like: "SQL CLR debugging will cause all managed threads on the server to stop. Do you want to continue?".
4. If you are not in a production server, then click Yes to enable debugging.

Open Test\Test.sql file. Write EXEC CLRDEMO in it, and click on RUN.

You can also put breakpoints in the test script. The result will be displayed in the Output window.

Conclusion

This is the primary introduction to CLR Stored Procedures. I will discuss more about the types of objects associated with CLR assemblies in the next part of the article. Please feel free to comment on the article.

We will continue with more in another article as this is getting bigger. You can see the next part of this article here.

References

• http://msdn.microsoft.com/en-us/library/84b1se47(VS.80).aspx
• http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1

History

• First edition: August 02, 2009.