logo标签列表

从List<>中使用SqlBulkCopy

c#sqlsql-serverado.netbulkinsert
36

如何使用SqlBulkCopy从简单对象的List<>进行大批量插入？

我需要实现自定义的IDataReader吗？

虽然有点晚了，但如果您添加这个EntityDataReader类，就会有一个AsDataReader()扩展方法可以完全实现这一点：https://github.com/matthewschrager/Repository/blob/master/Repository.EntityFramework/EntityDataReader.cs - RJB
请参见下面的答案，其中包含完整的翻译实现。 - RJB
5个回答
61

使用FastMember，您可以在不经过DataTable的情况下完成此操作（在我的测试中，这种方式的性能提高了两倍以上）：

using(var bcp = new SqlBulkCopy(connection))
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description"))
{
    bcp.DestinationTableName = "SomeTable";
    bcp.WriteToServer(reader);
}

请注意，ObjectReader还可以与非泛型源一起使用，并且不需要预先指定成员名称（尽管如果您没有在ObjectReader本身中指定它们，则可能希望使用SqlBulkCopy的ColumnMappings方面）。

1
优秀的库！我刚刚尝试了一下，它运行得非常好。 - alex
我知道这是几个月前的事了，但我遇到了类似的问题。首先加载DataTable太慢了，所以我想使用这种方法。然而，在params变量中列出的字符串是否是正在使用的变量的实际名称，以便从底层数据结构迭代的对象中按顺序使用它们？ - JNYRanger
算了，我想通了，答案是是的，那些确实是对象内部属性的名称。 - JNYRanger
1
@Marc Gravell，当我使用bcp和List<T>时，我会收到错误消息“无法将源字符串转换为datetime”。我猜我需要添加ColumnMappings，但要获取列名，我必须进行反射。是否有一种方法可以从ObjectReader获取属性名称？ - Afroz
3
@Marc Gravell，当我长大后我想像你一样写代码 :）。谢谢你，你总是给出很棒的答案。 - Bayeni
我尝试了你的ObjectReader.Create(entities)，但它比我使用Lazy<PropertyAccessor>从这个网站https://www.codeproject.com/Articles/1095790/Using-SqlBulkCopy-with-IDataReader-for-Hi-Perf-Ins创建的新ObjectDataReader<T>(entities)慢了8倍。为什么会这样？ - Elisabeth
22

只需从对象列表中创建一个DataTable，然后调用SqlBulkCopy.WriteToServer，将数据表传递进去即可。

您可能会发现以下内容有用：

为了获得最佳的SqlBulkCopy性能，您应该设置适当的BatchSize。 10,000似乎效果很好-但要针对您的数据进行分析。

当使用SqlBulkCopyOptions.TableLock时，您可能还会观察到更好的结果。

关于SqlBulkCopy性能的有趣且有启发性的分析可以在这里找到。

12
晚来的话，但如果你添加这个来自微软的EntityDataReader类，那么就会有一个AsDataReader()扩展方法可以实现这一功能：https://github.com/matthewschrager/Repository/blob/master/Repository.EntityFramework/EntityDataReader.cs （示例 [List].AsDataReader() 实现：）
var connStr = "";
using (var connection = new SqlConnection(connStr)) 
{
    var startTime = DateTime.Now;
    connection.Open();
    var transaction = connection.BeginTransaction();
    try
    {
        //var connStr = connection.ConnectionString;
        using (var sbCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
        {
            sbCopy.BulkCopyTimeout = 0;
            sbCopy.BatchSize = 10000;
            sbCopy.DestinationTableName = "Foobars";
            var reader = Foobars.AsDataReader();
            sbCopy.WriteToServer(reader);
        }
        transaction.Commit();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        transaction.Rollback();
    }
    finally
    {
        transaction.Dispose();
        connection.Close();
        var endTime = DateTime.Now;
        Console.WriteLine("Upload time elapsed: {0} seconds", (endTime - startTime).TotalSeconds);
    }
}
有些人可能需要为每个要导入的列添加一个列映射：sbCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("col1", "col1")); - Jaime Bennett
1
根据您调用SqlBulkCopy的目的，使用表值参数（TVP）可能更有意义。使用TVP可以轻松地发送任何自定义类型的集合。数据可以流式传输，因此可以避免DataTable（就像@Marc Gravell的答案中一样），也可以避免SqlBulkCopy。TVP允许完全灵活地处理数据，因为您调用存储过程将TVP数据传递到SQL Server，并且它显示为一个表变量，您可以对其进行任何操作，而不仅仅是INSERT（这是SqlBulkCopy的情况）。您还可以通过SqlDataReader获取数据，例如新创建的IDENTITY值。我在这个答案中添加了一个示例和一些附加说明: 如何在最短时间内插入1000万条记录？。几年前，我在SQL Server Central上写了一篇文章（需要免费注册），从应用程序流式传输数据到SQL Server 2008，该文章也在那个链接的答案中提到，提供了一个传递自定义类型的通用列表的工作示例，从一个300万行文本文件中流式传输。
0

