Introduction to SQL Server CLR Integration

Sqlserver
In 2005, many new features are added. For developers, the most important thing to mention is XML support and CLR integration. Of course, there are some new features about "High Availability" (Database Cluster/database image/log transmission), which are suitable for DBAs. If you are interested, please take a look.

I believe many people, like me, have always hoped to program in sqlserver very flexibly and implement various functions (just as nice as using C # programming in ). However, if you fail to do so, do not mention the development environment issues (Code Completion, color matching, etc ). The most important thing is that T-SQL is really not suitable for complex operations (it is indeed difficult for me such a cainiao. It is not difficult for the Titans) and lacks the flexibility of object-oriented programming. There is no way to implement some basic data structures, let alone object orientation.

With sqlserver
After CLR integration, this seems to have changed. What is Clr and what is CLR integration? Let's take a look at the msdn explanation.

By hosting CLR (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 the hosted code is compiled as the local code before execution, the performance can be greatly improved in some solutions.

In layman's terms, after sqlserver 2005, the. NET Framework Public Language Runtime (CLR) component is added to the database engine to facilitate interaction with. NET applications.

First, create an sqlserver
CLR database project,

After the project is created, you can open the attempt to see the structure, which only contains some SQL scripts for the test example.

Next, create a common C # class and define a method in the class. As follows:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using Microsoft.SqlServer.Server;namespace SqlServerProject1{   public  class demo    {       [Microsoft.SqlServer.Server.SqlProcedure]       public static void ClrProc() {           SqlContext.Pipe.Send("Hello World");       }    }}

Remember to introduce the namespace using Microsoft. sqlserver. server. The following code is used in the method body:

SqlContext.Pipe.Send("Hello World");

It can be understood that the client sends a result, just like the response. Write output.

Add the [microsoft. sqlserver. server. sqlprocedure] feature to this common method. It indicates that you are a CLR.
Stored procedure.

Note: attribute is also called an attribute. In order to avoid confusion with get/set in the object-oriented model, I am used to calling it a "feature". Similar to the annotations in Java.

After compilation, you can choose to directly select "deploy" under the generation menu of Vs, or manually create it in the database.

Open the query window for the specified database, start writing scripts, and check the database configuration to see if CLR support is enabled. If not, enable it manually:

-- View System Configuration select * From SYS. configurationsorder by namego -- enable clrsp_configure 'clr enabled', 1 goreconfigure; go

After the preparation is complete, start creating the Assembly and CLR Stored Procedure:

-- Create Assembly sqlserverproject1 from n' C: \ Users \ Administrator \ Documents ents \ Visual Studio 2010 \ projects \ database1 \ sqlserverproject1 \ bin \ debug \ sqlserverproject1.dll 'with permission_set = SAFEGO----DROP Assembly sqlserverproject1 -- go -- create a CLR Stored Procedure (assembly. namespace. type. method Name) Create proc DBO. helloworldas external name sqlserverproject1. [sqlserverproject1.demo]. clrprocgo -- execute the CLR Stored Procedure exec helloworldgo

NOTE: For the path of the DLL file compiled in the previous project (absolute path is recommended), the permission is directly given to safe. The creation of a CLR stored procedure is similar to that of a common Proc. It just adds one more sentence (generally, it means to specify the classes and corresponding methods in the namespace specified in the set of programs)

It can be executed just like calling a common stored procedure. Look at the results and there is "helloworld". Haha. Of course, this is just the simplest, and the purpose is to teach you how to create.

Of course, you can also create complex stored procedures, functions, and triggers. I will take the stored procedure as an example to create a slightly complicated object and return a sqldatareader object.

Right-click the project, create, and select a stored procedure (just create a common class to let everyone understand its basic implementation and ing ).

After the stored procedure is created, you can add some simple code to the method, execute a basic Single Table query, and return it to the client as a sqldatareader object. The Code is as follows:

Using system; using system. data; using system. data. sqlclient; using system. data. sqltypes; using Microsoft. sqlserver. server; public partial class storedprocedures {[Microsoft. sqlserver. server. sqlprocedure] public static void storedprocedure1 () {// place the code sqlconnection conn = new sqlconnection ("context connection = true") here; try {Conn. open (); sqlcommand cmd = new sqlcommand ("select count (*) from DBO. tempcatalog ", Conn); sqldatareader DR = cmd. executereader (); sqlcontext. pipe. send (DR);} catch (exception) {Conn. close ();}}};

After compilation, you can select "deploy" under the generation menu of vs (it will be automatically deployed to sqlserver and overwrite the original one ). Of course, you can also manually reload the Assembly and create the required stored procedure.

After creating and executing the stored procedure in sqlserver, check the result to see if you want it?

Tip: manual repeated loading and creation may conflict. You need to delete it before creating a new one. You can check in the Object Browser or use the following SQL statement to query related information.

SELECT * FROM sys.assembliesSELECT * FROM sys.assembly_files

After a simple example is completed, the advantage is conceivable. So when will we use Clr and Write T-SQL? I think it depends on the actual business needs and application scenarios.

According to some Microsoft experts, you should consider using CLR in the following situations:
SQL involves a large number of logical judgments and operations. For example, you need to customize the encryption and decryption algorithms at the database level.
The T-SQL cannot handle the demand. For example, you need to judge the Regular Expression in SQL.
The logic judgment or loop branch is too complex. Sometimes a large number of cursors are required for processing (or the cursor may be slow if it is not necessarily used, but the key is the person who clicks the Code ).

This article is from http://blog.csdn.net/dinglang_2009.