public DataTable ConvertEntitiesToDataTable <T>(IEnumerable <T> entityList, SqlBulkHelpersColumnDefinition identityColumnDefinition) { //Get the name of hte Identity Column //NOTE: BBernard - We take in the strongly typed class (immutable) to ensure that we have validated Parameter vs raw string! var identityColumnName = identityColumnDefinition.ColumnName; //NOTE: We Map all Properties to an anonymous type with Index, Name, and base PropInfo here for easier logic below, // and we ALWAYS convert to a List<> so that we always preserve the critical order of the PropertyInfo items! // to simplify all following code. //NOTE: The helper class provides internal Lazy type caching for better performance once a type has been loaded. var propertyDefs = SqlBulkHelpersObjectReflectionFactory.GetPropertyDefinitions <T>(identityColumnDefinition); DataTable dataTable = new DataTable(); dataTable.Columns.AddRange(propertyDefs.Select(pi => new DataColumn { ColumnName = pi.Name, DataType = Nullable.GetUnderlyingType(pi.PropInfo.PropertyType) ?? pi.PropInfo.PropertyType, //We Always allow Null to make below logic easier, and it's the Responsibility of the Model to ensure values are Not Null vs Nullable. AllowDBNull = true //Nullable.GetUnderlyingType(pi.PropertyType) == null ? false : true }).ToArray()); //BBernard - We ALWAYS Add the internal RowNumber reference so that we can exactly correlate Identity values from the Server // back to original records that we passed! //NOTE: THIS IS CRITICAL because SqlBulkCopy and Sql Server OUTPUT clause do not preserve Order; e.g. it may change based // on execution plan (indexes/no indexes, etc.). dataTable.Columns.Add(new DataColumn() { ColumnName = SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME, DataType = typeof(int), AllowDBNull = true }); int rowCounter = 1; int identityIdFakeCounter = -1; foreach (T entity in entityList) { var rowValues = propertyDefs.Select(p => { var value = p.PropInfo.GetValue(entity); //Handle special cases to ensure that Identity values are mapped to unique invalid values. if (p.IsIdentityProperty && (int)value <= 0) { //Create a Unique but Invalid Fake Identity Id (e.g. negative number)! value = identityIdFakeCounter--; } return(value); }).ToArray(); //Add the Values (must be critically in the same order as the PropertyInfos List) as a new Row! var newRow = dataTable.Rows.Add(rowValues); //Always set the unique Row Number identifier newRow[SqlBulkHelpersConstants.ROWNUMBER_COLUMN_NAME] = rowCounter++; } return(dataTable); }
protected virtual List <T> PostProcessEntitiesWithMergeResults(List <T> entityList, List <MergeResult> mergeResultsList, SqlBulkHelpersColumnDefinition identityColumnDefinition) { var propDefs = SqlBulkHelpersObjectReflectionFactory.GetPropertyDefinitions <T>(identityColumnDefinition); var identityPropDef = propDefs.FirstOrDefault(pi => pi.IsIdentityProperty); var identityPropInfo = identityPropDef.PropInfo; foreach (var mergeResult in mergeResultsList.Where(r => r.MergeAction.HasFlag(SqlBulkHelpersMergeAction.Insert))) { //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 chainability) and easier to read code //NOTE: even though we have actually mutated the original list by reference this helps with code readability. return(entityList); }
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); }