Click here to Skip to main content
Click here to Skip to main content

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

, 2 Aug 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Gives an introduction to CLR programming in SQL Server databases. This is the first part of a two-part article series.

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

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.

CLR1.JPG

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.

CLR2.JPG

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

coolimage1.JPG

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:

coolimage3.JPG

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:

coolimage4.JPG

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

coolimage5.JPG

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.

coolimage2.JPG

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.

coolimage6.JPG

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

coolimage7.JPG

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:
  3. 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
  4. Create a new Stored Procedure that is present in the assembly to access it directly using the code below:
  5. 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!

coolimage8.JPG

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

History

  • First edition: August 02, 2009.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Abhishek Sur
Architect
India India
Did you like his post?
 
Oh, lets go a bit further to know him better.
Visit his Website : www.abhisheksur.com to know more about Abhishek.
 
Abhishek also authored a book on .NET 4.5 Features and recommends you to read it, you will learn a lot from it.
http://bit.ly/EXPERTCookBook
 
Basically he is from India, who loves to explore the .NET world. He loves to code and in his leisure you always find him talking about technical stuffs.
 
Presently he is working in WPF, a new foundation to UI development, but mostly he likes to work on architecture and business classes. ASP.NET is one of his strength as well.
Have any problem? Write to him in his Forum.
 
You can also mail him directly to abhi2434@yahoo.com
 
Want a Coder like him for your project?
Drop him a mail to contact@abhisheksur.com
 
Visit His Blog

Dotnet Tricks and Tips



Dont forget to vote or share your comments about his Writing
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralMy vote of 5 PinmvpKanasz Robert25-Sep-12 22:44 
GeneralFantastic Work PinmvpAbhijit Jana2-Aug-09 10:01 
GeneralRe: Fantastic Work PinmemberAbhishek Sur2-Aug-09 10:22 
GeneralRe: Fantastic Work Pinmemberranjan_namitaputra2-Oct-09 11:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 2 Aug 2009
Article Copyright 2009 by Abhishek Sur
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid