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(_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(_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(_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 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 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);
}
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_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 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_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 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 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_MultipleConditionsTrue()
{
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);
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.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 async Task SqlBulkTools_UpdateQuery_MultipleConditionsFalse()
{
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);
// 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.True(updatedRecords == 0);
}
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 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());
}
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_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);
}
public void SqlBulkTools_WhenUsingReservedSqlKeywords()
{
_db.ReservedColumnNameTest.RemoveRange(_db.ReservedColumnNameTest.ToList());
BulkOperations bulk = new BulkOperations();
var list = new List <ReservedColumnNameTest>();
for (int i = 0; i < 30; i++)
{
list.Add(new ReservedColumnNameTest()
{
Key = i
});
}
bulk.Setup <ReservedColumnNameTest>()
.ForCollection(list)
.WithTable("ReservedColumnNameTests")
.AddAllColumns()
.BulkInsertOrUpdate()
.MatchTargetOn(x => x.Id)
.SetIdentityColumn(x => x.Id);
bulk.CommitTransaction("SqlBulkToolsTest");
Assert.IsTrue(_db.ReservedColumnNameTest.Any());
}
public async Task SqlBulkTools_Insert_AddAllColumns()
{
await DeleteAllBooks();
BulkOperations bulk = new BulkOperations();
int insertedRecords = 0;
using (TransactionScope trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
insertedRecords = await bulk.Setup <Book>()
.ForObject(new Book()
{
BestSeller = true,
Description = "Greatest dad in the world",
Title = "Hello World",
ISBN = "1234567",
Price = 23.99M
})
.WithTable("Books")
.AddAllColumns()
.Insert()
.SetIdentityColumn(x => x.Id)
.CommitAsync(conn);
}
trans.Complete();
}
Assert.AreEqual(1, insertedRecords);
Assert.IsNotNull(_dataAccess.GetBookList("1234567").SingleOrDefault());
}
public void SqlBulkTools_Upsert_AddAllColumns()
{
DeleteAllBooks();
using (TransactionScope tx = new TransactionScope())
{
using (SqlConnection con = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
var bulk = new BulkOperations();
bulk.Setup <Book>()
.ForObject(new Book()
{
BestSeller = true,
Description = "Greatest dad in the world",
Title = "Hello World",
ISBN = "1234567",
Price = 23.99M
})
.WithTable("Books")
.AddAllColumns()
.Upsert()
.SetIdentityColumn(x => x.Id, ColumnDirectionType.InputOutput)
.MatchTargetOn(x => x.Id)
.Commit(con);
}
tx.Complete();
}
Assert.AreEqual(1, _dataAccess.GetBookCount());
Assert.IsNotNull(_dataAccess.GetBookList("1234567").SingleOrDefault());
}
public async Task SqlBulkTools_Insert_ManualAddColumn()
{
await DeleteAllBooks();
var bulk = new BulkOperations();
var insertedRecords = 0;
using (var trans = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (var conn = new SqlConnection(_dataAccess.ConnectionString))
{
insertedRecords = await bulk.Setup <Book>()
.ForObject(new Book()
{
BestSeller = true, Description = "Greatest dad in the world", Title = "Hello World", ISBN = "1234567", Price = 23.99M
})
.WithTable("Books")
.AddColumn(x => x.Title)
.AddColumn(x => x.ISBN)
.AddColumn(x => x.BestSeller)
.AddColumn(x => x.Description)
.AddColumn(x => x.Price)
.Insert()
.SetIdentityColumn(x => x.Id, ColumnDirectionType.InputOutput)
.CommitAsync(conn);
}
trans.Complete();
}
Assert.Equal(1, insertedRecords);
Assert.NotNull(_dataAccess.GetBookList("1234567").SingleOrDefault());
}
public void Insert(IList <Motorista> motoristas)
{
var bulk = new BulkOperations();
using (TransactionScope trans = new TransactionScope())
{
//using (SqlConnection conn = Context.Open())
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionMotoristas"].ConnectionString))
{
bulk.Setup()
.ForCollection(motoristas)
.WithTable("Motoristas")
.AddColumn(x => x.Id)
.AddColumn(x => x.CEP)
.AddColumn(x => x.CPFCNPJ)
.AddColumn(x => x.Email)
.AddColumn(x => x.Nome)
.AddColumn(x => x.Telefone)
.BulkInsert()
.Commit(conn);
trans.Complete();
}
}
}
public void SqlBulkTools_BulkUpdateWithSelectedColumns_TestIdentityOutput()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
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.Description)
.AddColumn(x => x.Title)
.AddColumn(x => x.Price)
.BulkUpdate()
.MatchTargetOn(x => x.ISBN)
.SetIdentityColumn(x => x.Id, ColumnDirection.InputOutput);
bulk.CommitTransaction("SqlBulkToolsTest");
var test = _db.Books.ToList().ElementAt(10); // Random book within the 30 elements
var expected = books.Single(x => x.ISBN == test.ISBN);
Assert.AreEqual(expected.Id, test.Id);
}
public void SqlBulkTools_IdentityColumnSet_UpdatesTargetWhenSetIdentityColumn()
{
// Arrange
BulkDelete(_db.Books);
BulkOperations bulk = new BulkOperations();
_bookCollection = _randomizer.GetRandomCollection(20);
string testDesc = "New Description";
BulkInsert(_bookCollection);
_bookCollection = _db.Books.ToList();
_bookCollection.First().Description = testDesc;
bulk.Setup <Book>()
.ForCollection(_bookCollection)
.WithTable("Books")
.AddAllColumns()
.BulkUpdate()
.SetIdentityColumn(x => x.Id)
.MatchTargetOn(x => x.Id);
// Act
bulk.CommitTransaction("SqlBulkToolsTest");
// Assert
Assert.AreEqual(testDesc, _db.Books.First().Description);
}
public void SqlBulkTools_BulkInsertWithColumnMappings_CorrectlyMapsColumns()
{
BulkOperations bulk = new BulkOperations();
List <CustomColumnMappingTest> col = new List <CustomColumnMappingTest>();
for (int i = 0; i < 30; i++)
{
col.Add(new CustomColumnMappingTest()
{
NaturalId = i, ColumnXIsDifferent = "ColumnX " + i, ColumnYIsDifferentInDatabase = i
});
}
_db.CustomColumnMappingTest.RemoveRange(_db.CustomColumnMappingTest.ToList());
_db.SaveChanges();
bulk.Setup <CustomColumnMappingTest>()
.ForCollection(col)
.WithTable("CustomColumnMappingTests")
.AddAllColumns()
.CustomColumnMapping(x => x.ColumnXIsDifferent, "ColumnX")
.CustomColumnMapping(x => x.ColumnYIsDifferentInDatabase, "ColumnY")
.BulkInsert();
bulk.CommitTransaction("SqlBulkToolsTest");
// Assert
Assert.IsTrue(_db.CustomColumnMappingTest.Any());
}
public void SqlBulkTools_BulkUpdate_PartialUpdateOnlyUpdatesSelectedColumns()
{
// Arrange
BulkOperations bulk = new BulkOperations();
_bookCollection = _randomizer.GetRandomCollection(30);
BulkDelete(_db.Books.ToList());
BulkInsert(_bookCollection);
// Update just the price on element 5
int elemToUpdate = 5;
decimal updatedPrice = 9999999;
var originalElement = _bookCollection.ElementAt(elemToUpdate);
_bookCollection.ElementAt(elemToUpdate).Price = updatedPrice;
// Act
bulk.Setup <Book>()
.ForCollection(_bookCollection)
.WithTable("Books")
.AddColumn(x => x.Price)
.BulkUpdate()
.MatchTargetOn(x => x.ISBN);
bulk.CommitTransaction("SqlBulkToolsTest");
// Assert
Assert.AreEqual(updatedPrice, _db.Books.Single(x => x.ISBN == originalElement.ISBN).Price);
/* Profiler shows: MERGE INTO [SqlBulkTools].[dbo].[Books] WITH (HOLDLOCK) AS Target USING #TmpTable
* AS Source ON Target.ISBN = Source.ISBN WHEN MATCHED THEN UPDATE SET Target.Price = Source.Price,
* Target.ISBN = Source.ISBN ; DROP TABLE #TmpTable; */
}
public async Task SqlBulkTools_Upsert_AddAllColumns()
{
await DeleteAllBooks();
using (var tx = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
using (var con = new SqlConnection(_dataAccess.ConnectionString))
{
var bulk = new BulkOperations();
await bulk.Setup <Book>()
.ForObject(new Book()
{
BestSeller = true,
Description = "Greatest dad in the world",
Title = "Hello World",
ISBN = "1234567",
Price = 23.99M
})
.WithTable("Books")
.AddAllColumns()
.Upsert()
.SetIdentityColumn(x => x.Id, ColumnDirectionType.InputOutput)
.MatchTargetOn(x => x.Id)
.CommitAsync(con);
}
tx.Complete();
}
Assert.Equal(1, _dataAccess.GetBookCount());
Assert.NotNull(_dataAccess.GetBookList("1234567").SingleOrDefault());
}
public void SqlBulkTools_BulkInsert_TestIdentityOutput()
{
_db.Books.RemoveRange(_db.Books.ToList());
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
_db.Books.AddRange(_randomizer.GetRandomCollection(60)); // Add some random items before test.
_db.SaveChanges();
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.SetIdentityColumn(x => x.Id, ColumnDirection.InputOutput);
bulk.CommitTransaction("SqlBulkToolsTest");
var test = _db.Books.ToList().ElementAt(80); // Random between random items before test and total items after test.
var expected = books.Single(x => x.ISBN == test.ISBN);
Assert.AreEqual(expected.Id, test.Id);
}
