public void SqlBulkTools_DeleteQuery_DeleteSingleEntity()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
BulkOperations bulk = new BulkOperations();
List <Book> books = _randomizer.GetRandomCollection(30);
var bookIsbn = books[5].ISBN;
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert();
bulk.CommitTransaction("SqlBulkToolsTest");
bulk.Setup <Book>()
.ForSimpleDeleteQuery()
.WithTable("Books")
.Delete()
.Where(x => x.ISBN == bookIsbn);
int deletedRecords = bulk.CommitTransaction("SqlBulkToolsTest");
Assert.IsTrue(deletedRecords == 1);
Assert.AreEqual(29, _db.Books.Count());
}
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 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 void SqlBulkTools_BulkInsertOrUpdateWithPrivateIdentityField_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.Throws <SqlBulkToolsException>(() => bulk.CommitTransaction("SqlBulkToolsTest"),
"No setter method available on property 'Id'. Could not write output back to property.");
}
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 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_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 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);
}
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_BulkInsertOrUpdae_FloatValueCorrectlySet()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
float?expectedFloat = (float?)1.33;
BulkOperations bulk = new BulkOperations();
List <Book> books = new List <Book>()
{
new Book()
{
Description = "Test", ISBN = "12345678910", Price = 30, TestFloat = expectedFloat
}
};
bulk.Setup <Book>(x => x.ForCollection(books))
.WithTable("Books")
.AddAllColumns()
.BulkInsertOrUpdate()
.MatchTargetOn(x => x.ISBN)
.SetIdentityColumn(x => x.Id);
bulk.CommitTransaction("SqlBulkToolsTest");
Assert.AreEqual(_db.Books.First().TestFloat, expectedFloat);
}
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_BulkInsertOrUpdate_DecimalValueCorrectlySet()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
decimal?expectedPrice = (decimal?)1.33;
BulkOperations bulk = new BulkOperations();
List <Book> books = new List <Book>()
{
new Book()
{
Description = "Test", ISBN = "12345678910", Price = expectedPrice
}
};
bulk.Setup <Book>(x => x.ForCollection(books))
.WithTable("Books")
.AddAllColumns()
.BulkInsertOrUpdate()
.MatchTargetOn(x => x.ISBN)
.SetIdentityColumn(x => x.Id);
bulk.CommitTransaction(_connectionString);
Assert.AreEqual(_db.Books.First().Price, expectedPrice);
}
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;
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert();
bulk.CommitTransaction("SqlBulkToolsTest");
// Update price to 100
bulk.Setup <Book>()
.ForSimpleUpdateQuery(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);
int updatedRecords = bulk.CommitTransaction("SqlBulkToolsTest");
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_BulkDeleteOnId_AddItemsThenRemovesAllItems()
{
// Arrange
BulkOperations bulk = new BulkOperations();
List <SchemaTest1> col = new List <SchemaTest1>();
for (int i = 0; i < 30; i++)
{
col.Add(new SchemaTest1()
{
ColumnB = "ColumnA " + i
});
}
// Act
bulk.Setup <SchemaTest1>()
.ForCollection(col)
.WithTable("SchemaTest") // Don't specify schema. Default schema dbo is used.
.AddAllColumns()
.BulkInsert();
bulk.CommitTransaction("SqlBulkToolsTest");
var allItems = _db.SchemaTest1.ToList();
bulk.Setup <SchemaTest1>()
.ForCollection(allItems)
.WithTable("SchemaTest")
.AddColumn(x => x.Id)
.BulkDelete()
.MatchTargetOn(x => x.Id);
bulk.CommitTransaction("SqlBulkToolsTest");
// Assert
Assert.IsFalse(_db.SchemaTest1.Any());
}
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;
}
}
bulk.Setup <Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert();
bulk.CommitTransaction("SqlBulkToolsTest");
bulk.Setup <Book>()
.ForSimpleDeleteQuery()
.WithTable("Books")
.Delete()
.Where(x => x.WarehouseId == 1)
.And(x => x.Price >= 100)
.And(x => x.Description == null);
int deletedRecords = bulk.CommitTransaction("SqlBulkToolsTest");
Assert.AreEqual(5, deletedRecords);
Assert.AreEqual(25, _db.Books.Count());
}
public void SqlBulkTools_WithConflictingTableName_DeletesAndInsertsToCorrectTable()
{
// Arrange
BulkOperations bulk = new BulkOperations();
List <SchemaTest2> conflictingSchemaCol = new List <SchemaTest2>();
for (int i = 0; i < 30; i++)
{
conflictingSchemaCol.Add(new SchemaTest2()
{
ColumnA = "ColumnA " + i
});
}
// Act
bulk.Setup <SchemaTest2>()
.ForCollection(_db.SchemaTest2)
.WithTable("SchemaTest")
.WithSchema("AnotherSchema")
.AddAllColumns()
.BulkDelete(); // Remove existing rows
bulk.CommitTransaction("SqlBulkToolsTest");
bulk.Setup <SchemaTest2>()
.ForCollection(conflictingSchemaCol)
.WithTable("SchemaTest")
.WithSchema("AnotherSchema")
.AddAllColumns()
.BulkInsert(); // Add new rows
bulk.CommitTransaction("SqlBulkToolsTest");
// Assert
Assert.IsTrue(_db.SchemaTest2.Any());
}
public void SqlBulkTools_DeleteQuery_DeleteWhenNullWithWithSchema()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
BulkOperations bulk = new BulkOperations();
List <SchemaTest2> col = new List <SchemaTest2>();
for (int i = 0; i < 30; i++)
{
col.Add(new SchemaTest2()
{
ColumnA = null
});
}
List <Book> books = _randomizer.GetRandomCollection(30);
bulk.Setup <SchemaTest2>()
.ForCollection(col)
.WithTable("SchemaTest")
.WithSchema("AnotherSchema")
.AddAllColumns()
.BulkInsert();
bulk.CommitTransaction("SqlBulkToolsTest");
bulk.Setup <SchemaTest2>()
.ForSimpleDeleteQuery()
.WithTable("SchemaTest")
.WithSchema("AnotherSchema")
.Delete()
.Where(x => x.ColumnA == null);
int deletedRecords = bulk.CommitTransaction("SqlBulkToolsTest");
Assert.AreEqual(0, _db.SchemaTest2.Count());
}
public void SqlBulkTools_IdentityColumnWhenNotSet_ThrowsIdentityException()
{
// Arrange
BulkDelete(_db.Books);
BulkOperations bulk = new BulkOperations();
_bookCollection = _randomizer.GetRandomCollection(20);
bulk.Setup <Book>(x => x.ForCollection(_bookCollection))
.WithTable("Books")
.AddAllColumns()
.BulkUpdate()
.MatchTargetOn(x => x.Id);
// Act & Assert
Assert.Throws <IdentityException>(() => bulk.CommitTransaction("SqlBulkToolsTest"));
}
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"));
}
private long BulkInsertAllColumns(IEnumerable <Book> col)
{
BulkOperations bulk = new BulkOperations();
bulk.Setup <Book>(x => x.ForCollection(col))
.WithTable("Books")
.AddAllColumns()
.TmpDisableAllNonClusteredIndexes()
.BulkInsert();
var watch = System.Diagnostics.Stopwatch.StartNew();
bulk.CommitTransaction("SqlBulkToolsTest");
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
return(elapsedMs);
}
private long BulkDelete(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();
bulk.CommitTransaction("SqlBulkToolsTest");
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
return(elapsedMs);
}
private long BulkInsertOrUpdateAllColumns(IEnumerable <Book> col)
{
BulkOperations bulk = new BulkOperations();
bulk.Setup <Book>(x => x.ForCollection(col))
.WithTable("Books")
.AddAllColumns()
.BulkInsertOrUpdate()
.SetIdentityColumn(x => x.Id, false)
.MatchTargetOn(x => x.ISBN);
var watch = System.Diagnostics.Stopwatch.StartNew();
bulk.CommitTransaction(_connectionString);
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
return(elapsedMs);
}
private long BulkUpdate(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();
bulk.CommitTransaction(_connectionString);
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
return(elapsedMs);
}
public void SqlBulkTools_BulkDeleteWithSelectedColumns_TestIdentityOutput()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
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();
command.ExecuteNonQuery();
}
List <Book> books = _randomizer.GetRandomCollection(30);
BulkInsert(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);
bulk.CommitTransaction("SqlBulkToolsTest");
var test = books.First();
var expected = 11;
Assert.AreEqual(expected, test.Id);
}
private long BulkInsert(IEnumerable <Book> col)
{
BulkOperations bulk = new BulkOperations();
bulk.Setup <Book>(x => x.ForCollection(col))
.WithTable("Books")
.WithBulkCopyBatchSize(5000)
.AddColumn(x => x.Title)
.AddColumn(x => x.Price)
.AddColumn(x => x.Description)
.AddColumn(x => x.ISBN)
.AddColumn(x => x.PublishDate)
.TmpDisableAllNonClusteredIndexes()
.BulkInsert();
var watch = System.Diagnostics.Stopwatch.StartNew();
bulk.CommitTransaction("SqlBulkToolsTest");
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
return(elapsedMs);
}
public void SqlBulkTools_BulkInsertWithoutSetter_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.Throws <SqlBulkToolsException>(() => bulk.CommitTransaction("SqlBulkToolsTest"),
"No setter method available on property 'Id'. Could not write output back to property.");
}
public void SqlBulkTools_BulkInsertWithGenericType()
{
_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);
bulk.CommitTransaction("SqlBulkToolsTest");
Assert.IsTrue(_db.Books.Any());
}
public void SqlBulkTools_BulkInsertOrUpdae_TestDataTypes()
{
_db.Books.RemoveRange(_db.Books.ToList());
_db.SaveChanges();
var todaysDate = DateTime.Today;
Guid guid = Guid.NewGuid();
BulkOperations bulk = new BulkOperations();
List <TestDataType> dataTypeTest = new List <TestDataType>()
{
new TestDataType()
{
BigIntTest = 342324324324324324,
TinyIntTest = 126,
DateTimeTest = todaysDate,
DateTime2Test = new DateTime(2008, 12, 12, 10, 20, 30),
DateTest = new DateTime(2007, 7, 5, 20, 30, 10),
TimeTest = new TimeSpan(23, 32, 23),
SmallDateTimeTest = new DateTime(2005, 7, 14),
BinaryTest = new byte[] { 0, 3, 3, 2, 4, 3 },
VarBinaryTest = new byte[] { 3, 23, 33, 243 },
DecimalTest = 178.43M,
MoneyTest = 24333.99M,
SmallMoneyTest = 103.32M,
RealTest = 32.53F,
NumericTest = 154343.3434342M,
FloatTest = 232.43F,
FloatTest2 = 43243.34,
TextTest = "This is some text.",
GuidTest = guid,
CharTest = new char[] { 'S', 'o', 'm', 'e' },
XmlTest = "<title>The best SQL Bulk tool</title>",
NCharTest = "SomeText",
ImageTest = new byte[] { 3, 3, 32, 4 }
}
};
bulk.Setup <TestDataType>()
.ForCollection(dataTypeTest)
.WithTable("TestDataTypes")
.AddAllColumns()
.BulkInsertOrUpdate()
.MatchTargetOn(x => x.TimeTest);
bulk.CommitTransaction("SqlBulkToolsTest");
using (
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlBulkToolsTest"].ConnectionString)
)
using (var command = new SqlCommand("SELECT TOP 1 * FROM [dbo].[TestDataTypes]", conn)
{
CommandType = CommandType.Text
})
{
conn.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Assert.AreEqual(232.43F, reader["FloatTest"]);
Assert.AreEqual(43243.34, reader["FloatTest2"]);
Assert.AreEqual(178.43M, reader["DecimalTest"]);
Assert.AreEqual(24333.99M, reader["MoneyTest"]);
Assert.AreEqual(103.32M, reader["SmallMoneyTest"]);
Assert.AreEqual(32.53M, reader["RealTest"]);
Assert.AreEqual(154343.3434342M, reader["NumericTest"]);
Assert.AreEqual(todaysDate, reader["DateTimeTest"]);
Assert.AreEqual(new DateTime(2008, 12, 12, 10, 20, 30), reader["DateTime2Test"]);
Assert.AreEqual(new DateTime(2005, 7, 14), reader["SmallDateTimeTest"]);
Assert.AreEqual(new DateTime(2007, 7, 5), reader["DateTest"]);
Assert.AreEqual(new TimeSpan(23, 32, 23), reader["TimeTest"]);
Assert.AreEqual(guid, reader["GuidTest"]);
Assert.AreEqual("This is some text.", reader["TextTest"]);
Assert.AreEqual(new char[] { 'S', 'o', 'm', 'e' }, reader["CharTest"].ToString().Trim());
Assert.AreEqual(126, reader["TinyIntTest"]);
Assert.AreEqual(342324324324324324, reader["BigIntTest"]);
Assert.AreEqual("<title>The best SQL Bulk tool</title>", reader["XmlTest"]);
Assert.AreEqual("SomeText", reader["NCharTest"].ToString().Trim());
Assert.AreEqual(new byte[] { 3, 3, 32, 4 }, reader["ImageTest"]);
Assert.AreEqual(new byte[] { 0, 3, 3, 2, 4, 3 }, reader["BinaryTest"]);
Assert.AreEqual(new byte[] { 3, 23, 33, 243 }, reader["VarBinaryTest"]);
}
}
}
}
