protected internal virtual BulkOptions SetDefaults(BulkOptions defaultOptions)
{
if (!FieldsSelector.HasValue)
{
FieldsSelector = defaultOptions.FieldsSelector;
}
if (!CaseSensitiveFieldsMatching.HasValue)
{
CaseSensitiveFieldsMatching = defaultOptions.CaseSensitiveFieldsMatching;
}
if (!CreateTable.HasValue)
{
CreateTable = defaultOptions.CreateTable;
}
if (!IgnoreDataReaderSchemaTable.HasValue)
{
IgnoreDataReaderSchemaTable = defaultOptions.IgnoreDataReaderSchemaTable;
}
if (!CheckTableIfNotExistsBeforeCreation.HasValue)
{
CheckTableIfNotExistsBeforeCreation = defaultOptions.CheckTableIfNotExistsBeforeCreation;
}
return(this);
}
public EntityMetadataColumnSetupProvider(IEntityType entity, EntityState state, BulkOptions bulkOptions)
{
_bulkOptions = bulkOptions;
SchemaName = entity.GetSchema();
TableName = entity.GetTableName();
var properties = entity.GetProperties();
if (state == EntityState.Deleted)
{
properties = properties.Where(p => p.IsPrimaryKey());
}
if (_bulkOptions.ShadowPropertyAccessor == null)
{
properties = properties.Where(p => entity.FindDiscriminatorProperty() == p || !p.IsShadowProperty());
}
var columns = properties.Select((p, i) => CreateColumnSetup(entity, p, i, state, _bulkOptions)).Where(p => p.ValueDirection != ValueDirection.None);
if (!bulkOptions.PropagateValues)
{
columns = columns.Where(p => p.ValueDirection.HasFlag(ValueDirection.Write));
}
_columns = columns.ToImmutableList();
}
protected override BulkOptions SetDefaults(BulkOptions defaultOptions)
{
base.SetDefaults(defaultOptions);
if (!(defaultOptions is SqlBulkOptions opt))
{
return(this);
}
if (!BatchSize.HasValue)
{
BatchSize = opt.BatchSize;
}
if (!BulkCopyTimeout.HasValue)
{
BulkCopyTimeout = opt.BulkCopyTimeout;
}
if (!SqlBulkCopyOptions.HasValue)
{
SqlBulkCopyOptions = opt.SqlBulkCopyOptions;
}
if (!EnableStreaming.HasValue)
{
EnableStreaming = opt.EnableStreaming;
}
if (ColumnDefinitionOptions == null)
{
ColumnDefinitionOptions = opt.ColumnDefinitionOptions?.Clone();
}
return(this);
}
/// <summary>
/// </summary>
/// <param name="mapping"></param>
/// <param name="tableName"></param>
/// <param name="operationType"></param>
/// <param name="options"></param>
/// <returns></returns>
internal static string BuildStagingTableCommand(this IEntityMapping mapping, string tableName, Operation operationType,
BulkOptions options)
{
var paramList = mapping
.GetPropertiesByOperation(operationType)
.ToList();
if (paramList.All(s => s.IsPk && s.IsDbGenerated) &&
operationType == Operation.Update)
{
return(null);
}
var paramColumns = paramList
.Select(column => $"{Source}.[{column.ColumnName}]")
.ToList();
if (mapping.WillOutputGeneratedValues(options))
{
paramColumns.Add($"1 as [{Identity}]");
}
var paramListConcatenated = string.Join(", ", paramColumns);
return($"SELECT TOP 0 {paramListConcatenated} INTO {tableName} FROM {mapping.FullTableName} AS A " +
$"LEFT JOIN {mapping.FullTableName} AS {Source} ON 1 = 2");
}
protected BulkOptions(BulkOptions bulkOptions)
{
this.FieldsSelector = bulkOptions.FieldsSelector;
this.CaseSensitiveFieldsMatching = bulkOptions.CaseSensitiveFieldsMatching;
this.CreateTable = bulkOptions.CreateTable;
this.IgnoreDataReaderSchemaTable = bulkOptions.IgnoreDataReaderSchemaTable;
this.CheckTableIfNotExistsBeforeCreation = bulkOptions.CheckTableIfNotExistsBeforeCreation;
}
public void BulkInsert(IDbConnection dbConnection, DataTable dataTable, BulkOptions bulkOptions)
{
NpgsqlConnection connection = (NpgsqlConnection)dbConnection;
using (NpgsqlBinaryImporter writer = connection.BeginBinaryImport(BuildCopyFromCommand(dataTable)))
{
foreach (DataRow dataRow in dataTable.Rows)
{
writer.WriteRow(dataRow.ItemArray);
}
}
}
/// <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;
}
}
public void BulkInsert(IDbConnection dbConnection, DataTable dataTable, BulkOptions bulkOptions)
{
SqlConnection conn = (SqlConnection)dbConnection;
SqlBulkCopyOptions options = SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.UseInternalTransaction;
SqlTransaction transaction = null;
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn, options, transaction);
sqlBulkCopy.DestinationTableName = dataTable.TableName;
sqlBulkCopy.BulkCopyTimeout = bulkOptions.TimeoutInSeconds;
foreach (DataColumn column in dataTable.Columns)
{
sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
sqlBulkCopy.WriteToServer(dataTable);
}
protected internal SqlBulkOptions(BulkOptions bulkOptions)
: base(bulkOptions)
{
if (bulkOptions is SqlBulkOptions opt)
{
this.BatchSize = opt.BatchSize;
this.BulkCopyTimeout = opt.BulkCopyTimeout;
this.SqlBulkCopyOptions = opt.SqlBulkCopyOptions;
this.EnableStreaming = opt.EnableStreaming;
this.ColumnDefinitionOptions = opt.ColumnDefinitionOptions;
}
else
{
this.BatchSize = null;
this.BulkCopyTimeout = null;
this.SqlBulkCopyOptions = null;
this.EnableStreaming = null;
this.ColumnDefinitionOptions = null;
}
}
protected override ConnectionContext GetConnectionContext(DbConnection connection, QueryOptions queryOptions, BulkOptions bulkOptions, ReadOptions readOptions)
{
return(new SqlConnectionContext(this, (SqlConnection)connection, queryOptions, bulkOptions, readOptions));
}
/// <summary>
///
/// </summary>
/// <param name="mapping"></param>
/// <param name="tmpOutputTableName"></param>
/// <param name="tmpTableName"></param>
/// <param name="identityColumn"></param>
/// <returns></returns>
internal static string GetInsertIntoStagingTableCmd(this IEntityMapping mapping, string tmpOutputTableName,
string tmpTableName, string identityColumn, OperationType operationType, BulkOptions options)
{
var columns = mapping.Properties
.FilterPropertiesByOperation(operationType)
.Select(propertyMapping => propertyMapping.ColumnName)
.ToList();
var comm = GetOutputCreateTableCmd(tmpOutputTableName, identityColumn)
+ BuildInsertIntoSet(columns, identityColumn, mapping.FullTableName)
+ $"OUTPUT INSERTED.{identityColumn} INTO "
+ tmpOutputTableName + $"([{identityColumn}]) "
+ BuildSelectSet(columns, identityColumn)
+ $" FROM {tmpTableName} AS Source; "
+ GetDropTableCommand(tmpTableName);
return(comm);
}
internal static string BuildMergeCommand(this IDbContextWrapper context, string tmpTableName, OperationType operationType, BulkOptions options)
{
return($"MERGE INTO {context.EntityMapping.FullTableName} WITH (HOLDLOCK) AS Target USING {tmpTableName} AS Source " +
$"{context.EntityMapping.PrimaryKeysComparator()} WHEN MATCHED THEN UPDATE {context.EntityMapping.BuildUpdateSet(operationType, options)}; " +
GetDropTableCommand(tmpTableName));
}
protected internal NpgsqlTransactionContext(ContextProvider contextProvider, NpgsqlTransaction transaction,
QueryOptions queryOptions, BulkOptions bulkOptions, ReadOptions readOptions)
: base(contextProvider, transaction, queryOptions, bulkOptions, readOptions)
{
}
internal static IEnumerable <IPropertyMapping> GetPropertiesByOptions(this IEntityMapping mapping, BulkOptions options)
{
if (options.HasFlag(BulkOptions.OutputIdentity) && options.HasFlag(BulkOptions.OutputComputed))
{
return(mapping.Properties.Where(property => property.IsDbGenerated));
}
if (options.HasFlag(BulkOptions.OutputIdentity))
{
return(mapping.Properties.Where(property => property.IsPk && property.IsDbGenerated));
}
if (options.HasFlag(BulkOptions.OutputComputed))
{
return(mapping.Properties.Where(property => !property.IsPk && property.IsDbGenerated));
}
return(mapping.Properties);
}
protected internal NpgsqlConnectionStringContext(ContextProvider contextProvider, string connectionString,
QueryOptions queryOptions, BulkOptions bulkOptions, ReadOptions readOptions)
: base(contextProvider, connectionString, queryOptions, bulkOptions, readOptions)
{
}
/// <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;
}
}
protected override ConnectionStringContext GetConnectionStringContext(string connectionString, QueryOptions queryOptions, BulkOptions bulkOptions, ReadOptions readOptions)
{
return(new SqlConnectionStringContext(this, connectionString, queryOptions, bulkOptions, readOptions));
}
internal static IEnumerable <IPropertyMapping> FilterPropertiesByOptions(this IEnumerable <IPropertyMapping> propertyMappings, BulkOptions options)
{
if (!options.HasFlag(BulkOptions.KeepForeingKeys))
{
return(propertyMappings.Where(property => !property.IsFk));
}
return(propertyMappings);
}
/// <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;
}
}
internal static void BulkInsertToTable <TEntity>(this IDbContextWrapper context, IList <TEntity> entities,
string tableName, Operation operationType, BulkOptions options) where TEntity : class
{
var properties = context.EntityMapping
.GetPropertiesByOperation(operationType)
.ToList();
if (context.EntityMapping.WillOutputGeneratedValues(options))
{
properties.Add(new PropertyMapping
{
ColumnName = SqlHelper.Identity,
PropertyName = SqlHelper.Identity
});
}
using (var bulkcopy = new SqlBulkCopy((SqlConnection)context.Connection, SqlBulkCopyOptions.Default,
(SqlTransaction)context.Transaction))
{
foreach (var column in properties)
{
bulkcopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
bulkcopy.BatchSize = context.BatchSize;
bulkcopy.DestinationTableName = tableName;
bulkcopy.BulkCopyTimeout = context.Timeout;
bulkcopy.WriteToServer(entities.ToDataReader(context.EntityMapping, properties));
}
}
internal static void BulkInsertToTable <TEntity>(this IDbContextWrapper context, IEnumerable <TEntity> entities,
string tableName, OperationType operationType, BulkOptions options) where TEntity : class
{
var properties = context.EntityMapping.Properties
.FilterPropertiesByOperation(operationType);
var dataReader = entities.ToDataReader(context.EntityMapping, operationType, options);
using (var bulkcopy = new SqlBulkCopy((SqlConnection)context.Connection,
SqlBulkCopyOptions.Default | SqlBulkCopyOptions.KeepIdentity,
(SqlTransaction)context.Transaction))
{
foreach (var column in properties)
{
bulkcopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
bulkcopy.DestinationTableName = tableName;
bulkcopy.BulkCopyTimeout = context.Connection.ConnectionTimeout;
bulkcopy.WriteToServer(dataReader);
}
}
protected override TransactionContext GetTransactionContext(DbTransaction transaction, QueryOptions queryOptions, BulkOptions bulkOptions, ReadOptions readOptions)
{
return(new SqlTransactionContext(this, (SqlTransaction)transaction, queryOptions, bulkOptions, readOptions));
}
internal static bool WillOutputGeneratedValues(this IEntityMapping mapping, BulkOptions options)
{
return(options.HasFlag(BulkOptions.OutputIdentity) && mapping.HasGeneratedKeys ||
options.HasFlag(BulkOptions.OutputComputed) && mapping.HasComputedColumns);
}
protected override BulkCopy GetBulkCopy(ScopedContext context, DbDataReader reader, string destinationTable, BulkOptions bulkOptions, params string[] fields)
{
return(new MsSqlBulkCopy(context, reader, destinationTable, (SqlBulkOptions)bulkOptions, fields));
}
/// <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;
}
}
protected override BulkOptions GetTyped(BulkOptions options, out bool needClone)
{
needClone = false;
return(new SqlBulkOptions(options));
}
protected internal NpgsqlBulkOptions(BulkOptions bulkOptions)
: base(bulkOptions)
{
}
/// <summary>
/// </summary>
/// <param name="mapping"></param>
/// <param name="tableName"></param>
/// <param name="options"></param>
/// <returns></returns>
internal static string CreateTempTable(this IEntityMapping mapping, string tableName, OperationType operationType, BulkOptions options)
{
var columns = mapping.Properties
.FilterPropertiesByOperation(operationType)
.ToList();
var paramList = columns.Select(column => $"[{column.ColumnName}]")
.ToList();
var paramListConcatenated = string.Join(", ", paramList);
return($"SELECT {paramListConcatenated} INTO {tableName} FROM {mapping.TableName} WHERE 1 = 2");
}
/// <summary>
/// </summary>
/// <param name="mapping"></param>
/// <returns></returns>
private static string BuildUpdateSet(this IEntityMapping mapping, OperationType operationType, BulkOptions options)
{
var command = new StringBuilder();
var parameters = new List <string>();
command.Append("SET ");
var properties = mapping.Properties
.FilterPropertiesByOperation(operationType);
foreach (var column in properties)
{
if (column.IsPk)
{
continue;
}
parameters.Add($"[{Target}].[{column.ColumnName}] = [{Source}].[{column.ColumnName}]");
}
command.Append(string.Join(", ", parameters) + " ");
return(command.ToString());
}
protected override BulkOptions SetDefaults(BulkOptions defaultOptions)
{
base.SetDefaults(defaultOptions);
return(this);
}
