Friday, September 25, 2009

Query a web service with SQLCLR

Since SQL Server 2005 it's possible to create .NET based functions and procedures and call them from T-SQL. This feature should replace old approaches using extended procedures written in C/C++ and COM based solutions working with sp_OACreate. Problem of extended procedures is that bugs within these procedures may affect the whole SQL server service. Problems of COM based solutions is that they are hard do write.

Anyway, SQL CLR should not be used to replace usual SQL procedures. T-SQL is still much faster and better to maintain for CRUD (create, read, update, delete) actions. The idea of SQL CLR is to extend T-SQL tool set, not to replace it.

One of the tasks, which can be done with SQL CLR, is to request information from a web service.This sample uses the National Digital Forecast Database (NDFD) to query some weather information.

Database Configuration

Before we can start to develop the function, we have to set database to TRUSTWORTHY mode. This is needed, because our function needs to access external resources.You can use  the following  SQL statement to enable the TRUSTWORTHY mode for your database:
ALTER DATABASE Sandbox SET TRUSTWORTHY ON;

Server Configuration

If not yet done, you also have to enable CLR on SQL server. This can be done with the following SQL statement:
EXECUTE sp_configure 'clr enabled', 1;
RECONFIGURE;

Visual Studio Project Configuration

Open Visual Studio 2008 and create a new C# project from type "SQL Server Project". Open project properties and navigate to "Database" tab. Ensure the correct connection string. Permission level has to be changed to "External". Add a new item from type "User-Defined Function" with name "QueryWebService".

Now we got everything we need to create our CLR function.

The Sample Function

Each CLR table-valued function needs the attribute SqlFunction and has to return an IEnumerable. The SqlFunction attribute as to specify the returned table definition by parameter TableDefinition. A second parameter FillRowMehodName specifies the name of the method to be used from SQL Server to get the row items from current element of the enumerator.

First create a WebRequest for the URL to query the web service. Get the WebResponse and use a XPathDocument to parse the returned XML stream.

The following block shows an cutting of the returned XML.
<dwml 
   version="1.0" 
   xsi:noNamespaceSchemaLocation="http://www.nws.noaa.gov/forecasts/xml/DWMLgen/schema/DWML.xsd" 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   >
   <head>
      <!-- ... -->
   </head>
   <data>
      <location>
         <location-key>point1</location-key>
         <point latitude="38.99" longitude="-77.01" />
      </location>
      <!-- ... -->
      <time-layout time-coordinate="local" summarization="none">
         <layout-key>k-p24h-n7-1</layout-key>
         <start-valid-time>2009-09-23T08:00:00-04:00</start-valid-time>
         <end-valid-time>2009-09-23T20:00:00-04:00</end-valid-time>
         <start-valid-time>2009-09-24T08:00:00-04:00</start-valid-time>
         <end-valid-time>2009-09-24T20:00:00-04:00</end-valid-time>
         <!-- ... -->
      </time-layout>
      <time-layout time-coordinate="local" summarization="none">
         <layout-key>k-p24h-n6-2</layout-key>
         <!-- ... -->
      </time-layout>
      <parameters applicable-location="point1">
         <temperature type="maximum" units="Fahrenheit" time-layout="k-p24h-n7-1">
            <name>Daily Maximum Temperature</name>
            <value>83</value>
            <value>85</value>
            <!-- ... -->
         </temperature>
         <!-- ... -->
      </parameters>
   </data>
</dwml>

Last thing we have to do is to extract the information we want to return to SQL Server. Therefore we use XPath to query the returned location and forecast information.

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Net;
using System.IO;
using System.Xml.XPath;


public partial class StoredProcedures
{
   [SqlFunction(
      FillRowMethodName = "FillMethodWeather",
      TableDefinition = "Latitude FLOAT, Longitude FLOAT, "
                        + "ForecastDate DateTime, Temperature FLOAT"
      )
   ]
   public static IEnumerable QueryWebService()
   {
      // sample web request url to get a weather forecast
      String url =
         "http://www.weather.gov/forecasts/xml/sample_products"
         + "/browser_interface/ndfdXMLclient.php?"
         + "lat=38.99"
         + "&lon=-77.01"
         + "&product=time-series"
         + "&begin=2004-01-01T00:00:00"
         + "&end=2013-04-20T00:00:00"
         + "&maxt=maxt"
         + "&mint=mint";

      string xpath;

      // create teh web request and get the response
      WebRequest request = HttpWebRequest.Create(url);
      WebResponse response = request.GetResponse();
      
      // take the response stream and create an XML DOM
      Stream responseStream = response.GetResponseStream();
      XPathDocument dom = new XPathDocument(responseStream);

      XPathNavigator root = dom.CreateNavigator();

      // get latitude and longitude information
      Double latitude;
      Double longitude;
      xpath = "dwml/data/location/point";
      XPathNavigator point = root.SelectSingleNode(xpath);
      latitude = Double.Parse(point.GetAttribute("latitude", String.Empty));
      longitude = Double.Parse(point.GetAttribute("longitude", String.Empty));

      // get the date and temperature nodes from xml
      
      xpath = "dwml/data/time-layout [position()='1']/* "
              + "[name()='start-valid-time']";
      XPathNodeIterator dateNodes = root.Select(xpath);

      xpath = "dwml/data/parameters/temperature "
              + "[position()='1']/* [name()='value']";
      XPathNodeIterator tempNodes = root.Select(xpath);

      // loop over all date nodes
      while (true)
      {
         // break if we passed the last date element
         if (!dateNodes.MoveNext())
            break;
         tempNodes.MoveNext();

         // get current forecast information
         XPathNavigator dateNode = dateNodes.Current;
         XPathNavigator tempNode = tempNodes.Current;
         DateTime date = DateTime.Parse(dateNode.Value);
         Double temp = Double.Parse(tempNode.Value);

         // use a "yield return" to enumerate through all data
         yield return new object[] 
         {
            (SqlDouble)latitude, 
            (SqlDouble)longitude, 
            (SqlDateTime)date, 
            (SqlDouble)temp 
         };
      }
   }

   // function will be called from SQL Server to get row items 
   // for current element from enumerator
   public static void FillMethodWeather(object current, 
      out SqlDouble latitude, 
      out SqlDouble longitude, 
      out SqlDateTime forecastDate, 
      out SqlDouble temperature
      )
   {
      // set return information from current item
      object[] data = (object[])current;
      latitude = (SqlDouble)data[0];
      longitude = (SqlDouble)data[1];
      forecastDate = (SqlDateTime)data[2];
      temperature = (SqlDouble)data[3];
   }
};

Publish

The easiest way to deploy the created assembly to SQL Server is to use the Visual Studio deployment feature. Right click the project node in solution explorer and hit "Deploy" to make the function available in SQL Server.

The usage of this function within T-SQL is equal to any other table-valued function call.
SELECT * FROM QueryWebService();

The output shows the result of the web service call.

Latitude    Longitude    ForecastDate              Temperature
----------- ------------ ------------------------- -------------
38.99       -77.01       2009-09-25 14:00:00.000   72
38.99       -77.01       2009-09-26 14:00:00.000   65
38.99       -77.01       2009-09-27 14:00:00.000   78
38.99       -77.01       2009-09-28 14:00:00.000   75
38.99       -77.01       2009-09-29 14:00:00.000   68
38.99       -77.01       2009-09-30 14:00:00.000   69
38.99       -77.01       2009-10-01 14:00:00.000   70

(7 row(s) affected)


As you see SQL CLR is neat way to extent SQL Server functionality with .NET.

No comments:

Post a Comment