public async Task SqlBulkTools_UpdateQuery_MultipleConditionsTrue()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
for (int i = 0; i < books.Count; i++)
{
if (i < 20)
{
books[i].Price = 15;
}
else
{
books[i].Price = 25;
}
}
var bookToTest = books[5];
var isbn = bookToTest.ISBN;
int updatedRecords = 0;
using (TransactionScope trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
await bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.CommitAsync(conn);
updatedRecords = await bulk.Setup <Book>()
.ForObject(new Book()
{
Price = 100, WarehouseId = 5
})
.WithTable("Books")
.AddColumn(x => x.Price)
.AddColumn(x => x.WarehouseId)
.Update()
.Where(x => x.ISBN == isbn)
.And(x => x.Price == 15)
.CommitAsync(conn);
}
trans.Complete();
}
Assert.AreEqual(1, updatedRecords);
Assert.AreEqual(100, _db.Books.Single(x => x.ISBN == isbn).Price);
Assert.AreEqual(5, _db.Books.Single(x => x.ISBN == isbn).WarehouseId);
}
public void SqlBulkTools_UpdateQuery_MultipleConditionsTrue()
{
DeleteAllBooks();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
for (int i = 0; i < books.Count; i++)
{
if (i < 20)
{
books[i].Price = 15;
}
else
{
books[i].Price = 25;
}
}
var bookToTest = books[5];
var isbn = bookToTest.ISBN;
int updatedRecords = 0;
using (TransactionScope trans = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.Commit(conn);
updatedRecords = bulk.Setup <Book>()
.ForObject(new Book()
{
Price = 100, WarehouseId = 5
})
.WithTable("Books")
.AddColumn(x => x.Price)
.AddColumn(x => x.WarehouseId)
.Update()
.Where(x => x.ISBN == isbn)
.And(x => x.Price == 15)
.Commit(conn);
}
trans.Complete();
}
Assert.AreEqual(1, updatedRecords);
Assert.AreEqual(100, _dataAccess.GetBookList(isbn).Single().Price);
Assert.AreEqual(5, _dataAccess.GetBookList(isbn).Single().WarehouseId);
}
public async Task SqlBulkTools_UpdateQuery_MultipleConditionsFalse()
{
await DeleteAllBooks();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
for (int i = 0; i < books.Count; i++)
{
if (i < 20)
{
books[i].Price = 15;
}
else
{
books[i].Price = 25;
}
}
var bookToTest = books[5];
var isbn = bookToTest.ISBN;
int updatedRecords = 0;
using (TransactionScope trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
await bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.CommitAsync(conn);
// Update price to 100
updatedRecords = await bulk.Setup <Book>()
.ForObject(new Book()
{
Price = 100, WarehouseId = 5
})
.WithTable("Books")
.AddColumn(x => x.Price)
.AddColumn(x => x.WarehouseId)
.Update()
.Where(x => x.ISBN == isbn)
.And(x => x.Price == 16)
.CommitAsync(conn);
}
trans.Complete();
}
Assert.IsTrue(updatedRecords == 0);
}
public async Task Bulk_SimpleEmployeeTest()
{
var employees = EmployeesGenerator.GenerateEmployees(50);
using (var dbContext = new BulkDataContext(DataConstants.ConnectionString))
{
var bulk = new BulkOperations(new TableHandler(new PropertiesHandler()));
await bulk.BulkInsertAsync(dbContext, employees);
}
}
public void SqlBulkTools_UpdateQuery_WhenTypeIsComplex()
{
BulkOperations bulk = new BulkOperations();
var model = new ComplexTypeModel
{
AverageEstimate = new EstimatedStats
{
TotalCost = 234.3
},
MinEstimate = new EstimatedStats
{
TotalCost = 3434.33
},
Competition = 30,
SearchVolume = 234.34
};
int result;
using (TransactionScope trans = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
bulk.Setup <ComplexTypeModel>()
.ForDeleteQuery()
.WithTable("ComplexTypeTest")
.Delete()
.AllRecords()
.Commit(conn);
bulk.Setup <ComplexTypeModel>()
.ForObject(model)
.WithTable("ComplexTypeTest")
.AddAllColumns()
.Insert()
.SetIdentityColumn(x => x.Id)
.Commit(conn);
result = bulk.Setup <ComplexTypeModel>()
.ForObject(model)
.WithTable("ComplexTypeTest")
.AddAllColumns()
.Update()
.Where(x => x.MinEstimate.TotalCost > 3000)
.SetIdentityColumn(x => x.Id)
.Commit(conn);
}
trans.Complete();
}
Assert.IsTrue(result == 1);
}
public void SqlBulkTools_Update_CustomColumnMapping()
{
BulkOperations bulk = new BulkOperations();
var customColumn = new CustomColumnMappingTest()
{
NaturalIdTest = 1,
ColumnXIsDifferent = "ColumnX " + 1,
ColumnYIsDifferentInDatabase = 1
};
using (TransactionScope trans = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
bulk.Setup <CustomColumnMappingTest>()
.ForDeleteQuery()
.WithTable("CustomColumnMappingTests")
.Delete()
.AllRecords()
.Commit(conn);
bulk.Setup <CustomColumnMappingTest>()
.ForObject(customColumn)
.WithTable("CustomColumnMappingTests")
.AddAllColumns()
.CustomColumnMapping(x => x.ColumnXIsDifferent, "ColumnX")
.CustomColumnMapping(x => x.ColumnYIsDifferentInDatabase, "ColumnY")
.CustomColumnMapping(x => x.NaturalIdTest, "NaturalId")
.Insert()
.Commit(conn);
customColumn.ColumnXIsDifferent = "updated";
bulk.Setup <CustomColumnMappingTest>()
.ForObject(customColumn)
.WithTable("CustomColumnMappingTests")
.AddAllColumns()
.CustomColumnMapping(x => x.ColumnXIsDifferent, "ColumnX")
.CustomColumnMapping(x => x.ColumnYIsDifferentInDatabase, "ColumnY")
.CustomColumnMapping(x => x.NaturalIdTest, "NaturalId")
.Update()
.Where(x => x.NaturalIdTest == 1, "database_default")
.Commit(conn);
}
trans.Complete();
}
// Assert
Assert.IsTrue(_dataAccess.GetCustomColumnMappingTests().First().ColumnXIsDifferent == "updated");
}
public void SqlBulkTools_UpdateQuery_UpdateInBatches()
{
DeleteAllBooks();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(1000);
for (int i = 0; i < books.Count; i++)
{
if (i < 500)
{
books[i].Price = 15;
}
else
{
books[i].Price = 25;
}
}
int updatedRecords = 0;
using (TransactionScope trans = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.Commit(conn);
// Update price to 100
updatedRecords = bulk.Setup <Book>()
.ForObject(new Book()
{
Price = 100, WarehouseId = 5
})
.WithTable("Books")
.AddColumn(x => x.Price)
.AddColumn(x => x.WarehouseId)
.Update()
.Where(x => x.Price == 25)
.SetBatchQuantity(100)
.Commit(conn);
}
trans.Complete();
}
Assert.IsTrue(updatedRecords == 500);
}
public string SubmitCompressedBulkOperations(string callingUrl, Guid sessionId, string compressedOperations)
{
if (string.IsNullOrEmpty(compressedOperations))
{
return(CompressBulkOperationsResponse(new List <BulkOperationResponse>()));
}
BulkOperations operations = DecompressBulkOperations(compressedOperations);
return(CompressBulkOperationsResponse(SubmitBulkOperations(callingUrl, sessionId, operations)));
}
public void SqlBulkTools_Upsert_AddAllColumnsWithExistingRecord()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
BulkOperations bulk = new BulkOperations();
using (TransactionScope trans = new TransactionScope())
{
using (SqlConnection con = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
Book book = new Book()
{
BestSeller = true,
Description = "Greatest dad in the world",
Title = "Hello World",
ISBN = "1234567",
Price = 23.99M
};
bulk.Setup <Book>()
.ForObject(book)
.WithTable("Books")
.AddAllColumns()
.Insert()
.SetIdentityColumn(x => x.Id, ColumnDirectionType.InputOutput)
.Commit(con);
bulk.Setup <Book>()
.ForObject(new Book()
{
Id = book.Id,
BestSeller = true,
Description = "Greatest dad in the world",
Title = "Hello Greggo",
ISBN = "1234567",
Price = 23.99M
})
.WithTable("Books")
.AddAllColumns()
.Upsert()
.SetIdentityColumn(x => x.Id, ColumnDirectionType.InputOutput)
.MatchTargetOn(x => x.Id)
.ExcludeColumnFromUpdate(x => x.Price)
.Commit(con);
}
trans.Complete();
}
Assert.AreEqual(1, _db.Books.Count());
Assert.IsNotNull(_db.Books.SingleOrDefault(x => x.Title == "Hello Greggo"));
}
public async Task SqlBulkTools_BulkDeleteWithSelectedColumns_TestIdentityOutput()
{
await BulkDeleteAsync(_dataAccess.GetBookList());
using (
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlBulkToolsTest"].ConnectionString)
)
using (var command = new SqlCommand(
"DBCC CHECKIDENT ('[dbo].[Books]', RESEED, 10);", conn)
{
CommandType = CommandType.Text
})
{
conn.Open();
await command.ExecuteNonQueryAsync();
}
List <Book> books = _randomizer.GetRandomCollection(30);
await BulkInsertAsync(books);
BulkOperations bulk = new BulkOperations();
using (TransactionScope trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
await bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.WithBulkCopySettings(new BulkCopySettings()
{
BatchSize = 5000
})
.AddColumn(x => x.ISBN)
.BulkDelete()
.MatchTargetOn(x => x.ISBN)
.SetIdentityColumn(x => x.Id, ColumnDirectionType.InputOutput)
.CommitAsync(conn);
}
trans.Complete();
}
var test = books.First();
Assert.IsTrue(test.Id == 10 || test.Id == 11);
// Reset identity seed back to default
_dataAccess.ReseedBookIdentity(0);
}
public async Task SqlBulkTools_Upsert_AddAllColumnsWithExistingRecord()
{
await DeleteAllBooks();
BulkOperations bulk = new BulkOperations();
using (TransactionScope trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (SqlConnection con = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
Book book = new Book()
{
BestSeller = true,
Description = "Greatest dad in the world",
Title = "Hello World",
ISBN = "1234567",
Price = 23.99M
};
await bulk.Setup <Book>()
.ForObject(book)
.WithTable("Books")
.AddAllColumns()
.Insert()
.SetIdentityColumn(x => x.Id, ColumnDirectionType.InputOutput)
.CommitAsync(con);
await bulk.Setup <Book>()
.ForObject(new Book()
{
Id = book.Id,
BestSeller = true,
Description = "Greatest dad in the world",
Title = "Hello Greggo",
ISBN = "1234567",
Price = 23.99M
})
.WithTable("Books")
.AddAllColumns()
.Upsert()
.SetIdentityColumn(x => x.Id, ColumnDirectionType.Input)
.MatchTargetOn(x => x.Id)
.CommitAsync(con);
}
trans.Complete();
}
Assert.AreEqual(1, _dataAccess.GetBookCount());
Assert.IsNotNull(_dataAccess.GetBookList().SingleOrDefault(x => x.Title == "Hello Greggo"));
}
public void SqlBulkTools_UpdateQuery_MultipleConditionsFalse()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
for (int i = 0; i < books.Count; i++)
{
if (i < 20)
{
books[i].Price = 15;
}
else
{
books[i].Price = 25;
}
}
var bookToTest = books[5];
var isbn = bookToTest.ISBN;
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert();
bulk.CommitTransaction("SqlBulkToolsTest");
// Update price to 100
bulk.Setup <Book>()
.ForSimpleUpdateQuery(new Book()
{
Price = 100, WarehouseId = 5
})
.WithTable("Books")
.AddColumn(x => x.Price)
.AddColumn(x => x.WarehouseId)
.Update()
.Where(x => x.ISBN == isbn)
.And(x => x.Price == 16);
int updatedRecords = bulk.CommitTransaction("SqlBulkToolsTest");
Assert.IsTrue(updatedRecords == 0);
Assert.AreNotEqual(100, _db.Books.Single(x => x.ISBN == isbn).Price);
Assert.AreNotEqual(5, _db.Books.Single(x => x.ISBN == isbn).WarehouseId);
}
public async Task SqlBulkTools_UpdateQuery_SetPriceAndDescriptionOnSingleEntity()
{
await DeleteAllBooks();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
var bookToTest = books[5];
bookToTest.Price = 50;
var isbn = bookToTest.ISBN;
int updatedRecords = 0;
using (TransactionScope trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
await bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.CommitAsync(conn);
// Update price to 100
updatedRecords = await bulk.Setup <Book>()
.ForObject(new Book()
{
Price = 100,
Description = "Somebody will want me now! Yay"
})
.WithTable("Books")
.AddColumn(x => x.Price)
.AddColumn(x => x.Description)
.Update()
.Where(x => x.ISBN == isbn)
.CommitAsync(conn);
}
trans.Complete();
}
var firstBook = _dataAccess.GetBookList(isbn).Single();
Assert.IsTrue(updatedRecords == 1);
Assert.AreEqual(100, firstBook.Price);
Assert.AreEqual("Somebody will want me now! Yay", firstBook.Description);
}
public void SqlBulkTools_UpdateQuery_SetPriceAndDescriptionOnSingleEntity()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
var bookToTest = books[5];
bookToTest.Price = 50;
var isbn = bookToTest.ISBN;
int updatedRecords = 0;
using (TransactionScope trans = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.Commit(conn);
// Update price to 100
updatedRecords = bulk.Setup <Book>()
.ForObject(new Book()
{
Price = 100,
Description = "Somebody will want me now! Yay"
})
.WithTable("Books")
.AddColumn(x => x.Price)
.AddColumn(x => x.Description)
.Update()
.Where(x => x.ISBN == isbn)
.Commit(conn);
}
trans.Complete();
}
Assert.IsTrue(updatedRecords == 1);
Assert.AreEqual(100, _db.Books.Single(x => x.ISBN == isbn).Price);
Assert.AreEqual("Somebody will want me now! Yay", _db.Books.Single(x => x.ISBN == isbn).Description);
}
/// <summary>
///
/// </summary>
/// <param name="list"></param>
/// <param name="tableName"></param>
/// <param name="schema"></param>
public BulkTable(BulkOperations bulk, IEnumerable <T> list, string tableName, string schema)
{
this.bulk = bulk;
_list = list;
_schema = schema;
Columns = new HashSet <string>();
CustomColumnMappings = new Dictionary <string, string>();
_tableName = tableName;
Columns = new HashSet <string>();
CustomColumnMappings = new Dictionary <string, string>();
_bulkCopySettings = new BulkCopySettings();
_propertyInfoList = typeof(T).GetProperties().OrderBy(x => x.Name).ToList();
}
public void SqlBulkTools_Upsert_AddAllColumnsWithExistingRecord()
{
DeleteAllBooks();
var bulk = new BulkOperations();
using (var trans = new TransactionScope())
{
using (var con = new SqlConnection(_dataAccess.ConnectionString))
{
var book = new Book()
{
BestSeller = true,
Description = "Greatest dad in the world",
Title = "Hello World",
ISBN = "1234567",
Price = 23.99M
};
bulk.Setup <Book>()
.ForObject(book)
.WithTable("Books")
.AddAllColumns()
.Insert()
.SetIdentityColumn(x => x.Id, ColumnDirectionType.InputOutput)
.Commit(con);
bulk.Setup <Book>()
.ForObject(new Book()
{
Id = book.Id,
BestSeller = true,
Description = "Greatest dad in the world",
Title = "Hello Greggo",
ISBN = "1234567",
Price = 23.99M
})
.WithTable("Books")
.AddAllColumns()
.Upsert()
.SetIdentityColumn(x => x.Id, ColumnDirectionType.InputOutput)
.MatchTargetOn(x => x.Id)
.ExcludeColumnFromUpdate(x => x.Price)
.Commit(con);
}
trans.Complete();
}
Assert.Equal(1, _dataAccess.GetBookCount());
Assert.NotNull(_dataAccess.GetBookList().SingleOrDefault(x => x.Title == "Hello Greggo"));
}
public async Task SqlBulkTools_Insert_CustomColumnMapping()
{
BulkOperations bulk = new BulkOperations();
List <CustomColumnMappingTest> col = new List <CustomColumnMappingTest>();
for (int i = 0; i < 30; i++)
{
col.Add(new CustomColumnMappingTest()
{
NaturalIdTest = i, ColumnXIsDifferent = "ColumnX " + i, ColumnYIsDifferentInDatabase = i
});
}
var customColumn = new CustomColumnMappingTest()
{
NaturalIdTest = 1,
ColumnXIsDifferent = $"ColumnX 1",
ColumnYIsDifferentInDatabase = 1
};
using (TransactionScope trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
bulk.Setup <CustomColumnMappingTest>()
.ForDeleteQuery()
.WithTable("CustomColumnMappingTests")
.Delete()
.AllRecords()
.Commit(conn);
await bulk.Setup <CustomColumnMappingTest>()
.ForObject(customColumn)
.WithTable("CustomColumnMappingTests")
.AddAllColumns()
.CustomColumnMapping(x => x.ColumnXIsDifferent, "ColumnX")
.CustomColumnMapping(x => x.ColumnYIsDifferentInDatabase, "ColumnY")
.CustomColumnMapping(x => x.NaturalIdTest, "NaturalId")
.Insert()
.CommitAsync(conn);
}
trans.Complete();
}
// Assert
Assert.IsTrue(_dataAccess.GetCustomColumnMappingTests().First().ColumnXIsDifferent == "ColumnX 1");
}
public void SqlBulkTools_UpdateQuery_SetPriceAndDescriptionOnSingleEntity()
{
DeleteAllBooks();
var bulk = new BulkOperations();
var books = _randomizer.GetRandomCollection(30);
var bookToTest = books[5];
bookToTest.Price = 50;
var isbn = bookToTest.ISBN;
var updatedRecords = 0;
using (var trans = new TransactionScope())
{
using (var conn = new SqlConnection(_dataAccess.ConnectionString))
{
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.Commit(conn);
// Update price to 100
updatedRecords = bulk.Setup <Book>()
.ForObject(new Book()
{
Price = 100,
Description = "Somebody will want me now! Yay"
})
.WithTable("Books")
.AddColumn(x => x.Price)
.AddColumn(x => x.Description)
.Update()
.Where(x => x.ISBN == isbn)
.Commit(conn);
}
trans.Complete();
}
var firstBook = _dataAccess.GetBookList(isbn).First();
Assert.True(updatedRecords == 1);
Assert.Equal(100, firstBook.Price);
Assert.Equal("Somebody will want me now! Yay", firstBook.Description);
}
public async Task SqlBulkTools_DeleteQuery_DeleteWhenNotNullWithSchema()
{
BulkOperations bulk = new BulkOperations();
List <SchemaTest2> col = new List <SchemaTest2>();
for (int i = 0; i < 30; i++)
{
col.Add(new SchemaTest2()
{
ColumnA = "ColumnA " + i
});
}
using (TransactionScope trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
bulk.Setup <SchemaTest2>()
.ForDeleteQuery()
.WithTable("SchemaTest")
.WithSchema("AnotherSchema")
.Delete()
.AllRecords()
.Commit(conn);
await bulk.Setup <SchemaTest2>()
.ForCollection(col)
.WithTable("SchemaTest")
.WithSchema("AnotherSchema")
.AddAllColumns()
.BulkInsert()
.CommitAsync(conn);
await bulk.Setup <SchemaTest2>()
.ForDeleteQuery()
.WithTable("SchemaTest")
.WithSchema("AnotherSchema")
.Delete()
.Where(x => x.ColumnA != null)
.CommitAsync(conn);
}
trans.Complete();
}
Assert.AreEqual(0, _dataAccess.GetSchemaTest2List().Count);
}
public void SqlBulkTools_DeleteQuery_DeleteWhenNotNullWithSchema()
{
var bulk = new BulkOperations();
var col = new List <SchemaTest2>();
for (var i = 0; i < 30; i++)
{
col.Add(new SchemaTest2()
{
ColumnA = "ColumnA " + i
});
}
using (var trans = new TransactionScope())
{
using (var conn =
new SqlConnection(_dataAccess.ConnectionString))
{
bulk.Setup <SchemaTest2>()
.ForDeleteQuery()
.WithTable("SchemaTest")
.WithSchema("AnotherSchema")
.Delete()
.AllRecords()
.Commit(conn);
bulk.Setup <SchemaTest2>()
.ForCollection(col)
.WithTable("SchemaTest")
.WithSchema("AnotherSchema")
.AddAllColumns()
.BulkInsert()
.Commit(conn);
bulk.Setup <SchemaTest2>()
.ForDeleteQuery()
.WithTable("SchemaTest")
.WithSchema("AnotherSchema")
.Delete()
.Where(x => x.ColumnA != null)
.Commit(conn);
}
trans.Complete();
}
Assert.Empty(_dataAccess.GetSchemaTest2List());
}
public async Task SqlBulkTools_UpdateQuery_MultipleConditionsTrue()
{
await DeleteAllBooks();
var bulk = new BulkOperations();
var books = _randomizer.GetRandomCollection(30);
for (var i = 0; i < books.Count; i++)
{
books[i].Price = i < 20 ? 15 : 25;
}
var bookToTest = books[5];
var isbn = bookToTest.ISBN;
var updatedRecords = 0;
using (var trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (var conn = new SqlConnection(_dataAccess.ConnectionString))
{
await bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.CommitAsync(conn);
updatedRecords = await bulk.Setup <Book>()
.ForObject(new Book()
{
Price = 100, WarehouseId = 5
})
.WithTable("Books")
.AddColumn(x => x.Price)
.AddColumn(x => x.WarehouseId)
.Update()
.Where(x => x.ISBN == isbn)
.And(x => x.Price == 15)
.CommitAsync(conn);
}
trans.Complete();
}
Assert.Equal(1, updatedRecords);
Assert.Equal(100, _dataAccess.GetBookList(isbn).Single().Price);
Assert.Equal(5, _dataAccess.GetBookList(isbn).Single().WarehouseId);
}
public void SqlBulkTools_Insert_CustomColumnMapping()
{
var bulk = new BulkOperations();
var col = new List <CustomColumnMappingTest>();
for (var i = 0; i < 30; i++)
{
col.Add(new CustomColumnMappingTest()
{
NaturalIdTest = i, ColumnXIsDifferent = "ColumnX " + i, ColumnYIsDifferentInDatabase = i
});
}
var customColumn = new CustomColumnMappingTest()
{
NaturalIdTest = 1,
ColumnXIsDifferent = $"ColumnX 1",
ColumnYIsDifferentInDatabase = 1
};
using (var trans = new TransactionScope())
{
using (var conn = new SqlConnection(_dataAccess.ConnectionString))
{
bulk.Setup <CustomColumnMappingTest>()
.ForDeleteQuery()
.WithTable("CustomColumnMappingTests")
.Delete()
.AllRecords()
.Commit(conn);
bulk.Setup <CustomColumnMappingTest>()
.ForObject(customColumn)
.WithTable("CustomColumnMappingTests")
.AddAllColumns()
.CustomColumnMapping(x => x.ColumnXIsDifferent, "ColumnX")
.CustomColumnMapping(x => x.ColumnYIsDifferentInDatabase, "ColumnY")
.CustomColumnMapping(x => x.NaturalIdTest, "NaturalId")
.Insert()
.Commit(conn);
}
trans.Complete();
}
// Assert
Assert.True(_dataAccess.GetCustomColumnMappingTests().First().ColumnXIsDifferent == "ColumnX 1");
}
public void SqlBulkTools_DeleteQuery_DeleteWithMultipleConditions()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
for (int i = 0; i < books.Count; i++)
{
if (i < 6)
{
books[i].Price = 1 + (i * 100);
books[i].WarehouseId = 1;
books[i].Description = null;
}
}
int deletedRecords = 0;
using (TransactionScope trans = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.Commit(conn);
deletedRecords = bulk.Setup <Book>()
.ForDeleteQuery()
.WithTable("Books")
.Delete()
.Where(x => x.WarehouseId == 1)
.And(x => x.Price >= 100)
.And(x => x.Description == null)
.Commit(conn);
}
trans.Complete();
}
Assert.AreEqual(5, deletedRecords);
Assert.AreEqual(25, _db.Books.Count());
}
private BulkOperations DecompressBulkOperations(string compressedOperations)
{
MemoryStream compressedOperationsStream = null;
MemoryStream decompressedOperationsStream = null;
ZlibStream decompressionStream = null;
try
{
byte[] compressedOperationsBytes = Convert.FromBase64String(compressedOperations);
compressedOperationsStream = new MemoryStream(compressedOperationsBytes);
byte[] buffer = new byte[1024];
int numBytesRead = 0;
bool start = true;
decompressedOperationsStream = new MemoryStream();
using (decompressionStream = new ZlibStream(compressedOperationsStream, CompressionMode.Decompress))
{
while (start || numBytesRead > 0)
{
numBytesRead = decompressionStream.Read(buffer, 0, buffer.Length);
if (numBytesRead > 0)
{
decompressedOperationsStream.Write(buffer, 0, numBytesRead);
}
start = false;
}
}
decompressedOperationsStream.Position = 0;
DataContractSerializer deserializer = new DataContractSerializer(typeof(BulkOperations));
BulkOperations operations = deserializer.ReadObject(decompressedOperationsStream) as BulkOperations;
return(operations);
}
finally
{
if (decompressedOperationsStream != null)
{
decompressedOperationsStream.Dispose();
decompressedOperationsStream = null;
}
}
}
public void SqlBulkTools_UpdateQuery_MultipleConditionsFalse()
{
DeleteAllBooks();
var bulk = new BulkOperations();
var books = _randomizer.GetRandomCollection(30);
for (var i = 0; i < books.Count; i++)
{
books[i].Price = i < 20 ? 15 : 25;
}
var bookToTest = books[5];
var isbn = bookToTest.ISBN;
var updatedRecords = 0;
using (var trans = new TransactionScope())
{
using (var conn = new SqlConnection(_dataAccess.ConnectionString))
{
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.Commit(conn);
// Update price to 100
updatedRecords = bulk.Setup <Book>()
.ForObject(new Book()
{
Price = 100, WarehouseId = 5
})
.WithTable("Books")
.AddColumn(x => x.Price)
.AddColumn(x => x.WarehouseId)
.Update()
.Where(x => x.ISBN == isbn)
.And(x => x.Price == 16)
.Commit(conn);
}
trans.Complete();
}
Assert.True(updatedRecords == 0);
}
public void SqlBulkTools_BulkUpdateOnIdentityColumn(int rows)
{
var fixture = new Fixture();
fixture.Customizations.Add(new PriceBuilder());
fixture.Customizations.Add(new IsbnBuilder());
fixture.Customizations.Add(new TitleBuilder());
BulkOperations bulk = new BulkOperations();
BulkDelete(_db.Books.ToList());
_bookCollection = _randomizer.GetRandomCollection(rows);
bulk.Setup <Book>()
.ForCollection(_bookCollection)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.SetIdentityColumn(x => x.Id, ColumnDirection.InputOutput);
bulk.CommitTransaction("SqlBulkToolsTest");
// Update half the rows
for (int j = 0; j < rows / 2; j++)
{
var newBook = fixture.Build <Book>().Without(s => s.Id).Without(s => s.ISBN).Create();
var prevId = _bookCollection[j].Id;
_bookCollection[j] = newBook;
_bookCollection[j].Id = prevId;
}
bulk.Setup <Book>()
.ForCollection(_bookCollection)
.WithTable("Books")
.AddAllColumns()
.BulkUpdate()
.MatchTargetOn(x => x.Id)
.SetIdentityColumn(x => x.Id);
bulk.CommitTransaction("SqlBulkToolsTest");
var testUpdate = _db.Books.FirstOrDefault();
Assert.AreEqual(_bookCollection[0].Price, testUpdate.Price);
Assert.AreEqual(_bookCollection[0].Title, testUpdate.Title);
Assert.AreEqual(_db.Books.Count(), _bookCollection.Count);
}
public async Task SqlBulkTools_BulkDeleteWithSelectedColumns_TestIdentityOutput()
{
_db.Books.RemoveRange(_db.Books.ToList());
await _db.SaveChangesAsync();
using (
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlBulkToolsTest"].ConnectionString)
)
using (var command = new SqlCommand(
"DBCC CHECKIDENT ('[dbo].[Books]', RESEED, 10);", conn)
{
CommandType = CommandType.Text
})
{
conn.Open();
await command.ExecuteNonQueryAsync();
}
List <Book> books = _randomizer.GetRandomCollection(30);
await BulkInsertAsync(books);
BulkOperations bulk = new BulkOperations();
using (TransactionScope trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
await bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.WithBulkCopyBatchSize(5000)
.AddColumn(x => x.ISBN)
.BulkDelete()
.MatchTargetOn(x => x.ISBN)
.SetIdentityColumn(x => x.Id, ColumnDirectionType.InputOutput)
.CommitAsync(conn);
}
trans.Complete();
}
var test = books.First();
var expected = 11;
Assert.AreEqual(expected, test.Id);
}
public async Task SqlBulkTools_DeleteQuery_DeleteWithMultipleConditions()
{
await DeleteAllBooks();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
for (int i = 0; i < books.Count; i++)
{
if (i < 6)
{
books[i].Price = 1 + (i * 100);
books[i].WarehouseId = 1;
books[i].Description = null;
}
}
int deletedRecords = 0;
using (TransactionScope trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
await bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.CommitAsync(conn);
deletedRecords = await bulk.Setup <Book>()
.ForDeleteQuery()
.WithTable("Books")
.Delete()
.Where(x => x.WarehouseId == 1)
.And(x => x.Price >= 100)
.And(x => x.Description == null)
.CommitAsync(conn);
}
trans.Complete();
}
Assert.AreEqual(5, deletedRecords);
Assert.AreEqual(25, _dataAccess.GetBookList().Count);
}
private async Task ProcessChangesAsync(IReadOnlyCollection <DocumentMetadata> docs, CancellationToken cancellationToken)
{
try
{
Boolean isSyntheticKey = this.SourcePartitionKeys.Contains(",");
Boolean isNestedAttribute = this.SourcePartitionKeys.Contains("/");
Container targetContainer = this.destinationCollectionClient.GetContainer(this.config.DestDbName, this.config.DestCollectionName);
this.containerToStoreDocuments = targetContainer;
DocumentMetadata document;
BulkOperations <DocumentMetadata> bulkOperations = new BulkOperations <DocumentMetadata>(docs.Count);
foreach (DocumentMetadata doc in docs)
{
document = (this.SourcePartitionKeys != null & this.TargetPartitionKey != null) ?
MapPartitionKey(doc, isSyntheticKey, this.TargetPartitionKey, isNestedAttribute, this.SourcePartitionKeys) :
document = doc;
if (this.config.OnlyInsertMissingItems)
{
bulkOperations.Tasks.Add(this.containerToStoreDocuments.CreateItemAsync(
item: document,
cancellationToken: cancellationToken).CaptureOperationResponse(document, ignoreConflicts: true));
}
else
{
bulkOperations.Tasks.Add(this.containerToStoreDocuments.UpsertItemAsync(
item: document,
cancellationToken: cancellationToken).CaptureOperationResponse(document, ignoreConflicts: true));
}
}
BulkOperationResponse <DocumentMetadata> bulkOperationResponse = await bulkOperations.ExecuteAsync().ConfigureAwait(false);
if (bulkOperationResponse.Failures.Count > 0 && this.deadletterClient != null)
{
await this.WriteFailedDocsToBlob("FailedImportDocs", this.deadletterClient, bulkOperationResponse)
.ConfigureAwait(false);
}
TelemetryHelper.Singleton.LogMetrics(bulkOperationResponse);
}
catch (Exception error)
{
TelemetryHelper.Singleton.LogError(
"Processing changes in change feed processor {0} failed: {1}",
this.processorName,
error);
throw;
}
}
public void SqlBulkTools_BulkInsertAddInvalidDataType_ThrowsSqlBulkToolsExceptionException()
{
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
BulkInsert(books);
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddColumn(x => x.ISBN)
.AddColumn(x => x.InvalidType)
.BulkInsert();
Assert.Throws <SqlBulkToolsException>(() => bulk.CommitTransaction("SqlBulkToolsTest"));
}
public BulkOperationsTest()
{
_vimHost = new Mock<IVimHost>(MockBehavior.Loose);
_bulkOperationsRaw = new BulkOperations(_vimHost.Object);
_bulkOperations = _bulkOperationsRaw;
}
