|
While researching how to select unique rows from an existing DataSet I ran across many Google references to the following knowledge base article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;326176
NOW THAT YOU KNOW WHERE THE LINK IS, FORGET ABOUT IT!!!! In ADO.NET 2.0 this is now part of the DataView class built-in functionality, and the following article encapsulates this extended functionality in a more object-oriented manner for 1.x:
http://www.aspheute.com/english/20040123.asp
In 2.0, this functionality is available through the DataView class. Consider the following XML file stored as a string resource to indicate supported languages for a Web site:
<?xml version="1.0" encoding="utf-8" ?>
<supportedCultures>
<culture>
<culturecode>en</culturecode>
<language>English</language>
<country></country>
</culture>
<culture>
<culturecode>en-CA</culturecode>
<language>English</language>
<country>Canada</country>
</culture>
<culture>
<culturecode>en-US</culturecode>
<language>English</language>
<country>USA</country>
</culture>
<culture>
<culturecode>es</culturecode>
<language>Spanish</language>
<country></country>
</culture>
<culture>
<culturecode>es-EC</culturecode>
<language>Spanish</language>
<country>Ecuador</country>
</culture>
<culture>
<culturecode>es-ES</culturecode>
<language>Spanish</language>
<country>Spain</country>
</culture>
</supportedCultures>
If I want to populate a DataList with all supported languages, there are dupes in the file. But, I need the dupes to associate language with Country, for another purpose.
As the following code demonstrates, if I load a DataSet with XML (shown below using a StringReader to demonstrate how to load XML from a string resources, another hidden gem), grab the default view for the (only) table, set a filter on the view to exclude <language> elements that are empty, sort by language, then invoke the DataView.ToTable() overload that supports passing in DISTINCT requirements:
DataSet ds = new DataSet();
System.IO.StringReader rdr = new StringReader(cultures);
ds.ReadXml(rdr);
rdr.Close();
DataView dv = ds.Tables[0].DefaultView;
dv.RowFilter="language <> ''";
dv.Sort="language";
string [] distinct = {"language"};
DataTable tbl = dv.ToTable(true, distinct);
this.dlLanguages.DataSource = tbl;
this.dlLanguages.DataBind();
This is very useful if you are working with offline DataSets from within a smart client, or a cached DataSet supporting a Web application, and you want to bind different perspectives of the data to controls using consistent binding methods. No need to roll your own code to populate rows, or requery the data source.
|