Click here to Skip to main content
Click here to Skip to main content

Invoking a WCF Service from a CLR Trigger

, 30 Jan 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
A step by step guide to communicating with WCF from a CLR Trigger in SQL Server 2005.

Introduction

This article will walk you through all the steps necessary to setup a sample project demonstrating how to create a CLR Trigger in SQL Server 2005 that will communicate with a WCF service of your design. This is not an introduction to WCF, but an introduction to using WCF from SQL Server 2005 CLR Triggers.

Background

After reading up about WCF, I was keen to start utilizing it in some of my existing database projects. One of my objectives was to get a CLR Trigger speaking to a WCF service. I figured this should be a fairly straightforward process, but there are so many gotchas involved that I thought it would be useful to share some of them and to produce a demo of a way in which to achieve this goal.

Using the code

Prerequisites

  • Development machine: VS2005, WCF extensions for VS2005, .NET 3.0 Runtime
  • Database machine: SQL Server 2005 or SQL Server 2005 Express, .NET 3.0 Runtime

Create the WCF Service

  1. Open Visual Studio.
  2. Create a new C# console application and call it 'Service'.
  3. Add a reference to: System.ServiceModel.
  4. Create a service contract:
    1. Add an interface to the project and call it 'IServiceContract'.
    2. Replace the code in the interface with:
    3. using System;
      using System.Collections.Generic;
      using System.Text;
      using System.ServiceModel;
      
      namespace SampleService
      {
          [ServiceContract]
          interface IServiceContract
          {
              [OperationContract]
              void UpdateOccured();
              [OperationContract]
              void InsertOccured();
          }
  5. Implement the service contract.
    1. Add a new class and name it 'ServiceContract'.
    2. Replace the code in the class with:
    3. 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. Host the service
    1. Replace the code in Program.cs with:
    2. 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)
              {
                  //Create Uri that acts as the service Base Address
                  Uri baseAddress = new Uri("http://localhost:8000/services");
                  
                  //Create a service host
                  ServiceHost MyHost = new ServiceHost(typeof(MyService), 
                                                       baseAddress);
                  
                  //Create a binding context for the service
                  WSHttpBinding MyBinding = new WSHttpBinding();
                 
                  //Declare and configure Metadata behavoir 
                  //that we will later add to the serivce
                  ServiceMetadataBehavior smb = new ServiceMetadataBehavior();          
                  smb.HttpGetEnabled = true;
      
                  /*Add endpoint to the service. Aftering adding
                    this endpoint the full address
                    of the service will comprise base address 
                    (http://localhost:8000/services) 
                    and endpoint address ("MyService")
                  Full Service Address == http://localhost:8000/services/MyService*/
                  MyHost.AddServiceEndpoint(typeof(IServiceContract), 
                                            MyBinding, "MyService");
      
                  
                  //add behavour to host
                  MyHost.Description.Behaviors.Add(smb);
      
                  //Run the host
                  MyHost.Open();
                  Console.WriteLine("Your service has been started");
                  Console.WriteLine("Press <enter /> to terminate service.");
                  Console.WriteLine();
                  Console.ReadLine();
      
              
              }
          }
      }

Preparing the database

This part of the process took the longest to work out, and caused the greatest number of problems. If you follow the steps outlined here, it should allow you to prepare any database to allow communication with a WCF service.

  1. Create a basic database called 'custDB', and add a table called 'tbCR' to it with the following columns:
    • [CustomerName] [varchar](50)
    • [CustomerTel] [varchar](50)
    • [CustomerEmail] [varchar](50)
  2. By default, CLR is disabled in SQL Server 2005. Enable CLR execution by executing the following query:
  3. -- 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
  4. Your database will be accessing "unsafe" assemblies. In order to prevent security exceptions, you will have to mark your database as "trustworthy" by executing the following query. (For more information on this option, see: TRUSTWORTHY Database Property):
  5. use custdb
    ALTER DATABASE custdb SET TRUSTWORTHY ON
    reconfigure
  6. By default, the assemblies that you can reference from SQL Server CLR objects are limited. In order to access some of the assemblies we need in order to communicate with WCF, we need to load them into our database. To do this, execute the following query:
  7. 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

Preparing the DB - Points of interest

You may receive the following warning when creating the assemblies. It is quite ignorable.

Warning: The Microsoft .Net frameworks assembly 'system.servicemodel, version=3.0.0.0,
culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.'
you are registering is not fully tested in SQL Server hosted environment.

On one of my test systems (SQL Server 2005 - unpatched), I received various "Out of Memory" errors when creating the assemblies. There didn't seem to be any good reason for this, and the same problem was not evident on any of my other systems. The solution to the problem? Install SQL Server 2005 SP2.

Create the CLR objects

  1. Add a new C# SQL Database Project to the solution called "ServiceClient".
  2. Choose or add a reference to your target database (if you are not prompted: right click on your "ServiceClient" project, choose properties, Database, Browse, and select your connection).
  3. Add a reference to the service we created:
    1. Right click the 'Service' project in Solution Explorer and choose 'Debug' > 'Start New Instance'.
    2. With the service running: right click the 'ServiceClient' project and choose 'Add Service Reference'.
    3. In 'Service URI', type: http://localhost:8000/services.
    4. Click OK.

    You should now see that a Service Reference has been added to our client project and that a file named localhost.map has been created.

  4. Add a trigger to the project and name it 'WCFTrigger'.
    1. Replace the trigger code with the following:
    2. 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; 
        
               }
      
           }
      }

