sql 2008 与WCF交互

准备工作

场景：数据库中有张材料表，当材料数量发生变化的时候要通知供应商
问题抽象：材料数量变化只发生在当我们对该字段进行更新操作时候，我们可以通过clr编写自定义触发器，部署在数据库上,捕获这个过程，从而进行进一步操作。
开发/测试环境：.net frameWork 4.0/3.5, WCF 4.0, sql server 2008 R2, win7 ultimate(X64)
引用: 本文是参照老外的文章《Invoking a WCF Service from a CLR Trigger》，结合具体实践心得总结而成的。

正文

项目的架构由WCF4个元素组成(客户端、契约、主机、服务)，关于WCF入门以及深入的知识，可以参考园子里Artech前辈的系列作品。

我们创建的WCF服务十分简单，在目标数据库的插入、更新操作发生的时候打印一条控制台信息，因为WCF服务部分不是本文着重要讲的，而且实现功能十分简单，所以就贴出代码，各位感兴趣可以下载源码进行调试。

Contract项目,IServiceContract.cs代码:（需要添加对System.ServiceModel的引用）

using System.ServiceModel;
namespace Contract
{
    [ServiceContract]
   public interface IServiceContract
    {
        [OperationContract]
        void UpdateOccured();

        [OperationContract]
        void InsertOccured();
    }
}

Service项目,ServiceContract.cs代码

using Contract;
using System;
namespace Service
{
    public class ServiceContract:IServiceContract
   {
        public void UpdateOccured()
        {
            Console.WriteLine("Update Occured");
        }

        public void InsertOccured()
        {
            Console.WriteLine("Insert Occured");
        }
    }
}

Host项目，(需要添加对System.ServiceModel的引用,以及Contract和Service项目的引用)

Program.cs代码

using System;
using System.ServiceModel;
using Contract;
using Service;
namespace Host
{
    class Program
    {
        static void Main(string[] args)
        {
            using (ServiceHost host = new ServiceHost(typeof(ServiceContract)))
            {
                host.Opened += delegate
                {
                    Console.WriteLine("服务已经启动，按任意键退出");
                };

                host.Open();
                Console.Read();
            }
        }
    }
}
app.config代码

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.serviceModel>
    <behaviors>
      <serviceBehaviors>
        <behavior name="metadataBehavior">
          <serviceMetadata httpGetEnabled="true" httpGetUrl="http://127.0.0.1:8888/WCF_CLRService/metadata"/>
        </behavior>
      </serviceBehaviors>
    </behaviors>
    <services>
      <service name="Service.ServiceContract" behaviorConfiguration="metadataBehavior">
        <endpoint address="http://127.0.0.1:8888/WCF_CLRService" binding="wsHttpBinding" contract="Contract.IServiceContract" />
      </service>
    </services>
  </system.serviceModel>
</configuration>

OK，到此为止，WCF的服务、契约、主机部分我们已经写好，将host设为启动项目，运行后，我们看到控制台信息，显示WCF服务已经在运行了。接下来，我们需要创建一个名叫custDB的数据库，结构，表名任意，本文如下

CREATE TABLE [dbo].[tbCR](
    [CustomerName] [nchar](10) NULL,
    [CustomerTel] [nchar](10) NULL,
    [CustomerEmail] [nchar](10) NULL
) ON [PRIMARY]

创建好后，我们需要打开SQL的CLR启用选项(默认关闭)，代码如下

-- Turn advanced options on
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '0';
go

打开CLR选项后，为了防止访问unsafe属性的程序集(文章的后半部分,我们编写好的自定义CLR触发器就是以unsafe属性的程序集形式部署在sql上的),SQL报安全异常，我们还需要将custDB数据库的安全选项打开,代码如下:

use custdb
ALTER DATABASE custdb SET TRUSTWORTHY ON
reconfigure

OK,数据库已经建好,接下来是要将自定义CLR触发器的运行环境的程序集部署到SQL上,以本文(win7 X64)环境为例：(读者在实践的时候请参照实际文件路径进行修改)

CREATE ASSEMBLY 
SMDiagnostics from
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll'
with permission_set = UNSAFE

GO
 
