I wrote a stored procedure that didn't work because it was iterating through thousands of records and performing selects for each iteration. Extremely inefficient, and much to slow to be useful.
I hadn't used ado.net for anything more then the basics before yesterday, populating readers and datasets, looping through records, but I did think one day it might come in handy, possibly when a bad sproc just wasn't cutting it. I still haven’t done anything interesting with ado.net, but after poking around, playing with the .Select() method and reading msdn’s fascinating explanation of the .Expression property and .FindRows() and what have you, it seems that ado.net doesn't really act as a database in the logical sky as I would have hoped. I don't see a way to simply evaluate a "select distinct column" statement against data in a datatable/dataview/dataset.
What I'd like to do is return an entire table's data from my database into a disconnected holder in my app. Once it's there. I want to do a bunch of stuff to it, starting with returning a set of distinct values on one of the columns, and iterating through that collection doing 'count(*)'s where string evaluations on a column return true.
If all I have at my disposal is .Select() and .FindRows(), it seems as though I may be out of luck. Is there any way to return a subset of records in a ado.net holder that mirror the results of using the 'distinct' keyword on a column in the recordset? How would I retreive a record count of rows in a datatable where a varchar column matches a string? Is there any way to evaluate t-sql against data in a dataset/datatable/dataview?