WCF with CLR Trigger

Invoking a WCF Service from a CLR Trigger

By Sam Shiles, 30 Jan 2008

• Download source code - 21.9 KB

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:

 Collapse | Copy Code

using System;
using System.Collections.Generic;
using System.Text;
using System.ServiceModel;

namespace SampleService
{
    [ServiceContract]
    interface IServiceContract
    {
        [OperationContract]
        void UpdateOccured();
        [OperationContract]
        void InsertOccured();
    }

1. Implement the service contract.
1. Add a new class and name it 'ServiceContract'.
2. Replace the code in the class with:

 Collapse | Copy Code

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. Host the service
1. Replace the code in Program.cs with:

 Collapse | Copy Code

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:

 Collapse | Copy Code

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

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

 Collapse | Copy Code

use custdb
ALTER DATABASE custdb SET TRUSTWORTHY ON
reconfigure

1. 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:

 Collapse | Copy Code

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.

 Collapse | Copy Code

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 namedlocalhost.map has been created.

1. Add a trigger to the project and name it 'WCFTrigger'.
1. Replace the trigger code with the following:

 Collapse | Copy Code

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.