SQL2005CLR函數(shù)擴(kuò)展-解析天氣服務(wù)的實(shí)現(xiàn)

更新時(shí)間：2013年06月27日 09:40:42 作者：

其實(shí)我們可以用CLR獲取網(wǎng)絡(luò)服務(wù)，來顯示到數(shù)據(jù)庫自定函數(shù)的結(jié)果集中，比如163的天氣預(yù)報(bào)。需要的朋友參考下

我們可以用CLR獲取網(wǎng)絡(luò)服務(wù) 來顯示到數(shù)據(jù)庫自定函數(shù)的結(jié)果集中，比如163的天氣預(yù)報(bào)
http://news.163.com/xml/weather.xml
他的這個(gè)xml結(jié)果的日期是不正確的，但這個(gè)我們暫不討論。
從這個(gè)xml獲取天氣的CLR代碼如下，用WebClient訪問一下就可以了。然后通過Dom對象遍歷節(jié)點(diǎn)屬性返回給結(jié)果集。
--------------------------------------------------------------------------------

復(fù)制代碼代碼如下:

using System; 
using System.Data; 
using System.Data.SqlClient; 
using System.Data.SqlTypes; 
using System.Collections; 
using System.Collections.Generic; 
using Microsoft.SqlServer.Server; 

public partial class UserDefinedFunctions 
{ 

    [SqlFunction (TableDefinition = "city nvarchar(100),date nvarchar(100),general nvarchar(100),temperature nvarchar(100),wind nvarchar(100)" , Name = "GetWeather" , FillRowMethodName = "FillRow" )] 
    public static IEnumerable GetWeather() 
    { 
        System.Collections.Generic.List <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 = data.city; 
        date = data.date; 
        general = data.general; 
        temperature = data.temperature; 
        wind = data.wind; 
    } 

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

            string url = "http://news.163.com/xml/weather.xml" ; 
            System.Net.WebClient wb = new System.Net.WebClient (); 
            byte [] b = wb.DownloadData(url); 
            string data = System.Text.Encoding .Default.GetString(b); 
            System.Xml.XmlDocument doc = new System.Xml.XmlDocument (); 
            doc.LoadXml(data); 

            foreach (System.Xml.XmlNode node in doc.ChildNodes[1]) 
            { 
                string city = GetXMLAttrib(node, "name" ); 
                foreach (System.Xml.XmlNode subnode in node.ChildNodes) 
                { 
                    Item item = new Item (); 
                    item.city = city; 
                    item.date = GetXMLAttrib(subnode, "date" ); 
                    item.general = GetXMLAttrib(subnode, "general" ); 
                    item.temperature = GetXMLAttrib(subnode, "temperature" ); 
                    item.wind = GetXMLAttrib(subnode, "wind" ); 
                    ret.Add(item); 
                } 
            } 

        //} 
        //catch(Exception ex) 
        //{ 
        //    SqlContext.Pipe.Send(ex.Message); 
        //} 
        return ret; 
    } 

    static string GetXMLAttrib(System.Xml.XmlNode node, string attrib) 
    { 
        try 
        { 
            return node.Attributes[attrib].Value; 
        } 
        catch 
        { 
            return string .Empty; 
        } 
    } 
}; 

--------------------------------------------------------------------------------
部署這個(gè)clr函數(shù)的腳本如下
--------------------------------------------------------------------------------

復(fù)制代碼代碼如下:

 
drop function dbo. xfn_GetWeather 
drop   ASSEMBLY TestWeather 
go 
CREATE ASSEMBLY TestWeather FROM 'd:/sqlclr/TestWeather.dll' WITH PERMISSION_SET = UnSAFE; 
-- 
go 
CREATE FUNCTION dbo. xfn_GetWeather ()     
RETURNS table ( city nvarchar ( 100), date nvarchar ( 100), general nvarchar ( 100), temperature nvarchar ( 100), wind nvarchar ( 100)) 
AS EXTERNAL NAME TestWeather. UserDefinedFunctions. GetWeather

--------------------------------------------------------------------------------
測試函數(shù)
--------------------------------------------------------------------------------
select * from dbo. xfn_GetWeather ()