예제 #1
0
 public virtual IEnumerable <T> BulkUpdate(
     IEnumerable <T> entityList,
     string tableName,
     SqlTransaction transaction,
     SqlMergeMatchQualifierExpression matchQualifierExpression = null
     )
 {
     throw new NotImplementedException();
 }
예제 #2
0
        /// <summary>
        /// BBernard
        /// This is the Primary Synchronous method that supports Insert, Update, and InsertOrUpdate via the flexibility of the Sql MERGE query!
        /// </summary>
        /// <param name="entities"></param>
        /// <param name="tableName"></param>
        /// <param name="mergeAction"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        protected virtual IEnumerable <T> BulkInsertOrUpdateWithIdentityColumn(
            IEnumerable <T> entities,
            String tableName,
            SqlBulkHelpersMergeAction mergeAction,
            SqlTransaction transaction,
            SqlMergeMatchQualifierExpression matchQualifierExpression = null
            )
        {
            //For Performance we ensure the entities are only ever enumerated One Time!
            var entityList = entities.ToList();
            var bulkOperationTimeoutSeconds = this.BulkOperationTimeoutSeconds;

            using (ProcessHelper processHelper = this.CreateProcessHelper(
                       entityList, tableName, mergeAction, transaction, bulkOperationTimeoutSeconds, matchQualifierExpression))
            {
                var sqlCmd      = processHelper.SqlCommand;
                var sqlBulkCopy = processHelper.SqlBulkCopy;
                var sqlScripts  = processHelper.SqlMergeScripts;

                //***STEP #4: Create Tables for Buffering Data & Storing Output values
                sqlCmd.CommandText = sqlScripts.SqlScriptToInitializeTempTables;
                sqlCmd.ExecuteNonQuery();

                //***STEP #5: Write Data to the Staging/Buffer Table as fast as possible!
                sqlBulkCopy.DestinationTableName = $"[{sqlScripts.TempStagingTableName}]";
                sqlBulkCopy.WriteToServer(processHelper.DataTable);

                //***STEP #6: Merge Data from the Staging Table into the Real Table
                //          and simultaneously Output Identity Id values into Output Temp Table!
                sqlCmd.CommandText = sqlScripts.SqlScriptToExecuteMergeProcess;

                //Execute this script and load the results....
                var mergeResultsList = new List <MergeResult>();
                using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
                {
                    while (sqlReader.Read())
                    {
                        var mergeResult = ReadCurrentMergeResultHelper(sqlReader);
                        mergeResultsList.Add(mergeResult);
                    }
                }

                //***STEP #7: FINALLY Update all of the original Entities with INSERTED/New Identity Values
                //NOTE: IF MULTIPLE NON-UNIQUE items are updated then ONLY ONE Identity value can be returned, though multiple
                //      other items may have in-reality actually been updated within the DB.  This is a likely scenario
                //      IF a different non-unique Match Qualifier Field is specified.
                var updatedEntityList = this.PostProcessEntitiesWithMergeResults(
                    entityList,
                    mergeResultsList,
                    processHelper.TableDefinition.IdentityColumn,
                    processHelper.SqlMergeScripts.SqlMatchQualifierExpression
                    );

                //FINALLY Return the updated Entities with the Identity Id if it was Inserted!
                return(updatedEntityList);
            }
        }
예제 #3
0
        /// <summary>
        /// BBernard - 12/07/2020
        /// Delegate method to build the Match Qualification expression text from the MatchQualifierExpression model provided.
        /// NOTE: This can be overridden if needed to provide highly specialized logic and reutrn any match qualification expression
        ///     text needed in edge use-cases.
        /// </summary>
        /// <param name="matchQualifierExpression"></param>
        /// <returns></returns>
        protected virtual string BuildMergeMatchQualifierExpressionText(SqlMergeMatchQualifierExpression matchQualifierExpression)
        {
            //Construct the full Match Qualifier Expression
            var qualifierFields = matchQualifierExpression.MatchQualifierFields?.Select(f =>
                                                                                        $"target.[{f.SanitizedName}] = source.[{f.SanitizedName}]"
                                                                                        );

            var fullExpressionText = String.Join(" AND ", qualifierFields);

            return(fullExpressionText);
        }
예제 #4
0
 public SqlMergeScriptResults(
     string tempStagingTableName,
     string tempOutputTableName,
     string tempTableScript,
     string mergeProcessScript,
     SqlMergeMatchQualifierExpression sqlMatchQualifierExpression
     )
 {
     this.SqlScriptToInitializeTempTables = tempTableScript;
     this.SqlScriptToExecuteMergeProcess  = mergeProcessScript;
     this.TempStagingTableName            = tempStagingTableName;
     this.TempOutputTableName             = tempOutputTableName;
     this.SqlMatchQualifierExpression     = sqlMatchQualifierExpression;
 }
