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.
This comment has been removed by the author.
ReplyDelete