示例#1
0
        /// <summary>
        /// Insert multiple rows in the table in an asynchronous way.
        /// </summary>
        /// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</typeparam>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="tableName">The name of the target table to be used.</param>
        /// <param name="entities">The list of data entity or dynamic objects to be inserted.</param>
        /// <param name="batchSize">The batch size of the insertion.</param>
        /// <param name="fields">The mapping list of <see cref="Field"/> objects to be used.</param>
        /// <param name="hints">The table hints to be used.</param>
        /// <param name="commandTimeout">The command timeout in seconds to be used.</param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <param name="trace">The trace object to be used.</param>
        /// <param name="statementBuilder">The statement builder object to be used.</param>
        /// <param name="skipIdentityCheck">True to skip the identity check.</param>
        /// <returns>The number of inserted rows in the table.</returns>
        internal static async Task <int> InsertAllAsyncInternalBase <TEntity>(this IDbConnection connection,
                                                                              string tableName,
                                                                              IEnumerable <TEntity> entities,
                                                                              int batchSize = Constant.DefaultBatchOperationSize,
                                                                              IEnumerable <Field> fields = null,
                                                                              string hints                       = null,
                                                                              int?commandTimeout                 = null,
                                                                              IDbTransaction transaction         = null,
                                                                              ITrace trace                       = null,
                                                                              IStatementBuilder statementBuilder = null,
                                                                              bool skipIdentityCheck             = false)
            where TEntity : class
        {
            // Variables needed
            var dbSetting = connection.GetDbSetting();

            // Guard the parameters
            GuardInsertAll(entities);

            // Validate the batch size
            batchSize = (dbSetting.IsMultiStatementExecutable == true) ? Math.Min(batchSize, entities.Count()) : 1;

            // Get the context
            var context = InsertAllExecutionContextProvider.Create <TEntity>(connection,
                                                                             tableName,
                                                                             batchSize,
                                                                             fields,
                                                                             hints,
                                                                             transaction,
                                                                             statementBuilder,
                                                                             skipIdentityCheck);
            var sessionId = Guid.Empty;

            // Before Execution
            if (trace != null)
            {
                sessionId = Guid.NewGuid();
                var cancellableTraceLog = new CancellableTraceLog(sessionId, context.CommandText, entities, null);
                trace.BeforeInsertAll(cancellableTraceLog);
                if (cancellableTraceLog.IsCancelled)
                {
                    if (cancellableTraceLog.IsThrowException)
                    {
                        throw new CancelledExecutionException(context.CommandText);
                    }
                    return(0);
                }
                context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText);
                entities            = (IEnumerable <TEntity>)(cancellableTraceLog.Parameter ?? entities);
            }

            // Before Execution Time
            var beforeExecutionTime = DateTime.UtcNow;

            // Execution variables
            var result = 0;

            // Make sure to create transaction if there is no passed one
            var hasTransaction = (transaction != null || Transaction.Current != null);

            try
            {
                // Ensure the connection is open
                await connection.EnsureOpenAsync();

                if (hasTransaction == false)
                {
                    // Create a transaction
                    transaction = connection.BeginTransaction();
                }

                // Create the command
                using (var command = (DbCommand)connection.CreateCommand(context.CommandText,
                                                                         CommandType.Text, commandTimeout, transaction))
                {
                    // Directly execute if the entities is only 1 (performance)
                    if (context.BatchSize == 1)
                    {
                        foreach (var entity in entities.AsList())
                        {
                            // Set the values
                            context.SingleDataEntityParametersSetterFunc?.Invoke(command, entity);

                            // Prepare the command
                            if (dbSetting.IsPreparable)
                            {
                                command.Prepare();
                            }

                            // Actual Execution
                            var returnValue = Converter.DbNullToNull(await command.ExecuteScalarAsync());

                            // Get explicity if needed
                            if (Equals(returnValue, null) == true && dbSetting.IsMultiStatementExecutable == false)
                            {
                                returnValue = Converter.DbNullToNull(await connection.GetDbHelper().GetScopeIdentityAsync(connection, transaction));
                            }

                            // Set the return value
                            if (returnValue != null)
                            {
                                context.IdentityPropertySetterFunc?.Invoke(entity, returnValue);
                            }

                            // Iterate the result
                            result++;
                        }
                    }
                    else
                    {
                        foreach (var batchEntities in entities.AsList().Split(batchSize))
                        {
                            var batchItems = batchEntities.AsList();

                            // Break if there is no more records
                            if (batchItems.Count <= 0)
                            {
                                break;
                            }

                            // Check if the batch size has changed (probably the last batch on the enumerables)
                            if (batchItems.Count != batchSize)
                            {
                                // Get a new execution context from cache
                                context = await InsertAllExecutionContextProvider.CreateAsync <TEntity>(connection,
                                                                                                        tableName,
                                                                                                        batchItems.Count,
                                                                                                        fields,
                                                                                                        hints,
                                                                                                        transaction,
                                                                                                        statementBuilder,
                                                                                                        skipIdentityCheck);

                                // Set the command properties
                                command.CommandText = context.CommandText;
                            }

                            // Set the values
                            if (batchItems?.Count == 1)
                            {
                                context.SingleDataEntityParametersSetterFunc?.Invoke(command, batchItems.First());
                            }
                            else
                            {
                                context.MultipleDataEntitiesParametersSetterFunc?.Invoke(command, batchItems);
                            }

                            // Prepare the command
                            if (dbSetting.IsPreparable)
                            {
                                command.Prepare();
                            }

                            // Actual Execution
                            if (context.IdentityPropertySetterFunc == null)
                            {
                                result += await command.ExecuteNonQueryAsync();
                            }
                            else
                            {
                                using (var reader = await command.ExecuteReaderAsync())
                                {
                                    var index = 0;

                                    // Get the results
                                    do
                                    {
                                        if (await reader.ReadAsync())
                                        {
                                            var value = Converter.DbNullToNull(reader.GetValue(0));
                                            context.IdentityPropertySetterFunc.Invoke(batchItems[index], value);
                                            result++;
                                        }
                                        index++;
                                    }while (await reader.NextResultAsync());
                                }
                            }
                        }
                    }
                }

                if (hasTransaction == false)
                {
                    // Commit the transaction
                    transaction.Commit();
                }
            }
            catch
            {
                if (hasTransaction == false)
                {
                    // Rollback for any exception
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (hasTransaction == false)
                {
                    // Rollback and dispose the transaction
                    transaction.Dispose();
                }
            }

            // After Execution
            if (trace != null)
            {
                trace.AfterInsertAll(new TraceLog(sessionId, context.CommandText, entities, result,
                                                  DateTime.UtcNow.Subtract(beforeExecutionTime)));
            }

            // Return the result
            return(result);
        }
