Download SQL CLR demo - 357.3 KB

Table of Contents

Introduction
Background
Environment
Walkthrough
Point of Interest
References

Introduction

Most of the time, when you create any database objects, you use the typical SQL language. Nevertheless, you can also make use of .NET Framework languages to create these database objects, such as SQL functions, SQL stored procedures, etc. These objects are referred to as SQL Server Common Language Run-time objects, or SQL CLR objects. Despite being aware of this feature, I did not get a chance to work on it for a while. And recently, I got real world requirements which made me think of using SQL CLR however I couldn’t find enough information at one place. Finally, I was able to accomplish my task and I believe this article will help you as it is uniquely presenting all the required steps and brings all configurations in one place.

Background

Ability to create SQL CLR objects is release with SQL Server 2005 however creating, deploying of such feature was quite a lengthy procedure till Visual Studio 2013. You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). These managed database objects are similar to that of regular database objects like stored procedure, views, tables and so on. Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.

There are many advantages of creating object in managed code, you can get the entire list on MSDN. I am interested in better performance and reusability of code. Complex mathematical/string operations perform better in complied assemblies than Transact-SQL statements and you can leverage your existing managed assemblies/library to get results than inventing the wheel again.

Environment

Visual Studio 2013 with Update 3
.NET Framework 4.5
SQL SERVER 2012
Microsoft SQL Server Data Tools 2013 http://msdn.microsoft.com/en-us/data/hh297027

Walkthrough

Create a SQL Database Project

You can use database projects to create new databases, new data-tier applications, and to update existing databases and data-tier applications. Here, I am using Visual Studio 2013 database project for creating SQL CLR objects. With VS 2013, you don’t require to deploy assemble manually and even if you are using any third party assembly, it’s very easy to see it get deployed.

To create a new SQL Server database project:

From the File menu, create a new project.
In the New Project Dialog Box, select and expand other language node in the Project Types area.
Select the SQL Server.
Click OK.

Create new database on SQL server, for example ‘SQLCLRDemo’.

Now import this database schema into SQL Server database project.
From the Project menu, import newly created database, OR
Right click on project in solution explorer and select import and then database.

Once database schema is available into database project, then we are ready to create CLR function.

Add items to the SQL database project

Examples: Here, I am going to showcase two kinds of examples. First is a function using C#, which is part of database project, in this example when application gets compile and published, then separate application assembly will be created and get registered into CLR. Before VS 2013, all these steps need to done manually using SQL command. This assembly is a database project assembly. Second example will use some APIs from third-party DLL in its function. As its third-party assembly, we also need to create and deploy this assembly in CLR. To deploy this third-party DLL, we need set some of properties.
To create a new Scalar value SQL CLR function.
Right Click project and select Add->New Item-> SQL CLR C#.
Select the SQL CLR C# User Defined Function.
Click OK.

Something like below:
To create a new Table value CLR SQL function. (This example uses thirdparty assembly).
Add reference of DLL into project and again follow step 2. Refer to the code from the attached sample application. This table valued function will take recurrence pattern and date range, and based on it give call to third party assembly to extract all occurrences. To demonstrate, I used Telerick recurrence engine. Once you add any reference, make sure you have the following properties set for that assembly.
Build the project and now it's time to publish the project.

Publish and Deploy the assembly to a SQL Server and test it

Publishing database project allows to deploy all SQL script on database server and also create assemblies into .NET CLR. Follow the steps below to publish application. Make sure for target SQL server platform and to check 'always re-create database'.

You will not require to give any physical path for any of the assemblies. This is a real advantage and will avoid lots of configuration. Click on ‘View Script’ after publishing project.

Take a look at SQL Server, now you can see two functions are available.

Debug deployed .NET Framework database object

Debugging this C# code in SQL database is quite easy. You need to add test script and execute the SQL function.

Add build script into project:
Change database project properties and set script as start-up.

Add Pre- Deployment Script

The common language runtime (CLR) integration feature is off by default, and you must be enabled before deployment.
To enable CLR integration, use the CLRenabled option of the sp_configure in pre-deployment script:

Add pre-deployment script:
Add sp config to pre-deployment script:

Points of Interest

Visual Studio 2013 is allowing to create, deploy and debug SQL CLR objects straight away without any complex configuration and commands and its super easy. This article consists of two example writing code in database project which creates assembly and second example is using any third party DLL and deploying third party DLL.

References

Internet Calendaring and Scheduling Core Object Specification https://www.ietf.org/rfc/rfc2445.txt
MSDN http://msdn.microsoft.com/en-us/library/dd997440(v=vs.100).aspx
Telerik http://blogs.telerik.com/aspnet-ajax/posts/08-08-04/working-with-radscheduler-recurring-appointments-on-sql-server.aspx
SSDT https://www.microsoftpressstore.com/articles/article.aspx?p=2225062&seqNum=2

Create, Run, Debug and Deploy SQL CLR Function with Visual Studio 2013 Database Project