 |
||||||||||
| ||||||||||
| ||||||||||
Database »
Database »
General
Intermediate License: The Code Project Open License (CPOL)
Invoking a WCF Service from a CLR TriggerBy Sam ShilesA Step by step guide to communicating with WCF from a CLR trigger in SQL Server 2005 |
SQL, C# 3.0, C#, Windows, .NET, .NET 3.0SQL 2005, VS2005, SQL Server, Visual Studio, Architect, DBA, Dev Posted: 2 Nov 2007 Updated: 30 Jan 2008 Views: 26,403 Bookmarked: 22 times |
||
|
|
|||
|
Advanced Search Sitemap |
|
|
|
||||||||||||||||
Download wcfFromSQL_src.zip - 48.3 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 2005 that will communicate with a WCF service of your design. This is not an introduction to WCF but more 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 straight-forward 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 one way in which to achieve this goal.
Using the code
Prerequisites:Development machine: VS 2005, WCF extensions for VS2005, .NET 3.0 Runtime
Database machine: SQL 2005 or SQL 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 reference to: System.ServiceModel
- Add an interface to the project and call it 'IServiceContract'.
- Replace the code in the interface with:
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 - Add a new class and name it 'ServiceContract'
- Replace the code in the class with:
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 - Replace the code in Program.cs with:
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 db 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 2005. Enable CLR execution by executing the following query:
-- 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
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): use custdb
ALTER DATABASE custdb SET TRUSTWORTHY ON
reconfigure
4. As standard the assemblies that you can reference from SQL Server CLR objects is limited. In order to access some of the assemblies we need in order to communicate with WCF we need to load them into our databse. To do this execute the following query:
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 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 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:
- Right click the 'Service' project in Solution Explorer and choose 'Debug' > 'Start New Instance'.
- With the service running: Right click the 'ServiceClient' project and choose 'Add Service Reference'
- In 'Service URI' type: http://localhost:8000/services
- 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'.
- Replace the trigger code with the following:
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 of 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 Send Data SP with the correct parameters. Two methods for calling the SP are shown in the above code: the simple method (commented as SLOW METHOD), which is implemented, simply calls the SP directly. A second option (commented FAST METHOD) uses a delegate to asynccrhounsly call the SP. This helps improve the perform of the trigger.
- In my tests, using SQL profiler, the delegate based method reduce the time it took a query to complete from 100ms to 8ms!
- DISCLAIMER: I have included the Slow (syncrounous) method as the default solution in this project for the sake of simplicity. I strongly recommend using the Fast (asyncrounous) 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 DB (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 Proc: Right click your 'ServiceClient' in VS2005 and choose 'Deploy'.
4. Verify deployment of the Trigger and Proc 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.
