Example #1
0
        /// <summary>
        /// Creates a new instance of <see cref="DataEntityDataReader{TEntity}"/> object.
        /// </summary>
        /// <param name="entities">The list of the data entity object to be used for manipulation.</param>
        /// <param name="connection">The actual <see cref="IDbConnection"/> object used.</param>
        /// <param name="transaction">The transaction object that is currently in used.</param>
        public DataEntityDataReader(IEnumerable <TEntity> entities, IDbConnection connection, IDbTransaction transaction)
        {
            if (entities == null)
            {
                throw new NullReferenceException("The entities could not be null.");
            }

            // Fields
            m_isClosed        = false;
            m_isDisposed      = false;
            m_position        = -1;
            m_recordsAffected = -1;

            // Properties
            if (connection != null)
            {
                var fields = DbFieldCache.Get(connection, ClassMappedNameCache.Get <TEntity>(), transaction);
                if (fields?.Any() == true)
                {
                    Properties = PropertyCache.Get <TEntity>()
                                 .Where(p => fields.FirstOrDefault(f => string.Equals(f.UnquotedName, p.GetUnquotedMappedName(), StringComparison.OrdinalIgnoreCase)) != null)
                                 .AsList();
                }
            }
            if (Properties == null)
            {
                Properties = PropertyCache.Get <TEntity>().AsList();
            }
            Enumerator = entities.GetEnumerator();
            Entities   = entities;
            FieldCount = Properties.Count;
        }
Example #2
0
        /// <summary>
        /// Query all the data from the table in an asynchronous way.
        /// </summary>
        /// <typeparam name="TEntity">The type of the data entity.</typeparam>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="tableName">The name of the target table.</param>
        /// <param name="fields">The mapping list of <see cref="Field"/> objects to be used.</param>
        /// <param name="orderBy">The order definition of the fields to be used.</param>
        /// <param name="hints">The table hints to be used.</param>
        /// <param name="cacheKey">
        /// The key to the cache item.By setting this argument, it will return the item from the cache if present, otherwise it will query the database.
        /// This will only work if the 'cache' argument is set.
        /// </param>
        /// <param name="cacheItemExpiration">The expiration in minutes of the cache item.</param>
        /// <param name="commandTimeout">The command timeout in seconds to be used.</param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <param name="cache">The cache object 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>
        /// <returns>An enumerable list of data entity objects.</returns>
        internal static async Task <IEnumerable <TEntity> > QueryAllAsyncInternal <TEntity>(this IDbConnection connection,
                                                                                            string tableName,
                                                                                            IEnumerable <Field> fields       = null,
                                                                                            IEnumerable <OrderField> orderBy = null,
                                                                                            string hints                       = null,
                                                                                            string cacheKey                    = null,
                                                                                            int?cacheItemExpiration            = Constant.DefaultCacheItemExpirationInMinutes,
                                                                                            int?commandTimeout                 = null,
                                                                                            IDbTransaction transaction         = null,
                                                                                            ICache cache                       = null,
                                                                                            ITrace trace                       = null,
                                                                                            IStatementBuilder statementBuilder = null)
            where TEntity : class
        {
            // Ensure the fields
            fields = GetQualifiedFields <TEntity>(fields) ??
                     (await DbFieldCache.GetAsync(connection, tableName, transaction))?.AsFields();

            // Return
            return(await QueryAllAsyncInternalBase <TEntity>(connection : connection,
                                                             tableName : tableName,
                                                             fields : fields,
                                                             orderBy : orderBy,
                                                             hints : hints,
                                                             cacheKey : cacheKey,
                                                             cacheItemExpiration : cacheItemExpiration,
                                                             commandTimeout : commandTimeout,
                                                             transaction : transaction,
                                                             cache : cache,
                                                             trace : trace,
                                                             statementBuilder : statementBuilder));
        }
Example #3
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TPrimaryKey"></typeparam>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        /// <param name="primaryKeys"></param>
        /// <param name="bulkCopyTimeout"></param>
        /// <param name="batchSize"></param>
        /// <param name="pseudoTableType"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        private static int BinaryBulkDeleteByKeyBase <TPrimaryKey>(this NpgsqlConnection connection,
                                                                   string tableName,
                                                                   IEnumerable <TPrimaryKey> primaryKeys,
                                                                   int?bulkCopyTimeout = null,
                                                                   int?batchSize       = null,
                                                                   BulkImportPseudoTableType pseudoTableType = default,
                                                                   NpgsqlTransaction transaction             = null)
        {
            var identityBehavior = BulkImportIdentityBehavior.Unspecified;
            var dbSetting        = connection.GetDbSetting();
            var dbFields         = DbFieldCache.Get(connection, tableName, transaction);
            var primaryKey       = dbFields.FirstOrDefault(dbField => dbField.IsPrimary);
            var pseudoTableName  = tableName;
            IEnumerable <NpgsqlBulkInsertMapItem> mappings = null;

            return(PseudoBasedBinaryImport(connection,
                                           tableName,
                                           bulkCopyTimeout,
                                           dbFields,

                                           // getPseudoTableName
                                           () =>
                                           pseudoTableName = GetBinaryBulkDeleteByKeyPseudoTableName(tableName ?? ClassMappedNameCache.Get <TPrimaryKey>(), dbSetting),

                                           // getMappings
                                           () =>
                                           mappings = new[]
            {
                new NpgsqlBulkInsertMapItem(primaryKey.Name, primaryKey.Name)
            },

                                           // binaryImport
                                           (tableName) =>
                                           connection.BinaryImport(tableName,
                                                                   GetExpandoObjectData(primaryKeys, primaryKey.AsField()),
                                                                   mappings,
                                                                   dbFields,
                                                                   bulkCopyTimeout,
                                                                   batchSize,
                                                                   identityBehavior,
                                                                   dbSetting,
                                                                   transaction),

                                           // getDeleteToPseudoCommandText
                                           () =>
                                           GetDeleteByKeyCommandText(pseudoTableName,
                                                                     tableName,
                                                                     dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(),
                                                                     dbSetting),

                                           // setIdentities
                                           null,

                                           null,
                                           false,
                                           identityBehavior,
                                           pseudoTableType,
                                           dbSetting,
                                           transaction));
        }
