private static void WriteToServer <T>(BulkOperationParam <T> bulkOperationParam, BulkOperationConfig config)
{
using (var bulkCopy = new SqlBulkCopy(bulkOperationParam.Connection as SqlConnection, SqlBulkCopyOptions.Default, bulkOperationParam.Transaction))
{
bulkCopy.BulkCopyTimeout = bulkOperationParam.BulkCopyTimeout;
bulkCopy.BatchSize = bulkOperationParam.BatchSize;
bulkCopy.DestinationTableName = config.TempTable;
bulkCopy.WriteToServer(ToDataTable(bulkOperationParam.Data, config.TempTable, config.AllProperties, bulkOperationParam.ExtraColumns).CreateDataReader());
}
}
private static BulkOperationConfig GetBulkOperationConfig <T>(BulkOperationParam <T> bulkOperationParam, string tempTableName)
{
var type = typeof(T);
var bulkOperationConfig = new BulkOperationConfig
{
DestinationTableName = bulkOperationParam.TableName ?? GetTableName(type),
AllProperties = PropertiesCache.TypePropertiesCache(type)
};
if (bulkOperationParam.ExtraColumns != null)
{
var rowVersionPropertyInfo = bulkOperationConfig.AllProperties.FirstOrDefault(p => p.Name == "RowVersion");
if (rowVersionPropertyInfo != null)
{
bulkOperationConfig.AllProperties.Remove(rowVersionPropertyInfo);
}
foreach (var extraColumnsKey in bulkOperationParam.ExtraColumns.Keys)
{
bulkOperationConfig.AllProperties.Add(new CustomPropertyInfo(extraColumnsKey, typeof(object)));
}
if (rowVersionPropertyInfo != null)
{
bulkOperationConfig.AllProperties.Add(rowVersionPropertyInfo);
}
}
bulkOperationConfig.AllPropertiesString = GetColumnsStringSqlServer(bulkOperationConfig.AllProperties);
bulkOperationConfig.TempTable = $"##{bulkOperationConfig.DestinationTableName.Replace(".", "_").Replace("[", string.Empty).Replace("]", string.Empty)}{tempTableName}";
var columnNames = bulkOperationConfig.AllPropertiesString.Replace(", [RowVersion]", "");
var command = new SqlCommand(
$@"SELECT TOP 0 {columnNames} INTO {bulkOperationConfig.TempTable} FROM {bulkOperationConfig.DestinationTableName} target WITH(NOLOCK);",
bulkOperationParam.Connection as SqlConnection);
if (bulkOperationParam.Connection.State != ConnectionState.Open)
{
bulkOperationParam.Connection.Open();
}
command.ExecuteNonQuery();
var command2 = new SqlCommand($@"ALTER TABLE {bulkOperationConfig.TempTable}
ADD RowVersion binary(8) NULL;", bulkOperationParam.Connection as SqlConnection);
command2.ExecuteNonQuery();
return(bulkOperationConfig);
}
private static int BulkUpdate <T>(BulkOperationParam <T> bulkOperationParam, params Expression <Func <T, dynamic> >[] updateTargetProperties)
{
var tempTableName = nameof(BulkUpdate);
var bulkOperationConfig = GetBulkOperationConfig(bulkOperationParam, tempTableName);
WriteToServer(bulkOperationParam, bulkOperationConfig);
var columnsMustBeUpdated = new StringBuilder();
foreach (var prop in updateTargetProperties)
{
MemberExpression memberExpression;
if (prop.Body.NodeType == ExpressionType.Convert || prop.Body.NodeType == ExpressionType.ConvertChecked)
{
var unaryExpression = prop.Body as UnaryExpression;
memberExpression = unaryExpression?.Operand as MemberExpression;
}
else
{
memberExpression = (prop.Body as MemberExpression);
}
columnsMustBeUpdated.Append($"{memberExpression.Member.Name}=TT.{memberExpression.Member.Name},");
}
var query = $@" UPDATE {bulkOperationConfig.DestinationTableName} SET {columnsMustBeUpdated.ToString().Remove(columnsMustBeUpdated.ToString().Length - 1)}
FROM {bulkOperationConfig.DestinationTableName} T
INNER JOIN {bulkOperationConfig.TempTable} TT ON T.Id=TT.Id AND T.RowVersion=TT.RowVersion;";
var numberOfRowsAffected = ExecuteQuery(bulkOperationParam.Connection, query);
if (numberOfRowsAffected != bulkOperationParam.Data.Count())
{
DropTable(bulkOperationParam.Connection, bulkOperationConfig.TempTable);
throw new Exception("Number of affected rows are not expected, maybe CONCURRENCY occurred!");
}
DropTable(bulkOperationParam.Connection, bulkOperationConfig.TempTable);
return(numberOfRowsAffected);
}
public static void Main(string[] args)
{
using (var connection = new SqlConnection("Data Source=192.168.10.20;Initial Catalog=KF_Sales;User ID=sa;Password=123"))
//using (var connection = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=KF_Sales;Trusted_Connection=True;"))
{
var selectCommand = new SqlCommand("SELECT * FROM [SalesManagement].[Item]", connection);
connection.Open();
var dataReader = selectCommand.ExecuteReader();
var items = new List <Item>();
while (dataReader.Read())
{
var item = new Item
{
Id = dataReader.GetGuid(0),
RowVersion = dataReader.GetFieldValue <byte[]>(1),
CreatedDate = !dataReader.IsDBNull(2) ? dataReader.GetDateTime(2) : DateTime.Now,
LastChangedDate = !dataReader.IsDBNull(3) ? dataReader.GetDateTime(3) : DateTime.Now,
CreatedBy = !dataReader.IsDBNull(4) ? dataReader.GetString(4) : String.Empty,
LastChangedBy = !dataReader.IsDBNull(5) ? dataReader.GetString(5) : String.Empty,
Title = !dataReader.IsDBNull(6) ? dataReader.GetString(6) : String.Empty,
Description = !dataReader.IsDBNull(7) ? dataReader.GetString(7) : String.Empty,
GoodsId = !dataReader.IsDBNull(8) ? dataReader.GetGuid(8) : Guid.Empty,
};
items.Add(item);
}
dataReader.Close();
items.ForEach(i => i.Title += "1");
var updateTargetProperties = new Expression <Func <Item, object> >[] { p => p.Title };
var bulkOperationParam = new BulkOperationParam <Item>(connection, items);
var numberOfRowsAffected = BulkUpdate(bulkOperationParam, updateTargetProperties);
Console.WriteLine($"NumberOfRowsAffected:\t{numberOfRowsAffected}");
Console.ReadKey();
}
}