public void SqlBulkTools_BulkInsertOrUpdateAsyncWithPrivateIdentityField_ThrowsMeaningfulException()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
List <BookWithPrivateIdentity> booksWithPrivateIdentity = new List <BookWithPrivateIdentity>();
books.ForEach(x => booksWithPrivateIdentity.Add(new BookWithPrivateIdentity()
{
ISBN = x.ISBN,
Description = x.Description,
Price = x.Price
}));
bulk.Setup <BookWithPrivateIdentity>()
.ForCollection(booksWithPrivateIdentity)
.WithTable("Books")
.AddColumn(x => x.Id)
.AddColumn(x => x.Description)
.AddColumn(x => x.ISBN)
.AddColumn(x => x.Price)
.BulkInsertOrUpdate()
.MatchTargetOn(x => x.ISBN)
.SetIdentityColumn(x => x.Id, ColumnDirection.InputOutput);
Assert.ThrowsAsync <SqlBulkToolsException>(() => bulk.CommitTransactionAsync("SqlBulkToolsTest"),
"No setter method available on property 'Id'. Could not write output back to property.");
}
public async Task SqlBulkTools_BulkUpdateAsyncWithSelectedColumns_TestIdentityOutput()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
await BulkInsertAsync(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);
await bulk.CommitTransactionAsync("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 async Task SqlBulkTools_BulkInsertAsync_TestIdentityOutput()
{
_db.Books.RemoveRange(_db.Books.ToList());
await _db.SaveChangesAsync();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
_db.Books.AddRange(_randomizer.GetRandomCollection(60)); // Add some random items before test.
await _db.SaveChangesAsync();
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.SetIdentityColumn(x => x.Id, ColumnDirection.InputOutput);
await bulk.CommitTransactionAsync("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);
}
private async Task <long> BulkDeleteAsync(IEnumerable <Book> col)
{
BulkOperations bulk = new BulkOperations();
bulk.Setup <Book>(x => x.ForCollection(col))
.WithTable("Books")
.AddColumn(x => x.ISBN)
.BulkDelete()
.MatchTargetOn(x => x.ISBN);
var watch = System.Diagnostics.Stopwatch.StartNew();
await bulk.CommitTransactionAsync("SqlBulkToolsTest");
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
return(elapsedMs);
}
private async Task <long> BulkUpdateAsync(IEnumerable <Book> col)
{
BulkOperations bulk = new BulkOperations();
bulk.Setup <Book>(x => x.ForCollection(col))
.WithTable("Books")
.AddColumn(x => x.Title)
.AddColumn(x => x.Price)
.AddColumn(x => x.Description)
.AddColumn(x => x.PublishDate)
.BulkUpdate()
.MatchTargetOn(x => x.ISBN);
var watch = System.Diagnostics.Stopwatch.StartNew();
await bulk.CommitTransactionAsync(_connectionString);
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
return(elapsedMs);
}
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();
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.WithBulkCopyBatchSize(5000)
.AddColumn(x => x.ISBN)
.BulkDelete()
.MatchTargetOn(x => x.ISBN)
.SetIdentityColumn(x => x.Id, ColumnDirection.InputOutput);
await bulk.CommitTransactionAsync("SqlBulkToolsTest");
var test = books.First();
var expected = 11;
Assert.AreEqual(expected, test.Id);
}
private async Task <long> BulkInsertAsync(IEnumerable <Book> col)
{
BulkOperations bulk = new BulkOperations();
bulk.Setup <Book>(x => x.ForCollection(col))
.WithTable("Books")
.WithSqlBulkCopyOptions(SqlBulkCopyOptions.TableLock)
.WithBulkCopyBatchSize(3000)
.AddColumn(x => x.Title)
.AddColumn(x => x.Price)
.AddColumn(x => x.Description)
.AddColumn(x => x.ISBN)
.AddColumn(x => x.PublishDate)
.BulkInsert();
var watch = System.Diagnostics.Stopwatch.StartNew();
await bulk.CommitTransactionAsync("SqlBulkToolsTest");
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
return(elapsedMs);
}
public void SqlBulkTools_BulkInsertAsyncWithoutSetter_ThrowsMeaningfulException()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
BulkOperations bulk = new BulkOperations();
_bookCollection = _randomizer.GetRandomCollection(30);
bulk.Setup()
.ForCollection(
_bookCollection.Select(
x => new { x.Description, x.ISBN, x.Id, x.Price }))
.WithTable("Books")
.AddColumn(x => x.Id)
.AddColumn(x => x.Description)
.AddColumn(x => x.ISBN)
.AddColumn(x => x.Price)
.BulkInsert()
.SetIdentityColumn(x => x.Id, ColumnDirection.InputOutput);
Assert.ThrowsAsync <SqlBulkToolsException>(() => bulk.CommitTransactionAsync("SqlBulkToolsTest"),
"No setter method available on property 'Id'. Could not write output back to property.");
}
public async Task AdventureWorksTest()
{
List <Transaction> transactions = new List <Transaction>();
using (var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ConnectionString))
using (
var cmd =
new SqlCommand("GetTransactions", sqlConnection))
{
sqlConnection.Open();
cmd.CommandType = CommandType.StoredProcedure;
var reader = cmd.ExecuteReader();
while (reader.Read())
{
Transaction trans = new Transaction()
{
ActualCost = (decimal)reader["ActualCost"],
ProductId = (int)reader["ProductID"],
TransactionId = (int)reader["TransactionID"],
TransactionDate = (DateTime)reader["TransactionDate"],
Quantity = (int)reader["Quantity"]
};
transactions.Add(trans);
}
}
transactions.ForEach(x =>
{
x.ActualCost += (decimal)0.43;
x.Quantity += 32;
x.TransactionDate = DateTime.UtcNow;
});
IBulkOperations bulkOperations = new BulkOperations();
IBulkOperations bulkOperations2 = new BulkOperations();
bulkOperations.Setup <Transaction>()
.ForCollection(transactions)
.WithTable("bigTransactionHistory")
.AddColumn(x => x.TransactionId)
.AddColumn(x => x.ActualCost)
.AddColumn(x => x.ProductId)
.AddColumn(x => x.Quantity)
.AddColumn(x => x.TransactionDate)
.CustomColumnMapping(x => x.TransactionId, "TransactionID")
.CustomColumnMapping(x => x.ProductId, "ProductID")
.BulkUpdate()
.MatchTargetOn(x => x.TransactionId);
var watch = System.Diagnostics.Stopwatch.StartNew();
var cTask = bulkOperations.CommitTransactionAsync("AdventureWorks2014");
transactions.ForEach(x =>
{
x.ActualCost += (decimal)0.43;
x.Quantity += 32 * 2;
x.TransactionDate = DateTime.UtcNow;
});
bulkOperations2.Setup <Transaction>()
.ForCollection(transactions)
.WithTable("bigTransactionHistory")
.AddAllColumns()
.CustomColumnMapping(x => x.TransactionId, "TransactionID")
.CustomColumnMapping(x => x.ProductId, "ProductID")
.BulkUpdate()
.MatchTargetOn(x => x.TransactionId);
var lTask = bulkOperations2.CommitTransactionAsync("AdventureWorks2014");
await cTask;
await lTask;
watch.Stop();
// Add breakpoint here
var elapsedMs = watch.ElapsedMilliseconds;
Assert.IsTrue(transactions.Any());
}
