当前位置： 移动技术网 > IT编程>数据库>MSSQL > SQL2005CLR函数扩展-解析天气服务的实现

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

2017年12月08日  | 移动技术网IT编程  | 我要评论

李开复创新工场,炫腾卡盟,护树大行动

我们可以用clr获取网络服务 来显示到数据库自定函数的结果集中，比如163的天气预报
他的这个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 ()

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网