Introduction to SQL Server CLR integration

The Common Language runtime (CLR) is at the heart of the Microsoft. NET Framework and provides the execution environment for all. NET Framework code.  code that runs in the CLR is called managed code. the CLR provides the various functions and services required to execute a program, including just-in-time (JIT) compiling, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

by hosting the CLR (known as CLR integration) in Microsoft SQL Server, you can write stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregate functions in managed code. because managed code compiles to native code before it executes, there are scenarios in which performance can be greatly improved.

managed code uses code access security (CAS), code links, and application domains to prevent an assembly from performing certain operations. SQL Server uses CAS to help secure managed code and to prevent the operating system or database server from being compromised.

This section is intended to provide sufficient information to begin using SQL Server CLR integration programming, rather than to provide complete information. For more details, see the SQL Server Books Online version of the SQL Server version that you are using.

Enable CLR Integration

By default, the common language runtime (CLR) integration feature is disabled in Microsoft SQL Server and must be enabled to use objects that are implemented through CLR integration. to enable CLR integration using Transact-SQL, use the CLR enabled option for the sp_configure stored procedure as shown below:

sp_configure ' clr enabled ', 1GORECONFIGUREGO

You can disable CLR integration by setting the CLR enabled option to zero. when CLR integration is disabled, SQL Server stops executing all CLR routines and unloads all application domains.

The first step to setting up the environment

First we have to open the Sql2005 IDE, create a new database, this process is not much to say, the database name is Text

Next when we open VS2005 or 2008 (choose. net2.0 because only 2.0 of the CLR is supported here) New Project

It is very different from the new project here, we want to select a database under Office, choose SQL Server Project name called Sqlclrproject Location you can choose any location on your computer, click OK

here we have to choose our own data, if you are not the first time, then the default list of all the selected database, and here I have a choice, so there is an existing selection, of course, if you are the first time there is no selection of items, then we click Add New Reference (A) ...

I believe everyone here is familiar with it, choose the database we just built,

And then click OK

Here we click Yes, because we have to enable it to debug our actions below

The new project structure is

Because we're going to use a function here, we right-click the project---Add

We can see that there are a lot of objects used in the database,

We click New Item, which is the first one

Let's change the function name to strcount meaning to count the number of characters entered

We chose a user-defined function.

All right, click Add to do it,

The system will automatically generate a piece of code

Code

We're done with our pre-prep work here, so we just have to change this class.

[Microsoft.SqlServer.Server.SqlFunction]
This is the object type that is represented in SQL

SqlFunction is a function.

SqlProcedure Stored Procedures

Sqltrigger Trigger

And the rest of you, check it out for yourself.

Step two deploy your own defined functions

Our function is to calculate the length of the character I put the finished code down in the face

Code

Now that the function is written, this function does not do too much explanation, because this is just to get the length of the string this thing is too basic, hehe

How can we use this function in Sql2005?

It's actually very simple. We build the project, after the build succeeds, we right-click the project

Click Deploy Project, and so on the successful deployment. So the program we deployed is over there, and now we open the sql2005ide.

Find as shown

We will find a function of our own definition in the caption value function in the Sql2005, so how to use him, very simple is actually the same as what we usually use

We create a new query

Because our SQL default is to turn off CLR functionality, we need to start with a command

See as shown shows that your function opened successfully, the following we are free to use the function, and the use of the system's own definition of the function is the same, below I count a few people can see

Such a

This is the use of SQL CLR to execute C # program, is not very convenient, of course, we can according to their own needs to change this function, but the method is the same as the implementation of stored procedures and triggers are the same.

I added that the debug feature is the most problematic feature in. NET, and does the CLR support it? The answer is yes, as long as we make a point in this Text.sql file, because the results in this file are the same as in the database.

Code

We can use a combination of our own SQL statements, which is used in our own defined stored procedures so that the complex logic can be used in C # code to achieve is not feeling very convenient, the CLR is a good thing, he is not only these, but also what advanced features, we next time the blog to say it!!!

In order to make it easier for everyone to write code, I add

Use Text

EXECsp_configure'Show advanced Options','1';
Go

EXECsp_configure'CLR enabled','1'
Go

Reconfigure  withOverride;
Go

Introduction to SQL Server CLR integration