从 C# 批量插入数据库的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/682015/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What’s the best way to bulk database inserts from c#?
提问by
How do I/what's the best way to do bulk database inserts?
我如何/进行批量数据库插入的最佳方法是什么？
In C#, I am iterating over a collection and calling an insert stored procedure for each item in the collection.
在 C# 中，我正在迭代一个集合并为集合中的每个项目调用一个插入存储过程。
How do I send all the data in one database call?
如何在一次数据库调用中发送所有数据？
E.g. say I have a person list (List<Person>) containing 10 items. I am currently calling the InsertPerson stored proc 10 times. I would like to reduce this to 1 call.
例如，假设我有一个List<Person>包含 10 个项目的人员列表 ( )。我目前正在调用 InsertPerson 存储过程 10 次。我想把这个减少到 1 个电话。
I am using MS SQL Server 2005.
我正在使用 MS SQL Server 2005。
回答by Jason Punyon
Dump your data to a pipe delimited (or something else if your data has pipes in it) text file and use Bulk Insert.
将您的数据转储到管道分隔（或其他东西，如果您的数据中有管道）文本文件并使用Bulk Insert。
回答by daanish.rumani
You can build a BLOB (image) and send it as a parameter to a stored procedure. Inside the stored procedure, you can fetch all the items using substring().
您可以构建一个 BLOB（图像）并将其作为参数发送到存储过程。在存储过程中，您可以使用 substring() 获取所有项目。
回答by MichaelGG
The .NET SqlBulkCopyclass works quite well.
.NET SqlBulkCopy类工作得很好。
回答by dove
You could update with an Xml document, Sql 2005 works very well with them. One node per row, but just one parameter for Xml.
您可以使用 Xml 文档进行更新，Sql 2005 与它们配合得很好。每行一个节点，但 Xml 只有一个参数。
回答by Marc Gravell
Well, 10 items isn't what I call bulk, but for larger sets, SqlBulkCopyis your friend. All you need to do is feed it either a DataTableor an IDataReader(my preferred option, 'cos I like streaming APIs). I did something similar here(you can ignore the xml side - just subclass the SimpleDataReader).
嗯，10 件物品不是我所说的批量，但对于更大的套装，SqlBulkCopy是你的朋友。您需要做的就是提供一个DataTable或一个IDataReader（我的首选选项，因为我喜欢流式 API）。我在这里做了类似的事情（你可以忽略 xml 端——只需将 SimpleDataReader 子类化）。
回答by John Rasch
Here's a good example of SqlBulkCopyin action:
这是一个很好的例子SqlBulkCopy：
http://blogs.msdn.com/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx
http://blogs.msdn.com/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx
回答by Ian Ringrose
Create a XML document that contains all the items to be inserted. Then inside of a stored procedure, use the TSQL xml support (OPENXML) to read all the data from the XML document and insert it into your tables with hopefully one insert statement for each table.
创建一个 XML 文档，其中包含要插入的所有项目。然后在存储过程中，使用 TSQL xml 支持 ( OPENXML) 从 XML 文档中读取所有数据并将其插入到您的表中，希望每个表都有一个插入语句。
However if you are only inserting data into a single table and don't need any database side logic, why not use SqlBulkCopy?
但是，如果您只是将数据插入到单个表中并且不需要任何数据库端逻辑，为什么不使用SqlBulkCopy呢？
回答by Gulzar Nazim
I construct the list as an xml string and pass it to the stored proc. In SQL 2005, it has enhanced xml functionalities to parse the xml and do a bulk insert.
我将列表构造为 xml 字符串并将其传递给存储过程。在 SQL 2005 中，它增强了 xml 功能来解析 xml 并进行批量插入。
check this post: Passing lists to SQL Server 2005 with XML Parameters
检查这篇文章： 使用 XML 参数将列表传递给 SQL Server 2005
回答by JohnB
CsharperGuyInLondon, here's a simple example of SqlBulkCopy code:
CsharperGuyInLondon，这里有一个简单的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);
}
回答by Amir
Re the solution for SqlBulkCopy, I created a class than takes Datatableor a List<T>and a Buffer size (CommitBatchSize). It will convert the list to a data table using an extension (in the second class).
关于 SqlBulkCopy 的解决方案，我创建了一个类，而不是 takeDatatable或 aList<T>和一个缓冲区大小 ( CommitBatchSize)。它将使用扩展名（在第二类中）将列表转换为数据表。
It works very fast. On my PC, I am able to insert more than 10 million complicated records in less than 10 seconds.
它的工作速度非常快。在我的 PC 上，我能够在不到 10 秒的时间内插入超过 1000 万条复杂的记录。
Here is the class:
这是课程：
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;
}
}
}
}
Here is an example when I want to insert a List of my custom object List<PuckDetection>(ListDetections):
这是我想插入自定义对象列表List<PuckDetection>( ListDetections)时的示例：
var objBulk = new BulkUploadToSql<PuckDetection>()
{
InternalStore = ListDetections,
TableName= "PuckDetections",
CommitBatchSize=1000,
ConnectionString="ENTER YOU CONNECTION STRING"
};
objBulk.Commit();
