SQL 触发器与WebApi回执

野性酷女 2022-05-12 07:46 89阅读 0赞

1.需求数据库表添加，修改，删除数据，触发器生效，推送数据数据到WCF接口

a)创建WCF服务，发布服务

b)启用数据库CLR功能，默认是关闭状态

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

c)设置数据库安全选项

use custdb
    ALTER DATABASE custdb SET TRUSTWORTHY ON
    reconfigure

d)创建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 (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

e)创建数据库项目  
Sql Server数据库项目连接数据库

f)编写chufaqi代码

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;
    
            }
    
        }
       
    }

g)设置数据库全线集安全为无限制

3.第二种实现方式，创建数据库脚本触发器Trigger,在存储过程实现WebApi通信  
\`