logo标签列表

C#中将通用列表批量插入SQL Server

c#sql-serverbulkinsert
9

如何将一个C#的通用列表批量插入到SQL Server中，而不是循环遍历该列表并逐个插入项?

我目前有这个代码：

private void AddSnapshotData()
{
  var password =       Cryptography.DecryptString("vhx7Hv7hYD2bF9N4XhN5pkQm8MRfxi+kogALYqwqSuo=");
  var figDb = "ZEUS";
  var connString =
    String.Format(
      "Data Source=1xx.x.xx.xxx;Initial Catalog={0};;User ID=appuser;Password={1};MultipleActiveResultSets=True",
      figDb, password);
  var myConnection = new SqlConnection(connString);
  myConnection.Open();

  foreach (var holding in _dHoldList)
  {
    lbStatus.Text = "Adding information to SQL for client: " + holding.ClientNo;
    _reports.AddZeusData("tblAllBrooksHoldingsSnapshot", "CliNo, SEDOL, ISIN, QtyHeld, DateOfSnapshot",
                         "'" + holding.ClientNo + "','" + holding.Sedol + "','" + holding.ISIN + "','" +
                         holding.QuantityHeld + "','" + DateTime.Today.ToString("yyyyMMdd") + "'", false, myConnection);
  }
  myConnection.Close();
  lbStatus.Visible = false;
}

这里的 dHoldList 是一个 DHOLDS 列表；

public class DHOLDS : ExcelReport
 {
public String ClientNo { get; set; }
public String Sedol { get; set; }
public Double QuantityHeld { get; set; }
public Double ValueOfStock { get; set; }
public String Depot { get; set; }
public String ValC4 { get; set; }
public String StockR1 { get; set; }
public String StockR2 { get; set; }
public Double BookCost { get; set; }
public String ISIN { get; set; }
}
更多关于您计划如何存储它的信息会很有帮助，您可以使用自己的方法吗？还是必须使用“_reports”中的一些现有答案？ - SwissCoder
我可以使用自己的方法，数据库也属于我，所以我可以添加或进行任何更改。 - David Johnson
4个回答
10

您可以将列表映射到datatable，然后使用SqlBulkCopy一次性插入所有行。

1
这种方法需要在保存之前将列表转换为 DataTable，但从 SQL Server 的角度来看，这是最快的方式... - Richard Hooper
同意。我发现这是从服务器外部加载数据到SQL Server的最快方法。 - Erick T
祝你好运，如果你想要获取身份值的话。有人知道有什么更适合关系数据的东西吗？ - marknuzz
这是正确的答案。查看此文章示例，了解如何使用最少的代码行使用SqlBulkCopy。 - Radenko Zec
9

四年后，这是我的贡献。我遇到了同样的问题，我想要批量插入数据，但是跳过一些不需要存储在数据库中的字段，特别是EF导航属性，因此我编写了这个通用类：

/// <summary>
/// This class is intended to perform a bulk insert of a list of elements into a table in a Database. 
/// This class also allows you to use the same domain classes that you were already using because you
/// can include not mapped properties into the field excludedPropertyNames.
/// </summary>
/// <typeparam name="T">The class that is going to be mapped.</typeparam>
public class BulkInsert<T> where T : class
{
    #region Fields

    private readonly LoggingService _logger = new LoggingService(typeof(BulkInsert<T>));
    private string _connectionString;
    private string _tableName;
    private IEnumerable<string> _excludedPropertyNames;
    private int _batchSize;
    private IEnumerable<T> _data;
    private DataTable _dataTable;

    #endregion

    #region Constructor

    /// <summary>
    /// Initializes a new instance of the <see cref="BulkInsert{T}"/> class.
    /// </summary>
    /// <param name="connectionString">The connection string.</param>
    /// <param name="tableName">Name of the table.</param>
    /// <param name="data">The data.</param>
    /// <param name="excludedPropertyNames">The excluded property names.</param>
    /// <param name="batchSize">Size of the batch.</param>
    public BulkInsert(
        string connectionString,
        string tableName,
        IEnumerable<T> data,
        IEnumerable<string> excludedPropertyNames,
        int batchSize = 1000)
    {
        if (string.IsNullOrEmpty(connectionString)) throw new ArgumentNullException(nameof(connectionString));
        if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException(nameof(tableName));
        if (data == null) throw new ArgumentNullException(nameof(data));
        if (batchSize <= 0) throw new ArgumentOutOfRangeException(nameof(batchSize));

        _connectionString = connectionString;
        _tableName = tableName;
        _batchSize = batchSize;
        _data = data;
        _excludedPropertyNames = excludedPropertyNames == null ? new List<string>() : excludedPropertyNames;
        _dataTable = CreateCustomDataTable();
    }

    #endregion

    #region Public Methods

    /// <summary>
    /// Inserts the data with a bulk copy inside a transaction. 
    /// </summary>
    public void Insert()
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            SqlTransaction transaction = connection.BeginTransaction();

