有没有办法将任务并行库(TPL)与 SQLDataReader 一起使用?

Is there a way to use the Task Parallel Library(TPL) with SQLDataReader?(有没有办法将任务并行库(TPL)与 SQLDataReader 一起使用?)

问题描述

我喜欢 TPL 中 Parallel.For 和 Parallel.ForEach 扩展方法的简单性.我想知道是否有办法利用类似的东西，甚至是稍微高级一点的任务.

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.

下面是 SqlDataReader 的典型用法，我想知道这是否可行，如果可行，如何用 TPL 中的内容替换下面的 while 循环.因为读者不能提供固定数量的迭代，所以 For 扩展方法是不可能的，这让我不得不处理我收集的任务.我希望有人可能已经解决了这个问题，并通过 ADO.net 制定了一些注意事项.

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
        }
    }
}

推荐答案

你快到了.使用此签名将您发布在函数中的代码包装起来:

You're almost there. Wrap the code you posted in a function with this signature:

IEnumerable<IDataRecord> MyQuery()

然后将您的 //Do something with Reader 代码替换为:

and then replace your // Do something with Reader code with this:

yield return reader;

现在你有了在一个线程中工作的东西.不幸的是，当您阅读查询结果时，它每次都会返回对 same 对象的引用，并且该对象只会在每次迭代时自行改变.这意味着如果你尝试并行运行它，你会得到一些非常奇怪的结果，因为并行读取会改变不同线程中使用的对象.您需要代码来获取记录的副本，以发送到您的并行循环.

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 same 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 copy of the record to send to your parallel loop.

不过，在这一点上，我喜欢做的是跳过记录的额外副本，直接进入强类型类.不仅如此，我还喜欢使用泛型方法来做到这一点:

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:

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);
            }
        }
    }
}

假设您的工厂方法按预期创建副本，则此代码在 Parallel.ForEach 循环中使用应该是安全的.调用该方法看起来像这样(假设一个 Employee 类具有一个名为Create"的静态工厂方法):

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"):

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());

重要更新:
我对这段代码没有以前那么自信了.当另一个线程正在复制它时，一个单独的线程仍然可以改变阅读器.我可以锁定它，但我也担心另一个线程可能会在原件本身调用 Read() 之后但在它开始制作副本之前调用更新阅读器.因此，这里的关键部分由整个 while 循环组成……此时，您又回到了单线程.我希望有一种方法可以修改此代码，使其在多线程场景中按预期工作，但需要更多研究.

Important Update:
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.

这篇关于有没有办法将任务并行库(TPL)与 SQLDataReader 一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！