CLR触发器引用WCF服务

这是本人第一次翻译文章，希望和大家一起学习。不当之处，多多请教！

这篇文章通过创建一个项目实例演示如何在SQL2005中创建CLR触发器并和WCF服务相联系实现您的设计，详细介绍了所有必要的步骤。文中不仅介绍了WCF更介绍了在SQLServer2005中如何使用WCF。  

 

运行环境：VS 2005, WCF extensions for VS2005, .NET 3.0 Runtime

 

数据库：SQL 2005 or SQL 2005 Express, .NET 3.0 Runtime

创建WCF服务

1．打开visual studio

2．创建一个新的C＃控制台应用程序，并命名为“Service”

3．添加引用：System.ServiceModel

4．创建服务契约

1）  在项目中添加接口，取名“IServiceContract”

2）  用下面的代码替换

using System;

using System.Collections.Generic;

using System.Text;

using System.ServiceModel;

 

namespace SampleService

{

    [ServiceContract]

    interface IServiceContract

    {

        [OperationContract]

        void UpdateOccured();

        [OperationContract]

        void InsertOccured();

}   

5．实现服务契约

1）  添加一个新类，命名为“ServiceContract”

2）  用下面代码替换类中代码

using System;

using System.Collections.Generic;

using System.Text;

namespace SampleService

{

    class MyService : IServiceContract

    {

 

        public void UpdateOccured()

        {

    Console.WriteLine("Update Occured");

        }

 

        public void InsertOccured(int RecordID)

        {

             Console.WriteLine("Insert Occured");

        }

 

    }

}

6．主机服务

1）  在Program.cs中用下面代码替换

using System;

using System.Collections.Generic;

using System.Text;

using System.ServiceModel;

using System.ServiceModel.Description;

 

namespace SampleService

{

    class Program

    {

        static void Main (string[] args)

        {

            //创建服务基址

            Uri baseAddress = new Uri("http://localhost:8000/services");

           

            //创建主机处理服务实例

            ServiceHost MyHost = new ServiceHost(typeof(MyService), baseAddress);

           

            //为服务绑定文本

            WSHttpBinding MyBinding = new WSHttpBinding();

          

            //声明和配置Metadata 对象，稍后添加到servcie中

            ServiceMetadataBehavior smb = new ServiceMetadataBehavior();         

            smb.HttpGetEnabled = true;

 

            /*添加服务终端，完整服务地址有基址http://localhost:8000/services和终端地址MyService组成，地址为：http://localhost:8000/services/MyService*/

            MyHost.AddServiceEndpoint(typeof(IServiceContract), MyBinding, "MyService");

 

           

            //添加事件

            MyHost.Description.Behaviors.Add(smb);

 

            //运行

            MyHost.Open();

            Console.WriteLine("Your service has been started");

            Console.WriteLine("Press <enter /> to terminate service.");

            Console.WriteLine();

            Console.ReadLine();       

        }

    }

}

配置数据库

如果你遵循这里所说的步骤它允许你配置任何数据库系统，让你访问WCF服务。

1．创建一个基本数据库名为“custDB”，用下面的列创建表“tbCR”

[CustomerName] [varchar](50)

[CustomerTel] [varchar](50)

[CustomerEmail] [varchar](50)

2．默认CLR是不行的，让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

3．此时你的数据库是不安全配置。为了防止安全例外，你必须通过执行以下查询是数据库标志为“可信任”

use custdb

 ALTER DATABASE custdb SET TRUSTWORTHY ON

 Reconfigure

4．作为标准配置我们可参考的SQL Server CLR 对象是有限的，为了获取某些配置，我们必须把WCF下载到数据库中并连接它，通过以下查询实现：

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/Reference Assemblies/Microsoft/Framework/v3.0/System.IdentityModel.dll'

with permission_set = UNSAFE

 

GO

 

CREATE ASSEMBLY 

[System.IdentityModel.Selectors] from

'C:/Program Files/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          

     

错误提示

在配置过程中可能会遇到以下错误信息：

Warning: The Microsoft .Net frameworks assembly 'system.servicemodel, version= 3.0.0 .0,

