/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <returns></returns>
public static InsertExecutionContext <TEntity> Create <TEntity>(IDbConnection connection,
string tableName,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null)
where TEntity : class
{
var key = GetKey <TEntity>(tableName, fields, hints);
// Get from cache
var context = InsertExecutionContextCache.Get <TEntity>(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = DbFieldCache.Get(connection, tableName, transaction);
context = CreateInternal <TEntity>(connection,
dbFields,
tableName,
fields,
hints,
transaction,
statementBuilder);
// Add to cache
InsertExecutionContextCache.Add <TEntity>(key, context);
// Return
return(context);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="batchSize"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <returns></returns>
public static InsertAllExecutionContext <TEntity> Create <TEntity>(IDbConnection connection,
string tableName,
int batchSize,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null)
where TEntity : class
{
var key = GetKey <TEntity>(tableName, fields, batchSize, hints);
// Get from cache
var context = InsertAllExecutionContextCache.Get <TEntity>(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = DbFieldCache.Get(connection, tableName, transaction);
var commandText = (string)null;
// Create a different kind of requests
if (batchSize > 1)
{
var request = new InsertAllRequest(tableName,
connection,
transaction,
fields,
batchSize,
hints,
statementBuilder);
commandText = CommandTextCache.GetInsertAllText(request);
}
else
{
var request = new InsertRequest(tableName,
connection,
transaction,
fields,
hints,
statementBuilder);
commandText = CommandTextCache.GetInsertText(request);
}
// Call
context = CreateInternal <TEntity>(connection,
tableName,
dbFields,
batchSize,
fields,
commandText);
// Add to cache
InsertAllExecutionContextCache.Add <TEntity>(key, context);
// Return
return(context);
}
public void TestSqlConnectionExecuteNonQueryFromQueryBuilderCreateInsert()
{
// Setup
var table = Helper.CreateIdentityTables(1).First();
var fields = FieldCache.Get <IdentityTable>();
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Setup
var dbFields = DbFieldCache.Get(connection, ClassMappedNameCache.Get <IdentityTable>(), null);
var builder = connection.GetStatementBuilder();
var sql = builder.CreateInsert(null,
ClassMappedNameCache.Get <IdentityTable>(),
fields: fields,
primaryField: dbFields.FirstOrDefault(e => e.IsPrimary),
identityField: dbFields.FirstOrDefault(e => e.IsIdentity));
// Act
var id = connection.ExecuteScalar(sql, table);
// Assert
Assert.IsNotNull(id);
// Setup
var result = connection.QueryAll <IdentityTable>().First();
// Assert
Helper.AssertPropertiesEquality(table, result);
}
}
public void TestExtractForPostgreSqlTypeNameToClientTypeResolver()
{
using (var connection = new NpgsqlConnection(Database.ConnectionString))
{
var dbFields = DbFieldCache.Get(connection, "CompleteTable", null).AsList();
var dictionary = new Dictionary <Type, List <string> >();
using (var reader = connection.ExecuteReader(GetSelectText()))
{
for (var i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
var type = reader.GetFieldType(i);
var dbField = dbFields
.FirstOrDefault(e =>
string.Equals(e.Name, name, StringComparison.CurrentCultureIgnoreCase));
if (dbField == null)
{
continue;
}
var databaseType = dbField.DatabaseType.ToLower();
var list = (List <string>)null;
if (dictionary.ContainsKey(type))
{
list = dictionary[type];
}
else
{
list = new List <string>();
dictionary[type] = list;
}
if (!list.Contains(databaseType))
{
list.Add(databaseType);
}
}
}
var entries = (string)null;
foreach (var kvp in dictionary)
{
var databaseTypes = kvp.Value;
foreach (var databaseType in databaseTypes)
{
entries = string.Concat(entries, $"case \"{databaseType}\":\n");
}
entries = string.Concat(entries, $"\treturn typeof({kvp.Key.FullName});\n");
}
}
}
/// <summary>
///
/// </summary>
/// <param name="entityType"></param>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="entities"></param>
/// <param name="qualifiers"></param>
/// <param name="batchSize"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <returns></returns>
public static UpdateAllExecutionContext Create(Type entityType,
IDbConnection connection,
string tableName,
IEnumerable <object> entities,
IEnumerable <Field> qualifiers,
int batchSize,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null)
{
var key = GetKey(entityType, tableName, qualifiers, fields, batchSize, hints);
// Get from cache
var context = UpdateAllExecutionContextCache.Get(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = DbFieldCache.Get(connection, tableName, transaction);
var request = new UpdateAllRequest(tableName,
connection,
transaction,
fields,
qualifiers,
batchSize,
hints,
statementBuilder);
var commandText = CommandTextCache.GetUpdateAllText(request);
// Call
context = CreateInternal(entityType,
connection,
tableName,
entities,
dbFields,
qualifiers,
batchSize,
fields,
commandText);
// Add to cache
UpdateAllExecutionContextCache.Add(key, context);
// Return
return(context);
}
/// <summary>
///
/// </summary>
/// <param name="entityType"></param>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="where"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <returns></returns>
public static UpdateExecutionContext Create(Type entityType,
IDbConnection connection,
string tableName,
QueryGroup where,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null)
{
var key = GetKey(entityType, tableName, fields, hints, where);
// Get from cache
var context = UpdateExecutionContextCache.Get(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = DbFieldCache.Get(connection, tableName, transaction);
var request = new UpdateRequest(tableName,
connection,
transaction,
where,
fields,
hints,
statementBuilder);
var commandText = CommandTextCache.GetUpdateText(request);
// Call
context = CreateInternal(entityType,
connection,
tableName,
dbFields,
fields,
commandText);
// Add to cache
UpdateExecutionContextCache.Add(key, context);
// Return
return(context);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="tableName"></param>
/// <param name="qualifiers"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <returns></returns>
public static MergeExecutionContext <TEntity> Create <TEntity>(IDbConnection connection,
string tableName,
IEnumerable <Field> qualifiers,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null)
where TEntity : class
{
var key = GetKey <TEntity>(tableName, qualifiers, fields, hints);
// Get from cache
var context = MergeExecutionContextCache.Get <TEntity>(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = DbFieldCache.Get(connection, tableName, transaction);
var request = new MergeRequest(tableName,
connection,
transaction,
fields,
qualifiers,
hints,
statementBuilder);
var commandText = CommandTextCache.GetMergeText(request);
// Call
context = CreateInternal <TEntity>(connection,
dbFields,
tableName,
fields,
commandText);
// Add to cache
MergeExecutionContextCache.Add <TEntity>(key, context);
// Return
return(context);
}
/// <summary>
///
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="connection"></param>
/// <param name="entities"></param>
/// <param name="tableName"></param>
/// <param name="qualifiers"></param>
/// <param name="batchSize"></param>
/// <param name="fields"></param>
/// <param name="hints"></param>
/// <param name="transaction"></param>
/// <param name="statementBuilder"></param>
/// <param name="skipIdentityCheck"></param>
/// <returns></returns>
public static MergeAllExecutionContext <TEntity> Create <TEntity>(IDbConnection connection,
IEnumerable <TEntity> entities,
string tableName,
IEnumerable <Field> qualifiers,
int batchSize,
IEnumerable <Field> fields,
string hints = null,
IDbTransaction transaction = null,
IStatementBuilder statementBuilder = null,
bool skipIdentityCheck = false)
where TEntity : class
{
var key = GetKey <TEntity>(tableName, qualifiers, fields, batchSize, hints);
// Get from cache
var context = MergeAllExecutionContextCache.Get <TEntity>(key);
if (context != null)
{
return(context);
}
// Create
var dbFields = DbFieldCache.Get(connection, tableName, transaction);
context = CreateInternal <TEntity>(connection,
entities,
dbFields,
tableName,
qualifiers,
batchSize,
fields,
hints,
transaction,
statementBuilder,
skipIdentityCheck);
// Add to cache
MergeAllExecutionContextCache.Add <TEntity>(key, context);
// Return
return(context);
}
public void TestSqlConnectionExecuteNonQueryFromQueryBuilderCreateUpdate()
{
// Setup
var table = Helper.CreateIdentityTables(1).First();
var fields = FieldCache.Get <IdentityTable>();
using (var connection = new SqlConnection(Database.ConnectionStringForRepoDb))
{
// Act
var id = connection.Insert(table);
// Set the properties
table.ColumnNVarChar = $"{table.ColumnNVarChar}-Updated";
// Setup
var where = new QueryGroup(new QueryField("Id", id));
// Setup
var dbFields = DbFieldCache.Get(connection, ClassMappedNameCache.Get <IdentityTable>(), null);
var builder = connection.GetStatementBuilder();
var sql = builder.CreateUpdate(null,
ClassMappedNameCache.Get <IdentityTable>(),
fields: fields,
where : where,
primaryField: dbFields.FirstOrDefault(e => e.IsPrimary),
identityField: dbFields.FirstOrDefault(e => e.IsIdentity));
// Act
var affectedRow = connection.ExecuteNonQuery(sql, table);
// Assert
Assert.AreEqual(1, affectedRow);
// Setup
var result = connection.QueryAll <IdentityTable>().First();
// Assert
Helper.AssertPropertiesEquality(table, result);
}
}
/// <summary>
/// Bulk insert an instance of <see cref="DataTable"/> object into the database.
/// </summary>
/// <param name="connection">The connection object to be used.</param>
/// <param name="tableName">The target table for bulk-insert operation.</param>
/// <param name="dataTable">The <see cref="DataTable"/> object to be used in the bulk-insert operation.</param>
/// <param name="rowState">The state of the rows to be copied to the destination.</param>
/// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param>
/// <param name="options">The bulk-copy options to be used.</param>
/// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param>
/// <param name="batchSize">The size per batch to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <returns>The number of rows affected by the execution.</returns>
public int BulkInsert(IDbConnection connection,
string tableName,
DataTable dataTable,
DataRowState rowState = DataRowState.Unchanged,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
int?bulkCopyTimeout = null,
int?batchSize = null,
IDbTransaction transaction = null)
{
// Validate the objects
ValidateConnection(connection);
ValidateTransaction(transaction);
DbConnectionExtension.ValidateTransactionConnectionObject(connection, transaction);
// Variables for the operation
var result = 0;
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Actual Execution
using (var sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection, options, (SqlTransaction)transaction))
{
// Set the destinationtable
sqlBulkCopy.DestinationTableName = tableName;
// Set the timeout
if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue)
{
sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
}
// Set the batch szie
if (batchSize != null && batchSize.HasValue)
{
sqlBulkCopy.BatchSize = batchSize.Value;
}
// Add the mappings
if (mappings == null)
{
// Get the actual DB fields
var dbFields = DbFieldCache.Get(connection, tableName, transaction);
var fields = GetDataColumns(dataTable).Select(column => column.ColumnName);
var filteredFields = new List <Tuple <string, string> >();
// To fix the casing problem of the bulk inserts
foreach (var dbField in dbFields)
{
var field = fields.FirstOrDefault(f => string.Equals(f, dbField.UnquotedName, StringComparison.OrdinalIgnoreCase));
if (field != null)
{
filteredFields.Add(new Tuple <string, string>(field, dbField.UnquotedName));
}
}
// Iterate the filtered fields
foreach (var field in filteredFields)
{
sqlBulkCopy.ColumnMappings.Add(field.Item1, field.Item2);
}
}
else
{
// Iterate the provided mappings
foreach (var mapItem in mappings)
{
sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn);
}
}
// Open the connection and do the operation
connection.EnsureOpen();
sqlBulkCopy.WriteToServer(dataTable, rowState);
// Set the return value
result = GetDataRows(dataTable, rowState).Count();
}
// Result
return(result);
}
/// <summary>
/// Bulk insert an instance of <see cref="DbDataReader"/> object into the database.
/// </summary>
/// <param name="connection">The connection object to be used.</param>
/// <param name="tableName">The target table for bulk-insert operation.</param>
/// <param name="reader">The <see cref="DbDataReader"/> object to be used in the bulk-insert operation.</param>
/// <param name="mappings">The list of the columns to be used for mappings. If this parameter is not set, then all columns will be used for mapping.</param>
/// <param name="options">The bulk-copy options to be used.</param>
/// <param name="bulkCopyTimeout">The timeout in seconds to be used.</param>
/// <param name="batchSize">The size per batch to be used.</param>
/// <param name="transaction">The transaction to be used.</param>
/// <returns>The number of rows affected by the execution.</returns>
public int BulkInsert(IDbConnection connection,
string tableName,
DbDataReader reader,
IEnumerable <BulkInsertMapItem> mappings = null,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
int?bulkCopyTimeout = null,
int?batchSize = null,
IDbTransaction transaction = null)
{
// Validate the objects
ValidateConnection(connection);
ValidateTransaction(transaction);
DbConnectionExtension.ValidateTransactionConnectionObject(connection, transaction);
// Variables for the operation
var result = 0;
// Before Execution Time
var beforeExecutionTime = DateTime.UtcNow;
// Actual Execution
using (var sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection, options, (SqlTransaction)transaction))
{
// Set the destinationtable
sqlBulkCopy.DestinationTableName = tableName;
// Set the timeout
if (bulkCopyTimeout != null && bulkCopyTimeout.HasValue)
{
sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout.Value;
}
// Set the batch szie
if (batchSize != null && batchSize.HasValue)
{
sqlBulkCopy.BatchSize = batchSize.Value;
}
// Add the mappings
if (mappings == null)
{
// Get the actual DB fields
var dbFields = DbFieldCache.Get(connection, tableName, transaction);
var fields = Enumerable.Range(0, reader.FieldCount).Select((index) => reader.GetName(index));
var filteredFields = new List <Tuple <string, string> >();
// To fix the casing problem of the bulk inserts
foreach (var dbField in dbFields)
{
var readerField = fields.FirstOrDefault(field => string.Equals(field, dbField.UnquotedName, StringComparison.OrdinalIgnoreCase));
if (!string.IsNullOrEmpty(readerField))
{
filteredFields.Add(new Tuple <string, string>(readerField, dbField.UnquotedName));
}
}
// Iterate the filtered fields
foreach (var field in filteredFields)
{
sqlBulkCopy.ColumnMappings.Add(field.Item1, field.Item2);
}
}
else
{
// Iterate the provided mappings
foreach (var mapItem in mappings)
{
sqlBulkCopy.ColumnMappings.Add(mapItem.SourceColumn, mapItem.DestinationColumn);
}
}
// Open the connection and do the operation
connection.EnsureOpen();
sqlBulkCopy.WriteToServer(reader);
// Hack the 'SqlBulkCopy' object
var copiedField = GetRowsCopiedField();
// Set the return value
result = copiedField != null ? (int)copiedField.GetValue(sqlBulkCopy) : reader.RecordsAffected;
}
// Result
return(result);
}