Shulou(Shulou.com)06/01 Report--

SQL CLR (SQL Common Language Runtime) is a new feature that has emerged since SQL Server 2005. It injects CLR services from the .NET Framework into SQL Server so that .NET code can be executed in the SQL Server server process.

By hosting CLR in Microsoft SQL Server (called CLR integration), developers can write stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregate functions in managed code, changing the situation where these functions were previously implemented only through the T-SQL language. Because managed code is compiled to native code before execution, performance can be greatly improved in some scenarios.

This paper records the research and use process of SQLCLR in these two days, and realizes that the calling function is passed into GUID and sent to the target application through named pipeline. The database is SQL Server2008R2

This is achieved by registering the .NET DLL class library with SQL Server and calling methods in the .NET class library from user-defined functions in SQL Server.

★ DLL class library

Namespace SQLCLRlib

{

Public class ControlActive

{

/ / /

/ send command

/ / /

/ / Target ID

/ 1: send successfully 0: send failed

Public static string sendControlCommand (string MBID)

{

Try

{

NamedPipeClient npc = new NamedPipeClient ("localhost", "jc-pipe")

Return npc.Query (MBID)

}

Catch (Exception ex)

{

Return ex.Message

}

}

}

}

If you need to access the database in the method, you need to add the declaration of the method: [Microsoft.SqlServer.Server.SqlFunction (SystemDataAccess = SystemDataAccessKind.Read,DataAccess = DataAccessKind.Read)]

Otherwise, an exception is reported: access to data is not allowed in this context. This context may be a function or method without a DataAccessKind.Read or SystemDataAccessKind.Read tag, a callback from the FillRow method of a table-valued function to obtain data, or an UDT validation method.

There is also a special CLR project template in VS: add a new project, select the template "Database"-> SQL Server, and select the CLR database project.

Configuration in ★ SQL Server

-- choose which database to use

-- USE DBname

-- check whether CLR is enabled

-- sp_configure 'clr enabled'

-- change installation CLR 1: enable 0: close

-- exec sp_configure 'clr enabled',1

-- reconfigure

Registration DLL,SQL2008R2 supports 3.5.The target framework of the class library project must correspond to the supported version of SQL Server.

-- whether the TRUSTWORTHY:SQL instance trusts the contents of the database. Default is OFF.

-- ALTER DATABASE DBname SET TRUSTWORTHY ON

-- create assembly asmSQLCLRlib from'D:\...\ SQLCLRlib.dll' WITH PERMISSION_SET = UNSAFE

-- create a custom function

-- create function dbo.clrControlActive

-(

@ MBID as nvarchar (36)

--)

Returns nvarchar (max) as EXTERNAL NAME [asmSQLCLRlib]. [Assembly. Class name]. [method name]

-- use custom functions

Select dbo.clrControlActive ('58A3D48E-A713-49C3-8FC6-76C8DF0DFA34')

references

Http://www.cnblogs.com/hsrzyn/archive/2013/05/28/1976555.html

Http://www.cnblogs.com/wshcn/archive/2011/12/02/2271630.html

Http://www.tuicool.com/articles/fANVzmn

I would like to thank the authors of the above information.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.