Calling a Web Service from within SQL Server

More and more shops are implementing web services. Doing this provides an architecture that allows applications to consume services to retrieve data. These services could be within your own organization or from a business partner. One of the problems you might run into when building applications that consume web services is how you can use web services data within a SQL Server instance. One of the reasons you might want to do this is so you can join a record set that is returned from a web service with one of your SQL Server tables. This can easily be done within an application, but how do you do this within a stored procedure that only runs within the context of SQL Server. In this article I will discuss one approach for doing this.

Using Web Services Data within SQL Server

If you need to write a T-SQL statement to join some web service information with a SQL Server table how might you go about doing this? Clearly, a web service is not a table or a view that allows you to easily join it with other compatible SQL Server objects. If you want to incorporate data from a web service into your server side logic like in a stored procedure, you need a method to call a web services directly from within SQL Server.

When Microsoft introduced SQL Server 2005, they implemented the CLR component. With a CLR, you can create a User Defined Function (UDF) that consumes a web service and returns a table, or sometimes referred to as a table value function. By using a UDF that calls a web service you are able to implement a solution that allows you to easily join a record set returned from a web service with a table or view. Using this methodology, you can now encapsulate a call to a web service within the code of a stored procedure.

Example of Building a CLR and a UDF to Consume a Web Service

For my example, I will be using the AdventureWorks database. I will be building a web service named “Product” to retrieve all the Production.Product data from the AdventureWorks database. This web service will be then be consumed by a UDF so I can join the information returned from this web service with the Sales.SalesOrderDetail table to display the Product Name information for each SalesOrderID.

First, let me show the code for my simple “Product” web services. Here is the C# sharp code for my web service:

using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MyWebService
{
    [WebService(Namespace = "MyWebSerice", Description = "Product")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

    public class wsProduct : System.Web.Services.WebService
    {
        [WebMethod(Description = "Returns all Products")]
        public System.Data.DataSet GetProduct()
        {
            DataSet ds = new DataSet();
            DataSet data = new DataSet();
            SqlConnection conn = new SqlConnection();
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Product"].ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT ProductId, Name FROM Production.Product", conn);
            // cmd.CommandType = CommandType.Text;  
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter();
            try
            {
                adapter.SelectCommand = cmd;
            }
            catch
            {
                return null;
            }
            adapter.Fill(data, "Product");
            return data;
        }
    }
}

This web service when called returns a record set that contains all the products in the Production.Product table. The record set returns a record set that only contains two columns: ProductId and Name.

Once my web service is up and running I can build my CLR. To build my CLR that calls the “Product” web service I will be using Visual Studio 2005. The first step in building my CLR is to create a new project. When I create my new project, I select the “SQL Server Project” template like so:

Before I can create my UDF object, I need to add a web reference to my project for the “Product” web service. To do this I right click on “Web Reference” in the Solution Explorer and select the “Add Web Reference…” item. When I do this, the following screen is displayed:

On this screen, I enter the web address (URL) of my web service into the URL textbox, like so:

Here you can see I entered “http://localhost/MyWebService/Product.asmx?WSDL. Once the address is typed, I click on the Go arrow. Doing this brings up the following window:

Here you can see it found my web server named “Product”. This web service only contains a single method named “GetProduct”. To finalize creating my web reference I will just need to change the “Web reference name” to something more appropriate than “localhost”. In my case, I enter “Product” in the “Web reference name” textbox and then click on the “Add Reference” button. This adds my “Product” web service as a web reference to my project.

The next step to building my solution is to create my UDF CLR object. To do this I use the Solution Explorer to add a new item. When I select the “Add New Item”, the following window is displayed:

Here I select the “User-Defined Function” template, and “Name” my UDF “GetProduct.cs”.

Here is the code for my CLR UDF:

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using UDF_CLR.Product;
public partial class UserDefinedFunctions
{
    /*
     * Author: Greg Larsen 
     * Description:  
     * This code creates a User Define Table Value Function that calls the GetProduct web service.
     * This Function is CLR that needs to be defined in SQL Server before it can be used in a T-SQL 
     * Statement. Keep in mind when building this code the following 
     * post processing is required to create a XML serialized assembly: 
     * "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force "$(TargetPath)"
     * Also a web reference called "Product" needs to be created that references: 
     * http://localhost/MyWebService/wsProduct.asmx?WSDL
    */
    [SqlFunction(
        DataAccess = DataAccessKind.Read,
        FillRowMethodName = "GetProduct_FillRow",
        // define columns returned 
        TableDefinition =
        "ProductID int, " +
        "Name ncharvar(50)  " 
        )
    ]
    public static IEnumerable GetProduct()
    {
        return new wsProduct().GetProduct().Tables[0].Rows;
    }

