PANVEGA’s Blog

DotNet Development, SharePoint Customizing, Silverlight, MS Infrastructure and other tips and tricks

How to create a SQL CLR-Trigger to update MOSS

Posted by PANVEGA on September 9, 2008

In my szenario I should trigger a custom Webservice which consumes a SharePoint Lists.asmx Service in order to add an item to a list. However the client did not want to cosume the custom Webservice in his website. The idea was to throw an trigger in the SQL DB when a website visitor submites a formular and stores it in the DB.

The event or trigger should call the webservice method and collect the appropriate values through a SQL Statement. In the next step the values should commit to the webserice method and call the updatelistitem function and create a new Item in a specific MOSS list.

Information:

In Microsoft SQL Server 2005 is integrated with Microsoft .NET Framework Common Language Runtime (CLR), so we can use any .NET Framework language to create database objects. The CLR provides the execution environment for all the server side objects that are created using a .NET language. This means the database developers can now perform tasks that were impossible or difficult to achieve with T-SQL alone. Especially when working with large amounts of server code, developers can easily organize and maintain their code investments.

Creates a DML, DDL, or logon trigger. A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.

These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

On your SQL Server Management Studio you will find in your DB structure under DB/Programmability your creates assembly. So since the above code compiles, well make a “Deploy” of the solution so our DLL will be in the Database When you create a new SQL ServerProject in Visual Studio, you automatically get a add Database Reference to your new Project.

Then add your custom Webservice here “HelloWorld” reference to your SQL Project.

In the next step add a new Trigger class to the project. This is the code you see below. Next right click on Solution, Add > Trigger

When deplying your project, Visual Studio automatically creates a DLL in your bin folder. You can add your assembly manually to your Global Assembly Cache GAC or let VS Deploymnet do it for you.

Here is the code insert a new Item: In this example I triggert a HelloWorld() WS function which can consume a SharePoint Service for instance.

First we need the attribute [SqlTrigger] who tell Vistual Studio to consider this class as a trigger.

Next, we need to create a variable named “triggContext ” to determine the column that is modified. The target attribute shows the name of the table. The most complicated part is to make that code work, since SQL Server 2005 has a security mechanism to call Web Services. Make an instance of your Webservice “trigger ” and call your method HelloWorld() and you should receive a string.

Below i am calling a custom Webservice which gets some contact parameter of instance here Method contact.setContact(contact). If the List update was successfully I will receive a true from the method.

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = “MyTrigger”, Target = “kontaktdaten”, Event = “FOR INSERT, UPDATE, DELETE”)]
public static void MyTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;

switch(triggContext.TriggerAction)
{
case TriggerAction.DropTable:
SqlContext.Pipe.Send(triggContext.EventData.Value);
break;

default:
Service trigger = new Service();
trigger.Credentials = System.Net.CredentialCache.DefaultCredentials;
string variable = trigger.HelloWorld();
SqlContext.Pipe.Send(variable);

//calling a custom Webservice which gets some contact parameter of instance

Trigger.MyService.Service contact= new Trigger.MyService.Service();
helloWorld.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
conatct.Email = “myEmail”;
conatct.Nachricht = “myNachticht”;
conatct.Name = “MyName”;
conatct.Ort = “myOrt”;
conatct.Plz = “MyPLZ”;
conatct.Strasse = “MyStrasse”;
conatct.Tel = “MyTel”;
bool value = contact.setContact(contact);

break;
}
}
}

In order to make this, we need to define our assembly as “unsafe“. If you want to modify and call your Webservice you receive an error. That’s because SQL Server need another assembly to access the web services, a “Serialization Assembly“. YOu find it in your bin folder e.g. Trigger.XmlSerializers.dll, too.  Simple, go to the project’s properties, and set the feature “generates serialization assembly” to “on“. Additionally you should also set the option “Allow unsafe code“.

Important: go to the “Database” tab and change the “Permission Level” setting to “Unsafe“. Note that you’ll need to do all this work because you’re accessing an external Web Service, and SQL Server considers this as a possible threat…

In the next step you should modify a property on SQL Server called Trustworthy by opening a new query and type:

ALTER DATABASE Northwind SET TRUSTWORTHY ON<code>

In the last step you need to register our Serialized Assembly DLL. First we can build the solution.

<code>CREATE ASSEMBLY xmlSerializer FROM D:\Visual Studio 2005\Projects\MyTrigger\MyTrigger\bin\Debug\MyTrigger.XmlSerializers.dll’ WITH PERMISSION_SET=EXTERNAL_ACCESS <code>

In your SQL DB you should find under Assemblies 2 references to the GAC. One for your Trigger DLL and the other is your XMLSerialzier DLL.

So finally we finished all steps.

More references:

http://msdn.microsoft.com/en-us/library/ms189799.aspx

http://www.simple-talk.com/sql/sql-server-2005/practical-sql-server-2005-clr-assemblies/

http://johanneskebeck.spaces.live.com/blog/cns!42E1F70205EC8A96!3615.entry

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: