有没有一种方法使用任务并行库（TPL）与SqlDataReader的？ [英] Is there a way to use the Task Parallel Library(TPL) with SQLDataReader?

问题描述

我喜欢在第三方物流中的Parallel.For和Parallel.ForEach扩展方法简单。我想知道是否有办法采取类似，甚至与更高级一些任务什么优势。

下面是一个典型使用情况SqlDataReader的，我想知道，如果有可能，如果是怎样的东西在TPL更换while循环以下。因为读者不能提供迭代的固定数目的有关扩展方法是不可能的哪个叶处理任务我会聚集。我希望有人可能已经解决了这一研制出了一些做的和Don'同ADO.net。

 使用（SqlConnection的康恩=新的SqlConnection（myConnString））
使用（SqlCommand的通讯=新的SqlCommand（更改为MyQuery，康涅狄格州））
{
    conn.Open（）;

    SqlDataReader的读者= comm.ExecuteReader（）;

    如果（reader.HasRows）
    {
        而（reader.Read（））
        {
            //做一些与阅读器
        }
    }
}
 

解决方案

你几乎没有。总结你张贴在功能与此签名的code：

 的IEnumerable＆LT; IDataRecord＆GT;更改为MyQuery（）
 

然后更换你的 //做一些与阅读器 code这一点：

 收益回报读者;
 

现在你有一些在单线程工作。不幸的是，当你通过查询读取结果是返回一个引用的一样的对象每一次，对象只是变异本身对每个迭代。这意味着，如果你尝试在并行运行它，你会得到一些非常奇怪的结果并行读取变异在不同的线程使用的对象。您需要code采取的复制的记录的发送给您的并行循环。

在这一点上，虽然，我喜欢做的是跳过记录的额外拷贝，直接进入一个强类型的类。更重要的是，我喜欢用一个通用的方法来做到这一点：

 的IEnumerable＆LT; T＆GT;的GetData＆LT; T＆GT;（Func键＆LT; IDataRecord，T＆GT;工厂，SQL字符串，动作＆LT; SqlParameterCollection＆GT; addParameters）
{
    使用（VAR CN =新的SqlConnection（我的连接字符串））
    使用（VAR CMD =新的SqlCommand（SQL，CN））
    {
        addParameters（cmd.Parameters）;

        cn.Open（）;
        使用（VAR RDR = cmd.ExecuteReader（））
        {
            而（rdr.Read（））
            {
                得到回报的工厂（RDR）;
            }
        }
    }
}
 

假设你的工厂方法创建如预期的副本，此code应该是安全的Parallel.ForEach循环使用。调用该方法会是这个样子（假设一个Employee类名为静态工厂方法创建）：

  VAR少缴=的GetData＆LT;员工＆GT;（Employee.Create，
       SELECT * FROM员工WHERE AnnualSalary＆LT; = @MinSalary
       P =＆GT; {
           p.Add（@ MinSalary，SqlDbType.Int）。价值= 50000;
       }）;
Parallel.ForEach（报酬过低，E =＆GT; e.GiveRa​​ise（））;
 

重要更新：
我没有信心在这个code，因为我曾经是。一个单独的线程仍然可以发生变异，读，而另一个线程处于使得它的副本的过程。我可以把周围的锁，但我也担心，另一个线程可以调用更新的读者后，原来自己已经被称为阅读（），但它开始进行复制之前。因此，关键的部分在这里由整个while循环......在这一点上，你又回到了单线程了。我希望有一种方法可以修改此code的工作如预期的多线程方案，但它会需要更多的研究。

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

解决方案

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

IEnumerable<IDataRecord> MyQuery()

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

yield return reader;

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

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

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的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！