public void TestExecuteScalarWithArrayParameters()
{
// Prepare
var repository = new DbRepository <SqlConnection>(Constants.TestDatabase);
var customers = new List <Customer>();
for (var i = 1; i <= 10; i++)
{
customers.Add(new Customer
{
Id = i,
GlobalId = Guid.NewGuid(),
FirstName = $"FirstName{i}",
LastName = $"LastName{i}",
MiddleName = $"MiddleName{i}",
Address = $"Address{i}",
IsActive = true,
Email = $"Email{i}",
LastUpdatedUtc = DateTime.UtcNow,
LastUserId = Environment.UserName
});
}
repository.BulkInsert(customers);
// Act
var globalIds = customers.Select(customer => customer.GlobalId).ToArray();
var result = repository.ExecuteScalar("SELECT MAX(LastUpdatedUtc) FROM [dbo].[Customer] WHERE GlobalId IN (@Ids);", new { Ids = globalIds });
var expected = customers.Max(c => c.LastUpdatedUtc);
// Assert
Assert.AreEqual(expected, result);
}
public void TestBulkInsertWithUnorderedColumns()
{
//arrange
var repository = new DbRepository <SqlConnection>(Constants.TestDatabase);
repository.ExecuteNonQuery(@"
CREATE TABLE [dbo].[TestTable](
[FullName] [NVARCHAR](50) NULL,
[Age] INT NULL,
[BirthDate] DATETIME NULL
) ");
var fixture = new Fixture();
var fixtureData = fixture.Build <TestTable>()
.With(x => x.FullName, "FirstName dela Cruz Pinto")
.With(x => x.Age, 25)
.With(x => x.BirthDate, new DateTime(1993, 1, 1))
.CreateMany(10);
//act
var affectedRows = repository.BulkInsert(fixtureData);
//assert
affectedRows.ShouldBe(10);
}
public void TestBulkInsert()
{
// Setup
var repository = new DbRepository <SqlConnection>(Constants.TestDatabase);
var fixture = new Fixture();
var fixtureDataList = fixture.Build <Customer>()
.With(x => x.FirstName, "FirstName")
.With(x => x.LastName, "dela Cruz")
.With(x => x.MiddleName, "Pinto")
.With(x => x.Address, "Address")
.With(x => x.Email, "*****@*****.**")
.With(x => x.LastUpdatedUtc, DateTime.UtcNow)
.With(x => x.LastUserId, Environment.UserName)
.CreateMany(100);
// Act
var affectedRows = repository.BulkInsert(fixtureDataList);
var customers = repository.Query <Customer>(top: 10).ToList();
// Assert
affectedRows.ShouldBe(100);
foreach (var customer in customers)
{
var fixtureData = fixtureDataList.Single(s => s.GlobalId == customer.GlobalId);
fixtureData.ShouldNotBeNull();
fixtureData.FirstName.ShouldBe(customer.FirstName);
fixtureData.LastName.ShouldBe(customer.LastName);
fixtureData.MiddleName.ShouldBe(customer.MiddleName);
fixtureData.Address.ShouldBe(customer.Address);
fixtureData.Email.ShouldBe(customer.Email);
fixtureData.IsActive.ShouldBe(customer.IsActive);
fixtureData.LastUpdatedUtc.ShouldBe(customer.LastUpdatedUtc);
fixtureData.LastUserId.ShouldBe(customer.LastUserId);
}
}
private static void InsertAll(int rows)
{
var employees = CreateEmployees(rows);
using (var repository = new DbRepository <SqlConnection>(ConnectionString))
{
repository.BulkInsert(employees);
Console.WriteLine($"{rows} row(s) has been inserted.");
}
}
private static void RepoDbBulkInsert(IEnumerable <Employee> employees)
{
var now = DateTime.UtcNow;
using (var repository = new DbRepository <SqlConnection>(ConnectionString))
{
repository.BulkInsert(employees);
var lapsedTime = DateTime.UtcNow - now;
Console.WriteLine($"RepoDb.BulkInsert: {lapsedTime.TotalSeconds} for {employees?.Count()} rows.");
}
}
private static void InsertAll(int rows)
{
var now = DateTime.UtcNow;
var employees = CreateEmployees(rows);
using (var repository = new DbRepository <SqlConnection>(ConnectionString))
{
repository.BulkInsert(employees);
var lapsedTime = DateTime.UtcNow - now;
Console.WriteLine($"{rows} row(s) has been inserted for {lapsedTime.TotalSeconds} second(s).");
}
}
public void TestBulkInsert()
{
//arrange
var repository = new DbRepository <SqlConnection>(Constants.TestDatabase);
var fixture = new Fixture();
var fixtureDataList = fixture.Build <Customer>()
.With(x => x.FirstName, "Juan")
.With(x => x.LastName, "dela Cruz")
.With(x => x.MiddleName, "Pinto")
.With(x => x.Address, "San Lorenzo, Makati, Philippines 4225")
.With(x => x.Email, "*****@*****.**")
.With(x => x.LastUpdatedUtc, DateTime.UtcNow)
.With(x => x.LastUserId, Environment.UserName)
.CreateMany(100);
//act
var affectedRows = repository.BulkInsert(fixtureDataList);
//assert
affectedRows.ShouldBe(100);
var savedDataList = repository.Query <Customer>(top: 10).ToList();
foreach (var savedData in savedDataList)
{
var fixtureData = fixtureDataList.Single(s => s.GlobalId == savedData.GlobalId);
fixtureData.ShouldNotBeNull();
fixtureData.FirstName.ShouldBe(savedData.FirstName);
fixtureData.LastName.ShouldBe(savedData.LastName);
fixtureData.MiddleName.ShouldBe(savedData.MiddleName);
fixtureData.Address.ShouldBe(savedData.Address);
fixtureData.Email.ShouldBe(savedData.Email);
fixtureData.IsActive.ShouldBe(savedData.IsActive);
fixtureData.LastUpdatedUtc.ShouldBe(savedData.LastUpdatedUtc);
fixtureData.LastUserId.ShouldBe(savedData.LastUserId);
}
}
public void TestExecuteQueryWithArrayParameters()
{
// Prepare
var repository = new DbRepository <SqlConnection>(Constants.TestDatabase);
var customers = new List <Customer>();
for (var i = 1; i <= 10; i++)
{
customers.Add(new Customer
{
Id = i,
GlobalId = Guid.NewGuid(),
FirstName = $"FirstName{i}",
LastName = $"LastName{i}",
MiddleName = $"MiddleName{i}",
Address = $"Address{i}",
IsActive = true,
Email = $"Email{i}",
LastUpdatedUtc = DateTime.UtcNow,
LastUserId = Environment.UserName
});
}
repository.BulkInsert(customers);
// Act
var globalIds = customers.Select(customer => customer.GlobalId).ToArray();
var result = repository.ExecuteQuery <Customer>("SELECT * FROM [dbo].[Customer] WHERE GlobalId IN (@Ids);", new { Ids = globalIds });
// Assert
Assert.AreEqual(customers.Count, result.ToList().Count);
result.ToList().ForEach(customer =>
{
var value = customers.FirstOrDefault(c => c.GlobalId == customer.GlobalId);
Assert.IsNotNull(value);
});
}
private static void TestAllOperations()
{
// Repository
var repository = new DbRepository <SqlConnection>(RepoDbConnectionString, ConnectionPersistency.Instance);
// Truncate
repository.Truncate <Animal>();
// Count
Console.WriteLine($"Counting Person Records: {repository.Count<Person>()}");
Console.WriteLine($"Counting Animal Records: {repository.Count<Animal>()}");
// BatchQuery
Console.WriteLine("BatchQuery Person");
var batchQueryResult = repository.BatchQuery <Person>(0, 1000, OrderField.Parse(new { Id = Order.Descending }));
// Query 100K
Console.WriteLine("Query Person: 100K");
var queryResult = repository.Query <Person>(new
{
Id = new
{
Operation = Operation.GreaterThan,
Value = 100
}
}, top: 100000);
// BulkInsert
Console.WriteLine("BulkInsert Person: 100K");
var bulkInsertResult = repository.BulkInsert(queryResult);
// Insert with Guid Primary Key
Console.WriteLine("Insert with Guid PrimaryKey");
var animalId = repository.Insert(new Animal()
{
Id = Guid.NewGuid(),
Name = $"Name: {Guid.NewGuid().ToString()}",
Address = $"Address: {Guid.NewGuid().ToString()}",
DateInserted = DateTime.UtcNow,
DateUpdated = DateTime.UtcNow
});
// Verify
Console.WriteLine($"Verify Insert with Guid PrimaryKey: {animalId}");
var animal = repository.Query <Animal>(animalId).FirstOrDefault();
if (animal == null)
{
throw new NullReferenceException("Animal is null.");
}
// Insert with Identity PrimaryKey
Console.WriteLine("Insert with Identity PrimaryKey");
var personId = repository.Insert(new Person()
{
Name = $"Name: {Guid.NewGuid().ToString()}",
Address = $"Address: {Guid.NewGuid().ToString()}",
DateInserted = DateTime.UtcNow,
DateOfBirth = DateTime.UtcNow.Date.AddYears(-32),
DateUpdated = DateTime.UtcNow,
Worth = new Random().Next(30000, 60000)
});
// Verify
Console.WriteLine($"Verify Insert with Identity PrimaryKey: {personId}");
var person = repository.Query <Person>(personId).FirstOrDefault();
if (person == null)
{
throw new NullReferenceException("Person is null.");
}
// Check InlineInsert with Guid
Console.WriteLine($"InlineInsert Animal");
animalId = repository.InlineInsert <Animal>(new
{
Id = Guid.NewGuid(),
Name = $"NAME-{Guid.NewGuid().ToString()} - InlineInsert",
Address = $"ADDR-{Guid.NewGuid().ToString()} - InlineInsert"
});
// Verify
Console.WriteLine($"Verify InlineInsert with Guid PrimaryKey: {animalId}");
animal = repository.Query <Animal>(animalId).FirstOrDefault();
if (animal == null)
{
throw new NullReferenceException("Animal is null.");
}
// Check InlineInsert with Identity
Console.WriteLine($"InlineInsert with Identity PrimaryKey");
personId = repository.InlineInsert <Person>(new
{
Name = $"NAME-{Guid.NewGuid().ToString()} - InlineInsert",
Address = $"ADDR-{Guid.NewGuid().ToString()} - InlineInsert"
});
// Verify
Console.WriteLine($"Verify Insert with Identity PrimaryKey: {personId}");
person = repository.Query <Person>(personId).FirstOrDefault();
if (person == null)
{
throw new NullReferenceException("Person is null.");
}
// InlineUpdate
Console.WriteLine("InlineUpdate with Guid PrimaryKey");
var affectedRows = repository.InlineUpdate <Animal>(new
{
Name = $"NAME-{Guid.NewGuid().ToString()} - InlineUpdate",
Address = $"ADDR-{Guid.NewGuid().ToString()} - InlineUpdate"
},
new
{
Id = animalId
});
// Verify
Console.WriteLine($"Verify InlineUpdate with Guid PrimaryKey: {personId}");
if (affectedRows <= 0)
{
throw new Exception("No rows has been affected by the inline update.");
}
animal = repository.Query <Animal>(animalId).FirstOrDefault();
if (animal == null)
{
throw new NullReferenceException("Animal is null.");
}
// InlineUpdate
Console.WriteLine("InlineUpdate with Identity PrimaryKey");
affectedRows = repository.InlineUpdate <Person>(new
{
Name = $"NAME-{Guid.NewGuid().ToString()} - InlineUpdate",
Address = $"ADDR-{Guid.NewGuid().ToString()} - InlineUpdate"
},
new
{
Id = personId
});
// Verify
Console.WriteLine($"Verify InlineUpdate with Identity PrimaryKey: {personId}");
if (affectedRows <= 0)
{
throw new Exception("No rows has been affected by the inline update.");
}
person = repository.Query <Person>(personId).FirstOrDefault();
if (person == null)
{
throw new NullReferenceException("Person is null.");
}
// Check InlineMerge
Console.WriteLine($"InlineMerge with Guid PrimaryKey: {animalId}");
affectedRows = repository.InlineMerge <Animal>(new
{
Id = animalId,
Name = $"{animal.Name} - InlineMerge",
Address = $"{animal.Name} - InlineMerge"
});
// Verify
Console.WriteLine($"Verify InlineMerge with Guid PrimaryKey: {animalId}");
if (affectedRows <= 0)
{
throw new Exception("No rows has been affected by the inline merge.");
}
animal = repository.Query <Animal>(animalId).FirstOrDefault();
if (animal == null)
{
throw new NullReferenceException("Animal is null.");
}
// Check InlineMerge
Console.WriteLine($"InlineMerge with Identity PrimaryKey: {personId}");
affectedRows = repository.InlineMerge <Person>(new
{
Id = personId,
Name = $"{person.Name} - InlineMerge",
Address = $"{person.Name} - InlineMerge"
});
// Verify
Console.WriteLine($"Verify InlineMerge with Identity PrimaryKey: {personId}");
if (affectedRows <= 0)
{
throw new Exception("No rows has been affected by the inline merge.");
}
person = repository.Query <Person>(personId).FirstOrDefault();
if (person == null)
{
throw new NullReferenceException("Person is null.");
}
// Update
Console.WriteLine($"Update Person: {personId}");
person.Name = $"Name: {Guid.NewGuid().ToString()} (Updated)";
person.Address = $"Address: {Guid.NewGuid().ToString()} (Updated)";
person.DateUpdated = DateTime.UtcNow;
person.DateOfBirth = DateTime.UtcNow;
affectedRows = repository.Update(person);
// Verify
Console.WriteLine($"Verify Person after Update: {personId}");
if (affectedRows <= 0)
{
throw new Exception("No rows has been affected by the update.");
}
person = repository.Query <Person>(personId).FirstOrDefault();
if (person == null)
{
throw new NullReferenceException("Person is null.");
}
// Merge
Console.WriteLine($"Merge: {personId}");
person.Name = $"{Guid.NewGuid().ToString()} (Merged)";
person.Address = $"Address: {Guid.NewGuid().ToString()} (Merged)";
person.DateUpdated = DateTime.UtcNow;
affectedRows = repository.Merge(person, Field.Parse(new { person.Id }));
// Verify
Console.WriteLine($"Query Person After Merge: {personId}");
if (affectedRows <= 0)
{
throw new Exception("No rows has been affected by the merge.");
}
person = repository.Query <Person>(personId).FirstOrDefault();
if (person == null)
{
throw new NullReferenceException("Person is null.");
}
// Delete
Console.WriteLine($"Delete Person: {personId}");
affectedRows = repository.Delete <Person>(personId);
// Verify
Console.WriteLine($"Verify Person After Delete: {personId}");
if (affectedRows <= 0)
{
throw new Exception("No rows has been affected by the delete.");
}
person = repository.Query <Person>(personId).FirstOrDefault();
if (person != null)
{
throw new NullReferenceException("Person should be null. We have just deleted it.");
}
// Count
Console.WriteLine($"Person Records: {repository.Count<Person>()}");
Console.WriteLine($"Animal Records: {repository.Count<Animal>()}");
// Dispose
repository.Dispose();
}
