c# - Is there a way to use the Task Parallel Library(TPL) with SQLDataReader?

Question

I like the simplicity of the Parallel.For and Parallel.ForEach extension methods in the TPL. I was wondering if there was a way to take advantage of something similar or even with the slightly more advanced Tasks.

Below is a typical usage for the SqlDataReader, and I was wondering if it was possible and if so how to replace the while loop below with something in the TPL. Because the reader can't provide a fixed number of iterations the For extension method is not possible which leaves dealing with Tasks I would gather. I was hoping someone may have tackled this already and worked out some do's and don''s with ADO.net.

using (SqlConnection conn = new SqlConnection("myConnString"))
using (SqlCommand comm = new SqlCommand("myQuery", conn))
{
    conn.Open();

    SqlDataReader reader = comm.ExecuteReader();

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            // Do something with Reader
        }
    }
}

Answer 1

You're going to have difficulty replacing that while loop directly. SqlDataReader is not a thread safe class, so you cannot use it directly from multiple threads.

That being said, you could potentially process the data you read using the TPL. There are a few options, here. The easiest might be to make your own IEnumerable<T> implementation that works on the reader, and returns a class or struct containing your data. You could then use PLINQ or a Parallel.ForEach statement to process your data in parallel:

public IEnumerable<MyDataClass> ReadData()
{
    using (SqlConnection conn = new SqlConnection("myConnString"))
    using (SqlCommand comm = new SqlCommand("myQuery", conn))
    {
        conn.Open();

        SqlDataReader reader = comm.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                yield return new MyDataClass(... data from reader ...);
            }
        }
    }
}

Once you have that method, you can process this directly, via PLINQ or TPL:

Parallel.ForEach(this.ReadData(), data =>
{
    // Use the data here...
});

Or:

this.ReadData().AsParallel().ForAll(data => 
{
    // Use the data here...
});