CREATE ASSEMBLY 
[System.Web] from
'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
with permission_set = UNSAFE

GO

CREATE ASSEMBLY 
[System.Messaging] from
'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
with permission_set = UNSAFE
 
GO

CREATE ASSEMBLY  
[System.IdentityModel] from
'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'
with permission_set = UNSAFE

GO

CREATE ASSEMBLY  
[System.IdentityModel.Selectors] from
'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'
with permission_set = UNSAFE

GO

CREATE ASSEMBLY -- this will add service modal

[Microsoft.Transactions.Bridge] from
'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'
with permission_set = UNSAFE

GO

两个地方需要注意

1：with permission_set = UNSAFE

必须要将 permission_set设置为UNSAFE，不然程序集权限不够的话就会报Attempted to perform an operation that was forbidden by the CLR host 这个错误

2:您可能注意到了，自定义CLR触发器的运行环境是基于.net framework3.5的，这点从我们家在运行环境的程序集地址就可以看出来

如果您想要尝试使用 4.0的框架，需要将SQL clr的 .net framework版本指定成4.0，版本匹配一致才能运行，不然就后面的运行中会报程序集GAC签名不一致错误

数据库也初步搭建好了，接下来就要写WCF的客户端的项目了，也是我们要部署到数据库上的项目。

1:在项目中，添加C# SQL子项目

首次创建会提示你选择要连接的数据库，按提示，选择我们刚创建好的custDB数据库

接着运行WCF服务，就是本项目中的host项目，在SQL子项目中添加服务引用，在弹出的窗口输入地址http://127.0.0.1:8888/WCF_CLRService

添加完服务引用后，咱们正是开始编写CLR自定义触发器了

WCFTrigger.cs 代码如下

using System;
using Client.SQLCLRServiceReference;
using Microsoft.SqlServer.Server;
using System.ServiceModel;
public partial class Triggers
{
  
    //本代理用来提供异步调用属性，异步处理比同步操作阻塞通道直到完成操作速度要开上好几十毫秒
    public delegate void MyDelagate(String crudType);

    //在本项目添加WCF服务之后，生成的客户端。
   static readonly ServiceContractClient proxy = new ServiceContractClient(new WSHttpBinding(), new EndpointAddress("http://127.0.0.1:8888/WCF_CLRService"));

    /// <summary>
   /// [SqlProcedure()]将程序集中本方法的定义标记为存储过程，这样在SQL服务器上注册该方法时，就能被认出来
    /// </summary>
    /// <param name="crudType"></param>
    [SqlProcedure()]
    public static void SendData(String crudType)
    {

        /*A very simple procedure that accepts a string parameter 
          based on the CRUD action performed by the
          trigger. It switches based on this parameter 
          and calls the appropriate method on the service proxy*/

        switch (crudType)
        {
            case "Update":
                
                proxy.UpdateOccured();

                break;

            case "Insert":

                proxy.InsertOccured();
                break;
        }

    }

    /// <summary>
    /// [SqlTrigger()]将程序集中本方法的定义标记为触发器，这样在SQL服务器上注册该方法时，就能被认出来
    /// Name 属性是指我们在SQL中要生成的触发器的名字
    /// Target 对应哪张表
    /// Event 对应哪些事件
    /// </summary>
    [SqlTrigger(Name = "WCFTrigger",
       Target = "tbCR", Event = "FOR UPDATE, INSERT")]
    public static void Trigger1()
    {
        //获触发器的上下文
        SqlTriggerContext myContext = SqlContext.TriggerContext;

        MyDelagate d;

        switch (myContext.TriggerAction)
        {
            case TriggerAction.Update:
                {
                    d = new MyDelagate(SendData);
                    //异步调用
                    d.BeginInvoke("Update", null, null);

                }
                break;

            case TriggerAction.Insert:
                {
                    d = new MyDelagate(SendData);
                    d.BeginInvoke("Insert", null, null);
                }
                break;

        }

    }
   
}

OK，全写好了之后，我们对该项目右键，点击部署，大概过个30来秒就会提示部署成功

此时，打开数据库后，我们可以看到程序已经被成功的部署到了SQL上

OK，打开tbCR插入条记录看看，什么？报错？

