C# 一次将整个 DataTable 插入数据库而不是逐行插入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10405373/
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
Insert entire DataTable into database at once instead of row by row?
提问by Kyle
I have a DataTable and need the entire thing pushed to a Database table.
我有一个 DataTable 并且需要将整个内容推送到一个数据库表中。
I can get it all in there with a foreach and inserting each row at a time. This goes very slow though since there are a few thousand rows.
我可以通过 foreach 将所有内容全部放入并一次插入每一行。这虽然有几千行，但速度很慢。
Is there any way to do the entire datatable at once that might be faster?
有没有办法一次完成整个数据表可能更快？
The DataTable has less columns than the SQL table. the rest of them should be left NULL.
DataTable 的列比 SQL 表少。其余的应该保留为NULL。
采纳答案by Kyle
I discovered SqlBulkCopy is an easy way to do this, and does not require a stored procedure to be written in SQL Server.
我发现 SqlBulkCopy 是一种简单的方法来做到这一点，并且不需要在 SQL Server 中编写存储过程。
Here is an example of how I implemented it:
这是我如何实现它的示例：
// take note of SqlBulkCopyOptions.KeepIdentity , you may or may not want to use this for your situation.
using (var bulkCopy = new SqlBulkCopy(_connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
// my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
foreach (DataColumn col in table.Columns)
{
bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
}
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = destinationTableName;
bulkCopy.WriteToServer(table);
}
回答by MrEyes
You can do this with a table value parameters.
您可以使用表值参数执行此操作。
Have a look at the following article:
看看下面的文章：
http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters
http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters
回答by Aaron Bertrand
Since you have a DataTable already, and since I am assuming you are using SQL Server 2008 or better, this is probably the most straightforward way. First, in your database, create the following two objects:
由于您已经有一个 DataTable，而且我假设您使用的是 SQL Server 2008 或更高版本，因此这可能是最直接的方法。首先，在您的数据库中，创建以下两个对象：
CREATE TYPE dbo.MyDataTable -- you can be more speciifc here
AS TABLE
(
col1 INT,
col2 DATETIME
-- etc etc. The columns you have in your data table.
);
GO
CREATE PROCEDURE dbo.InsertMyDataTable
@dt AS dbo.MyDataTable READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.RealTable(column list) SELECT column list FROM @dt;
END
GO
Now in your C# code:
现在在你的 C# 代码中：
DataTable tvp = new DataTable();
// define / populate DataTable
using (connectionObject)
{
SqlCommand cmd = new SqlCommand("dbo.InsertMyDataTable", connectionObject);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", tvp);
tvparam.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
}
If you had given more specific details in your question, I would have given a more specific answer.
如果您在问题中提供了更具体的细节，我会给出更具体的答案。
回答by Shamseer K
I would prefer user defined data type : it is super fast.
我更喜欢用户定义的数据类型：它非常快。
Step 1 : Create User Defined Table in Sql Server DB
第 1 步：在 Sql Server DB 中创建用户定义表
CREATE TYPE [dbo].[udtProduct] AS TABLE(
[ProductID] [int] NULL,
[ProductName] [varchar](50) NULL,
[ProductCode] [varchar](10) NULL
)
GO
Step 2 : Create Stored Procedure with User Defined Type
第 2 步：创建用户定义类型的存储过程
CREATE PROCEDURE ProductBulkInsertion
@product udtProduct readonly
AS
BEGIN
INSERT INTO Product
(ProductID,ProductName,ProductCode)
SELECT ProductID,ProductName,ProductCode
FROM @product
END
Step 3 : Execute Stored Procedure from c#
第 3 步：从 c# 执行存储过程
SqlCommand sqlcmd = new SqlCommand("ProductBulkInsertion", sqlcon);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@product", productTable);
sqlcmd.ExecuteNonQuery();
Possible Issue : Alter User Defined Table
可能的问题：更改用户定义的表
Actually there is no sql server command to alter user defined type But in management studio you can achieve this from following steps
实际上没有sql server命令来改变用户定义的类型但是在管理工作室中你可以通过以下步骤来实现
1.generate script for the type.(in new query window or as a file) 2.delete user defied table. 3.modify the create script and then execute.
1. 为该类型生成脚本。（在新的查询窗口中或作为文件） 2. 删除用户定义的表。3.修改创建脚本，然后执行。
回答by Alexei
If can deviate a little from the straight path of DataTable -> SQL table, it can also be done via a list of objects:
如果可以稍微偏离DataTable -> SQL table的直线路径，也可以通过对象列表来完成：
1) DataTable -> Generic list of objects
1) DataTable -> 通用对象列表
public static DataTable ConvertTo<T>(IList<T> list)
{
DataTable table = CreateTable<T>();
Type entityType = typeof(T);
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);
foreach (T item in list)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(item);
}
table.Rows.Add(row);
}
return table;
}
Source and more details can be found here. Missing properties will remain to their default values (0 for ints, null for reference types etc.)
可以在此处找到来源和更多详细信息。缺少的属性将保留其默认值（ints 为0 ，引用类型为null 等）
2) Push the objects into the database
2）将对象推入数据库
One way is to use EntityFramework.BulkInsertextension. An EF datacontext is required, though.
一种方法是使用EntityFramework.BulkInsert扩展。但是，需要 EF 数据上下文。
It generates the BULK INSERT command required for fast insert (user defined table type solution is much slower than this).
它生成快速插入所需的 BULK INSERT 命令（用户定义的表类型解决方案比这慢得多）。
Although not the straight method, it helps constructing a base of working with list of objects instead of DataTables which seems to be much more memory efficient.
虽然不是直接的方法，但它有助于构建使用对象列表而不是DataTables 的基础，这似乎更有效。
回答by jaleel
Consider this approach, you don't need a for loop:
考虑这种方法，您不需要 for 循环：
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(ExitingSqlTableName);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
