Click here to monitor SSC
  • Av rating:
  • Total votes: 40
  • Total comments: 6
Julian Skinner

Practical SQL Server 2005 CLR Assemblies

28 February 2006

Consuming a Web Service from a SQL Server 2005 CLR Assembly

Julian Skinner is a freelance programmer and technical author. This article is extracted from his book, Pro SQL Server 2005 Assemblies  published by Apress in December 2005. The source code can be downloaded from here.

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:

City Image Viewer

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:

Julian Skinner

Author profile:

Julian Skinner is a freelance C# programmer and technical author. He studied Germanic etymology to PhD level before learning computer programming while working first for Wrox Press and then for Apress. He is a co-author of The Programmer's Guide to SQL, Pro SQL Server 2005 and Pro SQL Server 2005 Assemblies. You can contact him through his website at http://www.JulianSkinner.com.

Search for other articles by Julian Skinner

Rate this article:   Avg rating: from a total of 40 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: WS problem
Posted by: Anonymous (not signed in)
Posted on: Friday, August 18, 2006 at 5:52 AM
Message: I need to write a simple stored procedure for SQL 2005 calling web services from Project Server 2007. I must use these web services to: log on to the project server, create a project, log out. I wrote the .dll file. I have created the assemblies etc. The problem occurs when calling that stored procedure. I am getting error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'Main1':
System.Net.WebException: The request failed with the error message:
--
<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="http://my_comp:22278/projectserver/_layouts/1033/error.aspx?ErrorText=Object%20reference%20not%20set%20to%20an%20instance%20of%20an%20object%2E">here</a>.</h2>
</body></html>

--.
System.Net.WebException:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at myAsm.ProjectWebSvc.Project.QueueCreateProject(Guid jobUid, ProjectDataSet dataset, Boolean validateOnly)
at myAsm.Program.Main1()
------------------------------------------

it means that the problem occurs when calling QueueCreateProject(...). Error: Object reference not set to an instance of an object

the code is as follow:


using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Net;
using PSLibrary = Microsoft.Office.Project.Server.Library;

namespace ConsoleApplication11
{
public class Program
{
public static void Main1()
{
const string LOGINWINDOWS = "_vti_bin/PSI/LoginWindows.asmx";
string baseUrl = "http://localhost:22278/projectserver/";
CookieContainer cookies = new CookieContainer();

LoginWindowsWebSvc.LoginWindows loginWindows = new LoginWindowsWebSvc.LoginWindows();
loginWindows.Url = baseUrl + LOGINWINDOWS;
loginWindows.Credentials = CredentialCache.DefaultCredentials;

loginWindows.Login();
ProjectWebSvc.Project project = new ProjectWebSvc.Project();
project.Credentials = loginWindows.Credentials;

project.Url = baseUrl + "_vti_bin/psi/project.asmx";
ProjectWebSvc.ProjectDataSet dsProject = new ProjectWebSvc.ProjectDataSet();
ProjectWebSvc.ProjectDataSet.ProjectRow projectRow = dsProject.Project.NewProjectRow();
Guid projectGuid = Guid.NewGuid();
projectRow.PROJ_UID = projectGuid;
projectRow.PROJ_TYPE = (int)PSLibrary.Project.ProjectType.Project;
projectRow.PROJ_NAME = "Name";
projectRow.PROJ_SESSION_UID = Guid.NewGuid();
dsProject.Project.AddProjectRow(projectRow);
Guid jobGuid = Guid.NewGuid();

project.QueueCreateProject(jobGuid, null, false);

System.Threading.Thread.Sleep(5000);

loginWindows.Logoff();
project.Credentials = null;
}
}
}


any help will be appreciated.

my email is: kczechlik@gmail.com

Subject: Converting assembly into byte stream
Posted by: Anonymous (not signed in)
Posted on: Monday, August 21, 2006 at 3:09 AM
Message: In Create Assembly,other than the .dll file, MSDN says that it is possible to give the assembly as a byte stram also. Any idea, how to do that??

Subject: WebService Problem
Posted by: Anonymous (not signed in)
Posted on: Tuesday, March 27, 2007 at 6:15 AM
Message: I have followed all the steps as detailed above, however when I call execute on my function I receive the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "TestFunction":
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
System.Net.Sockets.SocketException:
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)

The assembly is fine as it works when added to another project, but I wondered if there was a default setting in SQLExpress which prevents the webservice from being called?

Thanks

Subject: (de)Serialization of arrays
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 02, 2007 at 1:41 AM
Message: I'm running the non-express version of Sql server 2005. I have a SQL CLR stored-proc which has a SqlString parameter. The logic in C# SP attempts to deserialize the value of SqlString param into array of integers. I've sgen-ed my serializer assembly and registered in Sql server. I deploy my SP with unsafe option and being logged in under a user account with sysadmin and dbo permissions. I do not have any problems creating/deploying SP either. But when I tried to execute the SP, I ran into "System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer" error. I'm not sure why I still receive this error when I've alredy registered the serializer assembly. The problem ONLY occurs when deserializing array/collections. If I deserialize a single "Employee" object with FirstName and LastName properties then I do not encounter the problem.

Has anyone runinto similar problem and found a way out?

Thanks,

- Malhar

send email to: malhar1 at hotmail dot com

Subject: AWESOME!
Posted by: Anonymous (not signed in)
Posted on: Friday, February 15, 2008 at 6:32 PM
Message: This was exactly what we needed! Thanks!

Subject: Creating ASSEMBLY
Posted by: Aurimas (view profile)
Posted on: Sunday, March 14, 2010 at 9:40 AM
Message: Hello,

I tried to create procedures by your example. I got an error while creating an ASSEMBLY.

After:
CREATE ASSEMBLY ClrWebServices
FROM 'C:\Work\ws\InsertLocation.dll'
WITH PERMISSION_SET = UNSAFE;
GO

I got the error:

Msg 6218, Level 16, State 3, Line 1
CREATE ASSEMBLY for assembly 'InsertLocation' failed because assembly 'InsertLocation' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message

The parameter trustworthy is on.
Maybe You have any ideas?

Thank You.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.