public void BulkOperationsHelpers_BuildUpdateSet_BuildsCorrectSequenceForSingleColumn()
{
// Arrange
var updateOrInsertColumns = new HashSet <string> {
"Id"
};
const string expected = "SET [Target].[Id] = [Source].[Id] ";
// Act
var result = BulkOperationsHelper.BuildUpdateSet(updateOrInsertColumns, "Source", "Target", null);
// Assert
Assert.Equal(expected, result);
}
public void BulkOperationsHelpers_BuildInsertIntoSet_BuildsCorrectSequenceForMultipleColumns()
{
var columns = GetTestColumns();
var tableName = "TableName";
var expected =
"INSERT INTO TableName ([Name], [Town], [Email], [IsCool]) ";
var sut = new BulkOperationsHelper();
// Act
var result = sut.BuildInsertIntoSet(columns, "id", tableName);
// Assert
Assert.AreEqual(result, expected);
}
public void BulkOperationsHelpers_GetAllValueTypeAndStringColumns_ReturnsCorrectSet()
{
// Arrange
BulkOperationsHelper helper = new BulkOperationsHelper();
HashSet <string> expected = new HashSet <string>()
{
"Title", "CreatedTime", "BoolTest", "IntegerTest", "Price"
};
// Act
var result = helper.GetAllValueTypeAndStringColumns(typeof(ModelWithMixedTypes));
// Assert
CollectionAssert.AreEqual(expected, result);
}
public void BulkOperationsHelpers_GetAllValueTypeAndStringColumns_ReturnsCorrectSet()
{
// Arrange
HashSet <string> expected = new HashSet <string>()
{
"BoolTest", "CreatedTime", "IntegerTest", "Price", "Title"
};
List <PropertyInfo> propertyInfoList = typeof(ModelWithMixedTypes).GetProperties().OrderBy(x => x.Name).ToList();
// Act
var result = BulkOperationsHelper.GetAllValueTypeAndStringColumns(propertyInfoList, typeof(ModelWithMixedTypes));
// Assert
CollectionAssert.AreEqual(expected.ToList(), result.ToList());
}
/// <summary>
/// Add each column that you want to include in the query. Only include the columns that are relevant to the
/// procedure for best performance.
/// </summary>
/// <param name="columnName">Column name as represented in database</param>
/// <param name="destination">The actual name of column as represented in SQL table. By default SqlBulkTools will attempt to match the model property names to SQL column names (case insensitive).
/// If any of your model property names do not match
/// the SQL table column(s) as defined in given table, then use this overload to set up a custom mapping. </param>
/// <returns></returns>
public BulkAddColumn <T> AddColumn(Expression <Func <T, object> > columnName, string destination)
{
if (destination == null)
{
throw new ArgumentNullException(nameof(destination));
}
var propertyName = BulkOperationsHelper.GetPropertyName(columnName);
Columns.Add(propertyName);
CustomColumnMappings.Add(propertyName, destination);
return(new BulkAddColumn <T>(_bulk, _list, _tableName, Columns, CustomColumnMappings, _schema, _bulkCopySettings, _propertyInfoList));
}
public void BulkOperationsHelper_BuildValueSet_WithMultipleValuesWhenIdentitySet()
{
// Arrange
HashSet <String> columns = new HashSet <string>();
columns.Add("TestColumnA");
columns.Add("TestColumnB");
columns.Add("Id");
// Act
string result = BulkOperationsHelper.BuildValueSet(columns, "Id");
// Assert
Assert.AreEqual("(@TestColumnA, @TestColumnB)", result);
}
public void BulkOperationsHelpers_BuildInsertSet_BuildsCorrectSequenceForSingleColumn()
{
// Arrange
var updateOrInsertColumns = new HashSet <string>();
updateOrInsertColumns.Add("Id");
var expected =
"INSERT ([Id]) values ([Source].[Id])";
// Act
var result = BulkOperationsHelper.BuildInsertSet(updateOrInsertColumns, "Source", null);
// Assert
Assert.AreEqual(expected, result);
}
public void BulkOperationsHelper_GetAllPropertiesForComplexType_ReturnsCorrectSet()
{
// Arrange
HashSet <string> expected = new HashSet <string>()
{
"AverageEstimate_TotalCost", "AverageEstimate_CreationDate", "Competition", "Id", "MinEstimate_TotalCost", "MinEstimate_CreationDate", "SearchVolume"
};
List <PropertyInfo> propertyInfoList = typeof(ComplexTypeModel).GetProperties().OrderBy(x => x.Name).ToList();
// Act
var result = BulkOperationsHelper.GetAllValueTypeAndStringColumns(propertyInfoList, typeof(ComplexTypeModel));
// Assert
CollectionAssert.AreEqual(expected.ToList(), result.ToList());
}
public void BulkOperationsHelpers_BuildJoinConditionsForUpdateOrInsertWithTwoConditions()
{
// Arrange
var joinOnList = new List <string> {
"MarketPlaceId", "FK_BusinessId"
};
// Act
var result = BulkOperationsHelper.BuildJoinConditionsForInsertOrUpdate(joinOnList.ToArray(), "Source", "Target", new Dictionary <string, string> {
{ "FK_BusinessId", "DEFAULT_COLLATION" }
}, new Dictionary <string, bool>());
// Assert
Assert.Equal("ON ([Target].[MarketPlaceId] = [Source].[MarketPlaceId]) AND ([Target].[FK_BusinessId] = [Source].[FK_BusinessId] COLLATE DEFAULT_COLLATION) ", result);
}
public void BulkOperationsHelper_BuildMatchTargetOnListWithSingleValue_ReturnsCorrectString()
{
// Arrange
var columns = new HashSet <string> {
"id"
};
// ACt
var result = BulkOperationsHelper.BuildMatchTargetOnList(columns, new Dictionary <string, string> {
{ "id", "DEFAULT_COLLATION" }
}, new Dictionary <string, string>());
// Assert
Assert.Equal(result, "WHERE [id] = @id COLLATE DEFAULT_COLLATION");
}
public void BulkOperationsHelpers_BuildJoinConditionsForUpdateOrInsertWitSingleCondition()
{
// Arrange
List <string> joinOnList = new List <string>()
{
"MarketPlaceId"
};
var sut = new BulkOperationsHelper();
// Act
var result = sut.BuildJoinConditionsForUpdateOrInsert(joinOnList.ToArray(), "Source", "Target");
// Assert
Assert.AreEqual("ON [Target].[MarketPlaceId] = [Source].[MarketPlaceId] ", result);
}
public void BulkOperationsHelpers_BuildInsertIntoSet_BuildsCorrectSequenceForSingleColumn()
{
// Arrange
var columns = new HashSet <string> {
"Id"
};
const string tableName = "TableName";
const string expected = "INSERT INTO TableName ([Id]) ";
// Act
var result = BulkOperationsHelper.BuildInsertIntoSet(columns, null, tableName);
// Assert
Assert.Equal(expected, result);
}
public void BulkOperationsHelper_CreateDataTableForComplexType_IsStructuredCorrectly()
{
var columns = new HashSet <string> {
"AverageEstimate_TotalCost", "AverageEstimate_CreationDate", "Competition", "MinEstimate_TotalCost", "MinEstimate_CreationDate", "SearchVolume"
};
var propertyInfoList = typeof(ComplexTypeModel).GetProperties().OrderBy(x => x.Name).ToList();
var result = BulkOperationsHelper.CreateDataTable <ComplexTypeModel>(propertyInfoList, columns, null, new Dictionary <string, int>());
Assert.Equal(result.Columns["AverageEstimate_TotalCost"].DataType, typeof(double));
Assert.Equal(result.Columns["AverageEstimate_CreationDate"].DataType, typeof(DateTime));
Assert.Equal(result.Columns["MinEstimate_TotalCost"].DataType, typeof(double));
Assert.Equal(result.Columns["MinEstimate_CreationDate"].DataType, typeof(DateTime));
Assert.Equal(result.Columns["SearchVolume"].DataType, typeof(double));
Assert.Equal(result.Columns["Competition"].DataType, typeof(double));
}
public void BulkOperationsHelpers_GetIndexManagementCmd_WhenDisableAllIndexesIsTrueReturnsCorrectCmd()
{
// Arrange
const string expected = @"DECLARE @sql AS VARCHAR(MAX)=''; SELECT @sql = @sql + 'ALTER INDEX ' + sys.indexes.name + ' ON ' + sys.objects.name + ' DISABLE;' FROM sys.indexes JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id WHERE sys.indexes.type_desc = 'NONCLUSTERED' AND sys.objects.type_desc = 'USER_TABLE' AND sys.objects.name = '[SqlBulkTools].[dbo].[Books]'; EXEC(@sql);";
const string databaseName = "SqlBulkTools";
var sqlConnMock = Substitute.For <IDbConnection>();
sqlConnMock.Database.Returns(databaseName);
// Act
var result = BulkOperationsHelper.GetIndexManagementCmd(Constants.Disable, "Books", "dbo", sqlConnMock);
// Assert
Assert.Equal(expected, result);
}
public void BulkOperationsHelpers_BuildInsertIntoSet_BuildsCorrectSequenceForSingleColumn()
{
// Arrange
var columns = new HashSet <string>();
columns.Add("Id");
var tableName = "TableName";
var expected = "INSERT INTO TableName ([Id]) ";
// Act
var result = BulkOperationsHelper.BuildInsertIntoSet(columns, null, tableName);
// Assert
Assert.AreEqual(result, expected);
}
public void BulkOperationsHelpers_BuildUpdateSet_BuildsCorrectSequenceForSingleColumn()
{
// Arrange
var updateOrInsertColumns = new HashSet <string>();
updateOrInsertColumns.Add("Id");
var expected =
"UPDATE SET [Target].[Id] = [Source].[Id] ";
var sut = new BulkOperationsHelper();
// Act
var result = sut.BuildUpdateSet(updateOrInsertColumns, "Source", "Target", null);
// Assert
Assert.AreEqual(expected, result);
}
public void BulkOperationsHelpers_GetIndexManagementCmd_WhenDisableAllIndexesIsTrueReturnsCorrectCmd()
{
// Arrange
string expected =
@"DECLARE @sql AS VARCHAR(MAX)=''; SELECT @sql = @sql + 'ALTER INDEX ' + sys.indexes.name + ' ON ' + sys.objects.name + ' DISABLE;' FROM sys.indexes JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id WHERE sys.indexes.type_desc = 'NONCLUSTERED' AND sys.objects.type_desc = 'USER_TABLE' AND sys.objects.name = '[SqlBulkTools].[dbo].[Books]'; EXEC(@sql);";
BulkOperationsHelper helper = new BulkOperationsHelper();
var databaseName = "SqlBulkTools";
var sqlConnMock = new Mock <IDbConnection>();
sqlConnMock.Setup(x => x.Database).Returns(databaseName);
// Act
string result = helper.GetIndexManagementCmd(IndexOperation.Disable, "Books", "dbo", sqlConnMock.Object, null, true);
// Assert
Assert.AreEqual(expected, result);
}
/// <summary>
/// Removes a column that you want to be excluded.
/// </summary>
/// <param name="columnName"></param>
/// <returns></returns>
/// <exception cref="SqlBulkToolsException"></exception>
public QueryAddColumnList <T> RemoveColumn(Expression <Func <T, object> > columnName)
{
var propertyName = BulkOperationsHelper.GetPropertyName(columnName);
if (_columns.Contains(propertyName))
{
_columns.Remove(propertyName);
}
else
{
throw new SqlBulkToolsException("Could not remove the column with name "
+ columnName +
". This could be because it's not a value or string type and therefore not included.");
}
return(this);
}
public void BulkOperationsHelpers_GetIndexManagementCmd_WithOneIndexReturnsCorrectCmd()
{
// Arrange
string expected =
@"DECLARE @sql AS VARCHAR(MAX)=''; SELECT @sql = @sql + 'ALTER INDEX ' + sys.indexes.name + ' ON ' + sys.objects.name + ' DISABLE;' FROM sys.indexes JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id WHERE sys.indexes.type_desc = 'NONCLUSTERED' AND sys.objects.type_desc = 'USER_TABLE' AND sys.objects.name = '[SqlBulkTools].[dbo].[Books]' AND sys.indexes.name = 'IX_Title'; EXEC(@sql);";
HashSet <string> indexes = new HashSet <string>();
indexes.Add("IX_Title");
var databaseName = "SqlBulkTools";
var sqlConnMock = new Mock <IDbConnection>();
sqlConnMock.Setup(x => x.Database).Returns(databaseName);
// Act
string result = BulkOperationsHelper.GetIndexManagementCmd(Constants.Disable, "Books", "dbo", sqlConnMock.Object, indexes);
// Assert
Assert.AreEqual(expected, result);
}
public void BulkOperationsHelper_AddSqlParamsForUpdateQuery_GetsTypeAndValue()
{
var book = new Book
{
ISBN = "Some ISBN",
Price = 23.99M,
BestSeller = true
};
var columns = new HashSet <string> {
"ISBN", "Price", "BestSeller"
};
var sqlParams = new List <SqlParameter>();
var propertyInfoList = typeof(Book).GetProperties().OrderBy(x => x.Name).ToList();
BulkOperationsHelper.AddSqlParamsForQuery(propertyInfoList, sqlParams, columns, book);
Assert.Equal(3, sqlParams.Count);
}
public void BulkOperationsHelper_AddSqlParamsForUpdateQuery_GetsTypeAndValue()
{
Book book = new Book()
{
ISBN = "Some ISBN",
Price = 23.99M,
BestSeller = true
};
HashSet <string> columns = new HashSet <string>();
columns.Add("ISBN");
columns.Add("Price");
columns.Add("BestSeller");
List <SqlParameter> sqlParams = new List <SqlParameter>();
BulkOperationsHelper.AddSqlParamsForQuery(sqlParams, columns, book);
Assert.AreEqual(3, sqlParams.Count);
}
/// <summary>
/// Adds all properties in model that are either value, string, char[] or byte[] type.
/// </summary>
/// <returns></returns>
public BulkAddColumnList <T> AddAllColumns()
{
if (typeof(T).IsInstanceOfType(typeof(IDynamicMetaObjectProvider)))
{
var firstElement = _list.FirstOrDefault();
if (firstElement == null)
{
throw new Exception("Collection is empty");
}
if (firstElement is IDictionary <string, object> dict)
{
Columns = new HashSet <string>(dict.Keys);
}
}
else
{
Columns = BulkOperationsHelper.GetAllValueTypeAndStringColumns(_propertyInfoList, typeof(T));
}
return(new BulkAddColumnList <T>(bulk, _list, _tableName, Columns, CustomColumnMappings, _schema, _bulkCopySettings, _propertyInfoList));
}
/// <summary>
/// Sets the identity column for the table.
/// </summary>
/// <param name="columnName"></param>
/// <returns></returns>
public QueryInsertReady <T> SetIdentityColumn(Expression <Func <T, object> > columnName)
{
var propertyName = BulkOperationsHelper.GetPropertyName(columnName);
if (propertyName == null)
{
throw new SqlBulkToolsException("SetIdentityColumn column name can't be null");
}
if (_identityColumn == null)
{
_identityColumn = BulkOperationsHelper.GetActualColumn(_customColumnMappings, propertyName);
}
else
{
throw new SqlBulkToolsException("Can't have more than one identity column");
}
_columns.Add(propertyName);
return(this);
}
public void BulkOperationsHelper_BuildPredicateQuery_IsNotNullCondition()
{
// Arrange
var targetAlias = "Target";
var updateOn = new[] { "stub" };
var conditions = new List <PredicateCondition>()
{
new PredicateCondition()
{
Expression = ExpressionType.NotEqual,
LeftName = "Description",
Value = "null",
}
};
var expected = "AND [Target].[Description] IS NOT NULL ";
// Act
var result = BulkOperationsHelper.BuildPredicateQuery(updateOn, conditions, targetAlias, null);
// Assert
Assert.AreEqual(expected, result);
}
public void BulkOperationsHelper_BuildPredicateQuery_GreaterThanOrEqualTo()
{
// Arrange
var targetAlias = "Target";
var updateOn = new[] { "stub" };
var conditions = new List <PredicateCondition>()
{
new PredicateCondition()
{
Expression = ExpressionType.GreaterThanOrEqual,
LeftName = "Description",
Value = "null",
SortOrder = 1
}
};
var expected = "AND [Target].[Description] >= @DescriptionCondition1 ";
// Act
var result = BulkOperationsHelper.BuildPredicateQuery(updateOn, conditions, targetAlias, null);
// Assert
Assert.AreEqual(expected, result);
}
/// <summary>
/// Sets the identity column for the table.
/// </summary>
/// <param name="columnName"></param>
/// <param name="direction"></param>
/// <returns></returns>
public QueryInsertReady <T> SetIdentityColumn(Expression <Func <T, object> > columnName, ColumnDirectionType direction)
{
var propertyName = BulkOperationsHelper.GetPropertyName(columnName);
_outputIdentity = direction;
if (propertyName == null)
{
throw new SqlBulkToolsException("SetIdentityColumn column name can't be null");
}
if (_identityColumn == null)
{
_identityColumn = _customColumnMappings.TryGetValue(propertyName, out var actualPropertyName) ? actualPropertyName : propertyName;
}
else
{
throw new SqlBulkToolsException("Can't have more than one identity column");
}
_columns.Add(propertyName);
return(this);
}
/// <summary>
/// Commits a transaction to database asynchronously. A valid setup must exist for the operation to be
/// successful.
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
/// <exception cref="IdentityException"></exception>
public async Task <int> CommitAsync(SqlConnection connection)
{
int affectedRows = 0;
if (_singleEntity == null)
{
return(affectedRows);
}
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
try
{
BulkOperationsHelper.AddSqlParamsForQuery(_propertyInfoList, _sqlParams, _columns, _singleEntity, _identityColumn, _outputIdentity, _customColumnMappings);
BulkOperationsHelper.DoColumnMappings(_customColumnMappings, _columns);
SqlCommand command = connection.CreateCommand();
command.Connection = connection;
string fullQualifiedTableName = BulkOperationsHelper.GetFullQualifyingTableName(connection.Database, _schema,
_tableName);
StringBuilder sb = new StringBuilder();
sb.Append($"{BulkOperationsHelper.BuildInsertIntoSet(_columns, _identityColumn, fullQualifiedTableName)} " +
$"VALUES{BulkOperationsHelper.BuildValueSet(_columns, _identityColumn)} ");
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
sb.Append($"SET @{_identityColumn}=SCOPE_IDENTITY();");
}
command.CommandText = sb.ToString();
if (_sqlParams.Count > 0)
{
command.Parameters.AddRange(_sqlParams.ToArray());
}
affectedRows = await command.ExecuteNonQueryAsync();
if (_outputIdentity == ColumnDirectionType.InputOutput)
{
foreach (var x in _sqlParams)
{
if (x.Direction == ParameterDirection.InputOutput &&
x.ParameterName == $"@{_identityColumn}")
{
PropertyInfo propertyInfo = _singleEntity.GetType().GetProperty(_identityColumn);
propertyInfo.SetValue(_singleEntity, x.Value);
break;
}
}
}
return(affectedRows);
}
catch (SqlException e)
{
for (int i = 0; i < e.Errors.Count; i++)
{
// Error 8102 and 544 is identity error.
if (e.Errors[i].Number == 544 || e.Errors[i].Number == 8102)
{
// Expensive but neccessary to inform user of an important configuration setup.
throw new IdentityException(e.Errors[i].Message);
}
}
throw;
}
}
/// <summary>
/// Adds all properties in model that are either value, string, char[] or byte[] type.
/// </summary>
/// <returns></returns>
public BulkAddColumnList <T> AddAllColumns()
{
Columns = BulkOperationsHelper.GetAllValueTypeAndStringColumns(_propertyInfoList, typeof(T));
return(new BulkAddColumnList <T>(_list, _tableName, Columns, CustomColumnMappings, _schema, _bulkCopySettings, _propertyInfoList));
}
public void GetTableAndSchema_WithAnInvalidName()
{
var result = BulkOperationsHelper.GetTableAndSchema("TestSchema.InvalidName.MyTable");
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public QueryAddColumnList <T> AddAllColumns()
{
Columns = BulkOperationsHelper.GetAllValueTypeAndStringColumns(_propertyInfoList, typeof(T));
return(new QueryAddColumnList <T>(_singleEntity, _tableName, Columns, _schema, _sqlParams, _propertyInfoList));
}
