/// <summary>
///
/// </summary>
/// <param name="context"></param>
/// <param name="collection"></param>
/// <param name="options"></param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
int IBulkOperation.CommitTransaction <TEntity>(IDbContextWrapper context, IEnumerable <TEntity> collection, BulkOptions options)
{
var tmpTableName = context.EntityMapping.RandomTableName();
var entityList = collection.ToList();
if (!entityList.Any())
{
return(entityList.Count);
}
try
{
//Create temporary table.
context.ExecuteSqlCommand(context.EntityMapping.CreateTempTable(tmpTableName, OperationType.Update, options));
//Bulk inset data to temporary temporary table.
context.BulkInsertToTable(entityList, tmpTableName, OperationType.Update, options);
//Copy data from temporary table to destination table.
var affectedRows = context.ExecuteSqlCommand(context.BuildMergeCommand(tmpTableName, OperationType.Update, options));
//Commit if internal transaction exists.
context.Commit();
return(affectedRows);
}
catch (Exception)
{
//Rollback if internal transaction exists.
context.Rollback();
throw;
}
}
/// <summary>
/// </summary>
/// <param name="context"></param>
/// <param name="collection"></param>
/// <param name="options"></param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
int IBulkOperation.CommitTransaction <TEntity>(IDbContextWrapper context, IEnumerable <TEntity> collection, BulkOptions options)
{
var tmpTableName = context.EntityMapping.RandomTableName();
var entityList = collection.ToList();
if (!entityList.Any())
{
return(entityList.Count);
}
try
{
//Create temporary table with only the primary keys.
context.ExecuteSqlCommand(context.EntityMapping.CreateTempTable(tmpTableName, OperationType.Delete));
//Bulk inset data to temporary table.
context.BulkInsertToTable(entityList, tmpTableName, OperationType.Delete);
//Merge delete items from the target table that matches ids from the temporary table.
var affectedRows = context.ExecuteSqlCommand(context.BuildDeleteCommand(tmpTableName));
//Commit if internal transaction exists.
context.Commit();
return(affectedRows);
}
catch (Exception)
{
//Rollback if internal transaction exists.
context.Rollback();
throw;
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="context"></param>
/// <param name="collection"></param>
/// <param name="options"></param>
/// <returns></returns>
int IBulkOperation.CommitTransaction <TEntity>(IDbContextWrapper context, IEnumerable <TEntity> collection,
BulkOptions options)
{
var entityList = collection.ToList();
if (!entityList.Any())
{
return(entityList.Count);
}
try
{
//Return generated IDs for bulk inserted elements.
if (options.HasFlag(BulkOptions.OutputIdentity))
{
var tmpTableName = context.EntityMapping.RandomTableName();
//Create temporary table.
context.ExecuteSqlCommand(context.EntityMapping.CreateTempTable(tmpTableName, OperationType.Insert, options));
//Bulk inset data to temporary temporary table.
context.BulkInsertToTable(entityList, tmpTableName, OperationType.Insert, options);
var tmpOutputTableName = context.EntityMapping.RandomTableName();
//Copy data from temporary table to destination table with ID output to another temporary table.
var commandText = context.EntityMapping.GetInsertIntoStagingTableCmd(tmpOutputTableName,
tmpTableName, context.EntityMapping.Pks.First().ColumnName, OperationType.Insert, options);
context.ExecuteSqlCommand(commandText);
//Load generated IDs from temporary output table into the entities.
context.LoadFromTmpOutputTable(tmpOutputTableName, context.EntityMapping.Pks.First(), entityList);
}
else
{
//Bulk inset data to temporary destination table.
context.BulkInsertToTable(entityList, context.EntityMapping.FullTableName, OperationType.Insert, options);
}
//Commit if internal transaction exists.
context.Commit();
return(entityList.Count);
}
catch (Exception)
{
//Rollback if internal transaction exists.
context.Rollback();
throw;
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="context"></param>
/// <param name="tmpOutputTableName"></param>
/// <param name="propertyMapping"></param>
/// <param name="items"></param>
internal static void LoadFromTmpOutputTable <TEntity>(this IDbContextWrapper context, string tmpOutputTableName,
IPropertyMapping propertyMapping, IList <TEntity> items)
{
var command = $"SELECT {propertyMapping.ColumnName} FROM {tmpOutputTableName} ORDER BY {propertyMapping.ColumnName};";
var identities = context.SqlQuery <int>(command).ToList();
foreach (var result in identities)
{
var index = identities.IndexOf(result);
var property = items[index].GetType().GetProperty(propertyMapping.PropertyName);
if (property != null && property.CanWrite)
{
property.SetValue(items[index], result, null);
}
else
{
throw new Exception();
}
}
command = GetDropTableCommand(tmpOutputTableName);
context.ExecuteSqlCommand(command);
}
/// <summary>
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="context"></param>
/// <param name="outputTableName"></param>
/// <param name="propertyMappings"></param>
/// <param name="items"></param>
internal static void LoadFromOutputTable <TEntity>(this IDbContextWrapper context, string outputTableName,
IEnumerable <IPropertyMapping> propertyMappings, IList <TEntity> items)
{
var mappings = propertyMappings as IList <IPropertyMapping> ?? propertyMappings.ToList();
var columnNames = mappings.Select(property => property.ColumnName);
var command = $"SELECT {Identity}, {string.Join(", ", columnNames)} FROM {outputTableName}";
using (var reader = context.SqlQuery(command))
{
while (reader.Read())
{
var item = items.ElementAt((int)reader[Identity]);
foreach (var propertyMapping in mappings)
{
var propertyInfo = item.GetType().GetProperty(propertyMapping.PropertyName);
if (propertyInfo != null && propertyInfo.CanWrite)
{
propertyInfo.SetValue(item, reader[propertyMapping.ColumnName], null);
}
else
{
throw new Exception();
}
}
}
}
command = GetDropTableCommand(outputTableName);
context.ExecuteSqlCommand(command);
}
/// <summary>
/// </summary>
/// <param name="context"></param>
/// <param name="collection"></param>
/// <param name="operation"></param>
/// <param name="options"></param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
internal static int CommitTransaction <TEntity>(this IDbContextWrapper context, IEnumerable <TEntity> collection, Operation operation,
BulkOptions options = BulkOptions.Default) where TEntity : class
{
var stagingTableName = context.EntityMapping.RandomTableName();
var willOutputGeneratedValues = context.EntityMapping.WillOutputGeneratedValues(options);
var entityList = collection.ToList();
if (!entityList.Any())
{
return(entityList.Count);
}
try
{
var outputTableName = willOutputGeneratedValues
? context.EntityMapping.RandomTableName()
: null;
var generatedColumns = willOutputGeneratedValues
? context.EntityMapping.GetPropertiesByOptions(options).ToList()
: null;
//Create temporary table.
var stagingTableCommand = context.EntityMapping
.BuildStagingTableCommand(stagingTableName, operation, options);
if (string.IsNullOrEmpty(stagingTableCommand))
{
context.Rollback();
return(SqlHelper.NoRowsAffected);
}
context.ExecuteSqlCommand(stagingTableCommand);
//Bulk inset data to temporary staging table.
context.BulkInsertToTable(entityList, stagingTableName, operation, options);
if (willOutputGeneratedValues)
{
context.ExecuteSqlCommand(SqlHelper.BuildOutputTableCommand(outputTableName,
context.EntityMapping, generatedColumns));
}
//Copy data from temporary table to destination table.
var mergeCommand = context.BuildMergeCommand(stagingTableName, operation);
if (willOutputGeneratedValues)
{
mergeCommand += SqlHelper.BuildMergeOutputSet(outputTableName, generatedColumns);
}
mergeCommand += SqlHelper.GetDropTableCommand(stagingTableName);
var affectedRows = context.ExecuteSqlCommand(mergeCommand);
if (willOutputGeneratedValues)
{
//Load generated values from temporary output table into the entities.
context.LoadFromOutputTable(outputTableName, generatedColumns, entityList);
}
//Commit if internal transaction exists.
context.Commit();
return(affectedRows);
}
catch (Exception)
{
//Rollback if internal transaction exists.
context.Rollback();
throw;
}
}