    public static void GetProduct_FillRow(
        object ProductObj,
        out SqlInt32 ProductID,
        out SqlString Name
        )
    {
        DataRow r = (DataRow)ProductObj;
        ProductID = new SqlInt32(Convert.ToInt32(r["ProductID"].ToString()));
        Name = new SqlString(r["Name"].ToString());
    }
};

Let me walk through this code.

In this code, I first defined my UDF using SqlFunction attribute using the following code:

    [SqlFunction(
        DataAccess = DataAccessKind.Read,
        FillRowMethodName = "GetProduct_FillRow",
        // define columns returned 
        TableDefinition =
        "ProductID int, " +
        "Name ncharvar(50)  " 
        )
    ]

In this code snippet, I identified that my UDF will:

• Be read only
• Be populated using the “GetProduct_FillRow” method in my class
• Define that there will only be two columns “ProductID” and “Name” in the table returned.

In the next section of code I create the “GetProduct() “IEnumerable” object. This code returns the data from my “wsProducts” web service one row at a time.

The last section of code in the above C# code shows the “GetProduct_FillRow” method. This method is used to populate my UDF record set from the wsProduct object returned from the “IEnumerable” object. In this section of code, I convert the data returned from my web service to the appropriate SQL Server data types for each column.

Once the code above is included in my Visual Studio project the next step is to build the solution to create the ddls for my CLR. When I was working though building my first UDF CLR object I found out the XML objects are not serialized. In order to serialize my CLR I had to perform some post-processing to incorporate the XMLSerialization object into my CLR solution. This is done by using the sgen executable. You can either setup your Visual Studio project to perform this post-processing every time you build your solution, or call this sgen executable manually. I set up my Visual Studio project to do this automatically.

It is easy to set up your Visual Studio project to automatically do the XML serialization via the post processing properties of a project. To do this with my project I right clicked on my project in the Solution Explorer window and then selected “Properties” from the drop down window. When the properties window displayed, I then clicked the “Build Event” tab item in the menus on the left. Doing that displayed the window below:

In the “Post-build event command line:” item I enter the following code to execute the sgen executable:

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

At this point my project is all set up and ready to be built to create my CLR dlls using the “Build” menu item. During the build process, Visual Studio creates two dlls and places them in the “Output Path:” location identified under the “Build” properties of my solution. One dll is named UDF_CLR.dll, and the other is named UDF_CLR.Xmlserializer.dll. These are the two dlls I will need to incorporate into my SQL Server environment in order to get my UDF function to work.

To include both these two dlls into my SQL Server environment I first copy them to a drive on my SQL Server machine. For my example, I copied them to a directory named C:\CLR. Once my dlls are copied, I run the following T-SQL code on my SQL Server machine:

use AdventureWorks
go
-- allows you to create external access CLRs
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON;
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'GetProductWS')
   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 = 'GetProductCLR')
   DROP ASSEMBLY GetProductCLR
GO

CREATE ASSEMBLY GetProductCLR FROM 'C:\CLR\UDF_CLR.dll'
WITH PERMISSION_SET = External_Access

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

CREATE FUNCTION GetProductWS() 

RETURNS TABLE (
ProductID int,
Name nvarchar(50)  
   )
AS EXTERNAL NAME GetProductCLR.UserDefinedFunctions.[GetProduct]
GO

As you can see this T-SQL code used two different “CREATE ASSEMBLY” statements to incorporate my dlls into SQL Server. The first one creates the CLR for my GetProductCLR object, and the other one to create the XmlSerializers CLR. After my assembly are created I then use the CREATE FUNCTION statement to create my GetProductWS user defined function. At this point, I am done setting up my CLR. All that is left is to test my user defined function to determine if it can successfully return the data from my GetProduct method of my wsProduct web service. To do that testing I run the following code:

SELECT * from db.GetProductWS();

This is basically all it takes to execute my UDF that call a web service. Now that I have my UDF GetProductWS, I can join the output from my web service to a SQL Server table by running some code like this:

SELECT B.SalesOrderID, A.Name [ProductName]
FROM dbo.GetProductWS() A 
JOIN Sales.SalesOrderDetail  B 
ON A.ProductID = B.ProductID

Running code like this allows me to easily include data from by web service into a T-SQL script.

Incorporating a Web Service into a T-SQL Solution

With the proliferation of web services sooner or later you will find a need to join the output of a web service with a SQL Server table using T-SQL code. The example I showed you above created a UDF function to call a web service via a CLR and return that data as a table valued function. The output from a table valued function can then be joined to a SQL Server table quite easily. This method allows you a way to incorporate output from a web services into a T-SQL solution.