CLR Stored Procedure Calling External Web Service - SQL Server 2005 Tutorials

SQL Server 2005 has a number of cool new developer features, such as Http Endpoint, XML DataType, etc. The CLR ( Common Language Runtime ) is also hosted inside of SQL Server 2005, allowing one to leverage the power of the .NET Framework and T-SQL in a power combination, exploiting each to do what it does best. A good example I previously wrote was to leverage regular expressions ( RegEx ) in .NET and CLR User Defined Functions ( UDF 's ) to put a check constraint on a table that verifies an email address is indeed an email address when entered into a table:

SQLCLR - Create CLR User-Defined Function ( UDF ) - Check Constraint on EmailAddress Column Using RegEx
In this example, I am exploring the ability to write a CLR stored procedure that calls an external web service from within the stored procedure before inserting a record in a table. The developer in me says that this is not something I would do in a production environment, but the hobbyist in me wanted to test this functionality just to see how it could be done.

The inspiration for this example came from another article, called Consuming a Web Service from a SQL Server 2005 CLR Assembly, in Simple Talk doing essentially the same thing. I ran across some problems while working through the tutorial which I thought I would highlight here as well as changed the scenario so it was easier to understand, more thorough in many cases, and showed off a few other things. However, the inspiring tutorial is very enightening, so I recommend reading it here.



CLR Stored Procedure Application Architecture
The architecture for this CLR stored procedure is fairly straight forward. The client of this application calls a CLR stored procedure, called InsertLocation, which accepts 3 input parameters: City, State, and Country.

InsertLocation calls the publicly available external web service, called Terraserver-USA web service, to get an aerial view map of the city as stored in its database of aerial images provided by the US Geological Survey (USGS).

The City, State, Country, and AerialMap is then stored in a table, called Locations.

The architecture can be briefly shown as follows:







Enabling CLR in SQL Server 2005
First things first, one needs to enable CLR in SQL Server 2005 by doing the following:



sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO


Create ClrWebServices Database and Make Database Trustworthy
Create the ClrWebServices Database. Since our database will be accessing external resources, you will need to make it trustworthy:



alter database ClrWebServices set trustworthy on


Create Locations Table
Create the Locations Table in the ClrWebServices Database:



USE [ClrWebServices]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Locations](
[ID] [int] IDENTITY(1,1) NOT NULL,
[City] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[State] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Country] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AerialMap] [image] NOT NULL,
CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


WSDL - Run WSDL.exe Utility to Generate Web Service Proxy
We need a c# proxy class that will be used to access the Terraserver-USA web service. The WSDL.exe application located in the Visual Studio SDK/Bin directly will do this for us:



wsdl /o:TerraService.cs /n:ClrWebServices.Text
http://www.terraserver-usa.com/TerraService2.asmx


This generates a file, called TerraService.cs, that has a public c# class within it, called TerraService, within the namespace ClrWebServices.Test.



Create CLR Stored Procedure
Create the CLR Stored Procedure that uses TerraService to obtain a aerial view map of the city and then store the city, state, country, and map in the Locations Table:



using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using ClrWebServices.Test;


public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void InsertLocation(SqlString city,
SqlString state, SqlString country)
{
using (SqlConnection cn =
new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.Locations
(City,State,Country,AerialMap) VALUES
(@City, @State, @Country, @Image)";

using (SqlCommand insertCommand =
new SqlCommand(query,cn))
{
SqlParameter[] sqlParams =
new SqlParameter[4];
sqlParams[0] =
new SqlParameter("@City", city);
sqlParams[1] =
new SqlParameter("@State", state);
sqlParams[2] =
new SqlParameter("@Country", country);
sqlParams[3] =
new SqlParameter("@Image",
GetLocationImage(city,state,country));

insertCommand.Parameters.AddRange(sqlParams);

cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}
}

public static byte[] GetLocationImage(SqlString city,
SqlString state, SqlString country)
{
TerraService service = new TerraService();

Place place = new Place();
place.City = city.ToString();
place.State = state.ToString();
place.Country = country.ToString();

PlaceFacts facts = service.GetPlaceFacts(place);

TileMeta meta = service.GetTileMetaFromLonLatPt
(facts.Center, 1,
ClrWebServices.Test.Scale.Scale16m);
return service.GetTile(meta.Id);
}
};


This file is saved as InsertLocation.cs.



Compile CLR Stored Procedure with Web Services Proxy
Now one needs to compile the CLR Stored Procedure and Web Services Proxy into a single DLL as well as create XML Serialization Code, because dynamic XML Serialization is not allowed in SQL Server.



// Creates InsertLocation.dll
csc /t:library InsertLocation.cs TerraService.cs

// Generate XML Serialization Code
// Creates InsertLocation.XmlSerializers.dll
sgen /a:InsertLocation.dll


Create Assemblies and Stored Procedure
The very last piece is to create the assemblies and stored procedures in SQL Server 2005.



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


CREATE ASSEMBLY [ClrWebServices.XmlSerializers]
FROM 'C:\ClrWebServices\InsertLocation. XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO


CREATE PROCEDURE InsertLocation(@city nvarchar(200),
@state nvarchar(200), @country nvarchar(200))
AS
EXTERNAL NAME ClrWebServices.StoredProcedures .InsertLocation
GO


Here you can see all the major players in out CLR stored procedure example:









Test CLR Stored Procedure
Now one can go ahead and execute the stored procedure, InsertLocation, from withing SQL Server Management Studio. While executing the CLR stored procedure will execute a couple of web services calls to Terraserver to get the proper aerial view map. Don't expect it to be fast :) Check the Locations Table to verify the image has been properly stored in the AerialMap column.







Conclusion
CLR Stored Procedures can consume XML web services inside them. Although it is technically possible, this may not be the best architectural approach in your .NET applications. Proceed with caution, but have fun.