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服务未打开时，所需要进行的处理! } }
这样，在每次触发触发器的时候(不拗口吧。)，我们就可以检测WCF是否开启，没开启，也可以进行进一步处理了。 我们的clr触发器就能实现热插拔了
