Using a web service straight from SQL Server 2005

Interesting idea with lots of potential. I managed to consume web services from Window Forms application,from ASP.Net web pages but never directly from a SQL Server Database. I’ve researched a little and I know that it can be done and how it can be done. So let us proceed then.

To be able to use the web service from SQL Server we will use a component called CLR (Common Language Runtime) which has been included since the 2005 version. Using this component, we will create a UDF (User Defined Function) within Visual Studio which will consume the web service and which will be used in a SQL Server Table Valued Function to get the results as a SQL table. It does sound kind of tricky but I’ll try to explain it step by step.

I will make this into 2 parts, one that will take place in Visual Studio 2005 and one that will take place in SQL Server Management Studio.

Part I – The Visual Studio side of the story

Everything starts with creating a new project in Visual Studio pf SQL Server Project type. Its name will be CLRWebService

Before creating the UDF object we need to get a reference to the web service. The web service I will use for exemplification is offered free by www.infovalutar.ro , a website owned by a friend of mine.

To add a reference right click on the name of the solution and choose from the menu the option Add web reference. In the window that appears, it he URL field write the web service’s address

http://www.infovalutar.ro/curs.asmx?wsdl

In the right site of the window you can observe all the methods offered by the service and the parameters required. In The Web reference name filed will rename it to infovalutar and will press the button Add Reference

After the window closes you can see in Solution explorer that the reference has been added.

Now we can create the UDF object. Add a new item to the solution:

As I said before, the template used will be User-Defined Function and it will have the name GetCursuriValutare. The main scope of this function will be to access the getall(Date) method of the web service and get the exchange rate for the date sent as parameter.

Before writing any code we must analyze and see what exactly this function must achieve:

• it must be read only, since it only shows data
• it will have a method to populate the data (GetCursuriValutare_FillRow)
• will return a table that will contain the fields returned by web service’s method

Let’ start by defining the functions attributes using SQLFunction command

[SqlFunction(
       DataAccess = DataAccessKind.Read,
       FillRowMethodName = "GetCursuriValutare_FillRow",
       TableDefinition =   "IDCurrency NVARCHAR(10), " +
                           "Value float  "
       )
   ]

We have a method that returns the final result

public static IEnumerable GetCursuriValutare()
    {
        DateTime dt = DateTime.Now.AddDays(-1);
        return new Curs().getall(dt).Rows;
    }

Here we take care of the DateTime parameter requested. This method returns an object Of type IEnumerable for the web service Curs() contained in the namespace infovalutar.

The GetCursuriValutare_FillRow methods populates UDF the record set from the object Curs returned as IEnumerable

public static void GetCursuriValutare_FillRow(
        object CursObj,
        out SqlString IDCurrency,
        out SqlDouble Value
        )
    {
        DataRow r = (DataRow)CursObj;
        IDCurrency = new SqlString(r[0].ToString());
        Value = new SqlDouble(Convert.ToDouble(r[1]));
    }

The final piece of code should look like this:

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using CLRWebService.infovalutar;

public partial class UserDefinedFunctions
{
    [SqlFunction(
       DataAccess = DataAccessKind.Read,
       FillRowMethodName = "GetCursuriValutare_FillRow",
       TableDefinition =
      "IDCurrency NVARCHAR(10), " +
       "Value float  "
       )
   ]

    public static IEnumerable GetCursuriValutare()
    {
        DateTime dt = DateTime.Now.AddDays(-1);
        return new Curs().getall(dt).Rows;
    }

    public static void GetCursuriValutare_FillRow(
        object CursObj,
        out SqlString IDCurrency,
        out SqlDouble Value
        )
    {
        DataRow r = (DataRow)CursObj;
        IDCurrency = new SqlString(r[0].ToString());
        Value = new SqlDouble(Convert.ToDouble(r[1]));
    }

};

Because we managed to finalize the function we must now obtain the required dll files to be able to use it in SQL Server. A very important thing to mention here, the XML object aren’t automatically serialized so we need to do this our self. This is being done using a tool included in Visual Studio: sgen.

To automate things a little bit will use Solution’s Build events to tale care of serialization. Right click on Solution name -> Properties ->Build Events and in the Command line for the Post-build event add the following

“C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe” /force “$(TargetPath)”

From the Build menu choose Build CLRWebService and pray that there will be no errors.

If everything went well, in the project folder should be 3 generated files:

If you have those files it means that the UDF creation is done. Go grab a snack and come back for the second step of this tutorial.

Step II – What does SQL Server has to say

To make things easyier will make a new folder on C:\ named CLRGetCurs in which we will copy 2 of the 3 generated files: CLRWebService.dll and CLRWebService.XmlSerializers.dll

Open SQL Server Management Studio, connect to the desired SQL Server instance and open a new Query. For exemplification we will use the AdventureWorks database.

use AdventureWorks
go
-- allows access from exterior
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON;
GO

--checking that the objects exists before creation
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'GetCursuriValutareWS')
   DROP FUNCTION GetProductWS
go
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'XmlSerializers')
    DROP ASSEMBLY [XmlSerializers]
IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'GetCusturiValutareCLR')
   DROP ASSEMBLY GetProductCLR
GO

-- create assemblys for the  2 dll files from folder CLRGetCurs
Create ASSEMBLY GetCusturiValutareCLR FROM 'C:\CLRGetCurs\CLRWebService.dll'
WITH PERMISSION_SET = External_Access

CREATE ASSEMBLY [XmlSerializers] from
'C:\CLRGetCurs\CLRWebService.XmlSerializers.dll'
WITH permission_set = SAFE
GO

We are one step closer to the finish line. We only need to create a Table Valued Function that makes use of the external UDF function:

CREATE FUNCTION GetCursuriValutareWS() 

RETURNS TABLE (
IDCurrency NVARCHAR(10),
Value FLOAT
   )
AS EXTERNAL NAME GetCusturiValutareCLR.UserDefinedFunctions.[GetCursuriValutare]
GO

And now, let us enjoy the results of our work:

Impressive, I was really amassed to see the web service result in the SQL Server Management Studio. It makes you feel you can do anything you want. I wonder if these UDF functions can be used to alter the content of the database ? Hmm… I’ll check and see.

A good source of information for this article I’ve found on www.databasejournal.com.

I hope you enjoyed this short tutorial. Until next time, Happy coding.

P.S. I know I’ve started a series of tutorials regarding n-tier concepts. Now I left this aside and I started learning about DDD (Domain Driven Design) and how these concepts can be implemented using C# techniques. Still layers but in a more effective “proven” way and a more documented concept.  I would like to THANK Emanuel for guidance and for pointing me into the right direction. There will be some interesting tutorials so stay put.

For the romanian version of the tutorial check this address http://www.infovalutar.ro/howto/sqlserver.aspx