看到这个提示你想起什么了没？没错，是安全权限不够，在一开始在SQL数据库上搭建CLR环境时，我们也遇到这样的错误.

我们注意到在本项目中，部署实际上，就对Client这个程序集进行更新，所以我们对该程序集右键，点击属性选项，在弹出的页面将程序集从安全改成无限制

原因:在Visual studio 上通过部署形式，将程序集部署到SQL上默认是安全模式的。因此，在每次visual studio部署到sql上时，我们都要注意，被部署的程序集默认模式都是安全的，这就有可能引发安全的权限不足导致异常，这个时候就需要我们将其修改为无限制。

OK，改了之后，我们在对tbCR进行插入操作，这次控制台已经打印出信息了，说明我们已经成功捕获到更新的事件。

删除

有可能我们想要在数据库上讲该自定义的CLR卸载掉，在本例子中，我们主要用到client这个程序集，在写在的时候，我们需要在数据库的程序集中查看有什么程序引用了该程序集，方法时在程序集上右键，查看依赖关系

在弹出的窗口我们可以看到有个存储过程和触发器引用了该程序集，卸载掉后，就可以删除该DLL了

drop proc sendData;

drop trigger WCFTrigger;

一些开发中遇到的问题

1:部署到远程数据库上

我们一开始创建SQL CLR项目的时候，会提示你配置需要连接的数据库的参数，需要注意连接的账户的安全权限问题，不够的话，会部署失败。修改之后，还要注意需要修改程序集的安全属性为无限制。

2：你的例子我明白了，但是我们可以在触发器过程里面获取修改后的数据吗？

嗯。我们通过触发器在SQL中捕获了插入、更新这个过程，因此，我们就能捕获这个过程中临时产生的inserted、deleted两张表就像使用原生的SQL触发器一样，这两张表有什么用呢？

这是两个虚拟表，inserted 保存的是 insert 或 update 之后所影响的记录形成的表，deleted 保存的是 delete 或 update 之前所影响的记录形成的表。

所以，我们就可以在写好的Trigger1触发器方法里面用以下的代码来获取插入之后的数据

private static List<string> getInsertedInfo()
    {
        List<string> info = new List<string>();
        //这个连接字符串在触发器方法内部使用
        using (SqlConnection sqlConn = new SqlConnection(@"context connection=true"))
        {
            using (SqlCommand sqlCMD = new SqlCommand(@"SELECT TID FROM INSERTED;", sqlConn))
            {
                sqlConn.Open();
                using (SqlDataReader reader = sqlCMD.ExecuteReader())
                {
                    reader.Read();

                    info.Add(reader[0].ToString());
                }
            }
        }
        return info;
    }

3：你给我的程序在WCF停了之后重新开启，控制台就没有输出了啊

在给大家的SQL CLR项目的WCFTrigger.cs 文件内，我们仔细观察，可以看到

//在本项目添加WCF服务之后，生成的客户端。

static readonly ServiceContractClient proxy = new ServiceContractClient(new WSHttpBinding(), new EndpointAddress(http://127.0.0.1:8888/WCF_CLRService));

这句话就是声明WCF的代理类，通过它,我们才能实现SQL和WCF交互哦！所以问题就出在这个代理类的声明位置上

我们可以看到他是在类中声明，独立于方法之外，最初这么安排，只是单单从性能上着手，不用每次触发器调用都声明一个代理类。

实在有欠考虑，这样做的直接后果是，每次需要开启WCF服务器，然后重启sql服务器，因此获取这部分性能的代价太高，我们需要修改它！

LET’S GO

解决办法将代理类的声明放到存储过程里面

[SqlProcedure()]
   public static void SendData(String crudType)
   {

       try
       {
           //在本项目添加WCF服务之后，生成的客户端。
           ServiceContractClient proxy = new ServiceContractClient(new WSHttpBinding(), new EndpointAddress("http://127.0.0.1:8888/WCF_CLRService"));

           switch (crudType)
           {
               case "Update":

                   proxy.UpdateOccured();

                   break;

               case "Insert":

                   proxy.InsertOccured();
                   break;
           }
       }
       catch (Exception e)
       {
           //这里添加WCF服务未打开时，所需要进行的处理!
       }

   }