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:

select the

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:

right click on

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

enter the web address (URL) of my web service into the URL textbox

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:

Add Web Reference

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:

create the UDF CLR object

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:

clicked the

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.

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles