public void UpdateItems <T>(IEnumerable <T> items, string schema, string tableName, IList <ColumnMapping> properties, DbConnection storeConnection, int?batchSize, UpdateSpecification <T> updateSpecification)
{
var tempTableName = "#temp_" + tableName + "_" + DateTime.Now.Ticks;
var columnsToUpdate = updateSpecification.Properties.Select(p => p.GetPropertyName()).ToDictionary(x => x);
var filtered = properties.Where(p => columnsToUpdate.ContainsKey(p.NameOnObject) || p.IsPrimaryKey).ToList();
var columns = filtered.Select(c => "[" + c.NameInDatabase + "] " + c.DataType);
var pkConstraint = string.Join(", ", properties.Where(p => p.IsPrimaryKey).Select(c => "[" + c.NameInDatabase + "]"));
var str = string.Format("CREATE TABLE {0}.[{1}]({2}, PRIMARY KEY ({3}))", schema, tempTableName, string.Join(", ", columns), pkConstraint);
var con = storeConnection as SqlConnection;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
var setters = string.Join(",", filtered.Where(c => !c.IsPrimaryKey).Select(c => "[" + c.NameInDatabase + "] = TEMP.[" + c.NameInDatabase + "]"));
var pks = properties.Where(p => p.IsPrimaryKey).Select(x => "ORIG.[" + x.NameInDatabase + "] = TEMP.[" + x.NameInDatabase + "]");
var filter = string.Join(" and ", pks);
var mergeCommand = string.Format(@"UPDATE [{0}]
SET
{3}
FROM
[{0}] ORIG
INNER JOIN
[{1}] TEMP
ON
{2}", tableName, tempTableName, filter, setters);
var dropCommand = $@"IF Object_id('tempdb..{tempTableName}') IS NOT NULL
BEGIN DROP TABLE {tempTableName} END
ELSE
BEGIN THROW 51000,'Drop temp table {tempTableName} fail.',1; END";
using (var createCommand = new SqlCommand(str, con))
using (var mCommand = new SqlCommand(mergeCommand, con))
using (var dCommand = new SqlCommand(dropCommand, con))
{
createCommand.ExecuteNonQuery();
InsertItems(items, schema, tempTableName, filtered, storeConnection, batchSize);
mCommand.ExecuteNonQuery();
dCommand.ExecuteNonQuery();
}
}
public void UpdateItems <T>(IEnumerable <T> items, string schema, string tableName, IList <ColumnMapping> properties, DbConnection storeConnection, int?batchSize, UpdateSpecification <T> updateSpecification, int?executeTimeout, SqlBulkCopyOptions copyOptions, DbTransaction transaction, DbConnection insertConnection)
{
var tempTableName = "#" + Guid.NewGuid().ToString("N");
var columnsToUpdate = updateSpecification.Properties.Select(p => p.GetPropertyName()).ToDictionary(x => x);
var filtered = properties.Where(p => columnsToUpdate.ContainsKey(p.NameOnObject) || p.IsPrimaryKey).ToList();
var columns = filtered.Select(c => "[" + c.NameInDatabase + "] " + c.DataType);
var pkConstraint = string.Join(", ", properties.Where(p => p.IsPrimaryKey).Select(c => "[" + c.NameInDatabase + "]"));
var str = $"CREATE TABLE [{schema}].[{tempTableName}]({string.Join(", ", columns)}, PRIMARY KEY ({pkConstraint}))";
if (storeConnection.State != System.Data.ConnectionState.Open)
{
storeConnection.Open();
}
var setters = string.Join(",", filtered.Where(c => !c.IsPrimaryKey).Select(c => "ORIG.[" + c.NameInDatabase + "] = TEMP.[" + c.NameInDatabase + "]"));
var pks = properties.Where(p => p.IsPrimaryKey).Select(x => "ORIG.[" + x.NameInDatabase + "] = TEMP.[" + x.NameInDatabase + "]");
var filter = string.Join(" and ", pks);
var mergeCommand = string.Format(@"UPDATE ORIG
SET
{4}
FROM
[{0}].[{1}] ORIG
INNER JOIN
[{0}].[{2}] TEMP
ON
{3}" , schema, tableName, tempTableName, filter, setters);
using (var createCommand = storeConnection.CreateCommand())
using (var mCommand = storeConnection.CreateCommand())
using (var dCommand = storeConnection.CreateCommand())
{
createCommand.CommandText = str;
mCommand.CommandText = mergeCommand;
dCommand.CommandText = $"DROP table [{schema}].[{tempTableName}]";
createCommand.CommandTimeout = executeTimeout ?? 600;
mCommand.CommandTimeout = executeTimeout ?? 600;
dCommand.CommandTimeout = executeTimeout ?? 600;
createCommand.Transaction = transaction;
mCommand.Transaction = transaction;
dCommand.Transaction = transaction;
createCommand.ExecuteNonQuery();
InsertItems(items, schema, tempTableName, filtered, insertConnection, batchSize, executeTimeout, copyOptions, transaction);
mCommand.ExecuteNonQuery();
dCommand.ExecuteNonQuery();
}
}
