利用sqlbulkcopy实现批量修改

#region SqlBulkCopy方式批量修改数据
/// <summary>
/// SqlBulkCopy方式批量修改数据
/// </summary>
/// <typeparam name="T">对象</typeparam>
/// <param name="modelList">实体类集合</param>
/// <param name="onRelations">关联字段</param>
/// <param name="destinationTableName">目标表名</param>
/// <param name="removeColumns">移除的字段列集合</param>
/// <param name="UpdateColumns">更新的字段集合，不填则全部</param>
// public void BatchUpdate<T>(List<T> modelList, string onRelations, string destinationTableName = null, List<string> removeColumns = null, List<string> UpdateColumns = null)

DataTable dt：要导入的数据

string onRelations：关系字段

destinationTableName：目表数据表的名称（数据库里的表）

removeColumns ：要删除的字段

List<string> UpdateColumns = null：要修改的字段

,List<string> queryColumns=null：从数据库的表里查 出的字段，用于生成临时表的字段

string tempName=null：数据库中自己手动建的表结构，用于无法建临时表的情况

Dictionary<string,string>mappingColumn=null：将内存中的表字段映射到数据库中生成的临时表的字段

public int BatchUpdate<T>(DataTable dt, string onRelations, string destinationTableName = null, List<string> removeColumns = null, List<string> UpdateColumns = null,List<string> queryColumns=null,string tempName=null,Dictionary<string,string>mappingColumn=null)
{
//临时表
//if (tempName==null)
//{
// tempName = destinationTableName;
//}
if (string.IsNullOrEmpty(destinationTableName))
destinationTableName = typeof(T).Name.Replace("EN", "");
// var dt = ContainHelper.ListToTable(modelList);
if (removeColumns != null && removeColumns.Count > 0)
{
foreach (var item in removeColumns)
{
dt.Columns.Remove(item);
}
}
var sbQueryColumns = new StringBuilder();
var index = 0;
if (queryColumns != null && queryColumns.Count > 0)
{
foreach (var queryColumn in queryColumns)
{
if (index>0)
{
sbQueryColumns.Append(", ");
}
sbQueryColumns.AppendFormat("{0}",queryColumn);
index++;
}
} else//所有字段
{
sbQueryColumns.AppendFormat("{0}"," * ");
}

var sbUpdateColumns = new StringBuilder();
var columnsIndex = 0;
//只更新某字段
if (UpdateColumns != null && UpdateColumns.Count > 0)
{
foreach (var updateColumn in UpdateColumns)
{
if (columnsIndex > 0)
{
sbUpdateColumns.Append(", ");
}
sbUpdateColumns.AppendFormat("T.{0} = Tmp.{0}", updateColumn);
columnsIndex++;
}
}
else
{
//更新全部字段
for (var i = 0; i < dt.Columns.Count; i++)
{
var colname = dt.Columns[i];
if (colname.ColumnName != onRelations)
{
if (columnsIndex > 0)
{
sbUpdateColumns.Append(", ");
}
sbUpdateColumns.AppendFormat("T.{0} = Tmp.{0}", colname.ColumnName);
columnsIndex++;
}
}
}

string sbOnRelation = string.Format("T.{0} = Tmp.{1}", onRelations, onRelations);
// var tempTableName = @"#Temp" + destinationTableName;

 

var sbDelete = string.Format("delete from {0}",tempName);
var updatesql = string.Format(" UPDATE T SET {0} FROM {1} T , {2} Tmp where {3}; ", sbUpdateColumns.ToString(), destinationTableName, tempName, sbOnRelation.ToString());

if (tempName==null)
{
tempName = @"#Temp" + destinationTableName;
sbDelete = string.Format(" select {0} into {1} from {2} where 1=2 ", sbQueryColumns.ToString(), tempName, destinationTableName);
updatesql = string.Format(" UPDATE T SET {0} FROM {1} T , {2} Tmp where {3}; DROP TABLE {2} ", sbUpdateColumns.ToString(), destinationTableName, tempName, sbOnRelation.ToString());

}
int countQuery = 0;//批量修改影响的行数
using (SqlConnection conns = new SqlConnection(conn))
{
conns.Open();
using (SqlCommand command = new SqlCommand("", conns))
{

try
{
command.CommandText = sbDelete;//先清空数据库
int a = command.ExecuteNonQuery();
if (a != 0)
{
return -2;
}
else {
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conns))
{
int fg = dt.Rows.Count;
bulkcopy.BulkCopyTimeout = 1000;
bulkcopy.DestinationTableName = tempName;
if (mappingColumn != null && mappingColumn.Count > 0)
{
for (int i = 0; i < mappingColumn.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (mappingColumn.ElementAt(i).Key == dt.Columns[j].ColumnName)
{
bulkcopy.ColumnMappings.Add(mappingColumn.ElementAt(i).Key, mappingColumn.ElementAt(i).Value);
}
}
}
}
//bulkcopy.ColumnMappings.Add("物料id", "MaterialId");
//bulkcopy.ColumnMappings.Add("目录价", "BasePrice");
//bulkcopy.ColumnMappings.Add("目录价执行日期", "BasePriceExDate");
//bulkcopy.ColumnMappings.Add("目录价更新日期", "BasePriceUpDate");
//foreach (DataColumn dataColumn in dt.Columns)
//{
// bulkcopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);
//}
bulkcopy.WriteToServer(dt);
bulkcopy.Close();
command.CommandTimeout = 1000;
command.CommandText = updatesql;
countQuery = command.ExecuteNonQuery();
return countQuery < 0 ? -3 : countQuery;
}

}

}
catch (Exception ex)
{

Console.WriteLine("BatchUpdate:{0}表失败,原因:{1}", destinationTableName, ex.Message + ex.StackTrace);

return -4;

}
finally
{
if (conns.State == ConnectionState.Open)
{
conns.Close();

}
}


}
}

}
#endregion