SQL2005CLR函数扩展-解析天气服务的实现

我们可以用CLR获取网络服务 来显示到数据库自定函数的结果集中，比如163的天气预报
    http://news.163.com/xml/weather.xml
    他的这个xml结果的日期是不正确的，但这个我们暂不讨论。
    从这个xml获取天气的CLR代码如下，用WebClient访问一下就可以了。然后通过Dom对象遍历节点属性返回给结果集。
    --------------------------------------------------------------------------------
    复制代码 代码如下:
    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;
            }
        }
    };
    
    --------------------------------------------------------------------------------
    部署这个clr函数的脚本如下
    --------------------------------------------------------------------------------
    复制代码 代码如下:
    
    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
    --------------------------------------------------------------------------------
    测试函数
    --------------------------------------------------------------------------------
    select * from dbo. xfn_GetWeather ()