SqlBulkCopy 批量insert

/// <summary>
/// 从FMS取银行信息
/// </summary>
/// <returns></returns>
public void GetBankInfoFromFMS()
{
try
{
string connectionStr = Tool.GetConnectionStrings("BPMConnection");
BudgetServerClient client = new BudgetServerClient();
BankInfoRequest request = new BankInfoRequest();
request.PageIndex = ;
request.PageSize = int.MaxValue;
var response = client.QueryBankInfo(request);
if (response != null && response.total > && response.data != null && response.data.Count > )
{
//先删除
string sql = "delete from BPM_ExternalSupplier_BankInfo";
SqlHelper.ExecuteNonQuery(connectionStr, sql); //准备要批量插入的数据
DataTable table = new DataTable();
table.Columns.Add("ID");
table.Columns["ID"].DataType = typeof(Guid);
table.Columns.Add("Pkid");
table.Columns.Add("BankName");
table.Columns.Add("BankNum");
table.Columns.Add("Province");
table.Columns.Add("Citye");
table.Columns.Add("CreateUser");
table.Columns.Add("CreateTime");
table.Columns.Add("ModifiedUser");
table.Columns.Add("ModifiedTime");
table.Columns.Add("IsDeleted");
table.Columns["IsDeleted"].DataType = typeof(bool);
foreach (var item in response.data)
{
DataRow row = table.NewRow();
row["ID"] = Guid.NewGuid();
row["Pkid"] = item.Pkid;
row["BankName"] = item.BankName;
row["BankNum"] = item.BankNum;
row["Province"] = item.Province;
row["Citye"] = item.Citye;
row["CreateUser"] = "xuguanghui";
row["CreateTime"] = DateTime.Now;
row["ModifiedUser"] = "xuguanghui";
row["ModifiedTime"] = DateTime.Now;
row["IsDeleted"] = ;
table.Rows.Add(row);
}
SqlHelper.BulkCopyData(table, connectionStr, "BPM_ExternalSupplier_BankInfo");
}
}
catch (Exception ex)
{
Tool.SendErrorNotication("系统错误通知", "从FMS取银行信息GetBankInfoFromFMS方法错误，错误消息：" + ex.Message);
}
}

/// <summary>
/// 批量写入
/// </summary>
/// <param name="sourceTable">数据源</param>
/// <param name="connStr">数据库连接串</param>
/// <param name="destinationTableName">目标表</param>
public static void BulkCopyData(DataTable sourceTable, string connStr, string destinationTableName)
{
SqlBulkCopy sbc = new SqlBulkCopy(connStr, SqlBulkCopyOptions.UseInternalTransaction);
sbc.BulkCopyTimeout = ;
try
{
sbc.DestinationTableName = destinationTableName;
foreach (DataColumn dc in sourceTable.Columns)
{
sbc.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
sbc.WriteToServer(sourceTable);
}
catch
{
//The SqlBulkCopy object is automatically closed at the end of the using block.
//可以不写，会自动关闭
sbc.Close();
}
finally
{
//The SqlBulkCopy object is automatically closed at the end of the using block.
//可以不写，会自动关闭
sbc.Close();
}
}