【交流】SQL2005CLR函数扩展-天气服务
Blog原文:
http://blog.csdn.net/jinjazz/archive/2009/05/14/4187051.aspx

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

最终效果

SQL code

select * from dbo.xfn_GetWeather ()

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

clr代码

C# code

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;        }    }};

部署代码

SQL code

CREATE ASSEMBLY TestWeather FROM 'd:\sqlclr\TestWeather.dll' WITH PERMISSION_SET = UnSAFE;--goCREATE 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

[解决办法]
你肺:)
------解决方案--------------------

探讨

你肺:)

[解决办法]
太强了吧?
[解决办法]
向高手学习，up
[解决办法]
先顶一下 再慢慢看
[解决办法]
先顶，后学习~
[解决办法]
厉害！支持！
[解决办法]
我一般在很少帖。。。。
[解决办法]
呵呵,前段时间在搞那个google finance 的那个API,想搞一个出来看看实时股票的价格.
后来装了Windows 7后,发现别人都已经做好了,Desktop Gadget Galley.
呵呵.
[解决办法]

探讨

我一般在很少帖。。。。