Introduction
Four years ago, not many people knew about web services and fewer still had seen their practical use. I argued that a zip code application was ideal to implement as a web service because so many other types of applications could share its functionality. My article at the time, How to Create a Zip Code Webservice described how to complete a zip code locater using the best then available Microsoft technologies: SQL Server 2000 and ASP.NET 1.x. Those technologies were appropriate in a number of ways: Zip applications are data-intensive, with Zip codes changing over time, so the data must be updated regularly. SQL 2000 Data Transformation Services (DTS) automated the import and update process. Zip code calculations are mathematically intensive: T-SQL user-defined functions isolated the procedural logic. Zip code clients are ubiquitous, so IIS combined with ASP.NET exposed the zip code methods as a web service.
Nowadays, there are several map API's exposed as web services such as Google Maps and MSN MapPoint, but if you implement your own service, you get several benefits. First, you'll have complete control over hosting the service. For a variety of reasons, many corporations prefer to host their applications entirely in-house. Second, if you sell a software solution where the zip service is one of several components, you can install your application suite without worrying about the royalties or the support issues that come from using a third party. Third, if you are a typical programmer, you'll just love the sense of supernatural power you get from doing things without relying on Google or Microsoft.
SQL Server 2005 allows you to implement a zip code web service without ASP.NET or IIS. I'll show you how to create a self-contained zip code web service using the latest SQL Server 2005 Technologies: SQL Server Integration Services (SSIS), CLR integration, and Endpoints.
System Requirements
To run the code for this article you should have installed:
- Visual Studio 2005 Professional
- SQL Server 2005 Developer Edition
- The ZIPUSA.ASC data file that is available with my original article on ASP Today, How to Create a Zip Code Webservice. See the related links at the end of this article if you do not have access to the download for the original article.
Although Visual Studio 2005 comes with SQL Server 2005 Express, the Express version does not install the proper Visual Studio project templates, nor does it allow you to create endpoints. To expose the zip code application as a web service, you need to install one of the full versions of SQL Server 2005 separately from VS 2005.
Preparing SQL Server 2005
In this section, you'll create the ZipCodes database and enable CLR Integration in SQL Server. You should follow the directions in this section even if you are building your Visual Studio projects from scratch.
The sample download for this article contains a VS.NET solution that contains four projects.
- Zip Integration Services is the Integration Services Project that allows you to import the zip data from the ASCII flat file into your database. An Integration Services Project is new to Visual Studio 2005 and designed to use SQL Server Integration Services (SSIS) to move and transform data.
- ZipCodeSqlClr is a C# SQL Server Project that contains the code for the CLR stored procedures and user-defined functions. A SQL Server Project is also new to Visual Studio 2005 and is used to create CLR stored procedures, user-defined functions, user-defined types, aggregates, and triggers.
- ZipCodeDB is a Database Project that contains the raw T-SQL necessary to create both the zip code table and the endpoint that exposes your application as a web service. A Database Project is designed to manage T-SQL scripts and queries.
- ZipCodeClient is a C# Windows Application project that tests the zip code web service. A Windows Application project creates the traditional desktop application that uses Windows Forms.
Creating the ZipCodes Database
You will first need to create a database called ZipCodes before running any of the code. Open up VS2005, navigate to the Server Explorer, and then right-click on the Data Connections node. Select Create New SQL Server Database… from the context menu. Fill in the dialog as shown in Figure 1, and then click the OK button to create the database.
Figure 1. Creating the ZipCodes database
After you create the ZipCodes database, you'll need to create the ZIPUSA table. Open the ZipCodes solution in VS2005 and navigate to the ZipCodeDB project. In the Create Scripts folder, right click on the CreateZipUsaTable.sql script, and select Run from the context menu to execute the following SQL:
CREATE TABLE dbo.ZIPUSA ( ZIP_CODE varchar(50) NULL, CITYSTNAME varchar(50) NULL, STATE varchar(50) NULL, LAT real NULL, LNG real NULL, LAT_RADIANS AS RADIANS(LAT) PERSISTED, LNG_RADIANS AS RADIANS(LNG) PERSISTED, SOUNDEX_CITYNAME AS SOUNDEX(CITYSTNAME) PERSISTED )
The ZIPUSA.ASC flat file contains latitude and longitude coordinates specified in degrees. However, the zip code algorithms operate on coordinates specified in radians, so the table includes two calculated columns that use the RADIANS T-SQL function.
You'll also want searches by city name to tolerate misspellings, so another calculated column stores the SOUNDEX of the original city name. The SOUNDEX algorithm transforms words that sound similar into the same four-character code. The PERSISTED keyword ensures that the column values are stored rather than recalculated with every query. Using the PERSISTED keyword also allows the computed columns to be indexed.
Enabling SQL CLR Integration
SQL Server must be CLR enabled to create CLR stored procedures and user-defined functions. SQL Server is not CLR enabled by default. You can enable CLR Integration either via the sp_configure stored procedure, or through the SQL Server Surface Area Configuration Tool . To use the stored procedure approach, issue the following commands while connected to SQL Server:
EXEC sp_configure ‘clr enabled', 1 GO RECONFIGURE GO
To open the configuration tool, select the [SQL Server Menu]\Configuration Tools\SQL Server Surface Area Configuration Tool shortcut. In the window that opens, click the Surface Area Configuration for Features hyperlink. In the dialog that opens (see Figure 2), navigate to the MSSQLSERVER \ Database Engine \ CLR Integration node, and then make sure that the Enable CLR Integration checkbox is checked. Click the OK button to accept your changes.
Figure 2. Enabling SQL Server CLR
Solution Overview
The completed zip code service will expose three methods.
- GetZipCodeDistance() will return the distance in miles between any two zip codes
- GetLocationsNearZipcode() returns information about locations within a radius of a zip code
- GetZipsFromCityName() will return all the zip codes whose city name sounds similar to a given name
You can complete the service in three steps.
First, you'll import the zip data by creating an Integration Services Project that moves the zip data from a text file into the ZIPUSA data table.
Second, you'll create the zip code algorithms in a SQL Server project with CLR stored procedures and user-defined functions. This project will contain the bulk of the code that performs both the trigonometric calculations and data access logic.
Third, you'll use T-SQL to create an HTTP endpoint in SQL Server that exposes your CLR stored procedures as SOAP web methods.
To test the finished product, you'll create a trivial Windows Forms project to consume the web methods and graphically display the results in a DataGridView .
Developing the Zip Code Web Service
Figure 3 is a DataGridView that shows the results of calling the GetLocationsNearZipcode() web method.
B
Figure 3. Sample application showing locations within 60 Miles of 00601
Figure 4 demonstrates the results of calling the GetZipsFromCityName() web method.
Figure 4. Sample application showing zip codes whose city sounds like "Villa"
Figure 5 shows the results of consuming the GetZipCodeDistance() web method.
Figure 5. Sample application showing the distance between 00501 and 00601
Importing the Zip Code Data
In this section, you'll create the Integration Services Project that moves the zip code data from the text file into SQL Server. This section assumes that you've already created your ZipCode database and the ZipUsa table, as described in the Preparing SQL Server 2005 section.
Start up Visual Studio 2005 and make sure there is a Data Connection to the ZipCodes database in the Server Explorer window similar to Figure 6. If you did not create the ZipCode database through the Server Explorer , you will have to add a Data Connection manually.
Figure 6. Connection to the ZipCodes database
Next, create a new Business Intelligence Project using the Integration Services Project template. Name the project Zip Integration Services as shown in Figure 7.
Figure 7. Creating a new Integration Services Project
The wizard will automatically create a new IS package named Package.dtsx . If Package.dtsx does not automatically open for you in design view, navigate to the SSIS Packages node of the project, and double-click on Package.dtsx to open the package designer.
Create a Data Flow Task
Once the designer opens, click on the Data Flow tab, and then click on the hyperlink that says No Data Flow tasks have been added to this package. Click here to add a new Data Flow task. In the Toolbox window, double-click on the Flat File Source component in the Data Flow Sources section to produce the results shown in Figure 8.
Figure 8. Data flow task with a Flat File Data Source
Create the Connection Manager
At this point, you need to create and assign a Connection Manager to the Flat File Source . Right click on the Flat File Source icon in the designer and then select Edit… . In the Flat File Source Editor dialog that opens, click on the New… button to open a Flat File Connection Manager Editor dialog. In the General node of this dialog, fill out the form as shown in Figure 9.
Figure 9. Creating a Flat File Connection Manager.
- The File name should contain the complete path to your ZipUsa.ASC text file, which contains the zip code data.
- The Text qualifier field should contain a double-quote character, since strings in the text file are surrounded by double quotes.
- The Column names in the first data row checkbox should be checked, because the first row of the text file contains the names of the columns.
Next, click on the Advanced node. Here, you can fine-tune the data types of each column. By default, all columns are set to be strings; you'll see this is the case if you click on the LAT and LNG columns as shown in Figure 10.
Figure 10. Default String Data Types for columns
To correct the data types, click on the Suggest Types… button. In the dialog that opens, change the number of rows to 500, and then click the OK button. The editor will use a little artificial intelligence to analyze the rows of data and guess the column data types. Notice that the data type for LAT and LNG has changed to float . This works well for the trigonometric calculations that use latitude and longitude. However, if you click on the ZIP_CODE column, you'll notice that the type has been changed from string to two-byte signed integer . Although zip codes are represented by integers, you don't perform mathematical calculations on them directly: They are really nominal designations. Make sure you change the ZIP_CODE data type back to string . Click OK to close the Flat File Connection Manager .
Finally, click on the Columns node of the Flat File Source Editor . In this project you'll only need a few of the columns from the text file. Uncheck all of the columns except ZIP_CODE , CITYSTNAME , STATE , LAT , and LNG . Click OK again to close the Flat File Source Editor .
Creating the SQL Server Destination
Now that you've created a connection to access the zip data, you can create an output destination for the data. From the Destinations section of the Toolbox , drag a SQL Server Destination to the Data Flow Designer . Click on the Flat File icon, then drag and drop the green arrow onto the new SQL destination as in Figure 11.
Figure 11. Creating a SQL Server Data Flow Destination
Right click on the SQL Server Destination icon and choose Edit… . In the dialog that opens, select the ZipCode database and ZipUSA table for the respective Connection Manager dropdowns. Click on the Mappings node, and then drag from the Available Input Columns table to the Available Destination Columns table so that each of the corresponding columns match up as in Figure 12.
Figure 12. Matching columns between the data source and destination
Save and compile your changes, and then run the project in the debugger. Your data will be imported from the flat file to the ZipUSA table in your database.
Create the Zip Code Algorithms
Now that you've successfully imported the data, you can create the SQL CLR functions and procedures that perform the zip calculations. You'll create two user-defined functions ( GeoDistanceFunction() and LocationsInRadiusFunction() ) and three stored procedures ( ZipDistanceProcedure , LocationsNearZipProcedure , and ZipsFromCityNameProcedure ).
Add a new C# SQL Server Project named ZipSqlClr (see Figure 13). Note the location of the project template in the Project Types tree: under the Visual C#\Database node. The Database Project template under Other Project Types\Database creates the traditional project for a library of SQL text scripts rather than a SQL CLR library.
Figure 13. Adding a SQL Server Project
Creating the CLR User-Defined Functions
The following sections will provide a detailed look at each of the CLR User-Defined functions.
GeoDistanceFunction()
The first user-defined function calculates the distance between any two geometric coordinates. You can add user-defined functions to the project by right-clicking on the project node and selecting Add / User-Defined Function , but you would have to edit the code quite a bit. Instead, add a new GeoDistanceFunction class to the project with the following content:
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public class GeoDistanceFunction
{
[SqlFunction(
Name = "Ufn_GetGeoDistance",
IsDeterministic = true,
IsPrecise = false
)]
public static SqlDouble GetGeoDistance(
SqlDouble latitudeA,
SqlDouble longitudeA,
SqlDouble latitudeB,
SqlDouble longitudeB
){
//Code omitted
}
}
This class creates a scalar-valued function that returns the distance between two latitudes and longitudes.
The SqlFunctionAttribute indicates to the compiler that the GetGeoDistance() method should be exposed in ZipCodes.ZIPUSA as a user-defined function whose name is Ufn_GetGeoDistance() . The name of the function exposed in SQL Server need not be the same as the method called in the CLR class.
The IsDeterministic property indicates whether passing the same parameter values to the function always produces the same result. Setting the value to true allows SQL Server to properly optimize indexes on columns that depend on the function. The IsPrecise attribute has a similar optimization purpose, but pertains to whether the function is subject to numerical rounding error. Calculations with SqlDoubles can introduce imprecise results due to the nature of floating point calculations, so the IsPrecise property is set to false .
The System.Data.SqlTypes namespace contains CLR structs that semantically map to SQL Server native data types. SqlTypes allow you to check for SQL NULL values, which is necessary when you perform logic involving data columns that have been configured to allow NULLs.
A SQL NULL is not the same as a CLR null . GetGeoDistance() may be passed values from the database where the latitude or longitude for one of the coordinates is NULL , indicating that the value is unknown, rather than does not exist. This makes sense, in that a zip code must physically have an associated latitude and longitude, but its coordinate will not necessarily be recorded in the corresponding database record. In this case, the SqlType object passed to the function will be a valid (i.e., non CLR null ) object reference, but its IsNull property will return true . The first line in the GetDistanceFunction() indicates that if one of the coordinate values is undetermined (i.e., is a SQL NULL ), then the value for the distance between the coordinates is undetermined as well.
if( latitudeA.IsNull || longitudeA.IsNull || latitudeB.IsNull ||
longitudeB.IsNull )
return SqlDouble.Null;
The second line of the method returns the results of the trigonometric distance calculation:
return 3959 * Math.Acos(
( Math.Sin( latitudeA.Value ) * Math.Sin( latitudeB.Value ) ) +
( Math.Cos( latitudeA.Value ) * Math.Cos( latitudeB.Value )
* Math.Cos( longitudeB.Value - longitudeA.Value ) )
);
See the Related Links section for a link to the details on how this calculation works.
LocationsInRadiusFunction()
The second user-defined function is a table-valued function that returns all the database records within a specified radius of a coordinate. Add a new class named LocationsInRadiusFunction to the project.
A table-valued function is more complicated to create than a scalar-valued function in that it requires at least two methods in the corresponding CLR class.
- The Initialization() method creates the desired set of data and puts it into an enumerable container. The table structure that ultimately returns is described in the SqlFunction attribute that decorates the method.
- The FillRow() method is called once for each item in the enumerable container. Its job is to transform the current item of the enumerable into a row that conforms to the table structure specified by the initialization method's SqlFunction attribute.
The initialization method must be named InitMethod() , as the compiler uses reflection to identify table-valued functions by this name. The return value must be an IEnumerable interface. This abstraction is a clever design that allows you to return just about any enumerable collection as a SQL data stream: a list of local directories, a mathematical series, or even the results of a query to a third party API. Each item in the enumeration will typically correspond to a row of data you wish to return to the client. The only hitch is that you must be able to translate each item into a set of valid SQL data types via the FillRow() method.
[SqlFunction(
Name = "Ufn_GetLocationsInRadius",
DataAccess = DataAccessKind.Read,
TableDefinition = "city NVARCHAR(50), state NVARCHAR(50), zipCode
NVARCHAR(50), distance float",
FillRowMethodName = "FillRow" )
]
public static IEnumerable InitMethod(
SqlDouble latitude,
SqlDouble longitude,
SqlInt32 radius ){}
The [SqlFunction] attribute's Name property indicates that the name of the function in SQL Server will be Ufn_GetLocationsInRadius . DataAccessKind.Read indicates that the function will read data from one or more SQL tables when it is called.
The TableDefinition property defines the column layout of the resulting table that will be marshaled to the calling client; in this case it is a table with three NVARCHAR(50) columns and one float column.
FillRowMethodName names the method that fills each row of the table with data in a manner consistent with the table's column layout (three NVARCHARs and a float). The signature of the FillRow() method directly corresponds to the columns specified in the TableDefinition . To conform, the FillRow() method must have a signature like so:
public static void FillRow( object currentEnumerableItem, out params[] columns );
There should be one out parameter for each column specified in the InitMethod() TableDefinition . In addition, the type of each out parameter must be a SqlType consistent with the native SQL data type specified in the corresponding column of the TableDefinition . The compiler will complain if the FillRow() signature is inconsistent with the TableDefinition . Here is the method as actually specified in the sample code:
public static void FillRow( Object current, out SqlString city, out
SqlString state, out SqlString zipCode, out SqlDouble distance ){}
Notice that there are three SqlString out parameters and one SqlDouble out parameter, which corresponds to the three NVARCHAR columns and one float column. SQL Server takes the IEnumerable results of InitMethod() and calls FillRow() once for each item in the enumeration. The current item is passed via the first parameter as an Object . Within the method, it is your job to cast this object into a type (typically a list of some sort) that allows you to extract a value for each column, and then assign that value to the corresponding out parameter. In this example, the IEnumerable returned by InitMethod() is a collection of System.Data.DataRow , so FillRow() casts the first parameter to a DataRow , then uses the row's indexer to assign a value to each of the out parameters.
DataRow row = (DataRow)current; zipCode = (string)row["ZIP_CODE"]; city = (string)row["CITYSTNAME"]; state = (string)row["STATE"]; distance = (double)row["DISTANCE"];
Let's return to the implementation of InitMethod() . The following SQL SELECT statement queries the SQL database for the records that are within a fixed radius of a latitude - longitude coordinate:
SELECT ZIP_CODE,
CITYSTNAME,
STATE,
dbo.Ufn_GetGeoDistance( @latitude, @longitude, LAT_RADIANS,
LNG_RADIANS ) AS DISTANCE
FROM ZIPUSA
WHERE LAT_RADIANS <= (@latitude + @latRange)
AND LAT_RADIANS >= (@latitude - @latRange)
AND LNG_RADIANS <= (@longitude + @lngRange)
AND LNG_RADIANS >= (@longitude - @lngRange)
ORDER BY DISTANCE
Notice how your Ufn_GetGeoDistance() function is called to calculate the distance of each database coordinate from the coordinate supplied as a parameter. The extra constraints in the WHERE clause narrow the range of longitudes and latitudes, thereby reducing the number of rows upon which the distance function is called. Note: Check the user comments of my previous article for a description of a more efficient algorithm.
To execute this SQL against the database, first calculate the @latRange and @lngRange parameter values:
double latRange = radius.Value / ( ( 6076 / 5280 ) * 60 );
double lngRange = latitude.Value /
( ( ( Math.Cos( latitude.Value ) * 6076 ) / 5280 ) * 60 );
Next, create a SqlCommand that executes the SELECT statement against the database:
SqlCommand command = new SqlCommand(
SELECT,
new SqlConnection( "Context Connection=true" ) );
command.Parameters.AddWithValue( "@latitude", latitude );
command.Parameters.AddWithValue( "@longitude", longitude );
command.Parameters.AddWithValue( "@latRange", latRange );
command.Parameters.AddWithValue( "@lngRange", lngRange );
Since the code is already running within the SQL Server CLR, it uses a Context Connection to directly connect to SQL Server's data engine. Finally, fill an ADO.NET DataTable with the results of the query and return its Rows property.
SqlDataAdapter adapter = new SqlDataAdapter( command ); DataTable table = new DataTable(); adapter.Fill( table ); return table.Rows;
DataTable does not implement IEnumerable , so it can not be the return value of the function. However, the Rows property of DataTable is a DataRowCollection , which implements IEnumerable . Hence the rows are returned by the method rather than the table itself.
Here's the completed class:
public class LocationsInRadiusFunction
{
private const string SELECT = @"
SELECT ZIP_CODE,
CITYSTNAME,
STATE,
dbo.Ufn_GetGeoDistance( @latitude, @longitude, LAT_RADIANS,
LNG_RADIANS ) AS distance
FROM ZIPUSA
WHERE LAT_RADIANS <= (@latitude + @latRange)
AND LAT_RADIANS >= (@latitude - @latRange)
AND LNG_RADIANS <= (@longitude + @lngRange)
AND LNG_RADIANS >= (@longitude - @lngRange)
ORDER BY DISTANCE
";
[SqlFunction(
Name = "Ufn_GetLocationsInRadius",
DataAccess = DataAccessKind.Read,
TableDefinition = "city NVARCHAR(50), state NVARCHAR(50),
zipCode NVARCHAR(50), distance float",
FillRowMethodName = "FillRow" )
]
public static IEnumerable InitMethod( SqlDouble latitude,
SqlDouble longitude, SqlInt32 radius )
{
if( latitude.IsNull || longitude.IsNull )
return new ArrayList();
if( radius.IsNull || radius < 60 ) radius = 60;
double latRange = radius.Value / ( ( 6076 / 5280 ) * 60 );
double lngRange = latitude.Value /
( ( ( Math.Cos( latitude.Value ) * 6076 ) / 5280 ) * 60 );
SqlCommand command = new SqlCommand(
SELECT,
new SqlConnection( "Context Connection=true" ) );
command.Parameters.AddWithValue( "@latitude", latitude );
command.Parameters.AddWithValue( "@longitude", longitude );
command.Parameters.AddWithValue( "@latRange", latRange );
command.Parameters.AddWithValue( "@lngRange", lngRange );
SqlDataAdapter adapter = new SqlDataAdapter( command );
DataTable table = new DataTable();
adapter.Fill( table );
return table.Rows;
}
public static void FillRow( Object current, out SqlString city, out
SqlString state, out SqlString zipCode, out SqlDouble distance )
{
DataRow row = (DataRow)current;
zipCode = (string)row[0];
city = (string)row[1];
state = (string)row[2];
distance = (double)row[3];
}
}
Creating the CLR Stored Procedures
The stored procedures will be much more straightforward to create than the user functions. Two of the procedures require you to find the latitude and longitude that is associated with a zip code, so this functionality is factored into a shared method that creates the appropriate SqlCommand to retrieve the coordinate:
internal static SqlCommand GetCommand( SqlString zipcode )
{
const string SELECT = @"
SELECT @latitude=LAT_RADIANS,
@longitude=LNG_RADIANS
FROM ZIPUSA
WHERE ZIP_CODE=@zipcode";
SqlConnection conn = new SqlConnection( "Context Connection=true" );
SqlCommand command = new SqlCommand( SELECT, conn );
command.Parameters.Add( "@latitude", SqlDbType.Float ).Direction
= ParameterDirection.Output;
command.Parameters.Add( "@longitude", SqlDbType.Float ).Direction
= ParameterDirection.Output;
command.Parameters.AddWithValue( "@zipcode", zipcode );
return command;
}
Each method that you wish to expose as a stored procedure must be decorated with a [SqlProcedure] attribute.
[SqlProcedure(Name="Usp_MyProcedure")]
Each procedure executes one or more SqlCommands using a Context Connection . I've left out the try / catch / finally structures for brevity.
ZipDistanceProcedure
The first stored procedure calculates the distance between any two zip codes. The results are returned by assigning a value to the single out parameter, distance .
[SqlProcedure(Name="Usp_GetZipCodeDistance")] public static void GetZipCodeDistance( SqlString zipcode1, SqlString zipcode2, out SqlDouble distance )
First, the coordinates of the two zip codes are retrieved.
SqlCommand command = GetCommand(zipcode1); command.Connection.Open(); command.ExecuteNonQuery(); //First zip code coordinates SqlDouble latitude1 = (SqlDouble)command.Parameters["@latitude"].SqlValue; SqlDouble longitude1 = (SqlDouble)command.Parameters["@longitude"].SqlValue; //Second zip code coordinates. command.Parameters["@zipcode"].Value = zipcode2; command.ExecuteNonQuery(); SqlDouble latitude2 = (SqlDouble)command.Parameters["@latitude"].SqlValue; SqlDouble longitude2 = (SqlDouble)command.Parameters["@longitude"].SqlValue;
Then, the distance is calculated directly by calling the static GeoDistanceFunction.GetGeoDistance() method. It is not necessary to call this method by making a roundtrip query to the database engine.
distance = GeoDistanceFunction.GetGeoDistance( latitude1, longitude1, latitude2, longitude2 );
Here is the completed class:
public class ZipDistanceProcedure
{
internal static SqlCommand GetCommand( SqlString zipcode )
{
const string SELECT_ZIP_COORDINATE = @"
SELECT @latitude=LAT_RADIANS,
@longitude=LNG_RADIANS
FROM ZIPUSA
WHERE ZIP_CODE=@zipcode";
SqlConnection conn = new SqlConnection( "Context Connection=true" );
SqlCommand command = new SqlCommand( SELECT_ZIP_COORDINATE, conn );
command.Parameters.Add( "@latitude", SqlDbType.Float ).Direction
= ParameterDirection.Output;
command.Parameters.Add( "@longitude", SqlDbType.Float ).Direction
= ParameterDirection.Output;
command.Parameters.AddWithValue( "@zipcode", zipcode );
return command;
}
[SqlProcedure(Name="Usp_GetZipCodeDistance")]
public static void GetZipCodeDistance( SqlString zipcode1,
SqlString zipcode2, out SqlDouble distance )
{
SqlCommand command = GetCommand(zipcode1);
command.Connection.Open();
command.ExecuteNonQuery();
SqlDouble latitude1
= (SqlDouble)command.Parameters["@latitude"].SqlValue;
SqlDouble longitude1
= (SqlDouble)command.Parameters["@longitude"].SqlValue;
command.Parameters["@zipcode"].Value = zipcode2;
command.ExecuteNonQuery();
SqlDouble latitude2 =
(SqlDouble)command.Parameters["@latitude"].SqlValue;
SqlDouble longitude2 =
(SqlDouble)command.Parameters["@longitude"].SqlValue;
distance = GeoDistanceFunction.GetGeoDistance(
latitude1,
longitude1,
latitude2,
longitude2 );
}
}
LocationsNearZipProcedure
The second stored procedure finds all locations within the radius of a zip code.
public class LocationsNearZipProcedure
{
private const string SELECT = @"
SELECT *
FROM dbo.Ufn_GetLocationsInRadius( @latitude,
@longitude, @radius )
WHERE distance <= @radius
";
[SqlProcedure(Name="Usp_GetLocationsNearZipcode")]
public static void GetLocationsNearZip( SqlString zipcode,
SqlInt32 radius )
{
SqlCommand coordinatesCommand
= ZipDistanceProcedure.GetCommand(zipcode);
coordinatesCommand.Connection.Open();
coordinatesCommand.ExecuteNonQuery();
SqlDouble latitude
= (SqlDouble)coordinatesCommand.Parameters["@latitude"].SqlValue;
SqlDouble longitude
= (SqlDouble)coordinatesCommand.Parameters["@longitude"].SqlValue;
SqlCommand locationsCommand = new SqlCommand(
SELECT, coordinatesCommand.Connection );
locationsCommand.Parameters.AddWithValue( "@latitude", latitude );
locationsCommand.Parameters.AddWithValue( "@longitude", longitude);
locationsCommand.Parameters.AddWithValue( "@radius", radius );
SqlContext.Pipe.ExecuteAndSend( locationsCommand );
}
}
Again, the coordinate of the zip code is retrieved.
SqlCommand coordinatesCommand = ZipDistanceProcedure.GetCommand(zipcode); coordinatesCommand.Connection.Open(); coordinatesCommand.ExecuteNonQuery(); SqlDouble latitude = (SqlDouble)coordinatesCommand.Parameters["@latitude"].SqlValue; SqlDouble longitude = (SqlDouble)coordinatesCommand.Parameters["@longitude"].SqlValue;
Then, a SqlCommand is constructed to find all the locations in the specified radius.
SqlCommand locationsCommand = new SqlCommand( SELECT, coordinatesCommand.Connection ); locationsCommand.Parameters.AddWithValue( "@latitude", latitude ); locationsCommand.Parameters.AddWithValue( "@longitude", longitude ); locationsCommand.Parameters.AddWithValue( "@radius", radius );
Finally, the command is executed and the results are piped back to the calling client.
SqlContext.Pipe.ExecuteAndSend( locationsCommand );
The SqlContext class allows you to access the client environment that is currently calling the code; this includes security characteristics of the caller and the transport stream ( SqlPipe ) that is used to return results. ExecuteAndSend() executes your SqlCommand in SQL Server and then sends the results back to the client.
ZipsFromCityNameProcedure()
The last stored procedure finds all the zip codes associated with a city by executing the following SQL SELECT query:
SELECT CITYSTNAME, ZIP_CODE FROM ZIPUSA WHERE SOUNDEX_CITYNAME = SOUNDEX( @city ) AND STATE = @state
The method implementation applies the name of the city and state abbreviation to the SELECT query parameters, executes the query, and then pipes the results back to the client.
public class ZipsFromCityNameProcedure
{
private const string SELECT = @"
SELECT CITYSTNAME, ZIP_CODE
FROM ZIPUSA
WHERE SOUNDEX_CITYNAME = SOUNDEX( @city )
AND STATE = @state
";
[SqlProcedure(Name="Usp_GetZipsFromCityName")]
public static void GetZipsFromCityName( SqlString city, SqlString state)
{
SqlCommand command = new SqlCommand(
SELECT, new SqlConnection( "Context Connection=true" ) );
command.Parameters.AddWithValue( "@city", city );
command.Parameters.AddWithValue( "@state", state );
command.Connection.Open();
SqlContext.Pipe.ExecuteAndSend( command );
}
}
Deploying the Assembly
Now that the functions and procedures have been completed, you can install them on SQL Server. First, Build the ZipSqlClr project. Next, right click on the project and select Deploy from the context menu. Visual Studio will automatically install the functions and procedures on SQL Server for you. You will need to Deploy your project any time you want to see your changes reflected in SQL Server. Refresh the database in the Server Explorer , and you should see your new functions and procedures as shown in Figure 14.
Figure 14. New CLR functions and procedures
Exposing the Zip Code Services
Now that you've completed the stored procedures that implement the zip code calculations, you're ready to expose them to the world. You can execute a single SQL statement to expose the procedures as a web service.
The first part of the statement indicates that you want to create an endpoint named ZipCodeService . Setting the state to STARTED ensures that the web service will start automatically once it is created. By default, endpoints don't run as soon as they are created.
CREATE ENDPOINT ZipCodeService STATE = STARTED
The second part of the statement indicates that the endpoint will use an HTTP listener using unencrypted ( CLEAR ) transmission and Windows Integrated authentication. The URL path to the endpoint will be http://localhost/sql .
AS HTTP ( PATH = '/sql', AUTHENTICATION = (INTEGRATED ), PORTS = ( CLEAR ) )
The final part of the statement indicates the names of SOAP operations and fully qualified names of the stored procedures that are associated with the web service.
FOR SOAP
(
WEBMETHOD 'GetLocationsNearZipcode'
(name='ZipCodes.dbo.Usp_GetLocationsNearZipcode'),
WEBMETHOD 'GetZipCodeDistance'
(name='ZipCodes.dbo.Usp_GetZipCodeDistance'),
WEBMETHOD 'GetZipsFromCityName'
(name='ZipCodes.dbo.Usp_GetZipsFromCityName'),
WSDL=DEFAULT,
DATABASE = 'ZipCodes'
);
To execute this SQL, right click on Create Scripts\CreateEndpoints.sql in the ZipCodeDB project, and choose Run from the context menu. Test your endpoint by using Internet Explorer to navigate to http://localhost/sql?wsdl . SQL Server will return the WSDL document that describes the available web methods (see Figure 15).
Figure 15. WSDL generated by navigating to http://localhost/sql?wsdl
Testing the Zip Code Service
The last thing to do is create a simple test client for the zip code service.
Create a new C# Windows Application project named ZipServiceClient . Right click on the project, and then select Add Web Reference… . Fill in the dialog as shown in Figure 16, using http://localhost/sql?wsdl as the URL. Click the Go button to retrieve the service description, and then click the Add Reference button to add the reference to your project.
Figure 16. Adding a reference to the Zip Code Endpoint
Rebuild the project and you will see a new ZipServiceClient Components section added to the ToolBox with a ZipCodeService component listed. Drag an instance of the component to Form1 . Use the Properties window to set the component's UseDefaultCredentials property to true .
Next, drag a DataGridView from the ToolBox to Form1 and dock it to the top of the form. Drag three buttons to the bottom of the form and label them as shown in Figure 17.
Figure 17. Web Service test client
Finally, double-click on each button and add the corresponding event handlers.
private void zipDistanceButton_Click( object sender, EventArgs e )
{
SqlDouble distance = SqlDouble.Null;
this.zipCodeService1.GetZipCodeDistance( "00501", "00601", ref distance );
MessageBox.Show( "The distance is " + distance.Value + " miles." );
}
private void getLocationsButton_Click( object sender, EventArgs e )
{
object[] locations
= this.zipCodeService1.GetLocationsNearZipcode( "00601", 60 );
this.dataGridView1.DataSource = ((DataSet)locations[0]).Tables[0];
}
private void cityNameButton_Click( object sender, EventArgs e )
{
object[] locations
= this.zipCodeService1.GetZipsFromCityName( "VILLA", "PR" );
this.dataGridView1.DataSource = ( (DataSet)locations[0] ).Tables[0];
}
Compile and run your project. Click on each of the buttons of the form to test your three web service methods.
Conclusion
SQL Server 2005 allows you to create a self-contained zip code solution, whereas SQL 2000 also required IIS and ASP.NET to expose the web service. Now, you never have to leave Visual Studio 2005 to complete your solution. SQL 2000 Data Transformation Services has been replaced with SQL Server Integration Services. Stored procedures and user-defined functions can now be created with either T-SQL or .NET code. Stored Procedures can be exposed as web services without IIS: you can now create endpoints directly via T-SQL.
When I first created the SQL 2000 solution, I was a bit dissatisfied with the architecture because I felt there was too much procedural logic embedded in the data tier. In the conclusion of my previous article, I suggested an alternate approach, where most of the zip calculations were performed in the ASP.NET application. The drawback to that approach was that too much data had to be shuffled between the database and the ASP.NET application. SQL2005 allows you to create a better architecture that allows you to isolate trigonometric logic using a procedural CLR language, without moving data excessively across application boundaries.
Microsoft recommends the best practice of keeping your set-based logic in T-SQL stored procedures and procedural logic in CLR procedures. You'll notice that this approach still requires you to create embedded SQL in your procedural code. This is no longer a performance issue as it would have been in ASP.NET, because the code is precompiled such that SQL Server can optimize its query execution plan.
There is still a drawback to embedded SQL in that you must manipulate string literals. The CLR compiler has no way of knowing whether your SQL strings are syntactically valid. Despite what you've seen in this article, I recommend placing all of your SQL strings in an assembly resource so that you can more easily debug your SQL. Alternatively, you might use an object-oriented approach to create SQL queries.
