Monday, 7 June 2010

ASP.NET: Select DISTINCT from DataTable

I needed to filter a GridView using a bunch of DropDownLists which were placed in its header. As you filter the GridView, the drop down lists had to update according to the filtered data in the GridView (i.e. they should not display filter options for data that's not visible in the table).

In order for this to work, I had to get a distinct list of values for each column with which to populate the drop down lists. This required getting a distinct list of values from a DataTable, and this behaviour does not come naturally to a DataTable since it's a representation of a SQL Database table rather than an actual database table which you can run SQL against.

I found a nice tutorial on this, which I'm linking to here for future reference, in both VB.NET and C#.NET versions.

I had to pass the DataSource my GridView was pointing at into a method which bound the DropDownLists using the function demonstrated in Microsoft's tutorials above.

Implemented, hooked into my GridView and Bob's My Uncle.

Very nice!