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

发布时间：2023-08-04 14:16:05 所属栏目：MsSql教程来源：

导读：我们可以用CLR获取网络服务来显示到数据库自定函数的结果集中，比如163的天气预报

http://news.163.com/xml/weather.xml

<FONT style="COLOR: #ff0000">他的这个xml结果的日期是不正确的，但这个我们暂不讨

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

http://news.163.com/xml/weather.xml

<FONT style="COLOR: #ff0000">他的这个xml结果的日期是不正确的，但这个我们暂不讨论。

从这个xml获取天气的CLR代码如下，用WebClient访问一下就可以了。然后通过Dom对象遍历节点属性返回给结果集。

--------------------------------------------------------------------------------

<div class="codetitle"><a style="CURSOR: pointer" data="14017" class="copybut" id="copybut14017" onclick="doCopy('code14017')"> 代码如下:

<div class="codebody" id="code14017">

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 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 GetData()

{

System.Collections.Generic.List ret = new List ();

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

--------------------------------------------------------------------------------

<div class="codetitle"><a style="CURSOR: pointer" data="5731" class="copybut" id="copybut5731" onclick="doCopy('code5731')"> 代码如下:

<div class="codebody" id="code5731">

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

--------------------------------------------------------------------------------

测试函数

--------------------------------------------------------------------------------

<FONT style="COLOR: #ff0000">select * from dbo. xfn_GetWeather ()

<IMG alt="" src="https://files.jb51.cc/file_images/article/201306/201306270935444.jpg">

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!