public virtual IEnumerable <T> BulkUpdate(
IEnumerable <T> entityList,
string tableName,
SqlTransaction transaction,
SqlMergeMatchQualifierExpression matchQualifierExpression = null
)
{
throw new NotImplementedException();
}
/// <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);
}
}
/// <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);
}
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;
}
public virtual IEnumerable <T> BulkInsert(
IEnumerable <T> entityList,
string tableName,
SqlTransaction transaction,
SqlMergeMatchQualifierExpression matchQualifierExpression = null
)
{
return(BulkInsertOrUpdateWithIdentityColumn(
entityList,
tableName,
SqlBulkHelpersMergeAction.Insert,
transaction,
matchQualifierExpression
));
}
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);
}
/// <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
});
}
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);
}
