public void BatchUpdate <T>(List <T> dataList, params Expression <Func <T, string> >[] predicates)
{
var fields = ParseColumnPredicates(predicates);
StringBuilder updateFields = new StringBuilder();
for (int i = 0; i < fields.Count; i++)
{
var field = fields[i];
updateFields.Append(i == 0 ? $"T.{field}=Temp.{field}" : $",T.{field}=Temp.{field}");
}
var type = typeof(T);
var id = GetProperties(type);
var innerJoin = $"T.{id}=Temp.{id}";
var tempTableName = $"#TmpTable{type.Name}";
var dataTableName = BulkCopyRepositoryExtension.GetTableName(type);
var sqlConnection = (SqlConnection)_unit.Connection;
var sqlTransaction = (SqlTransaction)_unit.Transaction;
var sqlCommand = (SqlCommand)_unit.Command;
sqlCommand.CommandText = $"SELECT * INTO {tempTableName} FROM {dataTableName} WHERE 1 = 2";
sqlCommand.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction))
{
bulkCopy.DestinationTableName = tempTableName;
using (var reader = new ObjectReader(type, dataList, BulkCopyRepositoryExtension.GetFields(type)))
{
bulkCopy.WriteToServer(reader);
}
}
sqlCommand.CommandText = $"UPDATE T SET {updateFields} FROM {dataTableName} T INNER JOIN {tempTableName} Temp ON {innerJoin}; DROP TABLE {tempTableName};";
sqlCommand.ExecuteNonQuery();
}
public void BatchInsert <T>(List <T> dataList)
{
var sqlConnection = (SqlConnection)_unit.Connection;
var sqlTransaction = (SqlTransaction)_unit.Transaction;
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction))
{
var type = typeof(T);
bulkCopy.DestinationTableName = BulkCopyRepositoryExtension.GetTableName(type);
using (var reader = new ObjectReader(type, dataList, BulkCopyRepositoryExtension.GetFields(type)))
{
bulkCopy.WriteToServer(reader);
}
}
}
public void BatchUpdate <T>(List <T> dataList, string field, string where)
{
var type = typeof(T);
var tempTableName = "#TmpTable";
var dataTableName = BulkCopyRepositoryExtension.GetTableName(type);
var sqlConnection = (SqlConnection)_unit.Connection;
var sqlTransaction = (SqlTransaction)_unit.Transaction;
var sqlCommand = (SqlCommand)_unit.Command;
sqlCommand.CommandText = $"SELECT * INTO {tempTableName} FROM {dataTableName} WHERE 1 = 2";;
sqlCommand.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction))
{
bulkCopy.DestinationTableName = tempTableName;
using (var reader = new ObjectReader(type, dataList, BulkCopyRepositoryExtension.GetFields(type)))
{
bulkCopy.WriteToServer(reader);
}
}
sqlCommand.CommandText = $"UPDATE T SET {field} FROM {dataTableName} T INNER JOIN {tempTableName} Temp ON {where}; DROP TABLE {tempTableName};";
sqlCommand.ExecuteNonQuery();
}
public void BatchDelete <T>(List <T> idList)
{
var type = typeof(T);
var id = GetProperties(type);
var innerJoin = $"a.{id}=b.{id}";
var tempTableName = $"#TmpTable{type.Name}";
var dataTableName = BulkCopyRepositoryExtension.GetTableName(type);
var sqlConnection = (SqlConnection)_unit.Connection;
var sqlTransaction = (SqlTransaction)_unit.Transaction;
var sqlCommand = (SqlCommand)_unit.Command;
sqlCommand.CommandText = $"SELECT * INTO {tempTableName} FROM {dataTableName} WHERE 1 = 2";
sqlCommand.ExecuteNonQuery();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction))
{
bulkCopy.DestinationTableName = tempTableName;
using (var reader = new ObjectReader(type, idList, BulkCopyRepositoryExtension.GetFields(type)))
{
bulkCopy.WriteToServer(reader);
}
}
sqlCommand.CommandText = $"DELETE a FROM {dataTableName} AS a INNER JOIN {tempTableName} AS b ON {innerJoin}; DROP TABLE {tempTableName};";
sqlCommand.ExecuteNonQuery();
}