            using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default | SqlBulkCopyOptions.KeepIdentity, transaction))
            {
                bulkCopy.BatchSize = _batchSize;
                bulkCopy.DestinationTableName = _tableName;

                // Let's fix tons of mapping issues by
                // Setting the column mapping in SqlBulkCopy instance:
                foreach (DataColumn dataColumn in _dataTable.Columns)
                {
                    bulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);
                }

                try
                {
                    bulkCopy.WriteToServer(_dataTable);
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex.Message);
                    transaction.Rollback();
                    connection.Close();
                }
            }

            transaction.Commit();
        }
    }

    #endregion

    #region Private Helper Methods

    /// <summary>
    /// Creates the custom data table.
    /// </summary>
    private DataTable CreateCustomDataTable()
    {
        PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
        var table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
        {
            // Just include the not excluded columns
            if (_excludedPropertyNames.All(epn => epn != prop.Name))
            {                  
                if (prop.PropertyType.Name == "DbGeography")
                {
                    var type = typeof(SqlGeography);
                    table.Columns.Add(prop.Name, type);
                }
                else
                {
                    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)
            {
                // Just include the values in not excluded properties 
                if (_excludedPropertyNames.All(epn => epn != prop.Name))
                {
                    if (prop.PropertyType.Name == "DbGeography")
                    {                           
                        row[prop.Name] = SqlGeography.Parse(((DbGeography)prop.GetValue(item)).AsText()).MakeValid();
                    }
                    else
                    {
                        row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                    }
                }
            }
            table.Rows.Add(row);
        }
        return table;
    }

    #endregion

}

它的使用方式如下：

//1st.- You would have a colection of entities:
var myEntities = new List<MyEntity>();
// [...] With thousands or millions of items

// 2nd.- You would create the BulkInsert:
myEntityTypeBulk = new BulkInsert<MyEntity>(_connectionString, "MyEntitiesTableName", myEntities, new[] { "ObjectState", "SkippedEntityProperty1", "SkippedEntityProperty2" });

// 3rd.- You would execute it:
myEntityTypeBulk.Insert();

这个类获得的性能和可重用性是值得这个消息的。希望它有所帮助：
Juan
什么是SqlGeography？ - Vincent Ducroquet
1
我应该注释一下，这样就可以解决一个常见的问题，当你使用 Entity Framework 处理地理空间数据时，类型并不相同。你的实体将使用 EF 知道并用于此目的的 DbGeography 类型，但要将其插入到 SQL 中，则必须使用 SqlGeography。 - Juan
0

或者你也可以将列表转换为XML，就像这篇博客文章中所描述的那样：http://charleskong.com/blog/2009/09/insert-aspnet-objects-to-sql-server/ 但是使用SqlBulkCopy方法似乎更好。

另一个注意点：如果你想通过在代码中迭代元素来解决它，那么如果你在一个单一事务中执行所有插入操作，可能会提高性能。

我是在一个事务中进行所有插入操作的吗？所有循环完成后我断开连接吗？ - David Johnson
我的意思是利用SQLTransaction。通过在连接上打开事务，例如var t = connection.BeginTransaction();将其添加到执行查询的SQLMethod中，并在循环后调用t.Commit();。有关为什么它可能更快的信息，请参见此stackoverflow问题：https://dev59.com/HlTTa4cB1Zd3GeqPt5k1 - SwissCoder
0
Juan 的解决方案对我非常有效，所以我进行了一些修改，现在支持异步和一些重构。
public class BulkInsert<T> where T : class
{
    private readonly string _connectionString;
    private readonly string _tableName;
    private readonly IEnumerable<string>? _excludedPropertyNames;
    private readonly int _batchSize;
    private readonly IEnumerable<T> _data;
    private readonly DataTable _dataTable;

    /// <summary>
    /// Initializes a new instance of the <see cref="BulkInsert{T}"/> class.
    /// </summary>
    /// <param name="connectionString">The connection string.</param>
    /// <param name="tableName">Name of the table.</param>
    /// <param name="data">The data.</param>
    /// <param name="excludedPropertyNames">The excluded property names.</param>
    /// <param name="batchSize">Size of the batch.</param>
    public BulkInsert(
        string connectionString,
        string tableName,
        IEnumerable<T> data,
        IEnumerable<string>? excludedPropertyNames,
        int batchSize = 1000)
    {
        if (string.IsNullOrEmpty(connectionString)) throw new ArgumentNullException(nameof(connectionString));
        if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException(nameof(tableName));
        if (data == null) throw new ArgumentNullException(nameof(data));
        if (batchSize <= 0) throw new ArgumentOutOfRangeException(nameof(batchSize));

        _connectionString = connectionString;
        _tableName = tableName;
        _batchSize = batchSize;
        _data = data;
        _excludedPropertyNames = excludedPropertyNames ?? new List<string>();
        _dataTable = CreateCustomDataTable();
    }

    /// <summary>
    /// Inserts the data with a bulk copy inside a transaction.
    /// </summary>
    public async Task InsertAsync()
    {
        await using var connection = new SqlConnection(_connectionString);
        connection.Open();
        var transaction = connection.BeginTransaction();

        using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default | SqlBulkCopyOptions.KeepIdentity, transaction))
        {
            bulkCopy.BatchSize = _batchSize;
            bulkCopy.DestinationTableName = _tableName;

            // Let's fix tons of mapping issues by
            // Setting the column mapping in SqlBulkCopy instance:
            foreach (DataColumn dataColumn in _dataTable.Columns)
            {
                bulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);
            }

            try
            {
                await bulkCopy.WriteToServerAsync(_dataTable);
            }
            catch (Exception)
            {
                await transaction.RollbackAsync();
                await connection.CloseAsync();
            }
        }

        transaction.Commit();
    }

    /// <summary>
    /// Creates the custom data table.
    /// </summary>
    private DataTable CreateCustomDataTable()
    {
        var properties = TypeDescriptor.GetProperties(typeof(T));
        var table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
        {
            // Just include the not excluded columns
            if (_excludedPropertyNames != null && _excludedPropertyNames.All(epn => epn != prop.Name))
            {
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }
        }
        foreach (T item in _data)
        {
            var row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
            {
                // Just include the values in not excluded properties
                if (_excludedPropertyNames != null && _excludedPropertyNames.All(epn => epn != prop.Name))
                {
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                }
            }
            table.Rows.Add(row);
        }
        return table;
    }
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接