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