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 ()

From:http://tw.wingwit.com/Article/program/MySQL/201311/29543.html