sql2005clr function Extension-analysis of the implementation of weather Service

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, out of 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 = 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


CREATE FUNCTION dbo. Xfn_getweather ()


RETURNS table (city nvarchar), date nvarchar (MB), General nvarchar (m), temperature nvarchar (MB), WI nd nvarchar (100))


as EXTERNAL NAME testweather. Userdefinedfunctions. GetWeather

--------------------------------------------------------------------------------
test function
--------------------------------------------------------------------------------the
select * FROM dbo. xfn_ GetWeather ()