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()); }