示例#2
0
        /// <summary>
        /// Inserts multiple data in the database in an asynchronous way.
        /// </summary>
        /// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</typeparam>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="tableName">The name of the target table to be used.</param>
        /// <param name="entities">The list of data entity or dynamic objects to be inserted.</param>
        /// <param name="batchSize">The batch size of the insertion.</param>
        /// <param name="fields">The mapping list of <see cref="Field"/> objects to be used.</param>
        /// <param name="commandTimeout">The command timeout in seconds to be used.</param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <param name="trace">The trace object to be used.</param>
        /// <param name="statementBuilder">The statement builder object to be used.</param>
        /// <param name="skipIdentityCheck">True to skip the identity check.</param>
        /// <returns>The number of inserted rows.</returns>
        internal static async Task <int> InsertAllAsyncInternalBase <TEntity>(this IDbConnection connection,
                                                                              string tableName,
                                                                              IEnumerable <TEntity> entities,
                                                                              int batchSize = Constant.DefaultBatchOperationSize,
                                                                              IEnumerable <Field> fields = null,
                                                                              int?commandTimeout         = null,
                                                                              IDbTransaction transaction = null,
                                                                              ITrace trace = null,
                                                                              IStatementBuilder statementBuilder = null,
                                                                              bool skipIdentityCheck             = false)
            where TEntity : class
        {
            // Guard the parameters
            var count = GuardInsertAll(entities);

            // Validate the batch size
            batchSize = Math.Min(batchSize, count);

            // Get the function
            var callback = new Func <int, InsertAllExecutionContext <TEntity> >((int batchSizeValue) =>
            {
                // Variables needed
                var identity        = (Field)null;
                var dbFields        = DbFieldCache.Get(connection, tableName);
                var inputFields     = (IEnumerable <DbField>)null;
                var outputFields    = (IEnumerable <DbField>)null;
                var identityDbField = dbFields?.FirstOrDefault(f => f.IsIdentity);

                // Set the identity value
                if (skipIdentityCheck == false)
                {
                    identity = IdentityCache.Get <TEntity>()?.AsField();
                    if (identity == null && identityDbField != null)
                    {
                        identity = FieldCache.Get <TEntity>().FirstOrDefault(field =>
                                                                             field.UnquotedName.ToLower() == identityDbField.UnquotedName.ToLower());
                    }
                }

                // Filter the actual properties for input fields
                inputFields = dbFields?
                              .Where(dbField => dbField.IsIdentity == false)
                              .Where(dbField =>
                                     fields.FirstOrDefault(field => field.UnquotedName.ToLower() == dbField.UnquotedName.ToLower()) != null)
                              .AsList();

                // Set the output fields
                if (batchSizeValue > 1)
                {
                    outputFields = identityDbField?.AsEnumerable();
                }

                // Variables for the context
                var multipleEntitiesFunc = (Action <DbCommand, IList <TEntity> >)null;
                var identitySettersFunc  = (List <Action <TEntity, DbCommand> >)null;
                var singleEntityFunc     = (Action <DbCommand, TEntity>)null;
                var identitySetterFunc   = (Action <TEntity, object>)null;

                // Get if we have not skipped it
                if (skipIdentityCheck == false && identity != null)
                {
                    if (batchSizeValue <= 1)
                    {
                        identitySetterFunc = FunctionCache.GetDataEntityPropertyValueSetterFunction <TEntity>(identity);
                    }
                    else
                    {
                        identitySettersFunc = new List <Action <TEntity, DbCommand> >();
                        for (var index = 0; index < batchSizeValue; index++)
                        {
                            identitySettersFunc.Add(FunctionCache.GetDataEntityPropertySetterFromDbCommandParameterFunction <TEntity>(identity, identity.UnquotedName, index));
                        }
                    }
                }

                // Identity which objects to set
                if (batchSizeValue <= 1)
                {
                    singleEntityFunc = FunctionCache.GetDataEntityDbCommandParameterSetterFunction <TEntity>(
                        string.Concat(typeof(TEntity).FullName, ".", tableName, ".InsertAll"),
                        inputFields?.AsList(),
                        null);
                }
                else
                {
                    multipleEntitiesFunc = FunctionCache.GetDataEntitiesDbCommandParameterSetterFunction <TEntity>(
                        string.Concat(typeof(TEntity).FullName, ".", tableName, ".InsertAll"),
                        inputFields?.AsList(),
                        outputFields,
                        batchSizeValue);
                }

                // Identify the requests
                var insertAllRequest = (InsertAllRequest)null;
                var insertRequest    = (InsertRequest)null;

                // Create a different kind of requests
                if (typeof(TEntity) == typeof(object))
                {
                    if (batchSizeValue > 1)
                    {
                        insertAllRequest = new InsertAllRequest(tableName,
                                                                connection,
                                                                fields,
                                                                batchSizeValue,
                                                                statementBuilder);
                    }
                    else
                    {
                        insertRequest = new InsertRequest(tableName,
                                                          connection,
                                                          fields,
                                                          statementBuilder);
                    }
                }
                else
                {
                    if (batchSizeValue > 1)
                    {
                        insertAllRequest = new InsertAllRequest(typeof(TEntity),
                                                                connection,
                                                                fields,
                                                                batchSizeValue,
                                                                statementBuilder);
                    }
                    else
                    {
                        insertRequest = new InsertRequest(typeof(TEntity),
                                                          connection,
                                                          fields,
                                                          statementBuilder);
                    }
                }

                // Return the value
                return(new InsertAllExecutionContext <TEntity>
                {
                    CommandText = batchSizeValue > 1 ? CommandTextCache.GetInsertAllText(insertAllRequest) : CommandTextCache.GetInsertText(insertRequest),
                    InputFields = inputFields,
                    OutputFields = outputFields,
                    BatchSize = batchSizeValue,
                    SingleDataEntityParametersSetterFunc = singleEntityFunc,
                    MultipleDataEntitiesParametersSetterFunc = multipleEntitiesFunc,
                    IdentityPropertySetterFunc = identitySetterFunc,
                    IdentityPropertySettersFunc = identitySettersFunc
                });
            });

            // Get the context
            var context = (InsertAllExecutionContext <TEntity>)null;

            // Identify the number of entities (performance), get an execution context from cache
            context = batchSize == 1 ? InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, 1, callback) :
                      InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, batchSize, callback);

            // Before Execution
            if (trace != null)
            {
                var cancellableTraceLog = new CancellableTraceLog(context.CommandText, entities, null);
                trace.BeforeInsertAll(cancellableTraceLog);
                if (cancellableTraceLog.IsCancelled)
                {
                    if (cancellableTraceLog.IsThrowException)
                    {
                        throw new CancelledExecutionException(context.CommandText);
                    }
                    return(0);
                }
                context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText);
                entities            = (IEnumerable <TEntity>)(cancellableTraceLog.Parameter ?? entities);
            }

            // Before Execution Time
            var beforeExecutionTime = DateTime.UtcNow;

            // Execution variables
            var result = 0;

            // Make sure to create transaction if there is no passed one
            var hasTransaction = (transaction != null);

            try
            {
                // Ensure the connection is open
                await connection.EnsureOpenAsync();

                if (hasTransaction == false)
                {
                    // Create a transaction
                    transaction = connection.BeginTransaction();
                }

                // Create the command
                using (var command = (DbCommand)connection.CreateCommand(context.CommandText,
                                                                         CommandType.Text, commandTimeout, transaction))
                {
                    // Directly execute if the entities is only 1 (performance)
                    if (context.BatchSize == 1)
                    {
                        foreach (var entity in entities)
                        {
                            // Set the values
                            context.SingleDataEntityParametersSetterFunc(command, entity);

                            // Actual Execution
                            var returnValue = ObjectConverter.DbNullToNull(await command.ExecuteScalarAsync());

                            // Set the return value
                            if (returnValue != null)
                            {
                                context.IdentityPropertySetterFunc?.Invoke(entity, returnValue);
                            }

                            // Iterate the result
                            result++;
                        }
                    }
                    else
                    {
                        foreach (var batchEntities in entities.Split(batchSize))
                        {
                            var batchItems = batchEntities.AsList();

                            // Break if there is no more records
                            if (batchItems.Count <= 0)
                            {
                                break;
                            }

                            // Check if the batch size has changed (probably the last batch on the enumerables)
                            if (batchItems.Count != batchSize)
                            {
                                // Get a new execution context from cache
                                context = InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, batchItems.Count, callback);

                                // Set the command properties
                                command.CommandText = context.CommandText;

                                // Prepare the command
                                command.Prepare();
                            }

                            // Set the values
                            context.MultipleDataEntitiesParametersSetterFunc(command, batchItems);

                            // Actual Execution
                            result += await command.ExecuteNonQueryAsync();

                            // Set the identities
                            if (context.IdentityPropertySettersFunc != null && command.Parameters.Count > 0)
                            {
                                for (var index = 0; index < batchItems.Count; index++)
                                {
                                    var func = context.IdentityPropertySettersFunc.ElementAt(index);
                                    func(batchItems[index], command);
                                }
                            }
                        }
                    }
                }

                if (hasTransaction == false)
                {
                    // Commit the transaction
                    transaction.Commit();
                }
            }
            catch
            {
                if (hasTransaction == false)
                {
                    // Rollback for any exception
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (hasTransaction == false)
                {
                    // Rollback and dispose the transaction
                    transaction.Dispose();
                }
            }

            // After Execution
            if (trace != null)
            {
                trace.AfterInsertAll(new TraceLog(context.CommandText, entities, result,
                                                  DateTime.UtcNow.Subtract(beforeExecutionTime)));
            }

            // Return the result
            return(result);
        }