/// <summary>
/// Merges the rows of the <see cref="DataTable"/> into the target table by bulk in an asynchronous way. This operation is inserting a row (if not present), and updating an existing
/// row (if present), based on the given qualifiers. It uses either of the 'INSERT/UPDATE' and 'ON CONFLICT DO UPDATE' commands of the
/// PostgreSQL based on the value passed to the '<paramref name="mergeCommandType"/>' argument. Underneath this operation is a call directly to
/// the existing <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method via the customized 'BinaryBulkInsertAsync' extended method.
/// </summary>
/// <param name="connection">The current connection object in used.</param>
/// <param name="tableName">The name of the target table from the database. If not specified, the <see cref="DataTable.TableName"/> property will be used.</param>
/// <param name="table">The source <see cref="DataTable"/> object that contains the rows to be bulk-merged to the target table.</param>
/// <param name="rowState">The state of the rows to be bulk-merged. If not specified, all the rows of the table will be used.</param>
/// <param name="qualifiers">The list of qualifier fields to be used during the operation. Ensure to target the indexed columns to make the execution more performant. If not specified, the primary key will be used.</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not the entity mappings, but is working on top of it)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the rows of the table will be sent together in one-go.</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="mergeCommandType">The value that defines the type of command to be used during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The number of rows that has been merged into the target table.</returns>
public static async Task <int> BinaryBulkMergeAsync(this NpgsqlConnection connection,
string tableName,
DataTable table,
DataRowState?rowState = null,
IEnumerable <Field> qualifiers = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportMergeCommandType mergeCommandType = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default) =>
await BinaryBulkMergeBaseAsync(connection : connection,
tableName : (tableName ?? table?.TableName),
table : table,
rowState : rowState,
qualifiers : qualifiers,
mappings : mappings,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
identityBehavior : identityBehavior,
mergeCommandType : mergeCommandType,
pseudoTableType : pseudoTableType,
transaction : transaction,
cancellationToken : cancellationToken);
/// <summary>
/// Merges a list of entities into the target table by bulk in an asynchronous way. This operation is inserting a row (if not present), and updating an existing
/// row (if present), based on the given qualifiers. It uses either of the 'INSERT/UPDATE' and 'ON CONFLICT DO UPDATE' commands of the
/// PostgreSQL based on the value passed to the '<paramref name="mergeCommandType"/>' argument. Underneath this operation is a call directly to
/// the existing <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method via the customized 'BinaryBulkInsertAsync' extended method.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="connection">The current connection object in used.</param>
/// <param name="tableName">The name of the target table from the database.</param>
/// <param name="entities">The list of entities to be bulk-merged to the target table.
/// This can be an <see cref="IEnumerable{T}"/> of the following objects (<typeparamref name="TEntity"/> (as class/model), <see cref="ExpandoObject"/>,
/// <see cref="IDictionary{TKey, TValue}"/> (of <see cref="string"/>/<see cref="object"/>) and Anonymous Types).</param>
/// <param name="qualifiers">The list of qualifier fields to be used during the operation. Ensure to target the indexed columns to make the execution more performant. If not specified, the primary key will be used.</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not the entity mappings, but is working on top of it)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the rows of the table will be sent together in one-go.</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="mergeCommandType">The value that defines the type of command to be used during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The number of rows that has been merged into the target table.</returns>
public static async Task <int> BinaryBulkMergeAsync <TEntity>(this NpgsqlConnection connection,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <Field> qualifiers = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportMergeCommandType mergeCommandType = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
where TEntity : class =>
await BinaryBulkMergeBaseAsync <TEntity>(connection : connection,
tableName : (tableName ?? ClassMappedNameCache.Get <TEntity>()),
entities : entities,
qualifiers : qualifiers,
mappings : mappings,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
identityBehavior : identityBehavior,
mergeCommandType : mergeCommandType,
pseudoTableType : pseudoTableType,
transaction : transaction,
cancellationToken : cancellationToken);
/// <summary>
///
/// </summary>
/// <param name="importer"></param>
/// <param name="rows"></param>
/// <param name="mappings"></param>
/// <param name="identityBehavior"></param>
private static int BinaryImport(NpgsqlBinaryImporter importer,
IEnumerable <DataRow> rows,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
BulkImportIdentityBehavior identityBehavior)
{
var enumerator = rows.GetEnumerator();
return(BinaryImportWrite(importer,
() => enumerator.MoveNext(),
() => enumerator.Current,
(row) =>
{
foreach (var mapping in mappings)
{
var data = row[mapping.SourceColumn];
if (data is Enum)
{
if (mapping.NpgsqlDbType == NpgsqlDbType.Integer ||
mapping.NpgsqlDbType == NpgsqlDbType.Bigint ||
mapping.NpgsqlDbType == NpgsqlDbType.Smallint)
{
data = Convert.ToInt32(data);
}
else if (mapping.NpgsqlDbType == NpgsqlDbType.Text)
{
data = Convert.ToString(data);
}
}
BinaryImportWrite(importer, data, mapping.NpgsqlDbType);
}
},
identityBehavior));
}
/// <summary>
///
/// </summary>
/// <param name="mappings"></param>
/// <param name="identityBehavior"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
private static string GetCreatePseudoTableQueryColumns(IEnumerable <NpgsqlBulkInsertMapItem> mappings,
BulkImportIdentityBehavior identityBehavior,
IDbSetting dbSetting) =>
identityBehavior != BulkImportIdentityBehavior.ReturnIdentity ?
mappings.Select(field => field.DestinationColumn.AsQuoted(true, dbSetting)).Join(", ") :
$"0 AS {"__RepoDb_OrderColumn".AsQuoted(dbSetting)}, " +
$"{mappings.Select(field => field.DestinationColumn.AsQuoted(true, dbSetting)).Join(", ")}";
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="importer"></param>
/// <param name="moveNextAsync"></param>
/// <param name="getCurrentAsync"></param>
/// <param name="writeAsync"></param>
/// <param name="identityBehavior"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <int> BinaryImportWriteAsync <TEntity>(NpgsqlBinaryImporter importer,
Func <Task <bool> > moveNextAsync,
Func <Task <TEntity> > getCurrentAsync,
Func <TEntity, Task> writeAsync,
BulkImportIdentityBehavior identityBehavior,
CancellationToken cancellationToken = default)
where TEntity : class
{
var result = 0;
while (await moveNextAsync())
{
await importer.StartRowAsync(cancellationToken);
await EnsureCustomizedOrderColumnAsync(importer, identityBehavior, result,
cancellationToken);
await writeAsync(await getCurrentAsync());
result++;
}
await importer.CompleteAsync(cancellationToken);
return(result);
}
/// <summary>
/// Inserts the rows of the <see cref="DbDataReader"/> into the target table by bulk. Underneath this operation is a call directly to the existing
/// <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method.
/// </summary>
/// <param name="repository">The instance of <see cref="DbRepository{TDbConnection}"/> object.</param>
/// <param name="tableName">The name of the target table from the database.</param>
/// <param name="reader">The instance of <see cref="DbDataReader"/> object that contains the rows to be bulk-inserted to the target table.</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not an entity mapping)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <returns>The number of rows that has been inserted into the target table.</returns>
public static int BinaryBulkInsert(this DbRepository <NpgsqlConnection> repository,
string tableName,
DbDataReader reader,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null)
{
// Create a connection
var connection = (transaction?.Connection ?? repository.CreateConnection());
try
{
// Call the method
return(connection.BinaryBulkInsert(tableName: tableName,
reader: reader,
mappings: mappings,
bulkCopyTimeout: bulkCopyTimeout,
identityBehavior: identityBehavior,
pseudoTableType: pseudoTableType,
transaction: transaction));
}
finally
{
// Dispose the connection
if (repository.ConnectionPersistency == ConnectionPersistency.PerCall)
{
if (transaction == null)
{
connection.Dispose();
}
}
}
}
/// <summary>
/// For __RepoDb_OrderColumn
/// </summary>
/// <param name="importer"></param>
/// <param name="identityBehavior"></param>
/// <param name="index"></param>
private static void EnsureCustomizedOrderColumn(NpgsqlBinaryImporter importer,
BulkImportIdentityBehavior identityBehavior,
int index)
{
if (identityBehavior == BulkImportIdentityBehavior.ReturnIdentity)
{
importer.Write(index, NpgsqlDbType.Integer);
}
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="pseudoTableName"></param>
/// <param name="mappings"></param>
/// <param name="identityBehavior"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
private static string GetCreatePseudoTemporaryTableCommandText(string tableName,
string pseudoTableName,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
BulkImportIdentityBehavior identityBehavior,
IDbSetting dbSetting) =>
$"SELECT {GetCreatePseudoTableQueryColumns(mappings, identityBehavior, dbSetting)} " +
$"INTO TEMPORARY {pseudoTableName.AsQuoted(true, dbSetting)} " +
$"FROM {tableName.AsQuoted(true, dbSetting)} " +
$"WHERE (1 = 0);";
/// <summary>
/// For __RepoDb_OrderColumn
/// </summary>
/// <param name="importer"></param>
/// <param name="identityBehavior"></param>
/// <param name="index"></param>
/// <param name="cancellationToken"></param>
private static async Task EnsureCustomizedOrderColumnAsync(NpgsqlBinaryImporter importer,
BulkImportIdentityBehavior identityBehavior,
int index,
CancellationToken cancellationToken = default)
{
if (identityBehavior == BulkImportIdentityBehavior.ReturnIdentity)
{
await importer.WriteAsync(index, NpgsqlDbType.Integer, cancellationToken);
}
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="mappings"></param>
/// <param name="identityBehavior"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
private static string GetBinaryImportCopyCommand(string tableName,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
BulkImportIdentityBehavior identityBehavior,
IDbSetting dbSetting)
{
if (identityBehavior == BulkImportIdentityBehavior.ReturnIdentity &&
mappings.FirstOrDefault(mapping =>
string.Equals(mapping.DestinationColumn, "__RepoDb_OrderColumn", StringComparison.OrdinalIgnoreCase)) == null)
{
mappings = AddOrderColumnMapping(mappings);
}
var textColumns = GetTextColumns(mappings, dbSetting);
return($"COPY {tableName.AsQuoted(true, dbSetting)} ({textColumns}) FROM STDIN (FORMAT BINARY)");
}
/// <summary>
/// Inserts the rows of the <see cref="DbDataReader"/> into the target table by bulk. Underneath this operation is a call directly to the existing
/// <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method.
/// </summary>
/// <param name="connection">The current connection object in used.</param>
/// <param name="tableName">The name of the target table from the database.</param>
/// <param name="reader">The instance of <see cref="DbDataReader"/> object that contains the rows to be bulk-inserted to the target table.</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not an entity mapping)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <returns>The number of rows that has been inserted into the target table.</returns>
public static int BinaryBulkInsert(this NpgsqlConnection connection,
string tableName,
DbDataReader reader,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null) =>
BinaryBulkInsertBase(connection: connection,
tableName: tableName,
reader: reader,
mappings: mappings,
bulkCopyTimeout: bulkCopyTimeout,
identityBehavior: identityBehavior,
pseudoTableType: pseudoTableType,
transaction: transaction);
/// <summary>
///
/// </summary>
/// <param name="importer"></param>
/// <param name="reader"></param>
/// <param name="mappings"></param>
/// <param name="identityBehavior"></param>
private static int BinaryImport(NpgsqlBinaryImporter importer,
DbDataReader reader,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
BulkImportIdentityBehavior identityBehavior)
{
return(BinaryImportWrite(importer,
() => reader.Read(),
() => reader,
(current) =>
{
foreach (var mapping in mappings)
{
BinaryImportWrite(importer, current[mapping.SourceColumn], mapping.NpgsqlDbType);
}
},
identityBehavior));
}
/// <summary>
/// Inserts a list of entities into the target table by bulk. Underneath this operation is a call directly to the existing
/// <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method via the 'BinaryImport' extended method.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="repository">The instance of <see cref="BaseRepository{TEntity, TDbConnection}"/> object.</param>
/// <param name="entities">The list of entities to be bulk-inserted to the target table.
/// This can be an <see cref="IEnumerable{T}"/> of the following objects (<typeparamref name="TEntity"/> (as class/model), <see cref="ExpandoObject"/>,
/// <see cref="IDictionary{TKey, TValue}"/> (of <see cref="string"/>/<see cref="object"/>) and Anonymous Types).</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not an entity mapping)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the entities will be sent together in one-go.</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <returns>The number of rows that has been inserted into the target table.</returns>
public static int BinaryBulkInsert <TEntity>(this BaseRepository <TEntity, NpgsqlConnection> repository,
IEnumerable <TEntity> entities,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null)
where TEntity : class =>
repository.DbRepository.BinaryBulkInsert <TEntity>(tableName : ClassMappedNameCache.Get <TEntity>(),
entities : entities,
mappings : mappings,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
identityBehavior : identityBehavior,
pseudoTableType : pseudoTableType,
transaction : transaction);
/// <summary>
/// Inserts the rows of the <see cref="DbDataReader"/> into the target table by bulk in an asynchronous way. Underneath this operation is a call directly to the existing
/// <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method.
/// </summary>
/// <param name="connection">The current connection object in used.</param>
/// <param name="tableName">The name of the target table from the database.</param>
/// <param name="reader">The instance of <see cref="DbDataReader"/> object that contains the rows to be bulk-inserted to the target table.</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not an entity mapping)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The number of rows that has been inserted into the target table.</returns>
public static async Task <int> BinaryBulkInsertAsync(this NpgsqlConnection connection,
string tableName,
DbDataReader reader,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default) =>
await BinaryBulkInsertBaseAsync(connection : connection,
tableName : tableName,
reader : reader,
mappings : mappings,
bulkCopyTimeout : bulkCopyTimeout,
identityBehavior : identityBehavior,
pseudoTableType : pseudoTableType,
transaction : transaction,
cancellationToken : cancellationToken);
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="pseudoTableName"></param>
/// <param name="mappings"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="identityBehavior"></param>
/// <param name="pseudoTableType"></param>
/// <param name="dbSetting"></param>
/// <param name="transaction"></param>
private static void CreatePseudoTable(NpgsqlConnection connection,
string tableName,
string pseudoTableName,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
int?bulkCopyTimeout = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
IDbSetting dbSetting = null,
NpgsqlTransaction transaction = null)
{
var commandText = pseudoTableType == BulkImportPseudoTableType.Physical ?
GetCreatePseudoTableCommandText(tableName, pseudoTableName, mappings, identityBehavior, dbSetting) :
GetCreatePseudoTemporaryTableCommandText(tableName, pseudoTableName, mappings, identityBehavior, dbSetting);
connection.ExecuteNonQuery(commandText,
bulkCopyTimeout,
transaction: transaction);
}
/// <summary>
/// Inserts the rows of the <see cref="DataTable"/> into the target table by bulk. It uses the <see cref="DataTable.TableName"/> property
/// as the target table. Underneath this operation is a call directly to the existing <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method.
/// </summary>
/// <param name="connection">The current connection object in used.</param>
/// <param name="table">The source <see cref="DataTable"/> object that contains the rows to be bulk-inserted to the target table.</param>
/// <param name="rowState">The state of the rows to be bulk-inserted. If not specified, all the rows of the table will be used.</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not an entity mapping)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the rows of the table will be sent together in one-go.</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <returns>The number of rows that has been inserted into the target table.</returns>
public static int BinaryBulkInsert(this NpgsqlConnection connection,
DataTable table,
DataRowState?rowState = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null) =>
BinaryBulkInsert(connection: connection,
tableName: table?.TableName,
table: table,
rowState: rowState,
mappings: mappings,
bulkCopyTimeout: bulkCopyTimeout,
batchSize: batchSize,
identityBehavior: identityBehavior,
pseudoTableType: pseudoTableType,
transaction: transaction);
/// <summary>
///
/// </summary>
/// <param name="importer"></param>
/// <param name="dictionaries"></param>
/// <param name="mappings"></param>
/// <param name="identityBehavior"></param>
private static int BinaryImport(NpgsqlBinaryImporter importer,
IEnumerable <IDictionary <string, object> > dictionaries,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
BulkImportIdentityBehavior identityBehavior)
{
var enumerator = dictionaries.GetEnumerator();
return(BinaryImportWrite(importer,
() => enumerator.MoveNext(),
() => enumerator.Current,
(dictionary) =>
{
foreach (var mapping in mappings)
{
BinaryImportWrite(importer, dictionary[mapping.SourceColumn], mapping.NpgsqlDbType);
}
},
identityBehavior));
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="importer"></param>
/// <param name="tableName"></param>
/// <param name="entities"></param>
/// <param name="mappings"></param>
/// <param name="entityType"></param>
/// <param name="identityBehavior"></param>
private static int BinaryImport <TEntity>(NpgsqlBinaryImporter importer,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
Type entityType,
BulkImportIdentityBehavior identityBehavior)
where TEntity : class
{
var func = Compiler.GetNpgsqlBinaryImporterWriteFunc <TEntity>(tableName,
mappings,
entityType);
var enumerator = entities.GetEnumerator();
return(BinaryImportWrite(importer,
() => enumerator.MoveNext(),
() => enumerator.Current,
(entity) => func(importer, entity),
identityBehavior));
}
/// <summary>
/// Inserts a list of entities into the target table by bulk in an asynchronous way. Underneath this operation is a call directly to the existing
/// <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method via the 'BinaryImport' extended method.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="connection">The current connection object in used.</param>
/// <param name="entities">The list of entities to be bulk-inserted to the target table.
/// This can be an <see cref="IEnumerable{T}"/> of the following objects (<typeparamref name="TEntity"/> (as class/model), <see cref="ExpandoObject"/>,
/// <see cref="IDictionary{TKey, TValue}"/> (of <see cref="string"/>/<see cref="object"/>) and Anonymous Types).</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not an entity mapping)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the entities will be sent together in one-go.</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The number of rows that has been inserted into the target table.</returns>
public static Task <int> BinaryBulkInsertAsync <TEntity>(this NpgsqlConnection connection,
IEnumerable <TEntity> entities,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
where TEntity : class =>
BinaryBulkInsertAsync <TEntity>(connection : connection,
tableName : ClassMappedNameCache.Get <TEntity>(),
entities : entities,
mappings : mappings,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
identityBehavior : identityBehavior,
pseudoTableType : pseudoTableType,
transaction : transaction,
cancellationToken : cancellationToken);
/// <summary>
///
/// </summary>
/// <param name="importer"></param>
/// <param name="reader"></param>
/// <param name="mappings"></param>
/// <param name="identityBehavior"></param>
/// <param name="cancellationToken"></param>
private static async Task <int> BinaryImportAsync(NpgsqlBinaryImporter importer,
DbDataReader reader,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
BulkImportIdentityBehavior identityBehavior,
CancellationToken cancellationToken = default)
{
return(await BinaryImportWriteAsync(importer,
async() => await reader.ReadAsync(cancellationToken),
async() => await Task.FromResult(reader),
async (current) =>
{
foreach (var mapping in mappings)
{
await BinaryImportWriteAsync(importer, current[mapping.SourceColumn],
mapping.NpgsqlDbType, cancellationToken);
}
},
identityBehavior,
cancellationToken : cancellationToken));
}
/// <summary>
/// Inserts the rows of the <see cref="DataTable"/> into the target table by bulk in an asynchronous way. It uses the <see cref="DataTable.TableName"/> property
/// as the target table. Underneath this operation is a call directly to the existing <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method.
/// </summary>
/// <param name="repository">The instance of <see cref="DbRepository{TDbConnection}"/> object.</param>
/// <param name="tableName">The name of the target table from the database. If not specified, the <see cref="DataTable.TableName"/> property will be used.</param>
/// <param name="table">The source <see cref="DataTable"/> object that contains the rows to be bulk-inserted to the target table.</param>
/// <param name="rowState">The state of the rows to be bulk-inserted. If not specified, all the rows of the table will be used.</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not an entity mapping)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the rows of the table will be sent together in one-go.</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The number of rows that has been inserted into the target table.</returns>
public static async Task <int> BinaryBulkInsertAsync(this DbRepository <NpgsqlConnection> repository,
string tableName,
DataTable table,
DataRowState?rowState = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
{
// Create a connection
var connection = (transaction?.Connection ?? repository.CreateConnection());
try
{
// Call the method
return(await connection.BinaryBulkInsertAsync(tableName : (tableName ?? table?.TableName),
table : table,
rowState : rowState,
mappings : mappings,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
identityBehavior : identityBehavior,
pseudoTableType : pseudoTableType,
transaction : transaction,
cancellationToken : cancellationToken));
}
finally
{
// Dispose the connection
if (repository.ConnectionPersistency == ConnectionPersistency.PerCall)
{
if (transaction == null)
{
connection.Dispose();
}
}
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="importer"></param>
/// <param name="tableName"></param>
/// <param name="entities"></param>
/// <param name="mappings"></param>
/// <param name="entityType"></param>
/// <param name="identityBehavior"></param>
/// <param name="cancellationToken"></param>
private static async Task <int> BinaryImportAsync <TEntity>(NpgsqlBinaryImporter importer,
string tableName,
IEnumerable <TEntity> entities,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
Type entityType,
BulkImportIdentityBehavior identityBehavior,
CancellationToken cancellationToken = default)
where TEntity : class
{
var func = Compiler.GetNpgsqlBinaryImporterWriteAsyncFunc <TEntity>(tableName,
mappings,
entityType);
var enumerator = entities.GetEnumerator();
return(await BinaryImportWriteAsync(importer,
async() => await Task.FromResult(enumerator.MoveNext()),
async() => await Task.FromResult(enumerator.Current),
async (entity) => await func(importer, entity, cancellationToken),
identityBehavior,
cancellationToken));
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <param name="dbFields"></param>
/// <param name="properties"></param>
/// <param name="entityType"></param>
/// <param name="identityBehavior"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
public static Action <NpgsqlBinaryImporter, TEntity> Get(string tableName,
IEnumerable <DbField> dbFields,
IEnumerable <ClassProperty> properties,
Type entityType,
BulkImportIdentityBehavior identityBehavior,
IDbSetting dbSetting = null)
{
var includeIdentity = (identityBehavior == BulkImportIdentityBehavior.KeepIdentity);
var primaryDbField = dbFields?.FirstOrDefault(dbField => dbField.IsPrimary);
var isPrimaryAnIdentity = primaryDbField?.IsIdentity == true;
var includePrimary = isPrimaryAnIdentity == false ||
(isPrimaryAnIdentity && identityBehavior == BulkImportIdentityBehavior.KeepIdentity);
var matchedProperties = NpgsqlConnectionExtension.GetMatchedProperties(dbFields,
properties,
includePrimary,
includeIdentity,
dbSetting);
var mappings = matchedProperties.Select(property =>
new NpgsqlBulkInsertMapItem(property.PropertyInfo.Name, property.GetMappedName()));
return(GetFunc(tableName, mappings, entityType));
}
/// <summary>
///
/// </summary>
/// <param name="importer"></param>
/// <param name="rows"></param>
/// <param name="mappings"></param>
/// <param name="identityBehavior"></param>
/// <param name="cancellationToken"></param>
private static async Task <int> BinaryImportAsync(NpgsqlBinaryImporter importer,
IEnumerable <DataRow> rows,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
BulkImportIdentityBehavior identityBehavior,
CancellationToken cancellationToken = default)
{
var enumerator = rows.GetEnumerator();
return(await BinaryImportWriteAsync(importer,
async() => await Task.FromResult(enumerator.MoveNext()),
async() => await Task.FromResult(enumerator.Current),
async (row) =>
{
foreach (var mapping in mappings)
{
await BinaryImportWriteAsync(importer, row[mapping.SourceColumn],
mapping.NpgsqlDbType, cancellationToken);
}
},
identityBehavior,
cancellationToken));
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="importer"></param>
/// <param name="moveNext"></param>
/// <param name="getCurrent"></param>
/// <param name="write"></param>
/// <param name="identityBehavior"></param>
/// <returns></returns>
private static int BinaryImportWrite <TEntity>(NpgsqlBinaryImporter importer,
Func <bool> moveNext,
Func <TEntity> getCurrent,
Action <TEntity> write,
BulkImportIdentityBehavior identityBehavior)
where TEntity : class
{
var result = 0;
while (moveNext())
{
importer.StartRow();
EnsureCustomizedOrderColumn(importer, identityBehavior, result);
write(getCurrent());
result++;
}
importer.Complete();
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="mappings"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="identityBehavior"></param>
/// <param name="dbSetting"></param>
/// <returns></returns>
private static NpgsqlBinaryImporter GetNpgsqlBinaryImporter(NpgsqlConnection connection,
string tableName,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
int?bulkCopyTimeout,
BulkImportIdentityBehavior identityBehavior,
IDbSetting dbSetting)
{
var copyCommand = GetBinaryImportCopyCommand(tableName,
mappings,
identityBehavior,
dbSetting);
var importer = connection.BeginBinaryImport(copyCommand);
// Timeout
if (bulkCopyTimeout.HasValue)
{
importer.Timeout = TimeSpan.FromSeconds(bulkCopyTimeout.Value);
}
// Return
return(importer);
}
/// <summary>
/// Inserts a list of entities into the target table by bulk in an asynchronous way. Underneath this operation is a call directly to the existing
/// <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method via the 'BinaryImport' extended method.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="repository">The instance of <see cref="DbRepository{TDbConnection}"/> object.</param>
/// <param name="entities">The list of entities to be bulk-inserted to the target table.
/// This can be an <see cref="IEnumerable{T}"/> of the following objects (<typeparamref name="TEntity"/> (as class/model), <see cref="ExpandoObject"/>,
/// <see cref="IDictionary{TKey, TValue}"/> (of <see cref="string"/>/<see cref="object"/>) and Anonymous Types).</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not an entity mapping)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the entities will be sent together in one-go.</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
/// <returns>The number of rows that has been inserted into the target table.</returns>
public static async Task <int> BinaryBulkInsertAsync <TEntity>(this DbRepository <NpgsqlConnection> repository,
IEnumerable <TEntity> entities,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null,
CancellationToken cancellationToken = default)
where TEntity : class
{
// Create a connection
var connection = (transaction?.Connection ?? repository.CreateConnection());
try
{
// Call the method
return(await connection.BinaryBulkInsertAsync <TEntity>(tableName : ClassMappedNameCache.Get <TEntity>(),
entities : entities,
mappings : mappings,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
identityBehavior : identityBehavior,
pseudoTableType : pseudoTableType,
transaction : transaction,
cancellationToken : cancellationToken));
}
finally
{
// Dispose the connection
if (repository.ConnectionPersistency == ConnectionPersistency.PerCall)
{
if (transaction == null)
{
connection.Dispose();
}
}
}
}
/// <summary>
/// Merges a list of entities into the target table by bulk. This operation is inserting a row (if not present), and updating an existing
/// row (if present), based on the given qualifiers. It uses either of the 'INSERT/UPDATE' and 'ON CONFLICT DO UPDATE' commands of the
/// PostgreSQL based on the value passed to the '<paramref name="mergeCommandType"/>' argument. Underneath this operation is a call directly to
/// the existing <see cref="NpgsqlConnection.BeginBinaryExport(string)"/> method via the customized 'BinaryBulkInsert' extended method.
/// </summary>
/// <typeparam name="TEntity">The type of the entity.</typeparam>
/// <param name="connection">The current connection object in used.</param>
/// <param name="entities">The list of entities to be bulk-merged to the target table.
/// This can be an <see cref="IEnumerable{T}"/> of the following objects (<typeparamref name="TEntity"/> (as class/model), <see cref="ExpandoObject"/>,
/// <see cref="IDictionary{TKey, TValue}"/> (of <see cref="string"/>/<see cref="object"/>) and Anonymous Types).</param>
/// <param name="qualifiers">The list of qualifier fields to be used during the operation. Ensure to target the indexed columns to make the execution more performant. If not specified, the primary key will be used.</param>
/// <param name="mappings">The list of mappings to be used. If not specified, only the matching properties/columns from the target table will be used. (This is not the entity mappings, but is working on top of it)</param>
/// <param name="bulkCopyTimeout">The timeout expiration of the operation (see <see cref="NpgsqlBinaryImporter.Timeout"/>).</param>
/// <param name="batchSize">The size per batch to be sent to the database. If not specified, all the entities will be sent together in one-go.</param>
/// <param name="identityBehavior">The behavior of how the identity column would work during the operation.</param>
/// <param name="mergeCommandType">The value that defines the type of command to be used during the operation.</param>
/// <param name="pseudoTableType">The value that defines whether an actual or temporary table will be created for the pseudo-table.</param>
/// <param name="transaction">The current transaction object in used. If not specified, an implicit transaction will be created and used.</param>
/// <returns>The number of rows that has been merged into the target table.</returns>
public static int BinaryBulkMerge <TEntity>(this NpgsqlConnection connection,
IEnumerable <TEntity> entities,
IEnumerable <Field> qualifiers = null,
IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
int?bulkCopyTimeout = null,
int?batchSize = null,
BulkImportIdentityBehavior identityBehavior = default,
BulkImportMergeCommandType mergeCommandType = default,
BulkImportPseudoTableType pseudoTableType = default,
NpgsqlTransaction transaction = null)
where TEntity : class =>
BinaryBulkMerge <TEntity>(connection : connection,
tableName : ClassMappedNameCache.Get <TEntity>(),
entities : entities,
qualifiers : qualifiers,
mappings : mappings,
bulkCopyTimeout : bulkCopyTimeout,
batchSize : batchSize,
identityBehavior : identityBehavior,
mergeCommandType : mergeCommandType,
pseudoTableType : pseudoTableType,
transaction : transaction);
/// <summary>
///
/// </summary>
/// <param name="importer"></param>
/// <param name="dictionaries"></param>
/// <param name="mappings"></param>
/// <param name="identityBehavior"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
private static async Task <int> BinaryImportExplicitAsync(NpgsqlBinaryImporter importer,
IEnumerable <IDictionary <string, object> > dictionaries,
IEnumerable <NpgsqlBulkInsertMapItem> mappings,
BulkImportIdentityBehavior identityBehavior,
CancellationToken cancellationToken = default)
{
var enumerator = dictionaries.GetEnumerator();
return(await BinaryImportWriteAsync(importer,
async() => await Task.FromResult(enumerator.MoveNext()),
async() => await Task.FromResult(enumerator.Current),
async (dictionary) =>
{
foreach (var mapping in mappings)
{
await BinaryImportWriteAsync(importer, dictionary[mapping.SourceColumn],
mapping.NpgsqlDbType, cancellationToken);
}
},
identityBehavior,
cancellationToken));
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="bulkCopyTimeout"></param>
/// <param name="dbFields"></param>
/// <param name="getPseudoTableName"></param>
/// <param name="getMappings"></param>
/// <param name="binaryImport"></param>
/// <param name="getMergeToPseudoCommandText"></param>
/// <param name="setIdentities"></param>
/// <param name="qualifiers"></param>
/// <param name="isBinaryBulkInsert"></param>
/// <param name="identityBehavior"></param>
/// <param name="pseudoTableType"></param>
/// <param name="dbSetting"></param>
/// <param name="transaction"></param>
/// <returns></returns>
private static int PseudoBasedBinaryImport(this NpgsqlConnection connection,
string tableName,
int?bulkCopyTimeout,
IEnumerable <DbField> dbFields,
Func <string> getPseudoTableName,
Func <IEnumerable <NpgsqlBulkInsertMapItem> > getMappings,
Func <string, int> binaryImport,
Func <string> getMergeToPseudoCommandText,
Action <IEnumerable <IdentityResult> > setIdentities,
IEnumerable <Field> qualifiers,
bool isBinaryBulkInsert,
BulkImportIdentityBehavior identityBehavior,
BulkImportPseudoTableType pseudoTableType,
IDbSetting dbSetting,
NpgsqlTransaction transaction)
{
string pseudoTableName = null;
var withPseudoTable = identityBehavior == BulkImportIdentityBehavior.ReturnIdentity ||
isBinaryBulkInsert == false;
try
{
// Mappings
var mappings = getMappings?.Invoke();
// Create (TEMP)
if (withPseudoTable)
{
pseudoTableName = getPseudoTableName?.Invoke();
DropPseudoTable(connection,
pseudoTableName,
bulkCopyTimeout,
transaction);
CreatePseudoTable(connection,
tableName,
pseudoTableName,
mappings,
bulkCopyTimeout,
identityBehavior,
pseudoTableType,
dbSetting,
transaction);
}
// Import
var result = binaryImport?.Invoke(pseudoTableName ?? tableName);
// Create Index
if (isBinaryBulkInsert == false && withPseudoTable)
{
qualifiers = qualifiers?.Any() == true ? qualifiers :
dbFields?.FirstOrDefault(dbField => dbField.IsPrimary).AsField().AsEnumerable();
CreatePseudoTableIndex(connection,
pseudoTableName,
qualifiers,
bulkCopyTimeout,
dbSetting,
transaction);
}
// Merge/Update/Delete
if (withPseudoTable)
{
var identityResults = MergeToPseudoTableWithIdentityResults(connection,
getMergeToPseudoCommandText,
bulkCopyTimeout,
transaction)?.AsList();
if (identityBehavior == BulkImportIdentityBehavior.ReturnIdentity)
{
setIdentities?.Invoke(identityResults);
}
result = identityResults.Count();
}
// Return
return(result.GetValueOrDefault());
}
finally
{
if (withPseudoTable)
{
DropPseudoTable(connection,
pseudoTableName,
bulkCopyTimeout,
transaction);
}
}
}
