Blog原文:
http://blog.csdn.net/jinjazz/archive/2009/05/14/4187051.aspx我们可以用CLR获取网络服务 来显示到数据库自定函数的结果集中,比如163的天气预报
http://news.163.com/xml/weather.xml最终效果
select * from dbo.xfn_GetWeather ()
只要你知道如何用WebClient下载数据并分析xml,如何用IEnumerable写表值函数就行了。clr代码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;
}
}
};部署代码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
http://blog.csdn.net/jinjazz/archive/2009/05/14/4187051.aspx我们可以用CLR获取网络服务 来显示到数据库自定函数的结果集中,比如163的天气预报
http://news.163.com/xml/weather.xml最终效果
select * from dbo.xfn_GetWeather ()
只要你知道如何用WebClient下载数据并分析xml,如何用IEnumerable写表值函数就行了。clr代码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;
}
}
};部署代码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
解决方案 »
- 为什么bcp "SELECT ID,SUBSTRING(ExternalKey,7,32),NodeID,PM_Product_ID,LB_Product_ID,StyleTypeID FROM ExternalMapping" out "Exter
- 存储过程中的一个查询语句
- 这个SQL语句如何写?
- 请问一sql语句
- 数据库的mdf 和ldf都没了 报945错误
- 跪求文本文件导入到SQLSERVER数据库
- 如何检索AAA数据库中有没有BBB表!
- 关于SQL Server的效率,如何处理表达式or的问题
- 为什么连不上SQL服务器?用户名、密码都正确,服务器也开了,没有防火墙,还是说:sa not associate ...
- 怎么才能把图片存到SQL SERVER 2000中!!
- InvalidArgument=“0”的值对于“index”无效。
网易这个服务的日期输出错了...但他输出的5月8号应该对应的就是今天。
后来装了Windows 7后,发现别人都已经做好了,Desktop Gadget Galley.
呵呵.
看了楼住的 让我对CLR建表值函数的理解又加深了
厉害啊
看了楼住的 让我对CLR建表值函数的理解又加深了
ok!