Click here to Skip to main content
15,867,594 members
Articles / Database Development / SQL Server

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 243.9K   1.4K   62   76
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:
    C#
    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.ServiceModel;
    
    namespace SampleService
    {
        [ServiceContract]
        interface IServiceContract
        {
            [OperationContract]
            void UpdateOccured();
            [OperationContract]
            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.
    C#
    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
    C#
    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. After 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 behaviour 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();        
            }
        }
    }
    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.

SQL
-- 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
SQL
use custdb
ALTER DATABASE custdb SET TRUSTWORTHY ON
reconfigure
SQL
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
  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=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

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.

  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'.
    C#
    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);
       
    
        [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 RECOMMENDED 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 RECOMMENDED IN PRODUCTION!
                       SendData("Insert");
                                
                       //Fast method - STRONGLY RECOMMENDED FOR PRODUCTION!
                       //d = new MyDelagate(SendData);
                       //d.BeginInvoke("Insert", null, null);
                       
                        break;   
             }
         }
    }
    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

License

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


Written By
Software Developer
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionMSSQL 2012 Pin
michaelp190313-Feb-13 1:41
michaelp190313-Feb-13 1:41 
AnswerRe: MSSQL 2012 Pin
DJ van Wyk15-Sep-14 1:18
professionalDJ van Wyk15-Sep-14 1:18 
GeneralRe: MSSQL 2012 Pin
Solomon Rutzky28-Sep-21 8:23
Solomon Rutzky28-Sep-21 8:23 
QuestionError Pin
xyz132311-Feb-13 0:12
xyz132311-Feb-13 0:12 
AnswerRe: Error Pin
Sam Shiles11-Feb-13 0:16
Sam Shiles11-Feb-13 0:16 
GeneralRe: Error Pin
xyz132311-Feb-13 17:34
xyz132311-Feb-13 17:34 
QuestionLog from SendData (async) Pin
Kolompár Lajos15-Jan-13 22:54
Kolompár Lajos15-Jan-13 22:54 
AnswerRe: Log from SendData (async) Pin
michaelp190313-Feb-13 1:43
michaelp190313-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
Kolompár Lajos20-Mar-12 4:43 
AnswerRe: MSVS 2008, SQL 2008 Pin
Sam Shiles20-Mar-12 4:49
Sam Shiles20-Mar-12 4:49 
GeneralStruggling to fire delegate asyncronously Pin
Dan Barua26-May-11 3:57
Dan Barua26-May-11 3:57 
AnswerRe: Struggling to fire delegate asyncronously Pin
Dan Barua26-May-11 5:13
Dan Barua26-May-11 5:13 
GeneralMethod call speed [modified] Pin
Alexander Batishchev21-Jul-10 21:02
Alexander Batishchev21-Jul-10 21:02 
GeneralRe: Method call speed Pin
Sam Shiles4-Jan-11 20:34
Sam Shiles4-Jan-11 20:34 
GeneralError on 64-bit machine Pin
rpin2230-Mar-10 18:24
rpin2230-Mar-10 18:24 
GeneralRe: Error on 64-bit machine Pin
Dave A-W2-May-10 21:09
Dave A-W2-May-10 21:09 
GeneralRe: Error on 64-bit machine Pin
Kolompár Lajos21-Feb-13 2:51
Kolompár Lajos21-Feb-13 2:51 
QuestionPassing parameters to wcf- no luck. Pin
Sotart27-Aug-09 7:38
Sotart27-Aug-09 7:38 
AnswerRe: Passing parameters to wcf- no luck. Pin
Sam Shiles27-Aug-09 8:58
Sam Shiles27-Aug-09 8:58 
GeneralRe: Passing parameters to wcf- no luck. Pin
Sotart27-Aug-09 9:24
Sotart27-Aug-09 9:24 
Sam,
In addition to what I have already included in my previous post here is what I have in Trigg1.cs right now. You can see some Pipe.Send() debug statements which work as expected. Everything is predictable and works fine exept the output in a concole window reads "Insert Occured. File name returned: " instead of "Insert Occured. File name returned: Test.xml". I tried calling myClient.InsertOccured(fileName) from Trigger1, then did the same via SendData(), then even hardcoded the value. Result is still the same - no fileName passed. It's getting lost somewhere.

In my Sql management window after the first insert I can see this (which is correct):

no guid returned

(1 row(s) affected)

After I do the second insert (including reference quid) I also see what I expected:

49627BCA-2243-4A7D-8844-12B89E031234
Is guid, ok
Reader is not null, ok
File name: Test.xml

(1 row(s) affected)

Here is the code:
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.ServiceModel;


public 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 fileName);
   

    [SqlProcedure]
    public static void SendData(string fileName)
    {
     
        /*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*/

        myClient.InsertOccured(fileName);
        SqlContext.Pipe.Send("in send Data SP. File name: " + fileName);

    }

    [SqlTrigger(Name = "WCFTrigger", Target = "tblMessageInfo", Event = "FOR 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;

        if (myContext.TriggerAction != TriggerAction.Insert) return;

        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            SqlCommand command = new SqlCommand("SELECT RequestedGuid FROM INSERTED");
            connection.Open();
            command.Connection = connection;
            string guid = null;
            using (IDataReader reader = command.ExecuteReader())
            {
                if (reader == null) return;
                while (reader.Read())
                {
                    guid = reader[0].ToString();
                }
            }

            if (string.IsNullOrEmpty(guid)) SqlContext.Pipe.Send("no guid returned");
            else SqlContext.Pipe.Send(guid);

            if (string.IsNullOrEmpty(guid) || guid.Length != 36) return;

            SqlContext.Pipe.Send("Is guid, ok");
            command.CommandText =
                "SELECT mia.OriginalIdentifier " +
                "FROM dbo.tblMessageInfo mi " +
                "JOIN dbo.tblMessageInfo mia ON mi.RequestedGuid = mia.TransactionGuid WHERE mi.RequestedGuid = " +
                "'" + guid + "'";

            string fileName = null;
            using (IDataReader reader = command.ExecuteReader())
            {
                if (reader == null) return;
                SqlContext.Pipe.Send("Reader is not null, ok");
                while (reader.Read())
                {
                    fileName = reader[0].ToString();
                    SqlContext.Pipe.Send("File name: " + fileName);
                }
            }
            
            
            
            //SLOW METHOD
            //myClient.InsertOccured(fileName);
            //had-code filename
            //SendData("Test.test");
            SendData(fileName);
            //FAST METHOD
            //d = new MyDelagate(myClient.InsertOccured);
            //d.BeginInvoke(fileName,null,null);
        }
    }
}

GeneralRe: Passing parameters to wcf- no luck. Pin
Sam Shiles27-Aug-09 10:50
Sam Shiles27-Aug-09 10:50 
GeneralRe: Passing parameters to wcf- no luck. Pin
Sotart28-Aug-09 5:27
Sotart28-Aug-09 5:27 
GeneralRe: Passing parameters to wcf- no luck. Pin
Sam Shiles28-Aug-09 6:07
Sam Shiles28-Aug-09 6:07 
GeneralRe: Passing parameters to wcf- no luck. Pin
Sotart27-Aug-09 9:46
Sotart27-Aug-09 9:46 

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.