当前位置：首页 > 资讯 > 技术文档

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

时间：2020-10-22 15:49 编辑：来源：阅读：
扫一扫，手机访问

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

我们可以用CLR获取网络服务来显示到数据库自定函数的结果集中，比如163的天气预报 [url=http://news.163.com/xml/weather.xml]http://news.163.com/xml/weather.xml [/url]他的这个xml结果的日期是不正确的，但这个我们暂不讨论。从这个xml获取天气的CLR代码如下，用WebClient访问一下就可以了。然后通过Dom对象遍历节点属性返回给结果集。 --------------------------------------------------------------------------------

[u]复制代码[/u] 代码如下:

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函数的脚本如下 --------------------------------------------------------------------------------

[u]复制代码[/u] 代码如下:

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

-------------------------------------------------------------------------------- [b]测试函数 [/b]-------------------------------------------------------------------------------- select * from dbo. xfn_GetWeather () [img]http://files.jb51.net/file_images/article/201306/201306270935444.jpg[/img]

全部评论(0)

上一篇：sql2005 远程连接问题解决方法
下一篇：组策略中的审核策略提示 Windows无法读取模板信息的解决方法

资讯排行榜
更多>>