将通用列表C＃批量插入SQL Server [英] Bulk Insert of Generic List C# into SQL Server

问题描述

如何将c＃中的通用列表批量插入SQL Server中，而不是遍历该列表并一次插入一个项目？

How can I bulk insert a generic list in c# into SQL Server, rather than looping through the list and inserting individual items one at a time?

我目前有此；

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; }
}

推荐答案

您可以映射您的列表到数据表，然后使用 SqlBulkCopy 进行插入所有行一次。

You may map your list to a datatable and then use SqlBulkCopy to insert all the rows at once.

这篇关于将通用列表C＃批量插入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！