Consuming a Web Service from a SQL Server 2005 CLR Assembly
Practical SQL Server 2005 CLR Assemblies
One advantage of CLR assemblies is the ability to consume web services from within the database. This wouldn’t be easy with T-SQL, and would also require a lot of work in an unmanaged extended stored procedure. With .NET, it’s almost as simple as accessing a local DLL. There are just a couple of extra command-line utilities we need to run to be able to access web services from within a SQL assembly:
- wsdl.exe. This utility examines the WSDL file for the web service and generates from it a source code file that performs the actual web access. We simply compile this file into our assembly and we can use the classes in it to access the web service just like a local assembly. If you use Visual Studio instead of the command-line compiler, you can omit this step, and instead simply add a web reference to your project in the VS IDE.
- sgen.exe. By default, .NET web services use dynamic XML serialization to encode .NET types for transmission over the web. Dynamic code generation would be a security liability within SQL Server, and therefore isn’t permitted for SQL assemblies. The sgen.exe utility is used to generate the XML serialization code for an assembly before installing it into SQL Server. At the time of writing, this step must be performed at the command prompt, and isn’t available within the VS IDE.
NOTE: These tools are supplied only with the .NET SDK and Visual Studio 2005 - they aren’t shipped with the distribution version of the .NET Framework that comes with SQL Server 2005. So, if you don’t have a copy of Visual Studio 2005, you’ll need to download the full version of the .NET 2.0 SDK.
For this example, we’ll use an existing public web service: the Terraserver-USA web service, which returns information about locations in the USA as well as aerial images provided by the US Geological Survey (USGS). You can find out more about Terraserver-USA at http://www.terraserver-usa.com.
Our example resides in the AdventureWorks database, and it consists of a stored procedure that takes as a parameter the ID for a row in the Person.Address table. It uses this value to look up the row and select the city name, state or province and country for the address. We then pass this information to the Terraserver-USA web service, and retrieve a list of matching places. For each of these, it queries the web service again to retrieve the image associated with the place, and then inserts a new row into a table, CityDetails, in the database containing the binary data for the image and other information about the place.
This is admittedly code that could be placed outside the database, in an external business object written in .NET, but there are two advantages to creating this as a SQL assembly. Firstly, having the code inside the database allows it to be called from within other stored procedures, triggers, and so on. This means that it can be called, for example, whenever a new row is inserted into the Person.Address table. Secondly, the assembly performs several database queries, so we reduce network traffic by performing this within the database itself.
At first sight, this looks like code that might be more usefully placed in a table-valued UDF to enable reuse. Unfortunately, however, the tables returned from TVFs aren’t permitted to contain columns of type image. Columns of type varbinary are allowed, but this data type simply isn’t big enough for our purposes.
Storing Data from the Web Service
Before we get down to writing the code, let’s first create the CityDetails table where we’ll store the data from the web service. This should be created in the AdventureWorks database, and in the Person schema:
USE AdventureWorks;
GO
CREATE TABLE Person.CityDetails
(
CityDetailsID int IDENTITY PRIMARY KEY,
AddressID int FOREIGN KEY REFERENCES Person.Address(AddressID),
Name nvarchar(256),
Latitude float,
Longitude float,
Population int,
Image varbinary(max),
CONSTRAINT UNQ_NameLatLong UNIQUE (Name, Latitude, Longitude)
);
GO
The only major point to note here is that, to avoid duplicate data being entered, we’ll add a UNIQUE constraint based on the longitude and latitude coordinates of the city and the city name, as many places in the Terraserver-USA database seem to be duplicated. Specifying all three of these for the constraint will allow near duplicates to be entered (e.g. Seattle Center beside Seattle), and also ensure that different places that share the same name can be entered.
Writing the .NET Code
The next task is to write the .NET code that implements our stored procedure. In this case, with stunning originality, we’ve called the source code file WebServiceExample.cs. As usual, we start with the using directives; the only namespaces we need to import are the usual suspects for SQL Server assemblies (System, the System.Data namespaces, and Microsoft.SqlServer.Server) :
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
The code for the stored procedure will be contained in a single method, GetCityData. This takes one parameter - the ID of the address for which we want to get the city details. We’ll start by getting the city, state, and country for this address via the in-process provider, so we first need to open up the context connection to SQL Server:
namespace Apress.SqlAssemblies.Chapter11
{
public class WebServiceExample
{
[SqlProcedure]
public static void GetCityData(int addressID)
{
using (SqlConnection cn = new SqlConnection(\"context connection=true\"))
{
cn.Open();
Next, we need to build the SQL command to perform this query. The city, state and country are all in different tables in the AdventureWorks database, so our query contains a couple of inner joins. We also need to add a single parameter to the command - the ID for the address we want the data for. We won’t be reusing this command, so we simply add the parameter and its value at the same time by calling the SqlParametersCollection’s AddWithValue method. Once we’ve done this, we call the SqlCommand.ExecuteReader method to retrieve the data from the database:
string selectQuery = @\"SELECT a.City, s.Name As State, c.Name As Country
FROM Person.Address a
INNER JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID
INNER JOIN Person.CountryRegion c
ON s.CountryRegionCode = c.CountryRegionCode
WHERE a.AddressID = @addressID\";
SqlCommand selectCmd = new SqlCommand(selectQuery, cn);
selectCmd.Parameters.AddWithValue(\"@addressID\", addressID);
SqlDataReader reader = selectCmd.ExecuteReader();
Now we have the data as a SqlDataReader, we need to extract the names of the city, state, and country into local variables. First we check that the address ID supplied did match an address in the database, and so the reader contains data. If it does, we call Read to move to the first row, and then get the data from the three columns. The query should return only a single row, so we don’t need to call Read more than once. After we’ve done that, we close the SqlDataReader as it’s no longer needed:
if (reader.HasRows)
{
reader.Read();
string city = (string)reader[0];
string state = (string)reader[1];
string country = (string)reader[2];
reader.Close();
Once we have this information, we concatenate it into a single string (separated by commas); this is the form in which we’ll pass the data to the web service. Then we’ll instantiate the web service and call its GetPlaceList method. This method takes the name of the city (the string we’ve just constructed), the maximum number of entries to return, and a Boolean parameter to indicate whether or not only entries with an associated image are to be returned, and it returns an array of PlaceFacts objects. The PlaceFacts struct is a custom type used by the web service, and the code for this class will be generated when we run wsdl.exe on the Terraserver-USA web service.
NOTE: As we’re calling wsdl.exe from the command-line, we can place the TerraService class in the same namespace as the rest of our code. However, if you’re using VS, it will by default be placed in the namespace com.terraserver-usa.www, so you’ll need to either add a using directive for this namespace at the start of the code, or fully qualify the names of all the Terraserver-specific types.
Next we’ll create the command to insert a new row into the CityDetails table. This command will be executed for each PlaceFacts object in the places array that we got back from our first call to the web service. As we’ll be calling this multiple times with different values, we need to set up SqlParameter objects for each column value that we’ll be inserting into the table, and then add these to the SqlCommand object through the Parameters.AddRange method. The one value that will always be the same is the associated address ID from the Person.Address table, so we can set this straight away:
string insertQuery = @\"INSERT INTO Person.CityDetails
VALUES (@addressID, @name, @longitude,
@latitude, @population, @image)\";
SqlCommand insertCmd = new SqlCommand(insertQuery, cn);
SqlParameter addressIDParam = new SqlParameter(\"@addressID\",
SqlDbType.Int);
SqlParameter nameParam = new SqlParameter(\"@name\",
SqlDbType.NVarChar, 256);
SqlParameter longParam = new SqlParameter(\"@longitude\",
SqlDbType.Float);
SqlParameter latParam = new SqlParameter(\"@latitude\",
SqlDbType.Float);
SqlParameter popParam = new SqlParameter(\"@population\",
SqlDbType.Int);
SqlParameter imgParam = new SqlParameter(\"@image\", SqlDbType.Image);
insertCmd.Parameters.AddRange(new SqlParameter[] { addressIDParam,
nameParam, longParam, latParam, popParam, imgParam });
addressIDParam.Value = addressID;
The other parameter values will vary for each of the PlaceFacts objects in the array, so we iterate through these, retrieve the information and call the insert command for each one. The PlaceFacts struct has a property called Center, which returns a LonLatPt object that encapsulates the longitude and latitude of the center of the place that the PlaceFacts represents. From this, we can retrieve the longitude and latitude as floating-point numbers, so we’ll use these to set the values of two of our parameters to the insert command.
We can also use this LonLatPt to find out which image we need to download, as the image data itself isn’t included in the PlaceFacts object. Each image is regarded as a tile in the map of the USA, so we need to find out which tile we want. To do this, we call the GetTileMetaFromLonLatPt method, which takes three parameters:
- The LonLatPt that we want the tile for
- The type of image we want, as an integer from 1 to 4
- The scale of the image as a Scale enum value.
Here we’ve gone for image type 1 (aerial photograph), and a scale of Scale8m, which is the highest resolution available for non-subscribers. This returns an object of type TileMeta, containing the metadata for the selected tile, which we can use to get the image data itself. The TerraService has a method called GetTile, which takes as its parameter the ID for the tile we want to retrieve, and we can pass into this the value of the Id property of our TileMeta object. This returns the binary image data as a byte array.
We also get a couple of other bits of information from the PlaceFacts object - the population of the place (although this has always been zero in the places I’ve seen), and the name of the place from the City property of the Place object associated with the PlaceFacts (the Place struct simply contains string properties returning the city, state, and country of the place). Once we’ve used this information to set the values of the parameters to our insert command, we simply execute the command:
foreach (PlaceFacts facts in places)
{
LonLatPt coords = facts.Center;
TileMeta metadata = terraService.GetTileMetaFromLonLatPt(coords,
1, Scale.Scale8m);
byte[] image = terraService.GetTile(metadata.Id);
nameParam.Value = facts.Place.City;
longParam.Value = coords.Lon;
latParam.Value = coords.Lat;
popParam.Value = facts.Population;
imgParam.Value = image;
insertCmd.ExecuteNonQuery();
}
Once we’ve finished iterating through the PlaceFacts array and inserting rows into the table, we clean up the resources and send a message to the user:
SqlContext.Pipe.Send(\"Command executed successfully.\");
terraService.Dispose();
}
If no rows were found matching the supplied AddressID in our original database query, we’ll also clean up resources and send a message to the user informing them of this fact:
else
{
reader.Close();
SqlContext.Pipe.Send(
\"No addresses in the database match the specified ID.\");
}
}
}
}
}
Generating the Custom Types used by the Web Service
That completes the .NET code that we’ll be writing for this example. However, before we compile it, we need to run wsdl.exe to generate the custom types that are used by the web service, including the TerraService class itself. The wsdl command-line tool takes as input the Web Service Description Language (WSDL) file that defines the web service (in the case of .NET web services, this is the .asmx file). In our case, we also want to pass in a couple of other options:
- /o. The name of the source code file that will be generated.
- /n. The namespace that the code will be placed in.
Because the default language for the generated source code is C#, we don’t need to specify the language. However, if you want the code to be in another language, you’ll also have to include the /l option. The possible values for this are CS (C#), VB (Visual Basic), JS (JScript), or VJS (J#).
Our call to wsdl therefore looks like this:
wsdl /o:TerraService.cs /n:Apress.SqlAssemblies.Chapter11
http://www.terraserver-usa.com/TerraService2.asmx
This will generate a C# source code file called TerraService.cs, and you should now see the message:
Writing file 'TerraService.cs'.
Compiling the Code
The next step is to compile our .NET code into a DLL assembly, including the TerraService.cs file we’ve just generated:
csc /t:library WebServiceExample.cs TerraService.cs
For a normal .NET assembly not hosted in SQL Server, we wouldn’t need to do any more than this. However, as we noted at the start of this example, for security reasons SQL Server assemblies aren’t allowed to use the default dynamically generated XML serialization assembly, and we need to generate a static serialization assembly, by calling the command-line tool sgen.exe:
sgen /a:WebServiceExample.dll
The only option we need is /a, which indicates the name of the assembly we want to generate the XML serialization classes for. This generates a serialization assembly with the name WebServiceExample.XmlSerializers.dll. If you want to overwrite an existing serialization assembly, you can use the /f option to force sgen to overwrite an existing file; if this option isn’t included and the file already exists, sgen will throw an error.
NOTE: If you recompile the assembly for any reason, you need to run sgen on it again before redeploying the assemblies to SQL Server.
Deploying the Assemblies
We’re now ready to deploy these assemblies (WebServiceExample and WebServiceExample.XmlSerializers) to SQL Server. The WebServiceExample assembly needs to be deployed first, as the serialization assembly references it (if you deploy the serialization assembly first, it creates a hidden copy of the WebServiceExample assembly, which you then can’t use to create the stored procedure).
The code generated by wsdl contains synchronization attributes, and therefore the assembly must be installed with the UNSAFE permission set:
CREATE ASSEMBLY WebServiceExample
FROM 'C:\Apress\SqlAssemblies\Chapter11\WebServiceExample\WebServiceExample.dll'
WITH PERMISSION_SET = UNSAFE;
GO
Next we install the serialization assembly. This can be installed with the SAFE permission set:
CREATE ASSEMBLY [WebServiceExample.XmlSerializers]
FROM 'C:\Apress\SqlAssemblies\Chapter11\WebServiceExample\
WebServiceExample.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO
Finally, create the CLR stored procedure:
CREATE PROCEDURE uspGetCityData(@addressID int)
AS
EXTERNAL NAME WebServiceExample.[Apress.SqlAssemblies.Chapter11.
WebServiceExample].GetCityData
GO
Testing the Example
To test the example, run the stored procedure with an appropriate address ID from the Person.Address table, and then check the contents of the CityDetails table:
EXEC uspGetCityData 3
SELECT * FROM Person.CityDetails
After running the CLR stored procedure, you should see that a couple of rows have been added to the contents of the CityDetails table, as shown in the following figure:
Since viewing the image data in hex format isn’t very exciting, the code download contains the City Image Viewer - a small Windows application written in .NET 2.0 that you can use to view the images in this table; please see the readme.txt file for information on configuring this.
The following figure shows an image from the CityDetails table displayed in the City Image Viewer application:
