CLR触发器实时推送信息

背景

web数据采集平台，app客户端需要实时推送用户关心的数据，这里采用了sql server 2008数据库的触发器来实现这一功能,当采集数据写入数据库，主动推送给用户。QL Server 提供的存储过程、函数等十分有限，这里就需要clr集成.

SQL Server中的程序集

只有在添加了程序集后才能在该程序集的基础上建立CLR触发器等。

CLR代码（编译）→DLL文件（注册）→SQL Server （作为数据库对象）→执行数据库操作过程如下：

将托管程序编写为一组类定义。编写好代码后编译成一个DLL文件；

触发器的编写为类的静态方法；

用户自定义类型、聚合函数编写为一个结构体。
DLL文件上传SQL Server 磁盘上，并使用create assembly 将DLL程序集存储到系统目录；
创建SQL对象（函数、存储过程、触发器等）并将其绑定到程序集的入口点；

将DLL程序集添加到SQL Server中

使用sp_configure可以显示或更改服务器级别的设置。若要更改数据库级别设置，请使用 ALTER DATABASE。

sp_configure [ [ @configname = ] 'option_name' [ , [ @configvalue = ] 'value' ] ]

为option指定新的值的格式为如上，可以参照最上面的写法。其中clr enabled 选项提供下列值。

值说明 0 不允许在 SQL Server 上执行程序集。 1 允许在 SQL Server 上执行程序集。

clr enabled 选项是一个高级选项。如果使用 sp_configure 系统存储过程来更改该设置，则只有在 show advanced options 设置为 1 时才能更改 clr enabled。该设置在运行 sp_configure 后立即生效。不需要重新启动 SQL Server 实例。

注意：

运行 RECONFIGURE 时，clr enabled 选项的运行值将从 1 改为 0，所有包含用户程序集的应用程序域将立即被卸载

第一步：启用 "clr enabled" 配置选项

exec sp_configure 'show advanced options','1'; 
go 
reconfigure; 
go 
exec sp_configure 'clr enabled','1';
go 
reconfigure; 
go

第二步：打开数据库的TRUSTWORTHY 属性

ALTER DATABASE [CrawlerSYS] SET TRUSTWORTHY ON  
go

第三步：执行

EXEC dbo.sp_changedbowner @loginame = N'sa', @map = true

GO

绑定程序集

CREATE ASSEMBLY [CrawlerCMS.WCLR] AUTHORIZATION [dbo]
FROM 'C:\inetpub\wwwroot\tigger\CrawlerCMS.WCLR.dll' WITH PERMISSION_SET = UNSAFE

GO

建立序列化处理器程序集

CREATE ASSEMBLY SendNewsTrigger from 'C:\inetpub\wwwroot\tigger\CrawlerCMS.WCLR.dll'
WITH PERMISSION_SET = UNSAFE;
go

创建触发器

CREATE TRIGGER [CMS_Oeeee001Trigger] ON CMS_Oeeee001 
FOR INSERT
AS EXTERNAL NAME [CrawlerCMS.WCLR].[Triggers].[CMS_Oeeee001Trigger];

GO

删除触发器

IF OBJECT_ID(N'SendNewsTrigger') IS NOT NULL

drop TRIGGER SendNewsTrigger
go

CrawlerCMS.WCLR.dll 程序集

//SendNewsTrigger.cs
using System;
using System.Data;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
      // 为目标输入现有表或视图，并取消注释属性行  
    [SqlTrigger(Name = "CMS_Oeeee001Trigger", Target = "CMS_Oeeee001", Event = "FOR INSERT")]
    public static void CMS_Oeeee001Trigger()
    {
        NewsTrigger.SendNewsTrigger();
    }

}

//NewsTrigger.cs
using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Net.Mail;
using System.Text;
using System.Text.RegularExpressions;

public static class NewsTrigger
{
    public static void SendNewsTrigger()
    {
        string conTitle = "";
        string conContents = "";
        string conCreateTime = "";
        string conSiteId = "";
        SqlCommand command;
        SqlTriggerContext triggContext = SqlContext.TriggerContext;
        SqlPipe pipe = SqlContext.Pipe;
        SqlDataReader reader;

        switch (triggContext.TriggerAction)
        {
            case TriggerAction.Insert:
                // Retrieve the connection that the trigger is using
                using (SqlConnection connection
                = new SqlConnection(@"context connection=true"))
                {
                    connection.Open();
                    command = new SqlCommand(@"SELECT * FROM INSERTED;",
                    connection);
                    reader = command.ExecuteReader();
                    reader.Read();

                    for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
                    { //将每一列的列名通过pipe.Send方法发送到客户端
                        switch (reader.GetName(columnNumber))
                        {
                            case "Title":
                                conTitle = (string)reader[columnNumber];
                                break;
                            case "Contents":
                                conContents = (string)reader[columnNumber];
                                break;
                            case "CreateTime":
                                conCreateTime = ((DateTime)reader[columnNumber]).ToString("yyyy-MM-dd hh:mm:ss");
                                break;
                            case "SiteId":
                                conSiteId = (string)reader[columnNumber];
                                break;
                            case "Author":
                                break;
                            default:
                                break;
                        }
                    }

                    reader.Close(); 
                    sendMessage(conTitle, conContents, conCreateTime, conSiteId);
                    pipe.Send("您好！恭喜注册成功! 您的用户名是：" + conTitle);                   
                }
                break;
            default:
                break;
        }
    }

    public static void sendMessage(string pTitle, string pContent, string pCreateDate, string pSiteId)
    {
        try
        {
                List<string> newsList = new List<string>();
                newsList = SendNews.buildSendMessage(pTitle, pContent, pCreateDate, pSiteId);
                SendNews.SendNewsToPhone(newsList);
        }
        catch (Exception ex)
        {

            throw ex;
        }
    }

}

错误提示：消息 6218，级别 16，状态 3，第 2 行针对 'CrawlerCMS.WCLR' 的 CREATE ASSEMBLY 失败，原因是程序集 'CrawlerCMS.WCLR' 未通过身份验证。请检查被引用程序集是否是最新的，而且是可信的(external_access 或 unsafe)，能在该数据库中执行。如果有 CLR Verifier 错误消息，将显示在此消息之后


EXEC dbo.sp_changedbowner @loginame = N'sa', @map = true

GO

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

ALTER DATABASE [CrawlerSYS] SET TRUSTWORTHY ON

GO

CREATE ASSEMBLY [System.Web.Extensions]

AUTHORIZATION [dbo]

FROM 

'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5\System.Web.Extensions.dll'

WITH PERMISSION_SET = UNSAFE

GO

CLR触发器实时推送信息

背景

SQL Server中的程序集

将DLL程序集添加到SQL Server中

热门