/// <summary>
/// 批量更新数据,列名不区分大小写
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">实体集合</param>
/// <param name="keyColumns">主键集合非空</param>
/// <param name="columns">需要更新哪些列，为null是更新所有列</param>
/// <param name="onRelations">跟新条件，默认是主键，多个条件关系是并列的and</param>
public static int BatchUpdate <T>(List <T> list, List <string> keyColumns, IEnumerable <string> columns, Dictionary <string, string> onRelations = null, string tblName = "")
{
int result = 0;
if (list.Any())
{
Type type = typeof(T);
DataTable pdco = null;
var sbUpdateColumns = new StringBuilder();
var sbOnRelation = new StringBuilder();
if (string.IsNullOrEmpty(tblName))
{
pdco = ClassHelper.ForType(type, keyColumns);
}
else
{
pdco = ClassHelper.ForType(type, keyColumns, tblName);
}
var dt = new DataTable();
var tableName = pdco.TableName;
var columnsIndex = 0;
var onRelationIndex = 0;
var tempTableName = string.Empty;
//构建需要更新的列
if (columns == null)//更新除主键之外的列
{
foreach (DataColumn i in pdco.Columns)
{
if (!pdco.PrimaryKey[0].ColumnName.Equals(i.ColumnName, StringComparison.OrdinalIgnoreCase))
{
// Build the sql
if (columnsIndex > 0)
{
sbUpdateColumns.Append(", ");
}
sbUpdateColumns.AppendFormat("T.{0} = Temp.{0}", i.ColumnName);
columnsIndex++;
}
}
}
else//更新除主键之外的指定列
{
foreach (var colname in columns)
{
var pc = pdco.Columns[colname];
// Build the sql
if (columnsIndex > 0)
{
sbUpdateColumns.Append(", ");
}
sbUpdateColumns.AppendFormat("T.{0} = Temp.{0}", colname);
columnsIndex++;
}
}
//构建更新条件
if (onRelations == null)
{
sbOnRelation.AppendFormat("T.{0} = Temp.{0}", pdco.PrimaryKey[0].ColumnName);
}
else
{
foreach (var onRelation in onRelations)
{
if (onRelationIndex > 0)
{
sbOnRelation.Append(" AND ");
}
sbOnRelation.AppendFormat("T.{0} = Temp.{1}", onRelation.Key, onRelation.Value);
onRelationIndex++;
}
}
using (SqlConnection conn = new SqlConnection(constr))
{
using (SqlCommand command = new SqlCommand("", conn))
{
try
{
conn.Open();
//tempTableName = $"Temp{DateTime.Now.ToString("yyyMMddHHmmss")}{new Random().Next(1,10)}";
tempTableName = $"Temp{Guid.NewGuid().ToString("N")}";
//构建临时表
command.CommandText = $"SELECT * INTO {tempTableName} FROM {tableName} WHERE 1 = 2;";
command.ExecuteNonQuery();
//list.ToDataTable(a => new object[] { list });
//插入临时表
dt = list.ToDataTable(a => new object[] { list });
//dt = ConvertToDataTable(list);
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(constr, SqlBulkCopyOptions.KeepIdentity))
{
bulkCopy.DestinationTableName = tempTableName;
bulkCopy.BatchSize = list.Count;
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
}
}
//从临时表更新到原表，并删除临时表
command.CommandTimeout = 300;
command.CommandText = string.Format($"UPDATE T SET {sbUpdateColumns.ToString()} FROM {pdco.TableName} T INNER JOIN {tempTableName} Temp ON {sbOnRelation.ToString()};DROP TABLE {tempTableName};");
result = command.ExecuteNonQuery();
}
catch (Exception ex)
{
if (!string.IsNullOrEmpty(tempTableName))
{
command.CommandText = string.Format($"DROP TABLE {tempTableName};");
result = command.ExecuteNonQuery();
}
throw ex;
}
finally
{
conn.Close();
}
}
}
}
return(result);
}