Since SQL 2005 you can add .NET dlls into the SQL Assemblies, therefore get called on triggers and event from SQL to do something. Those should be simple utilities as the installation is still quite painful.
Now when you are trying to use WCF or WPF dlls referenced on your application that could be a little harder.
You’ll have to reference all the assemblies by hand that means setting the trust level .
ALTER DATABASE DatabaseName SET TRUSTWORTHY OFF
use MASTER
GRANT UNSAFE ASSEMBLY to public
Working with a DLL referencing WPF toolkit you first need all those:
CREATE ASSEMBLY [SMDiagnostics] FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMDiagnostics.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.Runtime.Serialization.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Data.Linq] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Data.Linq.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Core] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\system.core.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [WindowsBase] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\windowsbase.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [PresentationCFFRasterizer] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\WPF\PresentationCFFRasterizer.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Drawing] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [PresentationCore] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\PresentationCore.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [PresentationHost] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\WPF\PresentationHostDLL.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Windows.Forms] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.DirectoryServices] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.DirectoryServices.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [PresentationFramework] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\PresentationFramework.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [PresentationUI] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\WPF\PresentationUI.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.IdentityModel] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.IdentityModel.Selectors] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.ServiceModel] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.ServiceModel.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [Microsoft.Transactions.Bridge] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Web.Extensions] AUTHORIZATION dbo FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Web.Extensions.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [wpftoolkit] AUTHORIZATION dbo FROM 'C:\Program Files\WPF Toolkit\v3.5.40128.1\wpftoolkit.dll' WITH permission_set = unsafe
Now you are ready to add your dll into SQL
CREATE ASSEMBLY YourDLLName
FROM 'C:\temp\YourDLLName.dll'
Create a trigger to get called when there is a table insert.
CREATE TRIGGER InsertTrigger
ON TableName
FOR INSERT
AS EXTERNAL NAME Assembly.Class.Method
Of course you can add a trigger for update or delete.
Preparation
You’ll need to allow .NET CRL objects enabled on your SQL Database, to do that, please run:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
Looks like is a little complicated, yet with a little SQL script and patience you can have a very powerful trigger that will call you C# dll on SQL events.
A warning here is that there is no debugging or way to attach the debugger, so keep it simple or send the information to a WCF service.
Cheers
Al