Click here to Skip to main content
14,970,106 members
Articles / Database Development / SQL Server
Posted 1 Nov 2007


62 bookmarked

Invoking a WCF Service from a CLR Trigger

Rate me:
Please Sign up or sign in to vote.
4.90/5 (20 votes)
30 Jan 2008CPOL5 min read
A step by step guide to communicating with WCF from a CLR Trigger in SQL Server 2005


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.


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


  • 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:
    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.ServiceModel;
    namespace SampleService
        interface IServiceContract
            void UpdateOccured();
            void InsertOccured();
    1. Add an interface to the project and call it 'IServiceContract'.
    2. Replace the code in the interface with:
  5. Implement the service contract.
    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");
    1. Add a new class and name it 'ServiceContract'.
    2. Replace the code in the class with:
  6. Host the service
    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), 
                //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. After adding
                  this endpoint, the full address
                  of the service will comprise base address 
                  and endpoint address ("MyService")
                Full Service Address == http://localhost:8000/services/MyService*/
                                          MyBinding, "MyService");
                //add behaviour to host
                //Run the host
                Console.WriteLine("Your service has been started");
                Console.WriteLine("Press <enter /> to terminate service.");
    1. Replace the code in Program.cs with:

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.

-- Turn advanced options on
EXEC sp_configure 'show advanced options' , '1';
EXEC sp_configure 'clr enabled' , '1'
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '0';
use custdb
SMDiagnostics from
    Communication Foundation\SMDiagnostics.dll'
with permission_set = UNSAFE

[System.Web] from
with permission_set = UNSAFE


[System.Messaging] from
with permission_set = UNSAFE

[System.IdentityModel] from
'C:\Program Files\Reference Assemblies\Microsoft\
with permission_set = UNSAFE


[System.IdentityModel.Selectors] from
'C:\Program Files\Reference Assemblies\Microsoft\
with permission_set = UNSAFE


CREATE ASSEMBLY -- this will add service modal

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

  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. 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):
  4. 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:

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=,
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

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

  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.
  4. Add a trigger to the project and name it 'WCFTrigger'.
    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 address 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 asynchronously talk
        //to the service when using the FAST METHOD
        public delegate void MyDelagate(String crudType);
        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": 
                case "Insert":
        [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 RECOMMENDED IN PRODUCTION!
                        //Fast method - STRONGLY RECOMMENDED FOR PRODUCTION!
                        //d = new MyDelagate(SendData);
                case TriggerAction.Insert:
                       //Slow method - NOT RECOMMENDED IN PRODUCTION!
                       //Fast method - STRONGLY RECOMMENDED FOR PRODUCTION!
                       //d = new MyDelagate(SendData);
                       //d.BeginInvoke("Insert", null, null);
    1. Replace the trigger code with the following:

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


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


About the Author

Sam Shiles
Software Developer
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

QuestionMSSQL 2012 Pin
michaelp190313-Feb-13 1:41
Membermichaelp190313-Feb-13 1:41 
AnswerRe: MSSQL 2012 Pin
DJ van Wyk15-Sep-14 1:18
professionalDJ van Wyk15-Sep-14 1:18 
QuestionError Pin
xyz132311-Feb-13 0:12
Memberxyz132311-Feb-13 0:12 
AnswerRe: Error Pin
Sam Shiles11-Feb-13 0:16
MemberSam Shiles11-Feb-13 0:16 
GeneralRe: Error Pin
xyz132311-Feb-13 17:34
Memberxyz132311-Feb-13 17:34 
QuestionLog from SendData (async) Pin
Kolompár Lajos15-Jan-13 22:54
MemberKolompár Lajos15-Jan-13 22:54 
AnswerRe: Log from SendData (async) Pin
michaelp190313-Feb-13 1:43
Membermichaelp190313-Feb-13 1:43 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 5:56
professionalKanasz Robert24-Sep-12 5:56 
QuestionMSVS 2008, SQL 2008 Pin
Kolompár Lajos20-Mar-12 4:43
MemberKolompár Lajos20-Mar-12 4:43 
AnswerRe: MSVS 2008, SQL 2008 Pin
Sam Shiles20-Mar-12 4:49
MemberSam Shiles20-Mar-12 4:49 
GeneralStruggling to fire delegate asyncronously Pin
Dan Barua26-May-11 3:57
MemberDan Barua26-May-11 3:57 
AnswerRe: Struggling to fire delegate asyncronously Pin
Dan Barua26-May-11 5:13
MemberDan Barua26-May-11 5:13 
GeneralMethod call speed [modified] Pin
Alexander Batishchev21-Jul-10 21:02
MemberAlexander Batishchev21-Jul-10 21:02 
GeneralRe: Method call speed Pin
Sam Shiles4-Jan-11 20:34
MemberSam Shiles4-Jan-11 20:34 
GeneralError on 64-bit machine Pin
rpin2230-Mar-10 18:24
Memberrpin2230-Mar-10 18:24 
GeneralRe: Error on 64-bit machine Pin
Dave A-W2-May-10 21:09
MemberDave A-W2-May-10 21:09 
GeneralRe: Error on 64-bit machine Pin
Kolompár Lajos21-Feb-13 2:51
MemberKolompár Lajos21-Feb-13 2:51 
QuestionPassing parameters to wcf- no luck. Pin
Sotart27-Aug-09 7:38
MemberSotart27-Aug-09 7:38 
AnswerRe: Passing parameters to wcf- no luck. Pin
Sam Shiles27-Aug-09 8:58
MemberSam Shiles27-Aug-09 8:58 
GeneralRe: Passing parameters to wcf- no luck. Pin
Sotart27-Aug-09 9:24
MemberSotart27-Aug-09 9:24 
GeneralRe: Passing parameters to wcf- no luck. Pin
Sam Shiles27-Aug-09 10:50
MemberSam Shiles27-Aug-09 10:50 
GeneralRe: Passing parameters to wcf- no luck. Pin
Sotart28-Aug-09 5:27
MemberSotart28-Aug-09 5:27 
GeneralRe: Passing parameters to wcf- no luck. Pin
Sam Shiles28-Aug-09 6:07
MemberSam Shiles28-Aug-09 6:07 
GeneralRe: Passing parameters to wcf- no luck. Pin
Sotart27-Aug-09 9:46
MemberSotart27-Aug-09 9:46 
GeneralERROR CREATE ASSEMBLY SMDiagnostics ... Pin
Linus Li24-Apr-09 9:49
MemberLinus Li24-Apr-09 9:49 

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

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