【交流】SQL2005CLR函数扩展-天气服务

jinjazz 2009-05-15 10:35:55

加精

Blog原文:
http://blog.csdn.net/jinjazz/archive/2009/05/14/4187051.aspx

我们可以用CLR获取网络服务来显示到数据库自定函数的结果集中，比如163的天气预报
http://news.163.com/xml/weather.xml

最终效果

select * from dbo.xfn_GetWeather ()

只要你知道如何用WebClient下载数据并分析xml，如何用IEnumerable写表值函数就行了。

clr代码

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;

        }

    }

};

部署代码

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

...全文