SQLCLR, Triggers e Web Services in SQL Server 2005

Introduction

Data integration in today's applications coming from different servers/platforms play an important role. Arrival of Web Services have shown us a whole new world. Developers need more than ever to stay in touch with the new technologies offered by the recent tools.

In this tutorial we'll talk about a technology that was release with SQL Server 2005 called SQL CLR, and its integration ith Web Services.

Development

We'll use Microsoft's Northwind database. If you haven't installed it yet (since this database doesn't ship with SQL Server 2005) go to http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en These scripts are with SQL Server 2000, but they also work with the 2005 Release.

If you don't know what SQL CLR is, it's the integration between SQL Server 2005 and Visual Studio's CLR, so we can create triggers, stored procedures using our favorite language (here C#).

The example I'll show is not based Microsoft's the best practises and designs, it's only to illustrate one of the possibilities of SQL Server. The truth is it's not good to access a web service trought a trigger, due to performance concerns.

To execute this sample we'll need the following softwares:

- Visual Studio 2005 Professional

- SQL Server 2005 Developer

Let's imagine the following cenario: We have our Northwind Traders SQL database, and each time we change a product's price in dollar, we want to update another column (that we'll create) called "PriceInReal" with the current Brazilian Real's Rate from the internet. Then the following question may arise: why is this conversion not made by the client application that access the application? It's because we suppose there's a Windows Forms application and a Web Application. This way we would have to change the Real's price from inside the application, which is not practical. Well, as I've said before it's just a sample! So our trigger will catch the rate from the internet, and will automatically update all the prices in Real. To make that possible we'll use a free web service I've used some time ago and that's very easy to use. The web site is www.webservicex.net

Are you Ready?

First well need to create a column named "PriceInReal" in Northwind's Database. That should be done with no misteries, just use SQL Server Management Studio, open our database and add a new column to Products table.

So we'll create a SQL Server Project:

Wel'll call our project SQLCLRTest.

Next you'll be asked to choose a Database. Choose Northwind.

So now you should add a reference to the currency convertor web site.

You should type: http://www.webservicex.net/CurrencyConvertor.asmx

Click on Go and "Add Reference"

Ready! We now have our Web Service!

Next right click on Solution, Add > Trigger

We'll name our trigger "UpdateRates.cs".

And this will be our class's code:

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using SQLCLRTest.net.webservicex.www;

public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line
    [Microsoft.SqlServer.Server.SqlTrigger (Name="UpdateRates", Target="Products", Event="FOR UPDATE")]
    public static void UpdateRates()
    {
        SqlTriggerContext ctx = SqlContext.TriggerContext;
        if (ctx.IsUpdatedColumn(5))
        {
            CurrencyConvertor cc = new CurrencyConvertor();
            Double rate = cc.ConversionRate(Currency.USD, Currency.BLR);
            SqlConnection cnn = new SqlConnection("context connection=true");
            cnn.Open();
            SqlCommand cmd = new SqlCommand("UPDATE Products SET PriceInReal=" + rate + "*UnitPrice", cnn);
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
    }
}

Now I'll explain step-by-step the above code:

Well, first we need the attribute [SqlTrigger] who'll tell Vistual Studio to consider this class as a trigger.

Next, we need to create a variable named ctx to determine the column that is modified, which is column "5" (Unit Price). We'll then call the currency convertor from USD to BLR.

Then we call the update, no misteries!

The most complicated part is to make that code work, since SQL Server 2005 has a security mechanism to call Web Services. In order to make this, we need to define our assembly as "unsafe".

So let's see the problems that arise, as I'd like you to get involved with this.

So since the above code compiles, well make a "Deploy" of the solution so our DLL will be in the Database.

Right click on Solution, "Deploy", and because we are lazy we won't need to register the DLL manually!

Now if we try to modify some price with UnitPrice wel'll get our conversion, right?

Take it easy, you'll get the following error:

That's because SQL Server need another assembly to access the web services, a "Serialization Assembly". How can I do that? 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...

Anything else to do? Yes, you should modify a property on SQL Server called Trustworthy. How? Simply open a new query and type:

ALTER DATABASE Northwind SET TRUSTWORTHY ON<code>

Hit F5

Finished? Not yet, because we need to register our Serialized Assembly DLL...First we can build the solution. Note that you maybe need to modify the DLL path.

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

Thanks God, that's all!!If you have followed the above steps, all should be working.

Now the test phase, the most interesting.

Open the products table, and modify any product's price, and then hit Execute Query to refresh the list of updated products.

I know all these configuration are boring till you find the right solution. I've spend several hours to discover them!

Good luck and see you soon!