public void TestDbHelperForQueryViaTableName() { // Prepare var dbHelper = new Mock <IDbHelper>(); var connection = new CustomDbConnectionForDbHelper(); // Setup dbHelper.Setup(builder => builder.GetFields( It.Is <string>(s => s == connection.ConnectionString), It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>())) ).Returns(GetDbFields()); // Act CommandTextCache.Flush(); DbFieldCache.Flush(); StatementBuilderMapper.Add(typeof(CustomDbConnectionForDbHelper), new SqlStatementBuilder(), true); DbHelperMapper.Add(typeof(CustomDbConnectionForDbHelper), dbHelper.Object, true); // Act connection.Query(ClassMappedNameCache.Get <DataEntityForDbHelper>(), (object)null); // Assert dbHelper.Verify(builder => builder.GetFields( It.Is <string>(s => s == connection.ConnectionString), It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>())), Times.Exactly(1)); }
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); } }
private static async Task <Func <DbDataReader, TEntity> > GetRepoDbModelMappingFuncByBruteForce <TEntity>( DbConnection dbConn, DbDataReader reader, string tableName = null, IDbTransaction transaction = null, CancellationToken cancellationToken = default ) { //Get the Fields from Cache first (as this can't be done after Reader is opened... var tableNameForCache = string.IsNullOrWhiteSpace(tableName) ? ClassMappedNameCache.Get <TEntity>() : tableName; var dbSetting = dbConn.GetDbSetting(); var dbFieldsForCache = await DbFieldCache .GetAsync(dbConn, tableNameForCache, transaction, false, cancellationToken) .ConfigureAwait(false); //Initialize the RepoDb compiled entity mapping function (via Brute Force Proxy class; it's marked 'internal'. var functionCacheProxy = new RepoDbFunctionCacheProxy <TEntity>(); var repoDbMappingFunc = functionCacheProxy.GetDataReaderToDataEntityFunctionCompatible( reader, dbConn, transaction, true, dbFieldsForCache, dbSetting ) ?? throw new Exception($"Unable to retrieve the RepoDb entity mapping function for [{typeof(TEntity).Name}]."); return(repoDbMappingFunc); }
public void TestDbHelperForQueryAllViaTableName() { // Prepare var dbHelper = new Mock <IDbHelper>(); var connection = new DbHelperDbConnection(); // Setup dbHelper.Setup(builder => builder.GetFields( It.Is <IDbConnection>(s => s == connection), It.Is <string>(s => s == ClassMappedNameCache.Get <DbHelperDataEntity>()), It.IsAny <IDbTransaction>()) ).Returns(GetDbFields()); // Act CommandTextCache.Flush(); DbFieldCache.Flush(); DbHelperMapper.Add(typeof(DbHelperDbConnection), dbHelper.Object, true); // Act connection.QueryAll(ClassMappedNameCache.Get <DbHelperDataEntity>()); // Assert dbHelper.Verify(builder => builder.GetFields( It.Is <IDbConnection>(s => s == connection), It.Is <string>(s => s == ClassMappedNameCache.Get <DbHelperDataEntity>()), It.IsAny <IDbTransaction>()), Times.Exactly(1)); }
public void TestDbHelperForBatchQueryViaTableName() { // Prepare var dbHelper = GetMockedDbHelper(); var connection = new DbHelperDbConnection(); // Act CommandTextCache.Flush(); DbFieldCache.Flush(); DbHelperMapper.Add(typeof(DbHelperDbConnection), dbHelper.Object, true); // Act connection.BatchQuery(ClassMappedNameCache.Get <DbHelperDataEntity>(), 0, 10, OrderField.Ascending <DbHelperDataEntity>(e => e.Id).AsEnumerable(), new { Id = 1 }); // Assert dbHelper.Verify(builder => builder.GetFields( It.Is <IDbConnection>(s => s == connection), It.Is <string>(s => s == ClassMappedNameCache.Get <DbHelperDataEntity>()), It.IsAny <IDbTransaction>()), Times.Exactly(1)); }
public void TestDbHelperForBatchQuery() { // Prepare var dbHelper = new Mock <IDbHelper>(); var connection = new CustomDbConnectionForDbHelper(); // Setup dbHelper.Setup(builder => builder.GetFields( It.Is <IDbConnection>(s => s == connection), It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>()), It.IsAny <IDbTransaction>()) ).Returns(GetDbFields()); // Act CommandTextCache.Flush(); DbFieldCache.Flush(); StatementBuilderMapper.Add(typeof(CustomDbConnectionForDbHelper), new SqlServerStatementBuilder(), true); DbHelperMapper.Add(typeof(CustomDbConnectionForDbHelper), dbHelper.Object, true); // Act connection.BatchQuery <DataEntityForDbHelper>(0, 10, OrderField.Ascending <DataEntityForDbHelper>(e => e.Id).AsEnumerable(), e => e.Id == 1); // Assert dbHelper.Verify(builder => builder.GetFields( It.Is <IDbConnection>(s => s == connection), It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>()), It.IsAny <IDbTransaction>()), Times.Exactly(1)); }
public void TestDbHelperForUpdateAllViaTableName() { // Prepare var dbHelper = new Mock <IDbHelper>(); var connection = new CustomDbConnectionForDbHelper(); // Setup dbHelper.Setup(builder => builder.GetFields( It.Is <IDbConnection>(s => s == connection), It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>()), It.IsAny <IDbTransaction>()) ).Returns(GetDbFields()); // Act CommandTextCache.Flush(); DbFieldCache.Flush(); StatementBuilderMapper.Add(typeof(CustomDbConnectionForDbHelper), new SqlServerStatementBuilder(), true); DbHelperMapper.Add(typeof(CustomDbConnectionForDbHelper), dbHelper.Object, true); // Act connection.UpdateAll(ClassMappedNameCache.Get <DataEntityForDbHelper>(), new[] { new DataEntityForDbHelper { Id = 1, Name = "Name" } }); // Assert dbHelper.Verify(builder => builder.GetFields( It.Is <IDbConnection>(s => s == connection), It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>()), It.IsAny <IDbTransaction>()), Times.Exactly(1)); }
/// <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); }
/// <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 async Task <InsertExecutionContext <TEntity> > CreateAsync <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 = await DbFieldCache.GetAsync(connection, tableName, transaction); context = CreateInternal <TEntity>(connection, dbFields, tableName, fields, hints, transaction, statementBuilder); // Add to cache InsertExecutionContextCache.Add <TEntity>(key, context); // Return return(context); }
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> /// <param name="cancellationToken"></param> /// <returns></returns> public static async Task <UpdateAllExecutionContext> CreateAsync(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, CancellationToken cancellationToken = default) { 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 = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); var request = new UpdateAllRequest(tableName, connection, transaction, fields, qualifiers, batchSize, hints, statementBuilder); var commandText = await CommandTextCache.GetUpdateAllTextAsync(request, cancellationToken); // Call context = CreateInternal(entityType, connection, tableName, entities, dbFields, qualifiers, batchSize, fields, commandText); // Add to cache UpdateAllExecutionContextCache.Add(key, context); // Return return(context); }
/// <summary> /// database import as an asynchronous operation. /// </summary> /// <param name="parameters">The parameters.</param> /// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns> public async Task <ICollectionImportResult> DatabaseImportAsync(ModCollectionExporterParams parameters) { // Caching sucks in this ORM DbFieldCache.Flush(); FieldCache.Flush(); IdentityCache.Flush(); PrimaryCache.Flush(); if (await IsV4Async(parameters)) { return(await DatabaseImportv3Async(parameters)); } return(await DatabaseImportv2Async(parameters)); }
/// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <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 <TEntity> Create <TEntity>(IDbConnection connection, string tableName, IEnumerable <TEntity> entities, IEnumerable <Field> qualifiers, int batchSize, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null) where TEntity : class { var key = GetKey <TEntity>(tableName, qualifiers, fields, batchSize, hints); // Get from cache var context = UpdateAllExecutionContextCache.Get <TEntity>(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 <TEntity>(connection, tableName, entities, dbFields, qualifiers, batchSize, fields, commandText); // Add to cache UpdateAllExecutionContextCache.Add <TEntity>(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> /// <param name="cancellationToken"></param> /// <returns></returns> public static async Task <MergeExecutionContext <TEntity> > CreateAsync <TEntity>(IDbConnection connection, string tableName, IEnumerable <Field> qualifiers, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null, CancellationToken cancellationToken = default) 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 = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); var request = new MergeRequest(tableName, connection, transaction, fields, qualifiers, hints, statementBuilder); var commandText = await CommandTextCache.GetMergeTextAsync(request, cancellationToken); // Call context = CreateInternal <TEntity>(connection, dbFields, tableName, fields, commandText); // Add to cache MergeExecutionContextCache.Add <TEntity>(key, context); // Return return(context); }
/// <summary> /// /// </summary> /// <param name="entityType"></param> /// <param name="connection"></param> /// <param name="tableName"></param> /// <param name="fields"></param> /// <param name="hints"></param> /// <param name="transaction"></param> /// <param name="statementBuilder"></param> /// <param name="cancellationToken"></param> /// <returns></returns> public static async Task <InsertExecutionContext> CreateAsync(Type entityType, IDbConnection connection, string tableName, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null, CancellationToken cancellationToken = default) { var key = GetKey(entityType, tableName, fields, hints); // Get from cache var context = InsertExecutionContextCache.Get(key); if (context != null) { return(context); } // Create var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken); var request = new InsertRequest(tableName, connection, transaction, fields, hints, statementBuilder); var commandText = await CommandTextCache.GetInsertTextAsync(request, cancellationToken); // Call context = CreateInternal(entityType, connection, dbFields, tableName, fields, commandText); // Add to cache InsertExecutionContextCache.Add(entityType, key, context); // Return return(context); }
/// <summary> /// /// </summary> /// <param name="entityType"></param> /// <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 Create(Type entityType, IDbConnection connection, string tableName, IEnumerable <Field> qualifiers, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null) { var key = GetKey(entityType, tableName, qualifiers, fields, hints); // Get from cache var context = MergeExecutionContextCache.Get(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(entityType, connection, dbFields, tableName, fields, commandText); // Add to cache MergeExecutionContextCache.Add(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 TestDbHelperForQuery() { // Prepare var dbHelper = GetMockedDbHelper(); var connection = new DbHelperDbConnection(); // Act CommandTextCache.Flush(); DbFieldCache.Flush(); DbHelperMapper.Add(typeof(DbHelperDbConnection), dbHelper.Object, true); // Act connection.Query <DbHelperDataEntity>((object)null); // Assert dbHelper.Verify(builder => builder.GetFields( It.Is <IDbConnection>(s => s == connection), It.Is <string>(s => s == ClassMappedNameCache.Get <DbHelperDataEntity>()), It.IsAny <IDbTransaction>()), Times.Exactly(1)); }
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); } }
public void TestDbHelperForQueryAll() { // Prepare var dbHelper = new Mock <IDbHelper>(); var connection = new CustomDbConnectionForDbHelper(); // Act CommandTextCache.Flush(); DbFieldCache.Flush(); StatementBuilderMapper.Add(typeof(CustomDbConnectionForDbHelper), new SqlServerStatementBuilder(), true); DbHelperMapper.Add(typeof(CustomDbConnectionForDbHelper), dbHelper.Object, true); // Act connection.QueryAll <DataEntityForDbHelper>(); // Assert dbHelper.Verify(builder => builder.GetFields( It.Is <IDbConnection>(s => s == connection), It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>()), It.IsAny <IDbTransaction>()), Times.Exactly(1)); }
public void TestDbHelperForInsertAllViaTableName() { // Prepare var dbHelper = GetMockedDbHelper(); var connection = new DbHelperDbConnection(); // Act CommandTextCache.Flush(); DbFieldCache.Flush(); DbHelperMapper.Add(typeof(DbHelperDbConnection), dbHelper.Object, true); // Act connection.InsertAll(ClassMappedNameCache.Get <DbHelperDataEntity>(), new[] { new { Id = 1, Name = "Name" } }); // Assert dbHelper.Verify(builder => builder.GetFields( It.Is <IDbConnection>(s => s == connection), It.Is <string>(s => s == ClassMappedNameCache.Get <DbHelperDataEntity>()), It.IsAny <IDbTransaction>()), Times.Exactly(1)); }
public void TestDbHelperForInsert() { // Prepare var dbHelper = new Mock <IDbHelper>(); var connection = new CustomDbConnectionForDbHelper(); // Act CommandTextCache.Flush(); DbFieldCache.Flush(); StatementBuilderMapper.Add(typeof(CustomDbConnectionForDbHelper), new SqlStatementBuilder(), true); DbHelperMapper.Add(typeof(CustomDbConnectionForDbHelper), dbHelper.Object, true); // Act connection.Insert <DataEntityForDbHelper>(new DataEntityForDbHelper { Id = 1, Name = "Name" }); // Assert dbHelper.Verify(builder => builder.GetFields( It.Is <string>(s => s == connection.ConnectionString), It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>())), Times.Exactly(1)); }
/// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <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 <TEntity> Create <TEntity>(IDbConnection connection, string tableName, QueryGroup where, IEnumerable <Field> fields, string hints = null, IDbTransaction transaction = null, IStatementBuilder statementBuilder = null) where TEntity : class { var key = GetKey <TEntity>(tableName, fields, hints, where); // Get from cache var context = UpdateExecutionContextCache.Get <TEntity>(key); if (context != null) { return(context); } // Create var dbFields = DbFieldCache.Get(connection, tableName, transaction); context = CreateInternal <TEntity>(connection, tableName, where, dbFields, fields, hints, transaction, statementBuilder); // Add to cache UpdateExecutionContextCache.Add <TEntity>(key, context); // Return return(context); }
/// <summary> /// Bulk insert a list of data entity objects into the database. /// </summary> /// <typeparam name="TEntity">The type of the data entity object.</typeparam> /// <param name="connection">The connection object to be used.</param> /// <param name="entities">The list of the data entities to be bulk-inserted.</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 <TEntity>(IDbConnection connection, IEnumerable <TEntity> entities, IEnumerable <BulkInsertMapItem> mappings = null, SqlBulkCopyOptions options = SqlBulkCopyOptions.Default, int?bulkCopyTimeout = null, int?batchSize = null, IDbTransaction transaction = null) where TEntity : class { // 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 reader = new DataEntityDataReader <TEntity>(entities, connection)) { using (var sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection, options, (SqlTransaction)transaction)) { var dbSetting = connection.GetDbSetting(); // Set the destinationtable sqlBulkCopy.DestinationTableName = ClassMappedNameCache.Get <TEntity>(dbSetting); // 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, ClassMappedNameCache.Get <TEntity>(dbSetting), transaction); var fields = reader.Properties.AsFields(); 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.UnquotedName, dbField.UnquotedName, StringComparison.OrdinalIgnoreCase)); if (field != null) { filteredFields.Add(new Tuple <string, string>(field.UnquotedName, 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); }
/// <summary> /// Bulk insert an instance of <see cref="DbDataReader"/> object into the database in an asynchronous way. /// </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 async Task <int> BulkInsertAsync(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 = await DbFieldCache.GetAsync(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 await connection.EnsureOpenAsync(); await sqlBulkCopy.WriteToServerAsync(reader); // Hack the 'SqlBulkCopy' object var copiedField = GetRowsCopiedField(); // Set the return value result = copiedField != null ? (int)copiedField.GetValue(sqlBulkCopy) : reader.RecordsAffected; } // Result return(result); }
/// <summary> /// Bulk insert an instance of <see cref="DataTable"/> object into the database in an asynchronous way. /// </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 async Task <int> BulkInsertAsync(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 = await DbFieldCache.GetAsync(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(); await sqlBulkCopy.WriteToServerAsync(dataTable, rowState); // Set the return value result = GetDataRows(dataTable, rowState).Count(); } // Result return(result); }
/// <summary> /// /// </summary> /// <param name="entityType"></param> /// <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> /// <returns></returns> public static MergeAllExecutionContext Create(Type entityType, IDbConnection connection, IEnumerable <object> entities, string tableName, 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 = MergeAllExecutionContextCache.Get(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 MergeAllRequest(tableName, connection, transaction, fields, qualifiers, batchSize, hints, statementBuilder); commandText = CommandTextCache.GetMergeAllText(request); } else { var request = new MergeRequest(tableName, connection, transaction, fields, qualifiers, hints, statementBuilder); commandText = CommandTextCache.GetMergeText(request); } // Call context = CreateInternal(entityType, connection, entities, dbFields, tableName, qualifiers, batchSize, fields, commandText); // Add to cache MergeAllExecutionContextCache.Add(key, context); // Return return(context); }