【交流】SQL2005CLR函数扩展-天气服务
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 ()
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;--goCREATE 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