Thursday, 10 December 2009

SQLDataReader and Output Parameters

I have a WPF ListView bound to a Generic List of my custom business objects, I decided to implement a backgroundworker to populate the collection and report progress to a ProgressBar control.

So, I need a record count and, for performance, a SqlDataReader. My thought was to return both results from a stored procedure and the nicest way to do that is with an OUTPUT parameter.

The goal is to return the record count from the output parameter to my progress bar BEFORE reading the SqlDataReader so thet I can set ProgressBar.Maximym, then do a while loop to populate the collection incrementing ProgressBar.Value on each iteration.

Can I do this? NO.

I had it all coded, ran it, and got a result of null in the parameter, no matter what I tried it refused to give me a value. I researched for a couple of hours to no avail - then I found a small note on a web site that with a SqlDataReader, you must complete a full read of the reader before the output parameter becomes available.

Well, I'm screwed, I can revert to using a DataTable and yes it wont make a massive performance difference but for the population of the collection a SqlDataReader is definitely the best option and I am denied it.

No comments:

Post a Comment