SQL2005CLR函数扩展-解析天气服务的实现

我们可以用CLR获取网络服务 来显示到数据库自定函数的结果集中比如的天气预报

他的这个xml结果的日期是不正确的但这个我们暂不讨论
从这个xml获取天气的CLR代码如下用WebClient访问一下就可以了然后通过Dom对象遍历节点属性返回给结果集

复制代码 代码如下:
using System;
using SystemData;
using SystemDataSqlClient;
using SystemDataSqlTypes;
using SystemCollections;
using SystemCollectionsGeneric;
using MicrosoftSqlServerServer;

public partial class UserDefinedFunctions
{

[SqlFunction (TableDefinition = "city nvarchar()date  nvarchar()general nvarchar()temperature nvarchar()wind  nvarchar()" Name = "GetWeather" FillRowMethodName = "FillRow" )]
public static IEnumerable GetWeather()
{
SystemCollectionsGenericList <Item > list = GetData();
return list;
}
public static void FillRow(Object obj out SqlString city out  SqlString date out SqlString general out SqlString temperature out  SqlString wind)
{
Item data = (Item )obj;
city = datacity;
date = datadate;
general = datageneral;
temperature = datatemperature;
wind = datawind;
}

class Item
{
public string city;
public string date;
public string general;
public string temperature;
public string wind;
}
static SystemCollectionsGenericList <Item > GetData()
{
SystemCollectionsGenericList <Item > ret = new List <Item >();
//try
//{

string url = "
SystemNetWebClient wb = new SystemNetWebClient ();
byte [] b = wbDownloadData(url);
string data = SystemTextEncoding DefaultGetString(b);
SystemXmlXmlDocument doc = new SystemXmlXmlDocument ();
docLoadXml(data);

foreach (SystemXmlXmlNode node in docChildNodes[])
{
string city = GetXMLAttrib(node "name" );
foreach (SystemXmlXmlNode subnode in nodeChildNodes)
{
Item item = new Item ();
itemcity = city;
itemdate = GetXMLAttrib(subnode "date" );
itemgeneral = GetXMLAttrib(subnode "general" );
itemtemperature = GetXMLAttrib(subnode "temperature" );
itemwind = GetXMLAttrib(subnode "wind" );
retAdd(item);
}
}

//}
//catch(Exception ex)
//{
//    SqlContextPipeSend(exMessage);
//}
return ret;
}

static string GetXMLAttrib(SystemXmlXmlNode node string attrib)
{
try
{
return nodeAttributes[attrib]Value;
}
catch
{
return string Empty;
}
}
};

部署这个clr函数的脚本如下

复制代码 代码如下:
drop function dbo xfn_GetWeather
drop   ASSEMBLY TestWeather
go
CREATE ASSEMBLY TestWeather FROM d:/sqlclr/TestWeatherdll WITH PERMISSION_SET = UnSAFE;

go
CREATE FUNCTION dbo xfn_GetWeather ()     
RETURNS  table ( city nvarchar ( ) date nvarchar ( ) general nvarchar (  ) temperature nvarchar ( ) wind nvarchar ( ))
AS EXTERNAL NAME TestWeather UserDefinedFunctions GetWeather

测试函数

select * from dbo xfn_GetWeather ()