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);
}
//TODO: BBERNARD - Potentially Optimize this further by compiling Delegates for Field Setters for faster execution!
public PropInfoDefinition(PropertyInfo propInfo, SqlBulkHelpersColumnDefinition identityColumnDef = null)
{
this.PropInfo = propInfo;
this.Name = propInfo.Name;
this.PropertyType = propInfo.PropertyType;
//Early determination if a Property is an Identity Property for Fast processing later.
this.IsIdentityProperty = identityColumnDef?.ColumnName?.Equals(propInfo.Name, StringComparison.OrdinalIgnoreCase) ?? false;
}
protected virtual DataTable ConvertEntitiesToDataTableHelper(
IEnumerable <T> entityList,
SqlBulkHelpersColumnDefinition identityColumnDefinition = null
)
{
SqlBulkHelpersObjectMapper sqlBulkHelperModelMapper = new SqlBulkHelpersObjectMapper();
DataTable dataTable = sqlBulkHelperModelMapper.ConvertEntitiesToDataTable(entityList, identityColumnDefinition);
return(dataTable);
}
public static List <PropInfoDefinition> GetPropertyDefinitions <T>(SqlBulkHelpersColumnDefinition identityColumnDefinition = null)
{
var type = typeof(T);
var propInfoLazy = _propInfoLazyCache.GetOrAdd(
$"[Type={type.Name}][Identity={identityColumnDefinition?.ColumnName ?? "N/A"}]", //Cache Key
new Lazy <List <PropInfoDefinition> >(() => //Lazy (Thread Safe Lazy Loader)
{
var propertyInfos = type.GetProperties().Select((pi) => new PropInfoDefinition(pi, identityColumnDefinition)).ToList();
return(propertyInfos);
})
);
return(propInfoLazy.Value);
}
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);
}