SQL triggers invoke the. NET class method continuation SQLCLR Application

SQL CLR (SQL Common Language Runtime) is a new feature that has occurred since SQL Server 2005, which injects CLR services from the. NET framework into SQL Server. NET code can be executed in a SQL Server server process.

By hosting the CLR (known as CLR integration) in Microsoft SQL Server, developers can write stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregate functions in managed code, changing situations where they were previously only possible through the T-SQL language. Because managed code compiles to native code before it executes, there are scenarios in which performance can be greatly improved.

This article records the two days of the SQLCLR study using the procedure to implement the call function to pass in the GUID, sent through the named pipe to the target app. Database is SQL SERVER2008R2

The implementation is to register the. NET DLL class library with SQL Server and invoke the methods in the. NET class library from the user-defined function in SQL Server.

★dll Class Library

Namespace Sqlclrlib
{
public class Controlactive
{
<summary>
Send command
</summary>
<param name= "Mbid" > Target id</param>
1: Send success 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 access to a database in a method, you need to add a method declaration: [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 might be a function or method without a dataaccesskind.read or systemdataaccesskind.read tag, or it could be a callback from the FillRow method of a table-valued function for fetching data, or it might be a UDT validation method.

VS also has a dedicated CLR project template: Add new project, select template "Database"->sql Server, select CLR Database project

Configuration in the ★sql server

--Choose which database to use

--use DBname

--see if the CLR is turned on
--sp_configure ' CLR enabled '
--Change installation CLR 1: Turn on 0: off
--exec sp_configure ' clr enabled ', 1
--reconfigure
---Register DLL,SQL2008R2 support 3.5, the target framework of the class Library project must correspond to the supported version of SQL Server

--trustworthy:sql whether the instance trusts the contents of the database, default 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]
--Using Custom functions
Select Dbo.clrcontrolactive (' 58a3d48e-a713-49c3-8fc6-76c8df0dfa34 ')

Resources

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

Thanks to the author of the above information

This article is from the "Xiao Universe" blog, make sure to keep this source http://5460095.blog.51cto.com/5450095/1774910

SQL triggers invoke the. NET class method continuation SQLCLR Application