sql2005clr function Extension-analysis of the implementation of weather services _mssql2005

We can use the CLR to get the network service to display the result set of the database custom function, such as 163 weather forecast
Http://news.163.com/xml/weather.xml
His date for this XML result is incorrect, but we are not discussing it.
The CLR code to get the weather from this XML is as follows, with WebClient access to it. The node property is then traversed through the DOM object to return to the result set.
--------------------------------------------------------------------------------

Copy Code code as follows:

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 (), date nvarchar (m), General nvarchar (m), Temperature nvarchar (100 ), Wind nvarchar (m) ", Name =" GetWeather ", FillRowMethodName =" FillRow ")]
public static IEnumerable GetWeather ()
{
System.Collections.Generic.List <item > List = GetData ();
return list;
}
public static void FillRow (Object obj. SqlString City, out SqlString date, out SqlString general, out SqlString temper Ature, 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;
}
}
};

--------------------------------------------------------------------------------
The script to deploy this CLR function is as follows
--------------------------------------------------------------------------------

Copy Code code as follows:

Drop function dbo. Xfn_getweather
Drop ASSEMBLY Testweather
Go
CREATE ASSEMBLY testweather from ' d:/sqlclr/testweather.dll ' with permission_set = UnSAFE;
--
Go
The CREATE FUNCTION dbo. Xfn_getweather ()
RETURNS table (city nvarchar), date nvarchar (m), General nvarchar (MB), temperature nvarchar (m), wind NVA Rchar (100))
As EXTERNAL NAME Testweather. Userdefinedfunctions. GetWeather

--------------------------------------------------------------------------------
test function
--------------------------------------------------------------------------------
SELECT * FROM dbo. Xfn_getweather ()