 culture=neutral, publickeytoken=b 77a 5c 561934e089, processorarchitecture=msil.'

 you are registering is not fully tested in SQL Server hosted environment.

我的测试系统是SQL2005（未打补丁），在配置过程中出现了许多“out of memory”错误。解决问题的方法就是安装SQL 2005 SP2

 

创建CLR对象

1．  在解决方案中添加新的C＃SQL数据库项目，命名为“ServiceClient”

2．  为你的目标数据库选择或添加注释。（如果未被提示：右击ServiceClient项目，选择属性，数据库，浏览并选择你的连接）

3．  为创建的服务添加声明

1）  在解决方案浏览器中右击“service”项目，选择“调试”>“Start New Instance”

2）  服务运行：右击“ServiceClient”项目，选择“Add Service Reference”

3）  在“Service URI”中输入：http://localhost:8000/services

4）  点击“OK”

 

4．  项目中添加触发器，命名“WCFTrigger”

1）  用以下代码替换

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.ServiceModel.Description;

using System.ServiceModel;

using System.Collections;

using System.Diagnostics;

using System.Threading;

 

 

public partial class Triggers

{

    //Create an endpoint addresss for our serivce

    public static EndpointAddress endpoint = new EndpointAddress(new Uri("http://localhost:8000/services/myservice"));

    //Create a binding method for our service

    public static WSHttpBinding httpBinding = new WSHttpBinding();

    //Create an instance of the service proxy

    public static ServiceClient.localhost.ServiceContractClient myClient = new ServiceClient.localhost.ServiceContractClient(httpBinding, endpoint);

    //A delegate that is used to asynchrounously talk to the service when using the FAST METHOD

    public delegate void MyDelagate(String crudType);

  

 

    [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":

               

                myClient.UpdateOccured();

               

                break;

 

            case "Insert":

 

                myClient.InsertOccured();

                break;              

        }

 

    }  

   

   

    [Microsoft.SqlServer.Server.SqlTrigger(Name = "WCFTrigger", Target = "tbCR", Event = "FOR UPDATE, INSERT")]

    public static void Trigger1()

    {

       /*This is a very basic trigger that performs two very simple actions:

        * 1) Gets the current trigger Context and then switches based on the triggeraction

        * 2) Makes a call to a stored procedure

       

        * Two methods of calling the stored procedure are presented here.

        * View the article on Code Project for a discussion on these methods

        */

       

        SqlTriggerContext myContext = SqlContext.TriggerContext;

        //Used for the FAST METHOD

        MyDelagate d;

       

        switch (myContext.TriggerAction)

        {

            case TriggerAction.Update:                                       

                                 

                    //Slow method - NOT REMCOMMEND IN PRODUCTION!

                    SendData("Update");

                 

                    //Fast method - STRONGLY RECOMMENDED FOR PRODUCTION!

                    //d = new MyDelagate(SendData);

                    //d.BeginInvoke("Update",null,null);

                               

                    break;

           

            case TriggerAction.Insert:

 

                   //Slow method - NOT REMCOMMEND IN PRODUCTION!

                   SendData("Insert");

                            

                   //Fast method - STRONGLY RECOMMENDED FOR PRODUCTION!

                  //d = new MyDelagate(SendData);

                   //d.BeginInvoke("Insert", null, null);

                  

                    break;

  

         }

 

     }

}

上面的代码在数据库中创建了两个对象。存储过程SendData需要利用服务代理。触发器WCFTrigger当我们表中有更新或插入操作时触发。

在我的测试中，使用SQL编译器，基于方法的授权使查询时间从100ms减少到8ms

说明：在这个项目中慢（异步）方法作为默认的解决方案，它是以牺牲简洁性为代价的。我强烈建议在任何生产实施中使用快（同步）方法。

运行

一切准备妥当，开始运行。在表格中输入一些插入和更新操作，你会看到相应输出结果。