예제 #5
0
 public virtual IEnumerable <T> BulkInsert(
     IEnumerable <T> entityList,
     string tableName,
     SqlTransaction transaction,
     SqlMergeMatchQualifierExpression matchQualifierExpression = null
     )
 {
     return(BulkInsertOrUpdateWithIdentityColumn(
                entityList,
                tableName,
                SqlBulkHelpersMergeAction.Insert,
                transaction,
                matchQualifierExpression
                ));
 }
예제 #6
0
 public virtual async Task <IEnumerable <T> > BulkUpdateAsync(
     IEnumerable <T> entityList,
     string tableName,
     SqlTransaction transaction,
     SqlMergeMatchQualifierExpression matchQualifierExpression = null
     )
 {
     return(await BulkInsertOrUpdateWithIdentityColumnAsync(
                entityList,
                tableName,
                SqlBulkHelpersMergeAction.Update,
                transaction,
                matchQualifierExpression
                ));
 }
        //TODO: BBernard - If beneficial, we can Add Caching here at this point to cache the fully formed Merge Queries!
        protected virtual SqlMergeScriptResults BuildSqlMergeScriptsHelper(
            SqlBulkHelpersTableDefinition tableDefinition,
            SqlBulkHelpersMergeAction mergeAction,
            SqlMergeMatchQualifierExpression matchQualifierExpression = null
            )
        {
            var mergeScriptBuilder = new SqlBulkHelpersMergeScriptBuilder();
            var sqlScripts         = mergeScriptBuilder.BuildSqlMergeScripts(
                tableDefinition,
                mergeAction,
                matchQualifierExpression
                );

            return(sqlScripts);
        }
예제 #8
0
        /// <summary>
        /// BBernard - Private process helper to wrap up and encapsulate the initialization logic that is shared across both Async and Sync methods...
        /// </summary>
        /// <param name="entityList"></param>
        /// <param name="tableName"></param>
        /// <param name="mergeAction"></param>
        /// <param name="transaction"></param>
        /// <param name="timeoutSeconds"></param>
        /// <param name="matchQualifierExpression"></param>
        /// <returns></returns>
        protected virtual ProcessHelper CreateProcessHelper(
            List <T> entityList,
            String tableName,
            SqlBulkHelpersMergeAction mergeAction,
            SqlTransaction transaction,
            int timeoutSeconds,
            SqlMergeMatchQualifierExpression matchQualifierExpression = null
            )
        {
            //***STEP #1: Load the Table Schema Definitions (cached after initial Load)!!!
            //BBernard
            //NOTE: Prevent SqlInjection - by validating that the TableName must be a valid value (as retrieved from the DB Schema)
            //      we eliminate risk of Sql Injection.
            //NOTE: ALl other parameters are Strongly typed (vs raw Strings) thus eliminating risk of Sql Injection
            SqlBulkHelpersTableDefinition tableDefinition = this.GetTableSchemaDefinition(tableName);

            //***STEP #2: Dynamically Convert All Entities to a DataTable for consumption by the SqlBulkCopy class...
            DataTable dataTable = this.ConvertEntitiesToDataTableHelper(entityList, tableDefinition.IdentityColumn);

            //***STEP #3: Build all of the Sql Scripts needed to Process the entities based on the specified Table definition.
            SqlMergeScriptResults sqlScripts = this.BuildSqlMergeScriptsHelper(tableDefinition, mergeAction, matchQualifierExpression);

            //***STEP #4: Dynamically Initialize the Bulk Copy Helper using our Table data and table Definition!
            var sqlBulkCopyHelper = this.CreateSqlBulkCopyHelper(dataTable, tableDefinition, transaction, timeoutSeconds);

            return(new ProcessHelper()
            {
                TableDefinition = tableDefinition,
                DataTable = dataTable,
                SqlMergeScripts = sqlScripts,
                SqlCommand = new SqlCommand(String.Empty, transaction.Connection, transaction)
                {
                    CommandTimeout = timeoutSeconds
                },
                SqlBulkCopy = sqlBulkCopyHelper
            });
        }
