Should I pass a SqlDataReader by reference or not when passing it out to multiple threads.

Question

Hi all,

being new to c# I've run into this 'conundrum' when passing around a SqlDataReader between different threads. Without going into too much detail, the idea is to have a main thread fetching data from the database (a large recordset) and then have a helper-task run through this record by record and doing some stuff based upon the contents of this. There is no feedback to the recordset, it simply wades through until no records are left. This works fine, but given the nature of the job at hand it should be possible to have this job spread over different threads (CPUs) to maximize throughput (the order of execution is of no significance). The question then becomes, when I pass this recordset in a SqlDataReader, do I have to use ref or not ? It kind of boils down to the question : if I pass the object around without specifying ref, won't it create new copies in memory and have records processed n times ? Or, don't I risk having the record-position being moved forward while not all fields have been fully read yet ? The latter seems more like a 'data racing' issue and probably is covered by the lock()ing mechanism (or not?).

My initial take on the problem was that it doesn't really hurt passing the variable using ref, yet as a colleague put it : "you only need ref when you're doing something wrong" =) Additionally using ref restricts me from applying a Using() construction too which isn't very nice either. I thus create a "basic" project that tackles the same approach but without the ref notation. Tests so far show that it works flawlessly on a Core2Duo (2cpu) using any number of threads, yet I'm still a bit wary... What do you experts think about this ? Use ref or not ?

You can find the test-project here as it seems I can't upload it to this question directly ?!?

ps: it's just a test-project and I'm new to c#, so please be gentle on me when breaking down the code =P

Answer 1

Don't pass SqlDataReader around to multiple threads. The documentation says this class is not thread safe.

Even if it were, you would have all sorts of timing issues arising from this, especially when it comes time to dispose of your SqlDataReader. Add to that the fact that SqlDataReader already streams data in on another thread, and I think you're prematurely optimizing.

Answer 2

Thanks for all the comments, I certainly didn't expect so many reactions in such short time ! (don't you guys have anything better to do ? =) Sadly most of them point out my approach is flawed =/

Anyway, assuming there is at least some learning-merit to my current approach I still was hoping to find an answer to the 'use ref or not' question. Having waded through plenty of Q&A, forums & blogs for pretty much all day now I finally stumbled upon this one (AFTER I posted my question off course, typical) here . From what is said in that answer, I think it's safe to conclude there is no need to use ref because although it passes 'by value', I now understand it actually passes the pointer on the stack hence any changes made to the object (e.g.. moving to the next record) are done on the same instance of the object which is what I needed reassurance off.

As long as I don't do something silly like believing that another thread can replace the object with eg. the result of another SqlCommand.ExecuteReader(), things should be OK.

Rereading the comments above I now also better understand what Mitch Wheat meant with his remark

the use of ref (essentially a reference to a reference)

sorry I didn't "get" it earlier...