Example #4
0
        /// <summary>
        /// Inserts multiple data in the database in an asynchronous way. By default, the database fields are used unless the 'fields' argument is defined.
        /// </summary>
        /// <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 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>
        /// <returns>The number of inserted rows.</returns>
        internal static Task <int> InsertAllAsyncInternal(this IDbConnection connection,
                                                          string tableName,
                                                          IEnumerable <object> entities,
                                                          int batchSize = Constant.DefaultBatchOperationSize,
                                                          IEnumerable <Field> fields = null,
                                                          int?commandTimeout         = null,
                                                          IDbTransaction transaction = null,
                                                          ITrace trace = null,
                                                          IStatementBuilder statementBuilder = null)
        {
            // Check the fields
            if (fields == null)
            {
                fields = DbFieldCache.Get(connection, tableName)?.AsFields();
            }

            // Return the result
            return(InsertAllAsyncInternalBase <object>(connection: connection,
                                                       tableName: tableName,
                                                       entities: entities,
                                                       batchSize: batchSize,
                                                       fields: fields,
                                                       commandTimeout: commandTimeout,
                                                       transaction: transaction,
                                                       trace: trace,
                                                       statementBuilder: statementBuilder,
                                                       skipIdentityCheck: true));
        }
Example #5
0
 /// <summary>
 /// Gets the identity <see cref="DbField"/> object.
 /// </summary>
 /// <param name="request">The request object.</param>
 /// <returns>The identity <see cref="DbField"/> object.</returns>
 private static DbField GetIdentityField(BaseRequest request)
 {
     if (request.Type != null && request.Type != typeof(object))
     {
         var identityProperty = IdentityCache.Get(request.Type);
         if (identityProperty != null)
         {
             var primaryProperty = PrimaryCache.Get(request.Type);
             var isPrimary       = false;
             if (primaryProperty != null)
             {
                 isPrimary = string.Equals(primaryProperty.GetMappedName(), identityProperty.GetMappedName(), StringComparison.OrdinalIgnoreCase);
             }
             return(new DbField(identityProperty.GetMappedName(),
                                isPrimary,
                                true,
                                false,
                                identityProperty.PropertyInfo.PropertyType,
                                null,
                                null,
                                null,
                                null));
         }
     }
     return(DbFieldCache.Get(request.Connection, request.Name, request.Transaction)?.FirstOrDefault(f => f.IsIdentity));
 }
Example #6
0
        /// <summary>
        /// Count the number of rows from the table in an asynchronous way.
        /// </summary>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="request">The actual <see cref="CountAllRequest"/> object.</param>
        /// <param name="param">The mapped object parameters.</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="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
        /// <returns>An integer value that holds the number of rows from the table.</returns>
        internal static async Task <long> CountAllAsyncInternalBase(this IDbConnection connection,
                                                                    CountAllRequest request,
                                                                    object param,
                                                                    int?commandTimeout         = null,
                                                                    IDbTransaction transaction = null,
                                                                    ITrace trace = null,
                                                                    CancellationToken cancellationToken = default)
        {
            // Variables
            var commandType = CommandType.Text;
            var commandText = CommandTextCache.GetCountAllText(request);
            var sessionId   = Guid.Empty;

            // Before Execution
            if (trace != null)
            {
                sessionId = Guid.NewGuid();
                var cancellableTraceLog = new CancellableTraceLog(sessionId, commandText, param, null);
                trace.BeforeCountAll(cancellableTraceLog);
                if (cancellableTraceLog.IsCancelled)
                {
                    if (cancellableTraceLog.IsThrowException)
                    {
                        throw new CancelledExecutionException(commandText);
                    }
                    return(default(int));
                }
                commandText = (cancellableTraceLog.Statement ?? commandText);
                param       = (cancellableTraceLog.Parameter ?? param);
            }

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

            // Actual Execution
            var result = await ExecuteScalarAsyncInternal <long>(connection : connection,
                                                                 commandText : commandText,
                                                                 param : param,
                                                                 commandType : commandType,
                                                                 cacheKey : null,
                                                                 cacheItemExpiration : null,
                                                                 commandTimeout : commandTimeout,
                                                                 transaction : transaction,
                                                                 cache : null,
                                                                 cancellationToken : cancellationToken,
                                                                 entityType : request.Type,
                                                                 dbFields : await DbFieldCache.GetAsync(connection, request.Name, transaction, true, cancellationToken),
                                                                 skipCommandArrayParametersCheck : true);

            // After Execution
            if (trace != null)
            {
                trace.AfterCountAll(new TraceLog(sessionId, commandText, param, result,
                                                 DateTime.UtcNow.Subtract(beforeExecutionTime)));
            }

            // Result
            return(result);
        }
Example #7
0
        /// <summary>
        /// Computes the sum value of the target field.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="request">The actual <see cref="SumAllRequest"/> object.</param>
        /// <param name="param">The mapped object parameters.</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>
        /// <returns>The sum value of the target field.</returns>
        internal static TResult SumAllInternalBase <TResult>(this IDbConnection connection,
                                                             SumAllRequest request,
                                                             object param,
                                                             int?commandTimeout         = null,
                                                             IDbTransaction transaction = null,
                                                             ITrace trace = null)
        {
            // Variables
            var commandType = CommandType.Text;
            var commandText = CommandTextCache.GetSumAllText(request);
            var sessionId   = Guid.Empty;

            // Before Execution
            if (trace != null)
            {
                sessionId = Guid.NewGuid();
                var cancellableTraceLog = new CancellableTraceLog(sessionId, commandText, param, null);
                trace.BeforeSumAll(cancellableTraceLog);
                if (cancellableTraceLog.IsCancelled)
                {
                    if (cancellableTraceLog.IsThrowException)
                    {
                        throw new CancelledExecutionException(commandText);
                    }
                    return(default(TResult));
                }
                commandText = (cancellableTraceLog.Statement ?? commandText);
                param       = (cancellableTraceLog.Parameter ?? param);
            }

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

            // Actual Execution
            var result = ExecuteScalarInternal <TResult>(connection: connection,
                                                         commandText: commandText,
                                                         param: param,
                                                         commandType: commandType,
                                                         commandTimeout: commandTimeout,
                                                         transaction: transaction,
                                                         entityType: request.Type,
                                                         dbFields: DbFieldCache.Get(connection, request.Name, transaction, true),
                                                         skipCommandArrayParametersCheck: true);

            // After Execution
            if (trace != null)
            {
                trace.AfterSumAll(new TraceLog(sessionId, commandText, param, result,
                                               DateTime.UtcNow.Subtract(beforeExecutionTime)));
            }

            // Result
            return(result);
        }