예제 #9
0
        public virtual SqlMergeScriptResults BuildSqlMergeScripts(
            SqlBulkHelpersTableDefinition tableDefinition,
            SqlBulkHelpersMergeAction mergeAction,
            SqlMergeMatchQualifierExpression matchQualifierExpression = null
            )
        {
            //NOTE: BBernard - This temp table name MUST begin with 1 (and only 1) hash "#" to ensure it is a Transaction Scoped table!
            var tempStagingTableName        = $"#SqlBulkHelpers_STAGING_TABLE_{Guid.NewGuid()}";
            var tempOutputIdentityTableName = $"#SqlBulkHelpers_OUTPUT_IDENTITY_TABLE_{Guid.NewGuid()}";
            var identityColumnName          = tableDefinition.IdentityColumn?.ColumnName ?? String.Empty;

            var columnNamesListWithoutIdentity = tableDefinition.GetColumnNames(false);
            var columnNamesWithoutIdentityCSV  = columnNamesListWithoutIdentity.Select(c => $"[{c}]").ToCSV();

            //Dynamically build the Merge Match Qualifier Fields Expression
            //NOTE: This is an optional parameter now, but is initialized to the IdentityColumn by Default!
            var qualifierExpression = matchQualifierExpression ?? new SqlMergeMatchQualifierExpression(identityColumnName);
            var mergeMatchQualifierExpressionText = BuildMergeMatchQualifierExpressionText(qualifierExpression);

            //Initialize/Create the Staging Table!
            //NOTE: THe ROWNUMBER_COLUMN_NAME (3'rd Column) IS CRITICAL because SqlBulkCopy and Sql Server OUTPUT claus do not
            //          preserve Order; e.g. it may change based on execution plan (indexes/no indexes, etc.).
            String sqlScriptToInitializeTempTables = $@"
                SELECT TOP(0)
                    -1 as [{identityColumnName}],
                    {columnNamesWithoutIdentityCSV},
                    -1 as [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}] 
                INTO [{tempStagingTableName}] 
                FROM [{tableDefinition.TableName}];

                ALTER TABLE [{tempStagingTableName}] ADD PRIMARY KEY ([{identityColumnName}]);

                SELECT TOP(0)
                    CAST('' AS nvarchar(10)) as [MERGE_ACTION],
                    CAST(-1 AS int) as [IDENTITY_ID], 
                    CAST(-1 AS int) [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}] 
                INTO [{tempOutputIdentityTableName}];
            ";

            //NOTE: This is ALL now completed very efficiently on the Sql Server Database side with
            //          NO unnecessary round trips to the Database!
            var mergeInsertSql = String.Empty;

            if (mergeAction.HasFlag(SqlBulkHelpersMergeAction.Insert))
            {
                mergeInsertSql = $@"
                    WHEN NOT MATCHED BY TARGET THEN
                        INSERT ({columnNamesWithoutIdentityCSV}) 
                        VALUES ({columnNamesListWithoutIdentity.Select(c => $"source.[{c}]").ToCSV()})
                ";
            }

            var mergeUpdateSql = String.Empty;

            if (mergeAction.HasFlag(SqlBulkHelpersMergeAction.Update))
            {
                mergeUpdateSql = $@"
                    WHEN MATCHED THEN
                        UPDATE SET {columnNamesListWithoutIdentity.Select(c => $"target.[{c}] = source.[{c}]").ToCSV()} 
                ";
            }

            //Build the FULL Dynamic Merge Script here...
            //BBernard - 2019-08-07
            //NOTE: We now sort on the RowNumber column that we define; this FIXES issue with SqlBulkCopy.WriteToServer()
            //      where the order of data being written is NOT guaranteed, and there is still no support for the ORDER() hint.
            //      In general it results in inverting the order of data being sent in Bulk which then resulted in Identity
            //      values being incorrect based on the order of data specified.
            //NOTE: We MUST SORT the OUTPUT Results by ROWNUMBER and then by IDENTITY Column in case there are multiple matches due to
            //      custom match Qualifiers; this ensures that data is sorted in a way that postprocessing
            //      can occur & be validated as expected.
            String sqlScriptToExecuteMergeProcess = $@"
                MERGE [{tableDefinition.TableName}] as target
				USING (
					SELECT TOP 100 PERCENT * 
					FROM [{tempStagingTableName}] 
					ORDER BY [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}] ASC
				) as source
                ON {mergeMatchQualifierExpressionText}
                {mergeUpdateSql}
                {mergeInsertSql}
                OUTPUT $action, INSERTED.[{identityColumnName}], source.[{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}]
                    INTO [{tempOutputIdentityTableName}] ([MERGE_ACTION], [IDENTITY_ID], [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}]);

                SELECT 
                    [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}], 
                    [IDENTITY_ID], 
                    [MERGE_ACTION]
                FROM [{tempOutputIdentityTableName}]
                ORDER BY [{SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME}] ASC, [IDENTITY_ID] ASC;

                DROP TABLE [{tempStagingTableName}];
                DROP TABLE [{tempOutputIdentityTableName}];
            ";

            return(new SqlMergeScriptResults(
                       tempStagingTableName,
                       tempOutputIdentityTableName,
                       sqlScriptToInitializeTempTables,
                       sqlScriptToExecuteMergeProcess,
                       qualifierExpression
                       ));
        }
        protected virtual List <T> PostProcessEntitiesWithMergeResults(
            List <T> entityList,
            List <MergeResult> mergeResultsList,
            SqlBulkHelpersColumnDefinition identityColumnDefinition,
            SqlMergeMatchQualifierExpression sqlMatchQualifierExpression
            )
        {
            var propDefs         = SqlBulkHelpersObjectReflectionFactory.GetPropertyDefinitions <T>(identityColumnDefinition);
            var identityPropDef  = propDefs.FirstOrDefault(pi => pi.IsIdentityProperty);
            var identityPropInfo = identityPropDef?.PropInfo;

            //If there is no Identity Column (e.g. no Identity Column Definition and/or no PropInfo can be found)
            //  then we can short circuit.
            if (identityPropInfo == null)
            {
                return(entityList);
            }

            bool uniqueMatchValidationEnabled = sqlMatchQualifierExpression?.ThrowExceptionIfNonUniqueMatchesOccur == true;

            ////Get all Items Inserted or Updated....
            //NOTE: With the support for Custom Match Qualifiers we really need to handle Inserts & Updates,
            //      so there's no reason to filter the merge results anymore; this is more performant.
            var itemsInsertedOrUpdated = mergeResultsList;
            //var itemsInsertedOrUpdated = mergeResultsList.Where(r =>
            //    r.MergeAction.HasFlag(SqlBulkHelpersMergeAction.Insert)
            //    || r.MergeAction.HasFlag(SqlBulkHelpersMergeAction.Update)
            //);

            //BBernard this isn't needed since we updated the SQL Merge Script to sort correctly before returning
            //  data.... but leaving it here for future reference in case it's needed.
            //if (!uniqueMatchValidationEnabled)
            //{
            //    //BBernard - 12/08/2020
            //    //If Unique Match validation is Disabled, we must take additional steps to properly synchronize with
            //    //  the risk of multiple update matches....
            //    //NOTE: It is CRITICAL to sort by RowNumber & then by Identity value to handle edge cases where
            //    //      special Match Qualifier Fields are specified that are non-unique and result in multiple update
            //    //      matches; this ensures that at least correct data is matched/synced by the latest/last values ordered
            //    //      Ascending, when the validation is disabled.
            //    itemsInsertedOrUpdated = itemsInsertedOrUpdated.OrderBy(r => r.RowNumber).ThenBy(r => r.IdentityId);
            //}

            var uniqueMatchesHashSet = new HashSet <int>();

            //foreach (var mergeResult in mergeResultsList.Where(r => r.MergeAction.HasFlag(SqlBulkHelpersMergeAction.Insert)))
            foreach (var mergeResult in itemsInsertedOrUpdated)
            {
                //ONLY Process uniqueness validation if necessary... otherwise skip the logic altogether.
                if (uniqueMatchValidationEnabled)
                {
                    if (uniqueMatchesHashSet.Contains(mergeResult.RowNumber))
                    {
                        throw new ArgumentOutOfRangeException(
                                  nameof(mergeResultsList),
                                  "The bulk action has resulted in multiple matches for the the specified Match Qualifiers"
                                  + $" [{sqlMatchQualifierExpression}] and the original Entities List cannot be safely updated."
                                  + "Verify that the Match Qualifier fields result in unique matches or, if intentional, then "
                                  + "this validation check may be disabled on the SqlMergeMatchQualifierExpression parameter."
                                  );
                    }
                    else
                    {
                        uniqueMatchesHashSet.Add(mergeResult.RowNumber);
                    }
                }

                //NOTE: List is 0 (zero) based, but our RowNumber is 1 (one) based.
                var entity = entityList[mergeResult.RowNumber - 1];

                //BBernard
                //GENERICALLY Set the Identity Value to the Int value returned, this eliminates any dependency on a Base Class!
                //TODO: If needed we can optimize this with a Delegate for faster property access (vs pure Reflection).
                //(entity as Debug.ConsoleApp.TestElement).Id = mergeResult.IdentityId;
                identityPropInfo.SetValue(entity, mergeResult.IdentityId);
            }

            //Return the Updated Entities List (for fluent chain-ability) and easier to read code
            //NOTE: even though we have actually mutated the original list by reference this is very intuitive and helps with code readability.
            return(entityList);
        }