从C#进行大批量数据库插入的最佳方法是什么？

Question

如何批量插入数据库？

在C#中，我正在遍历一个集合，并为集合中的每个项调用插入存储过程。

如何通过一个数据库调用发送所有数据？

例如，假设我有一个人员列表(List<Person>)，其中包含10个条目。我目前正在调用InsertPerson存储过程10次。我想将此减少到1个调用。

我正在使用MS SQL Server 2005。

- CsharperGuyInLondon

---

一定要查看这个问题。最受欢迎的答案（不是我的:p）提供了一个非常优雅的解决方案来解决这个确切的问题。 - Brann

感谢您提出这个问题。 - Ji_in_coding

Answer 1

伦敦的CsharperGuy，在这里有一个SqlBulkCopy代码的简单示例：

using System.Data.SqlClient;

DataTable table = new DataTable("States");
// construct DataTable
table.Columns.Add(new DataColumn("id_state", typeof(int))); 
table.Columns.Add(new DataColumn("state_name", typeof(string)));

// note: if "id_state" is defined as an identity column in your DB,
// row values for that column will be ignored during the bulk copy
table.Rows.Add("1", "Atlanta");
table.Rows.Add("2", "Chicago");
table.Rows.Add("3", "Springfield");

using(SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
  bulkCopy.BulkCopyTimeout = 600; // in seconds
  bulkCopy.DestinationTableName = "state";
  bulkCopy.WriteToServer(table);
}

- JohnB

---

我很感激这个示例代码。它为我节省了大量时间。在我的网络设置下，迭代插入17k（每行32列）记录需要约200秒的时间。使用这种方法只需要不到10秒钟。 - Ji_in_coding

一些想法：您不再需要创建DataColumn对象，现在可以直接将列名和类型传递到Add函数中，例如Add("id_state", typeof(int))。此外，表格布局需要与SQL表格匹配，包括列的顺序。 - Lord Darth Vader

Answer 2

嗯，10个条目并不能算作批量操作，但对于更大的数据集，SqlBulkCopy是你的好朋友。你只需要提供一个DataTable或IDataReader（我更喜欢后者，因为我喜欢流式API）。我曾经在这里做过类似的事情（你可以忽略XML部分 - 只需继承SimpleDataReader即可）。

- Marc Gravell

---

Marc：你知道 SqlBulkCopy 的内部实现吗？ - Jason Punyon

+1 对于“流式API”。@Jason，我猜SqlBulkCopy包装了BULK插入语句：http://technet.microsoft.com/en-us/library/ms187042.aspx - Michael Meadows

抱歉 - 我不在电脑前 - 这个API本质上与“bcp”等使用的API相同，但是直接通过TDS流（它不包装exe或类似物）进行操作。 - Marc Gravell

Answer 3

.NET的SqlBulkCopy类运行良好。

- MichaelGG

Answer 4

关于SqlBulkCopy的解决方案，我创建了一个类，接受一个Datatable或List<T>和一个缓冲区大小(CommitBatchSize)。它将使用扩展方法将列表转换为数据表(在第二个类中实现)。

它的速度非常快。在我的电脑上，我能够在不到10秒钟的时间内插入超过1000万条复杂的记录。

这是这个类：

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{

public class BulkUploadToSql<T>
{
    public IList<T> InternalStore { get; set; }
    public string TableName { get; set; }
    public int CommitBatchSize { get; set; }=1000;
    public string ConnectionString { get; set; }

    public void Commit()
    {
        if (InternalStore.Count>0)
        {
            DataTable dt;
            int numberOfPages = (InternalStore.Count / CommitBatchSize)  + (InternalStore.Count % CommitBatchSize == 0 ? 0 : 1);
            for (int pageIndex = 0; pageIndex < numberOfPages; pageIndex++)
                {
                    dt= InternalStore.Skip(pageIndex * CommitBatchSize).Take(CommitBatchSize).ToDataTable();
                BulkInsert(dt);
                }
        } 
    }

    public void BulkInsert(DataTable dt)
    {
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            // make sure to enable triggers
            // more on triggers in next post
            SqlBulkCopy bulkCopy =
                new SqlBulkCopy
                (
                connection,
                SqlBulkCopyOptions.TableLock |
                SqlBulkCopyOptions.FireTriggers |
                SqlBulkCopyOptions.UseInternalTransaction,
                null
                );

            // set the destination table name
            bulkCopy.DestinationTableName = TableName;
            connection.Open();

            // write the data in the "dataTable"
            bulkCopy.WriteToServer(dt);
            connection.Close();
        }
        // reset
        //this.dataTable.Clear();
    }

}

public static class BulkUploadToSqlHelper
{
    public static DataTable ToDataTable<T>(this IEnumerable<T> data)
    {
        PropertyDescriptorCollection properties =
            TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;
    }
}

以下是一个示例，当我想要插入我的自定义对象List<PuckDetection> (ListDetections)列表时：

var objBulk = new BulkUploadToSql<PuckDetection>()
{
        InternalStore = ListDetections,
        TableName= "PuckDetections",
        CommitBatchSize=1000,
        ConnectionString="ENTER YOU CONNECTION STRING"
};
objBulk.Commit();

- Amir

Answer 5

你可以构建一个BLOB（图像），并将其作为参数发送到存储过程中。在存储过程内部，你可以使用substring()来获取所有项目。

- daanish.rumani

Answer 6

这是一个很好的 SqlBulkCopy 操作示例： http://blogs.msdn.com/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx

- John Rasch

Answer 7

我将列表构建为一个xml字符串，并将其传递给存储过程。在SQL 2005中，它增强了xml功能，可以解析xml并进行批量插入。

请查看此帖子：使用XML参数将列表传递到SQL Server 2005

- Gulzar

Answer 8

将您的数据转储到一个管道分隔的文本文件中（如果您的数据中有管道，则可以使用其他内容），然后使用Bulk Insert。

- Jason Punyon

---

该方法需要是程序化的和快速的 - 没有磁盘I/O。谢谢。 - CsharperGuyInLondon

你可以使用MemoryStream来避免文件I/O，但你可能只是在进行一个样板实现SQLBulkCopy。请参见我对@Mark Gavell答案的评论。 - Michael Meadows

Answer 9

您可以使用 Xml 文档进行更新，Sql 2005 与其非常兼容。每行一个节点，但只有一个 Xml 参数。

- dove

Answer 10

创建一个包含所有要插入的项的XML文档。在存储过程中，使用TSQL xml支持（OPENXML）从XML文档中读取所有数据，并使用每个表一个插入语句将其插入到您的表中。

但是，如果您只需要将数据插入单个表中而不需要任何数据库端逻辑，为什么不使用SqlBulkCopy呢？

- Ian Ringrose

---

你不需要在SQL 2005中使用OPENXML，只需使用内置的XML数据类型即可。 - Adam Hughes

@Adam，如果你只是使用XML作为传输行的方式，OPENXML仍然很有用。然而，如果你希望将XML存储在数据库中，SQL 2005 XML数据类型非常有用。 - Ian Ringrose