Example #8
0
        /// <summary>
        /// Truncates a table from the database in an asychronous way.
        /// </summary>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="request">The actual <see cref="TruncateRequest"/> object.</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="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
        /// <returns>The number of rows affected.</returns>
        internal static async Task <int> TruncateAsyncInternalBase(this IDbConnection connection,
                                                                   TruncateRequest request,
                                                                   int?commandTimeout         = null,
                                                                   IDbTransaction transaction = null,
                                                                   ITrace trace = null,
                                                                   CancellationToken cancellationToken = default)
        {
            // Variables
            var commandType = CommandType.Text;
            var commandText = CommandTextCache.GetTruncateText(request);
            var sessionId   = Guid.Empty;

            // Before Execution
            if (trace != null)
            {
                sessionId = Guid.NewGuid();
                var cancellableTraceLog = new CancellableTraceLog(sessionId, commandText, null, null);
                trace.BeforeTruncate(cancellableTraceLog);
                if (cancellableTraceLog.IsCancelled)
                {
                    if (cancellableTraceLog.IsThrowException)
                    {
                        throw new CancelledExecutionException(commandText);
                    }
                }
                commandText = (cancellableTraceLog.Statement ?? commandText);
            }

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

            // Actual Execution
            var result = await ExecuteNonQueryAsyncInternal(connection : connection,
                                                            commandText : commandText,
                                                            param : null,
                                                            commandType : commandType,
                                                            commandTimeout : commandTimeout,
                                                            transaction : transaction,
                                                            cancellationToken : cancellationToken,
                                                            entityType : request.Type,
                                                            dbFields : await DbFieldCache.GetAsync(connection, request.Name, transaction, true, cancellationToken),
                                                            skipCommandArrayParametersCheck : true);

            // After Execution
            if (trace != null)
            {
                trace.AfterTruncate(new TraceLog(sessionId, commandText, null, result,
                                                 DateTime.UtcNow.Subtract(beforeExecutionTime)));
            }

            // Return the result
            return(result);
        }
Example #9
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        private static IEnumerable <Field> GetActualFields(IDbConnection connection,
                                                           string tableName,
                                                           IEnumerable <Field> fields,
                                                           IDbTransaction transaction)
        {
            if (fields?.Any() != true)
            {
                return(null);
            }
            var dbFields = DbFieldCache.Get(connection, tableName, transaction);

            return(GetActualFieldsInternal(fields, dbFields, connection.GetDbSetting()));
        }
Example #10
0
        /// <summary>
        /// Initializes the current instance of <see cref="DataEntityDataReader{TEntity}"/> object.
        /// </summary>
        public void Initialize()
        {
            if (IsInitialized)
            {
                return;
            }
            var dbFields = (IEnumerable <DbField>)null;

            if (Connection != null)
            {
                dbFields = DbFieldCache.Get(Connection, ClassMappedNameCache.Get <TEntity>(), Transaction);
            }
            InitializeInternal(dbFields);
        }
Example #11
0
        /// <summary>
        /// Initializes the current instance of <see cref="DataEntityDataReader{TEntity}"/> object.
        /// </summary>
        public void Initialize()
        {
            if (IsInitialized)
            {
                return;
            }
            var dbFields = (IEnumerable <DbField>)null;

            if (Connection != null)
            {
                dbFields = DbFieldCache.Get(Connection, TableName, Transaction, false);
            }
            InitializeInternal(dbFields);
        }
Example #12
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="transaction"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        private static async Task <IEnumerable <Field> > GetActualFieldsAsync(IDbConnection connection,
                                                                              string tableName,
                                                                              IEnumerable <Field> fields,
                                                                              IDbTransaction transaction,
                                                                              CancellationToken cancellationToken = default)
        {
            if (fields?.Any() != true)
            {
                return(null);
            }
            var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);

            return(GetActualFieldsInternal(fields, dbFields, connection.GetDbSetting()));
        }
Example #13
0
        /// <summary>
        /// Initializes the current instance of <see cref="DataEntityDataReader{TEntity}"/> object.
        /// </summary>
        /// <param name="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
        public async Task InitializeAsync(CancellationToken cancellationToken = default)
        {
            if (IsInitialized)
            {
                return;
            }
            var dbFields = (IEnumerable <DbField>)null;

            if (Connection != null)
            {
                dbFields = await DbFieldCache.GetAsync(Connection, TableName, Transaction, false, cancellationToken);
            }
            InitializeInternal(dbFields);
        }
Example #14
0
        /// <summary>
        /// Initializes the current instance of <see cref="DataEntityDataReader{TEntity}"/> object.
        /// </summary>
        public async Task InitializeAsync()
        {
            if (IsInitialized)
            {
                return;
            }
            var dbFields = (IEnumerable <DbField>)null;

            if (Connection != null)
            {
                dbFields = await DbFieldCache.GetAsync(Connection, ClassMappedNameCache.Get <TEntity>(), Transaction);
            }
            InitializeInternal(dbFields);
        }
Example #15
0
        /// <summary>
        /// Get the actual list of <see cref="Field"/> objects of the table based on the actual list of <see cref="DbField"/> objects.
        /// </summary>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="fields">The target name of the table.</param>
        /// <param name="tableName">The list of fields from the data entity object.</param>
        /// <returns>The actual list of <see cref="Field"/> objects of the table.</returns>
        private static IEnumerable <Field> GetActualFields(IDbConnection connection, string tableName, IEnumerable <Field> fields)
        {
            if (fields?.Any() != true)
            {
                return(null);
            }

            // Get all the fields from the database
            var dbFields = DbFieldCache.Get(connection, tableName);

            // Return the filtered one
            return(dbFields?.Any() == true?
                   fields.Where(f => dbFields.FirstOrDefault(df => df.UnquotedName.ToLower() == f.UnquotedName.ToLower()) != null) :
                       fields);
        }
Example #16
0
        /// <summary>
        /// Get the actual list of <see cref="Field"/> objects of the table based on the actual list of <see cref="DbField"/> objects.
        /// </summary>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="fields">The target name of the table.</param>
        /// <param name="tableName">The list of fields from the data entity object.</param>
        /// <param name="transaction">The transaction object that is currently in used.</param>
        /// <returns>The actual list of <see cref="Field"/> objects of the table.</returns>
        private static IEnumerable <Field> GetActualFields(IDbConnection connection, string tableName, IEnumerable <Field> fields, IDbTransaction transaction)
        {
            if (fields?.Any() != true)
            {
                return(null);
            }

            // Get all the fields from the database
            var dbFields = DbFieldCache.Get(connection, tableName, transaction);

            // Return the filtered one
            return(dbFields?.Any() == true?
                   fields.Where(f => dbFields.FirstOrDefault(df => string.Equals(df.UnquotedName, f.UnquotedName, StringComparison.OrdinalIgnoreCase)) != null) :
                       fields);
        }