在尝试将数百万行插入数据库时，遇到了类似的情况。

通过将列表转换为 DataTable，然后将表格插入数据库来完成此操作。

private static DataTable CreateDataTableItem(List<ListItem> ItemList)
    {
        DataTable dt = new DataTable();
        try
        {
            dt.TableName = "PartPrice";

            foreach (PropertyInfo property in typeof(ListItem).GetProperties())
            {
                dt.Columns.Add(new DataColumn() { ColumnName = property.Name, DataType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType, AllowDBNull = true });
            }

            foreach (var item in ItemList)
            {
                DataRow newRow = dt.NewRow();
                foreach (PropertyInfo property in typeof(ListItem).GetProperties())
                {
                    newRow[property.Name] = item.GetType().GetProperty(property.Name)?.GetValue(item, null) ?? DBNull.Value;
                }
                dt.Rows.Add(newRow);
            }
            return dt;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            return null;
        }
    }

public class ListItem
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? NullableId { get; set; }

}

然后使用批量插入

    private void BulkInsert(DataTable dt)
    {
        string consString = _config.GetConnectionString("yourConnectionStringkey");
        using SqlConnection connection = new SqlConnection(consString);

        connection.Open();
        using var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null);
        sqlBulkCopy.DestinationTableName = "dbo.TargetDb";
        sqlBulkCopy.ColumnMappings.Add("Id", "Id");
        sqlBulkCopy.ColumnMappings.Add("Name", "Name");
        sqlBulkCopy.ColumnMappings.Add("NullableId", "NullableId");


        sqlBulkCopy.WriteToServer(dt);
        connection.Close();
    }

您不必执行给定的列映射

    // Summary:
    //     Returns a collection of Microsoft.Data.SqlClient.SqlBulkCopyColumnMapping items.
    //     Column mappings define the relationships between columns in the data source and
    //     columns in the destination.
    //
    // Value:
    //     A collection of column mappings. By default, it is an empty collection.
    //
    // Remarks:
    //     If the data source and the destination table have the same number of columns,
    //     and the ordinal position of each source column within the data source matches
    //     the ordinal position of the corresponding destination column, the <xref:Microsoft.Data.SqlClient.SqlBulkCopy.ColumnMappings>
    //     collection is unnecessary. However, if the column counts differ, or the ordinal
    //     positions are not consistent, you must use <xref:Microsoft.Data.SqlClient.SqlBulkCopy.ColumnMappings>
    //     to make sure that data is copied into the correct columns. During the execution
    //     of a bulk copy operation, this collection can be accessed, but it cannot be changed.
    //     Any attempt to change it will throw an <xref:System.InvalidOperationException>.

这两行代码允许您将可空值插入数据表列中

 { ColumnName = property.Name, DataType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType, AllowDBNull = true }

newRow[property.Name] = item.GetType().GetProperty(property.Name)?.GetValue(item, null) ?? DBNull.Value;
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接