在循环中使用SqlConnection的最佳方法

Question

我每天在 Windows 服务中处理大约 24k 条记录，我将这些行插入数据库，然后将压缩的图像保存到目录中。一般来说，最好的方法是什么？

using (SqlConnection cnn = new SqlConnection())
{
    foreach (var item in collection)
    {
        //do stuff 

        SaveImage(item.filename,item.img);
    }
}

或者

foreach (var item in collection)
{
    using (SqlConnection cnn = new SqlConnection())
    {
        //do stuff
    }

    SaveImage(item.filename, item.img);
}

谢谢

- Mauro Petrini

---

你可能需要明确什么是“最好的”。效率、可读性、安全性、可维护性等等。 - But I'm Not A Wrapper Class

Answer 1

我会在循环外建立连接。如果它是一些永久运行的东西，我的回答会不同，例如，如果您正在运行 Web 服务，我建议为每个请求打开连接（这非常不同，但我只是举个例子）。但是打开连接是一项资源密集型任务，在这种情况下，使用单个连接将更容易。这真的取决于您在这里的关注点，就像其他几个人说的那样，但我认为单个连接听起来完全可以接受。正如我看到另一位评论者所说的那样，如果需要，您可以使用事务将其包装起来。

---

这里的其他内容并不是直接回答你的问题，但考虑到你的情况，我认为思考一下这些内容也是值得的。

你可能想要了解SQL的MERGE语句。如果你能一次发送多条记录给SQL，速度会更快。当然，你仍然需要将图片单独保存，但这至少可以让SQL方面更快。当然，你也可以使用INSERT语句来实现，但这取决于你传递的每个新记录是否真正是新的，或者它是否违反唯一键。

你还可以看看TPL Dataflow，因为它有一些有用的类可以同时进行多个操作和批处理，这可能使你的插入速度更快。不过要记住，单个连接有时不是处理并发请求的最佳选择。但是，如果你将数据分成100组（请记住，每个SqlCommand的最大查询参数数是2100，所以只需将其除以所需数量并减去误差），我可能会建议为每个组创建一个连接。

我知道这比你要求的要多得多，但我经常处理这样的情况，所以我想分享一些我在路上学到的东西。

- Matthew Haugen

---

刚刚发现了关于MERGE的内容 https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ - Kunal

Answer 2

如果连接中断，您的第一种方法将跳过整个过程。

您的第二种方法是在每个循环中创建一个新连接，这会严重拖慢性能。

您可以分批处理（例如50个）。

var batchSize = 50;

for(int i = 0; i<collection.Count ; i+=batchSize)
{
     using (var cnn = new SqlConnection())
     {
      int j = 0
      foreach (var item in collection.Skip(i))
      {
        if(j++ == batchSize) continue;

         //do stuff     
         SaveImage(item.filename,item.img);     
      }
     }
}

在这里，您每50行数据创建一次连接。 您可以根据需要进行调整以优化性能与可靠性的平衡。

- Milan

Answer 3

如果您在每个循环中使用连接，那么首选解决方案是将其创建在每个循环中重新创建。

创建连接是一个耗时的过程。

此外，如果可能的话，建议使用单个transaction来处理所有查询。

- Jester

---

"创建连接是一个耗时的过程"，实际上如果您一遍又一遍地连接到同一个地方，它并不耗时。ADO使用连接池，在关闭连接后会保持连接一段时间，以便其他部分可以重用该连接。 - Scott Chamberlain

@ScottChamberlain 这不是一个在所有平台上都保证的实现细节吗？ - itsme86

@itsme86 这在所有的 SqlConnection 对象上都是有保证的（http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx）。对于其他 DbConnection 派生类，这取决于类的实现者，但如果正确设置了 .NET OLE DB、ODBC 和 OracleClient，它们也可以支持它。 - Scott Chamberlain

Answer 4

这里有很多因素，但在大多数情况下，我建议在循环内打开/关闭连接，并且只在该连接期间进行必要的数据库操作。

你需要大量的错误处理，但如果在连接或图像处理过程中出现问题，它会使得继续循环更容易处理更多记录。

- Mikanikal