Example #17
0
        /// <summary>
        /// Ensures that the <see cref="DbFieldCache.Get(IDbConnection, string, IDbTransaction)"/> method is being called one.
        /// </summary>
        /// <typeparam name="TEntity">The type of the entity to check.</typeparam>
        /// <param name="transaction">The transaction object that is currently in used.</param>
        private void EnsureSingleCallForDbFieldCacheGet <TEntity>(IDbTransaction transaction)
            where TEntity : class
        {
            var key      = GetDbFieldGetCallsCacheKey <TEntity>();
            var dbFields = (IEnumerable <DbField>)null;

            // Try get the value
            if (cache.TryGetValue(key, out dbFields) == false)
            {
                using (var connection = (IDbConnection)Activator.CreateInstance(this.connection.GetType(), new object[] { connectionString }))
                {
                    dbFields = DbFieldCache.Get(connection, ClassMappedNameCache.Get <TEntity>(), transaction, false);
                    cache.TryAdd(key, dbFields);
                }
            }
        }
Example #18
0
        /// <summary>
        /// Ensures that the <see cref="DbFieldCache.GetAsync(IDbConnection, string, IDbTransaction)"/> method is being called one.
        /// </summary>
        /// <typeparam name="TEntity">The type of the entity to check.</typeparam>
        /// <param name="transaction">The transaction object that is currently in used.</param>
        private async Task EnsureSingleCallForDbFieldCacheGeAsync <TEntity>(IDbTransaction transaction)
            where TEntity : class
        {
            var key      = GetDbFieldGetCallsCacheKey <TEntity>();
            var dbFields = (IEnumerable <DbField>)null;

            // Try get the value
            if (m_cache.TryGetValue(key, out dbFields) == false)
            {
                using (var connection = (IDbConnection)Activator.CreateInstance(m_connection.GetType(), new object[] { m_connectionString }))
                {
                    dbFields = await DbFieldCache.GetAsync(connection, ClassMappedNameCache.Get <TEntity>(connection.GetDbSetting()), transaction);

                    m_cache.TryAdd(key, dbFields);
                }
            }
        }
Example #19
0
 /// <summary>
 /// Gets the identity <see cref="DbField"/> object.
 /// </summary>
 /// <param name="request">The request object.</param>
 /// <returns>The identity <see cref="DbField"/> object.</returns>
 private static DbField GetIdentityField(BaseRequest request)
 {
     if (request.Type != null && request.Type != typeof(object))
     {
         var identityProperty = IdentityCache.Get(request.Type);
         if (identityProperty != null)
         {
             var primaryPropery = PrimaryCache.Get(request.Type);
             var isPrimary      = false;
             if (primaryPropery != null)
             {
                 isPrimary = primaryPropery.GetUnquotedMappedName().ToLower() == identityProperty.GetUnquotedMappedName().ToLower();
             }
             return(new DbField(identityProperty.GetUnquotedMappedName(), isPrimary, true, false,
                                identityProperty.PropertyInfo.PropertyType, null, null, null));
         }
     }
     return(DbFieldCache.Get(request.Connection, request.Name)?.FirstOrDefault(f => f.IsIdentity));
 }
Example #20
0
        public static async Task <IEnumerable <OrderField> > GetValidatedDbFieldsAsync(
            this DbConnection dbConnection,
            string tableName,
            IEnumerable <OrderField> sortFields
            )
        {
            if (dbConnection == null || sortFields == null || string.IsNullOrWhiteSpace(tableName))
            {
                return(null);
            }

            //FILTER for only VALID fields from the sort fields by safely comparing to the valid fields from the DB Schema!
            //NOTE: Per RepoDb source we need to compare unquoted names to get pure matches...
            var dbSetting = dbConnection.GetDbSetting();
            var dbFields  = await DbFieldCache
                            .GetAsync(dbConnection, tableName, null, false)
                            .ConfigureAwait(false);

            var dbFieldLookup  = dbFields.ToLookup(f => f.Name.AsUnquoted(dbSetting).ToLower());
            var safeSortFields = sortFields.Where(s => dbFieldLookup.Contains(s.Name.AsUnquoted(dbSetting).ToLower()));

            return(safeSortFields);
        }
Example #21
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        /// <param name="reader"></param>
        /// <param name="qualifiers"></param>
        /// <param name="mappings"></param>
        /// <param name="bulkCopyTimeout"></param>
        /// <param name="keepIdentity"></param>
        /// <param name="pseudoTableType"></param>
        /// <param name="transaction"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        private static async Task <int> BinaryBulkDeleteBaseAsync(this NpgsqlConnection connection,
                                                                  string tableName,
                                                                  DbDataReader reader,
                                                                  IEnumerable <Field> qualifiers = null,
                                                                  IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
                                                                  int?bulkCopyTimeout = null,
                                                                  bool keepIdentity   = true,
                                                                  BulkImportPseudoTableType pseudoTableType = default,
                                                                  NpgsqlTransaction transaction             = null,
                                                                  CancellationToken cancellationToken       = default)
        {
            var dbSetting = connection.GetDbSetting();
            var dbFields  = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);

            var pseudoTableName  = tableName;
            var identityBehavior = keepIdentity ? BulkImportIdentityBehavior.KeepIdentity : BulkImportIdentityBehavior.Unspecified;

            return(await PseudoBasedBinaryImportAsync(connection,
                                                      tableName,
                                                      bulkCopyTimeout,
                                                      dbFields,

                                                      // getPseudoTableName
                                                      () =>
                                                      pseudoTableName = GetBinaryBulkDeletePseudoTableName(tableName, dbSetting),

                                                      // getMappings
                                                      () =>
            {
                var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity;
                var includePrimary = true;

                return mappings = mappings?.Any() == true ? mappings :
                                  GetMappings(reader,
                                              dbFields,
                                              includePrimary,
                                              includeIdentity,
                                              dbSetting);
            },

                                                      // binaryImport
                                                      async (tableName) =>
                                                      await connection.BinaryImportAsync(tableName,
                                                                                         reader,
                                                                                         mappings,
                                                                                         dbFields,
                                                                                         bulkCopyTimeout,
                                                                                         identityBehavior,
                                                                                         dbSetting,
                                                                                         transaction,
                                                                                         cancellationToken),

                                                      // getDeleteToPseudoCommandText
                                                      () =>
                                                      GetDeleteCommandText(pseudoTableName,
                                                                           tableName,
                                                                           mappings.Select(mapping => new Field(mapping.DestinationColumn)),
                                                                           qualifiers,
                                                                           dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(),
                                                                           dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(),
                                                                           identityBehavior,
                                                                           dbSetting),

                                                      // setIdentities
                                                      null,

                                                      qualifiers,
                                                      false,
                                                      identityBehavior : identityBehavior,
                                                      pseudoTableType : pseudoTableType,
                                                      dbSetting,
                                                      transaction : transaction,
                                                      cancellationToken));
        }
Example #22
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);
        }
Example #23
0
        /// <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="dbFields">The list of <see cref="DbField"/> objects.</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>
        internal static async Task <int> BulkInsertAsyncInternal(SqlConnection connection,
                                                                 string tableName,
                                                                 DataTable dataTable,
                                                                 DataRowState?rowState                    = null,
                                                                 IEnumerable <DbField> dbFields           = null,
                                                                 IEnumerable <BulkInsertMapItem> mappings = null,
                                                                 SqlBulkCopyOptions?options               = null,
                                                                 int?bulkCopyTimeout        = null,
                                                                 int?batchSize              = null,
                                                                 SqlTransaction transaction = null)
        {
            // Validate the objects
            SqlConnectionExtension.ValidateTransactionConnectionObject(connection, transaction);

            // Get the DB Fields
            dbFields = dbFields ?? await DbFieldCache.GetAsync(connection, tableName, transaction);

            if (dbFields?.Any() != true)
            {
                throw new InvalidOperationException($"No database fields found for '{tableName}'.");
            }

            // Variables needed
            var result      = 0;
            var tableFields = GetDataColumns(dataTable)
                              .Select(column => column.ColumnName);
            var mappingFields = new List <Tuple <string, string> >();

            // To fix the casing problem of the bulk inserts
            foreach (var dbField in dbFields)
            {
                var tableField = tableFields.FirstOrDefault(field =>
                                                            string.Equals(field, dbField.Name, StringComparison.OrdinalIgnoreCase));
                if (tableField != null)
                {
                    mappingFields.Add(new Tuple <string, string>(tableField, dbField.Name));
                }
            }

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

            // Actual Execution
            using (var sqlBulkCopy = new SqlBulkCopy(connection, options.GetValueOrDefault(), 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)
                {
                    // Iterate the filtered fields
                    foreach (var field in mappingFields)
                    {
                        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();
                if (rowState.HasValue == true)
                {
                    await sqlBulkCopy.WriteToServerAsync(dataTable, rowState.Value);
                }
                else
                {
                    await sqlBulkCopy.WriteToServerAsync(dataTable);
                }

                // Set the return value
                result = GetDataRows(dataTable, rowState).Count();
            }

            // Result
            return(result);
        }
Example #24
0
        /// <summary>
        /// Query all the data from the database.
        /// </summary>
        /// <param name="connection">The connection object to be used.</param>
        /// <param name="tableName">The name of the target table.</param>
        /// <param name="fields">The list of fields to be queried.</param>
        /// <param name="orderBy">The order definition of the fields to be used.</param>
        /// <param name="hints">The table hints to be used. See <see cref="SqlTableHints"/> class.</param>
        /// <param name="cacheKey">
        /// The key to the cache. If the cache key is present in the cache, then the item from the cache will be returned instead. Setting this
        /// to null would force to query from the database.
        /// </param>
        /// <param name="cacheItemExpiration">The expiration in minutes of the cache item.</param>
        /// <param name="commandTimeout">The command timeout in seconds to be used.</param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <param name="cache">The cache object 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>
        /// <returns>An enumerable list of data entity object.</returns>
        internal static async Task <IEnumerable <dynamic> > QueryAllAsyncInternalBase(this IDbConnection connection,
                                                                                      string tableName,
                                                                                      IEnumerable <Field> fields       = null,
                                                                                      IEnumerable <OrderField> orderBy = null,
                                                                                      string hints                       = null,
                                                                                      string cacheKey                    = null,
                                                                                      int cacheItemExpiration            = Constant.DefaultCacheItemExpirationInMinutes,
                                                                                      int?commandTimeout                 = null,
                                                                                      IDbTransaction transaction         = null,
                                                                                      ICache cache                       = null,
                                                                                      ITrace trace                       = null,
                                                                                      IStatementBuilder statementBuilder = null)
        {
            // Get Cache
            if (cacheKey != null)
            {
                var item = cache?.Get(cacheKey, false);
                if (item != null)
                {
                    return((IEnumerable <dynamic>)item.Value);
                }
            }

            // Check the fields
            if (fields?.Any() != true)
            {
                fields = DbFieldCache.Get(connection, tableName)?.Select(f => f.AsField());
            }

            // Variables
            var commandType = CommandType.Text;
            var request     = new QueryAllRequest(tableName,
                                                  connection,
                                                  fields,
                                                  orderBy,
                                                  hints,
                                                  statementBuilder);
            var commandText = CommandTextCache.GetQueryAllText(request);
            var param       = (object)null;

            // Before Execution
            if (trace != null)
            {
                var cancellableTraceLog = new CancellableTraceLog(commandText, param, null);
                trace.BeforeQueryAll(cancellableTraceLog);
                if (cancellableTraceLog.IsCancelled)
                {
                    if (cancellableTraceLog.IsThrowException)
                    {
                        throw new CancelledExecutionException(commandText);
                    }
                    return(null);
                }
                commandText = (cancellableTraceLog.Statement ?? commandText);
                param       = (cancellableTraceLog.Parameter ?? param);
            }

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

            // Actual Execution
            var result = await ExecuteQueryAsyncInternal(connection : connection,
                                                         commandText : commandText,
                                                         param : param,
                                                         commandType : commandType,
                                                         commandTimeout : commandTimeout,
                                                         transaction : transaction,
                                                         tableName : tableName,
                                                         skipCommandArrayParametersCheck : true);

            // After Execution
            if (trace != null)
            {
                trace.AfterQueryAll(new TraceLog(commandText, param, result,
                                                 DateTime.UtcNow.Subtract(beforeExecutionTime)));
            }

            // Set Cache
            if (cacheKey != null)
            {
                cache?.Add(cacheKey, result, cacheItemExpiration);
            }

            // Result
            return(result);
        }
Example #25
0
        /// <summary>
        /// Query all the data from 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="orderBy">The order definition of the fields to be used.</param>
        /// <param name="hints">The table hints to be used. See <see cref="SqlTableHints"/> class.</param>
        /// <param name="cacheKey">
        /// The key to the cache. If the cache key is present in the cache, then the item from the cache will be returned instead. Setting this
        /// to null would force to query from the database.
        /// </param>
        /// <param name="cacheItemExpiration">The expiration in minutes of the cache item.</param>
        /// <param name="commandTimeout">The command timeout in seconds to be used.</param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <param name="cache">The cache object 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>
        /// <returns>An enumerable list of data entity object.</returns>
        internal static Task <IEnumerable <TEntity> > QueryAllAsyncInternalBase <TEntity>(this IDbConnection connection,
                                                                                          IEnumerable <OrderField> orderBy = null,
                                                                                          string hints                       = null,
                                                                                          string cacheKey                    = null,
                                                                                          int cacheItemExpiration            = Constant.DefaultCacheItemExpirationInMinutes,
                                                                                          int?commandTimeout                 = null,
                                                                                          IDbTransaction transaction         = null,
                                                                                          ICache cache                       = null,
                                                                                          ITrace trace                       = null,
                                                                                          IStatementBuilder statementBuilder = null)
            where TEntity : class
        {
            // Get Cache
            if (cacheKey != null)
            {
                var item = cache?.Get(cacheKey, false);
                if (item != null)
                {
                    return(Task.FromResult((IEnumerable <TEntity>)item.Value));
                }
            }

            // Variables
            var commandType = CommandType.Text;
            var request     = new QueryAllRequest(typeof(TEntity),
                                                  connection,
                                                  FieldCache.Get <TEntity>(),
                                                  orderBy,
                                                  hints,
                                                  statementBuilder);
            var commandText = CommandTextCache.GetQueryAllText(request);
            var param       = (object)null;

            // Database pre-touch for field definitions
            DbFieldCache.Get(connection, ClassMappedNameCache.Get <TEntity>());

            // Before Execution
            if (trace != null)
            {
                var cancellableTraceLog = new CancellableTraceLog(commandText, param, null);
                trace.BeforeQueryAll(cancellableTraceLog);
                if (cancellableTraceLog.IsCancelled)
                {
                    if (cancellableTraceLog.IsThrowException)
                    {
                        throw new CancelledExecutionException(commandText);
                    }
                    return(Task.FromResult <IEnumerable <TEntity> >(null));
                }
                commandText = (cancellableTraceLog.Statement ?? commandText);
                param       = (cancellableTraceLog.Parameter ?? param);
            }

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

            // Actual Execution
            var result = ExecuteQueryAsyncInternal <TEntity>(connection: connection,
                                                             commandText: commandText,
                                                             param: param,
                                                             commandType: commandType,
                                                             commandTimeout: commandTimeout,
                                                             transaction: transaction,
                                                             basedOnFields: false,
                                                             skipCommandArrayParametersCheck: true);

            // After Execution
            if (trace != null)
            {
                trace.AfterQueryAll(new TraceLog(commandText, param, result,
                                                 DateTime.UtcNow.Subtract(beforeExecutionTime)));
            }

            // Set Cache
            if (cacheKey != null /* && result.Result?.Any() == true */)
            {
                cache?.Add(cacheKey, result, cacheItemExpiration);
            }

            // Result
            return(result);
        }
Example #26
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        /// <param name="reader"></param>
        /// <param name="qualifiers"></param>
        /// <param name="mappings"></param>
        /// <param name="bulkCopyTimeout"></param>
        /// <param name="identityBehavior"></param>
        /// <param name="mergeCommandType"></param>
        /// <param name="pseudoTableType"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        private static int BinaryBulkMergeBase(this NpgsqlConnection connection,
                                               string tableName,
                                               DbDataReader reader,
                                               IEnumerable <Field> qualifiers = null,
                                               IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
                                               int?bulkCopyTimeout = null,
                                               BulkImportIdentityBehavior identityBehavior = default,
                                               BulkImportMergeCommandType mergeCommandType = default,
                                               BulkImportPseudoTableType pseudoTableType   = default,
                                               NpgsqlTransaction transaction = null)
        {
            var dbSetting       = connection.GetDbSetting();
            var dbFields        = DbFieldCache.Get(connection, tableName, transaction);
            var pseudoTableName = tableName;

            return(PseudoBasedBinaryImport(connection,
                                           tableName,
                                           bulkCopyTimeout,
                                           dbFields,

                                           // getPseudoTableName
                                           () =>
                                           pseudoTableName = GetBinaryBulkMergePseudoTableName(tableName, dbSetting),

                                           // getMappings
                                           () =>
            {
                var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity;
                var includePrimary = true;

                return mappings = mappings?.Any() == true ? mappings :
                                  GetMappings(reader,
                                              dbFields,
                                              includePrimary,
                                              includeIdentity,
                                              dbSetting);
            },

                                           // binaryImport
                                           (tableName) =>
                                           connection.BinaryImport(tableName,
                                                                   reader,
                                                                   mappings,
                                                                   dbFields,
                                                                   bulkCopyTimeout,
                                                                   identityBehavior,
                                                                   dbSetting,
                                                                   transaction),

                                           // getMergeToPseudoCommandText
                                           () =>
                                           GetMergeCommandText(pseudoTableName,
                                                               tableName,
                                                               mappings.Select(mapping => new Field(mapping.DestinationColumn)),
                                                               qualifiers,
                                                               dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(),
                                                               dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(),
                                                               identityBehavior,
                                                               mergeCommandType,
                                                               dbSetting),

                                           // setIdentities
                                           null,

                                           qualifiers,
                                           false,
                                           identityBehavior,
                                           pseudoTableType,
                                           dbSetting,
                                           transaction: transaction));
        }
Example #27
0
        /// <summary>
        /// Inserts a new row in the table in an asynchronous way.
        /// </summary>
        /// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</typeparam>
        /// <typeparam name="TResult">The target type of the result.</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="entity">The data entity or dynamic object to be inserted.</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 value of the identity field if present, otherwise, the value of the primary field.</returns>
        internal async static Task <TResult> InsertAsyncInternalBase <TEntity, TResult>(this IDbConnection connection,
                                                                                        string tableName,
                                                                                        TEntity entity,
                                                                                        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();

            // Get the database fields
            var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction);

            // Get the function
            var callback = new Func <InsertExecutionContext <TEntity> >(() =>
            {
                // Variables needed
                var identity        = (Field)null;
                var inputFields     = (IEnumerable <DbField>)null;
                var identityDbField = dbFields?.FirstOrDefault(f => f.IsIdentity);

                // Set the identity field
                if (skipIdentityCheck == false)
                {
                    identity = IdentityCache.Get <TEntity>()?.AsField();
                    if (identity == null && identityDbField != null)
                    {
                        identity = FieldCache.Get <TEntity>().FirstOrDefault(field =>
                                                                             string.Equals(field.Name.AsUnquoted(true, dbSetting), identityDbField.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase));
                    }
                }

                // Filter the actual properties for input fields
                inputFields = dbFields?
                              .Where(dbField => dbField.IsIdentity == false)
                              .Where(dbField =>
                                     fields.FirstOrDefault(field =>
                                                           string.Equals(field.Name.AsUnquoted(true, dbSetting), dbField.Name.AsUnquoted(true, dbSetting), StringComparison.OrdinalIgnoreCase)) != null)
                              .AsList();

                // Variables for the entity action
                var identityPropertySetter = (Action <TEntity, object>)null;

                // Get the identity setter
                if (skipIdentityCheck == false && identity != null)
                {
                    identityPropertySetter = FunctionCache.GetDataEntityPropertyValueSetterFunction <TEntity>(identity);
                }

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

                // Create a different kind of requests
                if (typeof(TEntity) == StaticType.Object)
                {
                    insertRequest = new InsertRequest(tableName,
                                                      connection,
                                                      transaction,
                                                      fields,
                                                      hints,
                                                      statementBuilder);
                }
                else
                {
                    insertRequest = new InsertRequest(typeof(TEntity),
                                                      connection,
                                                      transaction,
                                                      fields,
                                                      hints,
                                                      statementBuilder);
                }

                // Return the value
                return(new InsertExecutionContext <TEntity>
                {
                    CommandText = CommandTextCache.GetInsertText(insertRequest),
                    InputFields = inputFields,
                    ParametersSetterFunc = FunctionCache.GetDataEntityDbCommandParameterSetterFunction <TEntity>(
                        string.Concat(typeof(TEntity).FullName, StringConstant.Period, tableName, ".Insert"),
                        inputFields?.AsList(),
                        null,
                        dbSetting),
                    IdentityPropertySetterFunc = identityPropertySetter
                });
            });

            // Get the context
            var context = InsertExecutionContextCache <TEntity> .Get(tableName, fields, callback);

            var sessionId = Guid.Empty;

            // Before Execution
            if (trace != null)
            {
                sessionId = Guid.NewGuid();
                var cancellableTraceLog = new CancellableTraceLog(sessionId, context.CommandText, entity, null);
                trace.BeforeInsert(cancellableTraceLog);
                if (cancellableTraceLog.IsCancelled)
                {
                    if (cancellableTraceLog.IsThrowException)
                    {
                        throw new CancelledExecutionException(context.CommandText);
                    }
                    return(default(TResult));
                }
                context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText);
                entity = (TEntity)(cancellableTraceLog.Parameter ?? entity);
            }

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

            // Execution variables
            var result = default(TResult);

            // Create the command
            using (var command = (DbCommand)(await connection.EnsureOpenAsync()).CreateCommand(context.CommandText,
                                                                                               CommandType.Text, commandTimeout, transaction))
            {
                // Set the values
                context.ParametersSetterFunc(command, entity);

                // Actual Execution
                result = Converter.ToType <TResult>(await command.ExecuteScalarAsync());

                // Get explicity if needed
                if (Equals(result, default(TResult)) == true && dbSetting.IsMultiStatementExecutable == false)
                {
                    result = Converter.ToType <TResult>(await connection.GetDbHelper().GetScopeIdentityAsync(connection, transaction));
                }

                // Set the return value
                if (Equals(result, default(TResult)) == false)
                {
                    context.IdentityPropertySetterFunc?.Invoke(entity, result);
                }
            }

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

            // Return the result
            return(result);
        }
Example #28
0
        /// <summary>
        /// Inserts a new row in the table in an asynchronous way.
        /// </summary>
        /// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</typeparam>
        /// <typeparam name="TResult">The target type of the result.</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="entity">The data entity or dynamic object to be inserted.</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="cancellationToken">The <see cref="CancellationToken"/> object to be used during the asynchronous operation.</param>
        /// <returns>The value of the identity field if present, otherwise, the value of the primary field.</returns>
        internal async static Task <TResult> InsertAsyncInternalBase <TEntity, TResult>(this IDbConnection connection,
                                                                                        string tableName,
                                                                                        TEntity entity,
                                                                                        IEnumerable <Field> fields = null,
                                                                                        string hints                        = null,
                                                                                        int?commandTimeout                  = null,
                                                                                        IDbTransaction transaction          = null,
                                                                                        ITrace trace                        = null,
                                                                                        IStatementBuilder statementBuilder  = null,
                                                                                        CancellationToken cancellationToken = default)
            where TEntity : class
        {
            // Variables needed
            var dbSetting = connection.GetDbSetting();

            // Get the database fields
            var dbFields = await DbFieldCache.GetAsync(connection, tableName, transaction, cancellationToken);

            // Get the context
            var context = await InsertExecutionContextProvider.CreateAsync <TEntity>(connection,
                                                                                     tableName,
                                                                                     fields,
                                                                                     hints,
                                                                                     transaction,
                                                                                     statementBuilder,
                                                                                     cancellationToken);

            var sessionId = Guid.Empty;

            // Before Execution
            if (trace != null)
            {
                sessionId = Guid.NewGuid();
                var cancellableTraceLog = new CancellableTraceLog(sessionId, context.CommandText, entity, null);
                trace.BeforeInsert(cancellableTraceLog);
                if (cancellableTraceLog.IsCancelled)
                {
                    if (cancellableTraceLog.IsThrowException)
                    {
                        throw new CancelledExecutionException(context.CommandText);
                    }
                    return(default(TResult));
                }
                context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText);
                entity = (TEntity)(cancellableTraceLog.Parameter ?? entity);
            }

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

            // Execution variables
            var result = default(TResult);

            // Create the command
            using (var command = (DbCommand)(await connection.EnsureOpenAsync(cancellationToken)).CreateCommand(context.CommandText,
                                                                                                                CommandType.Text, commandTimeout, transaction))
            {
                // Set the values
                context.ParametersSetterFunc(command, entity);

                // Actual Execution
                result = Converter.ToType <TResult>(await command.ExecuteScalarAsync(cancellationToken));

                // Get explicity if needed
                if (Equals(result, default(TResult)) == true && dbSetting.IsMultiStatementExecutable == false)
                {
                    result = Converter.ToType <TResult>(await connection.GetDbHelper().GetScopeIdentityAsync(connection, transaction, cancellationToken));
                }

                // Set the return value
                if (Equals(result, default(TResult)) == false)
                {
                    context.IdentityPropertySetterFunc?.Invoke(entity, result);
                }
            }

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

            // Return the result
            return(result);
        }
Example #29
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        /// <param name="table"></param>
        /// <param name="rowState"></param>
        /// <param name="qualifiers"></param>
        /// <param name="mappings"></param>
        /// <param name="bulkCopyTimeout"></param>
        /// <param name="batchSize"></param>
        /// <param name="keepIdentity"></param>
        /// <param name="pseudoTableType"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        private static int BinaryBulkDeleteBase(this NpgsqlConnection connection,
                                                string tableName,
                                                DataTable table,
                                                DataRowState?rowState          = null,
                                                IEnumerable <Field> qualifiers = null,
                                                IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
                                                int?bulkCopyTimeout = null,
                                                int?batchSize       = null,
                                                bool keepIdentity   = true,
                                                BulkImportPseudoTableType pseudoTableType = default,
                                                NpgsqlTransaction transaction             = null)
        {
            var dbSetting        = connection.GetDbSetting();
            var dbFields         = DbFieldCache.Get(connection, tableName, transaction);
            var pseudoTableName  = tableName;
            var identityBehavior = keepIdentity ? BulkImportIdentityBehavior.KeepIdentity : BulkImportIdentityBehavior.Unspecified;

            return(PseudoBasedBinaryImport(connection,
                                           tableName,
                                           bulkCopyTimeout,
                                           dbFields,

                                           // getPseudoTableName
                                           () =>
                                           pseudoTableName = GetBinaryBulkDeletePseudoTableName(tableName, dbSetting),

                                           // getMappings
                                           () =>
            {
                var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity;
                var includePrimary = true;

                return mappings = mappings?.Any() == true ? mappings :
                                  GetMappings(table,
                                              dbFields,
                                              includePrimary,
                                              includeIdentity,
                                              dbSetting);
            },

                                           // binaryImport
                                           (tableName) =>
                                           connection.BinaryImport(tableName,
                                                                   table,
                                                                   rowState,
                                                                   mappings,
                                                                   dbFields,
                                                                   bulkCopyTimeout,
                                                                   batchSize,
                                                                   identityBehavior,
                                                                   dbSetting,
                                                                   transaction),

                                           // getDeleteToPseudoCommandText
                                           () =>
                                           GetDeleteCommandText(pseudoTableName,
                                                                tableName,
                                                                mappings.Select(mapping => new Field(mapping.DestinationColumn)),
                                                                qualifiers,
                                                                dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(),
                                                                dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(),
                                                                identityBehavior,
                                                                dbSetting),

                                           // setIdentities
                                           (identityResults) =>
                                           SetDataTableIdentities(table, dbFields, identityResults, dbSetting),

                                           qualifiers,
                                           false,
                                           identityBehavior: identityBehavior,
                                           pseudoTableType: pseudoTableType,
                                           dbSetting,
                                           transaction: transaction));
        }
Example #30
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="connection"></param>
        /// <param name="tableName"></param>
        /// <param name="entities"></param>
        /// <param name="qualifiers"></param>
        /// <param name="mappings"></param>
        /// <param name="bulkCopyTimeout"></param>
        /// <param name="batchSize"></param>
        /// <param name="keepIdentity"></param>
        /// <param name="pseudoTableType"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        private static int BinaryBulkDeleteBase <TEntity>(this NpgsqlConnection connection,
                                                          string tableName,
                                                          IEnumerable <TEntity> entities,
                                                          IEnumerable <Field> qualifiers = null,
                                                          IEnumerable <NpgsqlBulkInsertMapItem> mappings = null,
                                                          int?bulkCopyTimeout = null,
                                                          int?batchSize       = null,
                                                          bool keepIdentity   = true,
                                                          BulkImportPseudoTableType pseudoTableType = default,
                                                          NpgsqlTransaction transaction             = null)
            where TEntity : class
        {
            var entityType       = entities?.First()?.GetType() ?? typeof(TEntity); // Solving the anonymous types
            var isDictionary     = entityType.IsDictionaryStringObject();
            var dbSetting        = connection.GetDbSetting();
            var dbFields         = DbFieldCache.Get(connection, tableName, transaction);
            var pseudoTableName  = tableName;
            var identityBehavior = keepIdentity ? BulkImportIdentityBehavior.KeepIdentity : BulkImportIdentityBehavior.Unspecified;

            return(PseudoBasedBinaryImport(connection,
                                           tableName,
                                           bulkCopyTimeout,
                                           dbFields,

                                           // getPseudoTableName
                                           () =>
                                           pseudoTableName = GetBinaryBulkDeletePseudoTableName(tableName ?? ClassMappedNameCache.Get <TEntity>(), dbSetting),

                                           // getMappings
                                           () =>
            {
                var includeIdentity = identityBehavior == BulkImportIdentityBehavior.KeepIdentity;
                var includePrimary = true;

                return mappings = mappings?.Any() == true ? mappings :
                                  isDictionary ?
                                  GetMappings(entities?.First() as IDictionary <string, object>,
                                              dbFields,
                                              includePrimary,
                                              includeIdentity,
                                              dbSetting) :
                                  GetMappings(dbFields,
                                              PropertyCache.Get(entityType),
                                              includePrimary,
                                              includeIdentity,
                                              dbSetting);
            },

                                           // binaryImport
                                           (tableName) =>
                                           connection.BinaryImport <TEntity>(tableName,
                                                                             entities,
                                                                             mappings,
                                                                             dbFields,
                                                                             bulkCopyTimeout,
                                                                             batchSize,
                                                                             identityBehavior,
                                                                             dbSetting,
                                                                             transaction),

                                           // getDeleteToPseudoCommandText
                                           () =>
                                           GetDeleteCommandText(pseudoTableName,
                                                                tableName,
                                                                mappings.Select(mapping => new Field(mapping.DestinationColumn)),
                                                                qualifiers,
                                                                dbFields.FirstOrDefault(dbField => dbField.IsPrimary)?.AsField(),
                                                                dbFields.FirstOrDefault(dbField => dbField.IsIdentity)?.AsField(),
                                                                identityBehavior,
                                                                dbSetting),

                                           // setIdentities
                                           (identityResults) =>
                                           SetIdentities(entityType, entities, dbFields, identityResults, dbSetting),

                                           qualifiers,
                                           false,
                                           identityBehavior,
                                           pseudoTableType,
                                           dbSetting,
                                           transaction));
        }