StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You're almost there. Wrap the code you posted in a function with this signature:</p> <pre><code>IEnumerable<IDataRecord> MyQuery() </code></pre> <p>and then replace your <code>// Do something with Reader</code> code with this:</p> <pre><code>yield return reader; </code></pre> <p>Now you have something that works in a single thread. Unfortunately, as you read through the query results it's return a reference to the <em>same</em> object each time, and the object just mutates itself for each iteration. This means that if you try to run it in parallel you'll get some really odd results as parallel reads mutate the object used in different threads. You need code to take a <em>copy</em> of the record to send to your parallel loop. </p> <p>At this point, though, what I like to do is skip the extra copy of the record and go straight to a strongly-typed class. More than that, I like to use a generic method to do it:</p> <pre><code>IEnumerable<T> GetData<T>(Func<IDataRecord, T> factory, string sql, Action<SqlParameterCollection> addParameters) { using (var cn = new SqlConnection("My connection string")) using (var cmd = new SqlCommand(sql, cn)) { addParameters(cmd.Parameters); cn.Open(); using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { yield return factory(rdr); } } } } </code></pre> <p>Assuming your factory methods create a copy as expected, this code should be safe to use in a Parallel.ForEach loop. Calling the method would look something like this (assuming a an Employee class with a static factory method named "Create"):</p> <pre><code>var UnderPaid = GetData<Employee>(Employee.Create, "SELECT * FROM Employee WHERE AnnualSalary <= @MinSalary", p => { p.Add("@MinSalary", SqlDbType.Int).Value = 50000; }); Parallel.ForEach(UnderPaid, e => e.GiveRaise()); </code></pre> <p><strong>Important Update:</strong><br> I'm not as confident in this code as I once was. A separate thread could still mutate the reader while another thread is in the process of making it's copy. I could put a lock around that, but I'm also concerned that another thread could call update the reader after the original has itself called Read() but before it begins to make the copy. Therefore, the critical section here consists of the entire while loop... and at this point, you're back to single-threaded again. I expect there is a way to modify this code to work as expected for multi-threaded scenarios, but it will need more study.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload