public static int BulkUpdate <T>(this DbContext context, IEnumerable <T> entities, BulkUpdateOptions <T> options)
{
int rowsUpdated = 0;
var outputRows = new List <BulkMergeOutputRow <T> >();
var tableMapping = context.GetTableMapping(typeof(T));
var dbConnection = context.GetSqlConnection();
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var transaction = dbConnection.BeginTransaction())
{
try
{
string stagingTableName = GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
string[] columnNames = tableMapping.Columns.Where(o => !o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
string[] storeGeneratedColumnNames = tableMapping.Columns.Where(o => o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
SqlUtil.CloneTable(destinationTableName, stagingTableName, null, dbConnection, transaction);
BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity);
IEnumerable <string> columnstoUpdate = columnNames.Where(o => !options.IgnoreColumnsOnUpdate.GetObjectProperties().Contains(o));
string updateSetExpression = string.Join(",", columnstoUpdate.Select(o => string.Format("t.{0}=s.{0}", o)));
string updateOnExpression = string.Join(" AND ", storeGeneratedColumnNames.Select(o => string.Format("s.{0}=t.{0}", o)));
string updateSql = string.Format("UPDATE t SET {0} FROM {1} AS s JOIN {2} AS t ON {3}; SELECT @@RowCount;",
updateSetExpression, stagingTableName, destinationTableName, updateOnExpression);
rowsUpdated = SqlUtil.ExecuteSql(updateSql, dbConnection, transaction);
SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction);
//ClearEntityStateToUnchanged(context, entities);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
dbConnection.Close();
}
return(rowsUpdated);
}
}
public static int BulkDelete <T>(this DbContext context, IEnumerable <T> entities, BulkDeleteOptions <T> options)
{
int rowsAffected = 0;
var tableMapping = context.GetTableMapping(typeof(T));
Validate(tableMapping);
var dbConnection = context.GetSqlConnection();
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var transaction = dbConnection.BeginTransaction())
{
try
{
string stagingTableName = GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
string[] keyColumnNames = options.DeleteOnCondition != null ? CommonUtil <T> .GetColumns(options.DeleteOnCondition, new[] { "s" })
: tableMapping.Columns.Where(o => o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
SqlUtil.CloneTable(destinationTableName, stagingTableName, keyColumnNames, dbConnection, transaction);
BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, keyColumnNames, SqlBulkCopyOptions.KeepIdentity);
string deleteSql = string.Format("DELETE t FROM {0} s JOIN {1} t ON {2}", stagingTableName, destinationTableName,
CommonUtil <T> .GetJoinConditionSql(options.DeleteOnCondition, keyColumnNames));
rowsAffected = SqlUtil.ExecuteSql(deleteSql, dbConnection, transaction, options.CommandTimeout);
SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction);
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
dbConnection.Close();
}
return(rowsAffected);
}
}
public static int BulkInsert <T>(this DbContext context, IEnumerable <T> entities, BulkInsertOptions <T> options)
{
int rowsAffected = 0;
var tableMapping = context.GetTableMapping(typeof(T));
var dbConnection = context.GetSqlConnection();
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var transaction = dbConnection.BeginTransaction())
{
try
{
string stagingTableName = GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
string[] columnNames = tableMapping.Columns.Where(o => options.KeepIdentity || !o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
string[] storeGeneratedColumnNames = tableMapping.Columns.Where(o => o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
SqlUtil.CloneTable(destinationTableName, stagingTableName, null, dbConnection, transaction, Common.Constants.Guid_ColumnName);
var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity, true);
IEnumerable <string> columnsToInsert = columnNames;
List <string> columnsToOutput = new List <string>();
List <PropertyInfo> propertySetters = new List <PropertyInfo>();
Type entityType = typeof(T);
foreach (var storeGeneratedColumnName in storeGeneratedColumnNames)
{
columnsToOutput.Add(string.Format("inserted.[{0}]", storeGeneratedColumnName));
propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName));
}
string mergeSqlText = string.Format("INSERT INTO {0} ({1}) OUTPUT {2} SELECT {3} FROM {4};",
destinationTableName, SqlUtil.ConvertToColumnString(columnsToInsert), SqlUtil.ConvertToColumnString(columnsToOutput), SqlUtil.ConvertToColumnString(columnsToInsert), stagingTableName);
if (options.KeepIdentity)
{
SqlUtil.ToggleIdentiyInsert(true, destinationTableName, dbConnection, transaction);
}
var bulkQueryResult = context.BulkQuery(mergeSqlText, dbConnection, transaction);
if (options.KeepIdentity)
{
SqlUtil.ToggleIdentiyInsert(false, destinationTableName, dbConnection, transaction);
}
rowsAffected = bulkQueryResult.RowsAffected;
if (options.AutoMapOutputIdentity)
{
if (rowsAffected == entities.Count())
{
var entityIndex = 1;
foreach (var result in bulkQueryResult.Results)
{
var entity = bulkInsertResult.EntityMap[entityIndex];
propertySetters[0].SetValue(entity, result[0]);
entityIndex++;
}
}
}
SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction);
//ClearEntityStateToUnchanged(context, entities);
transaction.Commit();
return(rowsAffected);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
dbConnection.Close();
}
}
}
public static BulkMergeResult <T> BulkMerge <T>(this DbContext context, IEnumerable <T> entities, BulkMergeOptions <T> options)
{
int rowsAffected = 0;
var outputRows = new List <BulkMergeOutputRow <T> >();
var tableMapping = context.GetTableMapping(typeof(T));
var dbConnection = context.GetSqlConnection();
int rowsInserted = 0;
int rowsUpdated = 0;
int rowsDeleted = 0;
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var transaction = dbConnection.BeginTransaction())
{
try
{
string stagingTableName = GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
string[] columnNames = tableMapping.Columns.Where(o => !o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
string[] storeGeneratedColumnNames = tableMapping.Columns.Where(o => o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
SqlUtil.CloneTable(destinationTableName, stagingTableName, null, dbConnection, transaction, Common.Constants.Guid_ColumnName);
var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity, true);
IEnumerable <string> columnsToInsert = columnNames.Where(o => !options.GetIgnoreColumnsOnInsert().Contains(o));
IEnumerable <string> columnstoUpdate = columnNames.Where(o => !options.GetIgnoreColumnsOnUpdate().Contains(o)).Select(o => string.Format("t.{0}=s.{0}", o));
List <string> columnsToOutput = new List <string> {
"$Action", string.Format("{0}.{1}", "s", Constants.Guid_ColumnName)
};
List <PropertyInfo> propertySetters = new List <PropertyInfo>();
Type entityType = typeof(T);
foreach (var storeGeneratedColumnName in storeGeneratedColumnNames)
{
//columnsToOutput.Add(string.Format("deleted.[{0}]", storeGeneratedColumnName)); Not Yet Supported
columnsToOutput.Add(string.Format("inserted.[{0}]", storeGeneratedColumnName));
var storedGeneratedColumn = tableMapping.Columns.First(o => o.Column.Name == storeGeneratedColumnName);
propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName));
}
string mergeSqlText = string.Format("MERGE {0} t USING {1} s ON ({2}) WHEN NOT MATCHED BY TARGET THEN INSERT ({3}) VALUES ({3}) WHEN MATCHED THEN UPDATE SET {4} OUTPUT {5};",
destinationTableName, stagingTableName, options.MergeOnCondition.ToSqlPredicate("s", "t"),
SqlUtil.ConvertToColumnString(columnsToInsert),
SqlUtil.ConvertToColumnString(columnstoUpdate),
SqlUtil.ConvertToColumnString(columnsToOutput)
);
var bulkQueryResult = context.BulkQuery(mergeSqlText, dbConnection, transaction);
rowsAffected = bulkQueryResult.RowsAffected;
//var entitiesEnumerator = entities.GetEnumerator();
//entitiesEnumerator.MoveNext();
foreach (var result in bulkQueryResult.Results)
{
string action = (string)result[0];
int id = (int)result[1];
var entity = bulkInsertResult.EntityMap[id];
outputRows.Add(new BulkMergeOutputRow <T>(action, entity));
if (options.AutoMapOutputIdentity && entity != null)
{
for (int i = 2; i < result.Length; i++)
{
propertySetters[0].SetValue(entity, result[i]);
}
}
if (action == SqlMergeAction.Insert)
{
rowsInserted++;
}
else if (action == SqlMergeAction.Update)
{
rowsUpdated++;
}
else if (action == SqlMergeAction.Detete)
{
rowsDeleted++;
}
}
SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction);
//ClearEntityStateToUnchanged(context, entities);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
dbConnection.Close();
}
return(new BulkMergeResult <T>
{
Output = outputRows,
RowsAffected = rowsAffected,
RowsDeleted = rowsDeleted,
RowsInserted = rowsInserted,
RowsUpdated = rowsUpdated,
});
}
}
public static int BulkInsert <T>(this DbContext context, IEnumerable <T> entities, BulkInsertOptions <T> options)
{
int rowsAffected = 0;
var tableMapping = context.GetTableMapping(typeof(T));
var dbConnection = context.GetSqlConnection();
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
using (var transaction = dbConnection.BeginTransaction())
{
try
{
string stagingTableName = GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
string[] columnNames = tableMapping.Columns.Where(o => options.KeepIdentity || !o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
string[] storeGeneratedColumnNames = tableMapping.Columns.Where(o => o.Column.IsStoreGeneratedIdentity).Select(o => o.Column.Name).ToArray();
SqlUtil.CloneTable(destinationTableName, stagingTableName, null, dbConnection, transaction, Common.Constants.InternalId_ColumnName);
var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity, true);
IEnumerable <string> columnsToInsert = columnNames;
List <string> columnsToOutput = new List <string> {
"$Action", string.Format("{0}.{1}", "s", Constants.InternalId_ColumnName)
};
List <PropertyInfo> propertySetters = new List <PropertyInfo>();
Type entityType = typeof(T);
foreach (var storeGeneratedColumnName in storeGeneratedColumnNames)
{
columnsToOutput.Add(string.Format("inserted.[{0}]", storeGeneratedColumnName));
propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName));
}
string insertSqlText = string.Format("MERGE {0} t USING {1} s ON {2} WHEN NOT MATCHED THEN INSERT ({3}) VALUES ({3}){4};",
destinationTableName,
stagingTableName,
options.InsertIfNotExists ? CommonUtil <T> .GetJoinConditionSql(options.InsertOnCondition, storeGeneratedColumnNames, "t", "s") : "1=2",
SqlUtil.ConvertToColumnString(columnsToInsert),
columnsToOutput.Count > 0 ? " OUTPUT " + SqlUtil.ConvertToColumnString(columnsToOutput) : "");
if (options.KeepIdentity)
{
SqlUtil.ToggleIdentityInsert(true, destinationTableName, dbConnection, transaction);
}
var bulkQueryResult = context.BulkQuery(insertSqlText, dbConnection, transaction, options);
if (options.KeepIdentity)
{
SqlUtil.ToggleIdentityInsert(false, destinationTableName, dbConnection, transaction);
}
rowsAffected = bulkQueryResult.RowsAffected;
if (options.AutoMapOutputIdentity)
{
if (rowsAffected == entities.Count())
{
//var entityIndex = 1;
foreach (var result in bulkQueryResult.Results)
{
int entityId = (int)result[1];
var entity = bulkInsertResult.EntityMap[entityId];
for (int i = 2; i < columnsToOutput.Count; i++)
{
propertySetters[2 - i].SetValue(entity, result[i]);
}
}
}
}
SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction);
//ClearEntityStateToUnchanged(context, entities);
transaction.Commit();
return(rowsAffected);
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
dbConnection.Close();
}
}
}
