主頁(yè) > 知識(shí)庫(kù) > SQL2005CLR函數(shù)擴(kuò)展-解析天氣服務(wù)的實(shí)現(xiàn)

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

熱門(mén)標(biāo)簽:舞鋼市地圖標(biāo)注app 海南自動(dòng)外呼系統(tǒng)價(jià)格 創(chuàng)業(yè)電銷機(jī)器人 電銷機(jī)器人虛擬號(hào)碼 沈陽(yáng)智能外呼系統(tǒng)代理 松原導(dǎo)航地圖標(biāo)注 九鹿林外呼系統(tǒng)怎么收費(fèi) 浙江地圖標(biāo)注 滄州營(yíng)銷外呼系統(tǒng)軟件
我們可以用CLR獲取網(wǎng)絡(luò)服務(wù) 來(lái)顯示到數(shù)據(jù)庫(kù)自定函數(shù)的結(jié)果集中,比如163的天氣預(yù)報(bào)
http://news.163.com/xml/weather.xml
他的這個(gè)xml結(jié)果的日期是不正確的,但這個(gè)我們暫不討論。
從這個(gè)xml獲取天氣的CLR代碼如下,用WebClient訪問(wèn)一下就可以了。然后通過(guò)Dom對(duì)象遍歷節(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

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

標(biāo)簽:臺(tái)灣 商洛 日喀則 公主嶺 咸寧 西藏 寶雞

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《SQL2005CLR函數(shù)擴(kuò)展-解析天氣服務(wù)的實(shí)現(xiàn)》,本文關(guān)鍵詞  SQL2005CLR,函數(shù),擴(kuò)展,解析,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《SQL2005CLR函數(shù)擴(kuò)展-解析天氣服務(wù)的實(shí)現(xiàn)》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于SQL2005CLR函數(shù)擴(kuò)展-解析天氣服務(wù)的實(shí)現(xiàn)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章