Creating the CLR objects - Points of interest

The code above creates two objects in the database:

  1. The Stored Procedure SendData is required for making use of the Service Proxy. You cannot reference a service proxy from inside a trigger. To be perfectly honest, I am not sure exactly why this is, but if anyone can shed any light on this, that would be great! SendData doesn't really do anything fancy, it just calls the correct method on the proxy based on the input parameter.
  2. The Trigger WCFTrigger is setup to fire when updates or inserts occur in our table. Again, nothing complicated here, the trigger is used to call the SendData Stored Procedure with the correct parameters. Two methods for calling the Stored Procedure are shown in the above code: the simple method (commented as the slow method) which is implemented simply calls the Stored Procedure directly. A second option (commented as the fast method) uses a delegate to asynchronously call the Stored Procedure. This helps to improve the performance of the Trigger.

Here are the points of interest:

  • In my tests, using the SQL profiler, the delegate based method reduced the time it took a query to complete from 100ms to 8ms!
  • DISCLAIMER: I have included the slow (synchronous) method as the default solution in this project for the sake of simplicity. I strongly recommend using the fast (asynchronous) method in any production implementation.

Bringing it all together

We have now created all the elements required to demonstrate communicating with a WCF service from an SQL Server 2005 CLR Trigger; all that is required now is to bring them together!

  1. Publish the Service to your database server: right click your 'Service' project in VS2005 and choose 'Publish'. Choose a location on the machine running your database (e.g., \\YourDBServer\Samples) and then click 'Finish'.
  2. Start the Service on your database server: Connect to your database server. Find the Service location you just published to. Double click "Setup.exe". Your service should now start.
  3. Back on the dev machine: deploy your Trigger and procedure: right click your 'ServiceClient' in VS2005 and choose 'Deploy'.
  4. Verify the deployment of the Trigger and procedure using SQL Server Management Studio (make sure you can see them!).

Ready to go!

Everything should now be ready to go. Run some INSERT and UPDATE queries against your test table, and you should see some output in the Service console.

Screenshot - WCFFromSQL.jpg

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sam Shiles
Software Developer
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
QuestionMSSQL 2012 [modified] Pinmembermichaelp190313-Feb-13 1:41 
AnswerRe: MSSQL 2012 PinprofessionalDJ van Wyk15-Sep-14 1:18 
QuestionError Pinmemberxyz132311-Feb-13 0:12 
AnswerRe: Error PinmemberSam Shiles11-Feb-13 0:16 
GeneralRe: Error Pinmemberxyz132311-Feb-13 17:34 
QuestionLog from SendData (async) PinmemberKolompas Lajos15-Jan-13 22:54 
AnswerRe: Log from SendData (async) Pinmembermichaelp190313-Feb-13 1:43 
GeneralMy vote of 5 PinmvpKanasz Robert24-Sep-12 5:56 
QuestionMSVS 2008, SQL 2008 PinmemberDavid Janos Csillik20-Mar-12 4:43 
AnswerRe: MSVS 2008, SQL 2008 PinmemberSam Shiles20-Mar-12 4:49 
GeneralStruggling to fire delegate asyncronously PinmemberDan Barua26-May-11 3:57 
AnswerRe: Struggling to fire delegate asyncronously PinmemberDan Barua26-May-11 5:13 
GeneralMethod call speed [modified] PinmemberAlexander M. Batishchev21-Jul-10 21:02 
GeneralRe: Method call speed PinmemberSam Shiles4-Jan-11 20:34 
GeneralError on 64-bit machine Pinmemberrpin2230-Mar-10 18:24 
GeneralRe: Error on 64-bit machine Pinmemberdaws@mtbsoft.com.au2-May-10 21:09 
GeneralRe: Error on 64-bit machine PinmemberKolompas Lajos21-Feb-13 2:51 
QuestionPassing parameters to wcf- no luck. PinmemberSotart27-Aug-09 7:38 
AnswerRe: Passing parameters to wcf- no luck. PinmemberSam Shiles27-Aug-09 8:58 
GeneralRe: Passing parameters to wcf- no luck. PinmemberSotart27-Aug-09 9:24 
GeneralRe: Passing parameters to wcf- no luck. PinmemberSam Shiles27-Aug-09 10:50 
GeneralRe: Passing parameters to wcf- no luck. PinmemberSotart28-Aug-09 5:27 
GeneralRe: Passing parameters to wcf- no luck. PinmemberSam Shiles28-Aug-09 6:07 
GeneralRe: Passing parameters to wcf- no luck. PinmemberSotart27-Aug-09 9:46 
GeneralERROR CREATE ASSEMBLY SMDiagnostics ... PinmemberLinus Li24-Apr-09 9:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 30 Jan 2008
Article Copyright 2007 by Sam Shiles
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid