Testing with a database in .NET

A common approach in .NET for testing applications that work with a database is dependency injection (Dependency Injection). It is proposed to separate the code working with the base from the main logic by creating an abstraction, which can later be replaced in tests. This is a very powerful and flexible approach, which nevertheless has some drawbacks - increasing complexity, separating logic, explosive growth in the number of types. More in the previous article Something is not right with testing in .NET (Java, etc.) or in Wiki / Dependency Injection .

There is a simpler approach, widespread in the world of dynamic languages. Instead of creating an abstraction that can be controlled in tests, this approach suggests controlling the base itself. The test framework provides a clean base for each test, and you can create a test script in it. It is easier and gives more confidence in the tests.

Example

There is some kind of application for inventory accounting of goods. Goods can be moved between warehouses using movement documents. A method is needed that allows to obtain balances at a specified warehouse at a specified point in time.

public class ReminesService { RemineItem[] GetReminesFor(Storage storage, DateTime time) { ... } } 

Test database

 <?xml version="1.0" encoding="utf-8"?> <configuration> <configSections> <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" /> </configSections> <entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework"> <parameters> <parameter value="MSSQLLocalDB" /> </parameters> </defaultConnectionFactory> <providers> <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /> </providers> </entityFramework> </configuration> 

Framework

Initial data

 public class Countries : IModelFixture<Country> { public string TableName => "Countries"; public static Country Scotland => new Country { Id = 1, Name = "Scotland", IsDeleted = false }; public static Country USA => new Country { Id = 2, Name = "USA", IsDeleted = false }; } 

 class Manufacturers : IModelFixture<Manufacturer> { public string TableName => "Manufacturers"; public static Manufacturer BrownForman => new Manufacturer { Id = 1, Name = "Brown-Forman", CountryId = Countries.USA.Id, IsDeleted = false }; public static Manufacturer TheEdringtonGroup => new Manufacturer { Id = 2, Name = "The Edrington Group", CountryId = Countries.Scotland.Id, IsDeleted = false }; } 

 public class Goods : IModelFixture<Good> { public string TableName => "Goods"; public static Good JackDaniels => new Good { Id = 1, Name = "Jack Daniels, 0.5l", ManufacturerId = Manufacturers.BrownForman.Id, IsDeleted = false }; public static Good FamousGrouseFinest => new Good { Id = 2, Name = "The Famous Grouse Finest, 0.5l", ManufacturerId = Manufacturers.TheEdringtonGroup.Id, IsDeleted = false }; } 

• when creating initial data, the studio suggests which fields are in the class, controls their types
• it is easy to relate the models to each other, in the same way you can use the initial data in the tests themselves
• with the development of the system and changes in models - the compiler will check for obvious errors in the types in the fixtures

 private static Good _famousGrouseFinest = new Lazy<Good>(() => new Good { Id = 2, Name = "The Famous Grouse Finest, 0.5l", ManufacturerId = Manufacturers.TheEdringtonGroup.Id, IsDeleted = false }; public static Good FamousGrouseFinest => _famousGrouseFinest.Value; 

Environment preparation

 static class World { public static void InitDatabase() { using (var context = new MyContext()) { var dbTest = new EFTestDatabase<MyContext>(context); dbTest.ResetWithFixtures( new Countries(), new Manufacturers(), new Goods() ); } } public static void InitContextWithUser() { HttpContext.Current = new HttpContext( new HttpRequest("", "http://your-domain.com", ""), new HttpResponse(new StringWriter()) ); HttpContext.Current.User = new GenericPrincipal( new GenericIdentity("root"), new string[0] ); } } 

Creating a test script

 public class ModelBuilder { public MoveDocument CreateDocument(string time, Storage source, Storage dest) { var document = new MoveDocument { Number = "#", SourceStorageId = source.Id, DestStorageId = dest.Id, Time = ParseTime(time), IsDeleted = false }; using (var db = new MyContext()) { db.MoveDocuments.Add(document); db.SaveChanges(); } return document; } public MoveDocumentItem AddGood(MoveDocument document, Good good, decimal count) { var item = new MoveDocumentItem { MoveDocumentId = document.Id, GoodId = good.Id, Count = count }; using (var db = new MyContext()) { db.MoveDocumentItems.Add(item); db.SaveChanges(); } return item; } } 

We are testing

 [SetUp] public void SetUp() { World.InitDatabase(); //      } [Test] public void CalculateRemainsForMoveDocuments() { /// ARRANGE -    var builder = new ModelBuilder(); //      var doc1 = builder.CreateDocument("15.01.2016 10:00:00", Storages.MainStorage, Storages.RemoteStorage); builder.AddGood(doc1, Goods.JackDaniels, 10); builder.AddGood(doc1, Goods.FamousGrouseFinest, 15); //      var doc2 = builder.CreateDocument("16.01.2016 20:00:00", Storages.RemoteStorage, Storages.MainStorage); builder.AddGood(doc2, Goods.FamousGrouseFinest, 7); /// ACT -    var remains = RemainsService.GetRemainFor(Storages.RemoteStorage, new DateTime(2016, 02, 01)); /// ASSERT -   Assert.AreEqual(2, remains.Count); Assert.AreEqual(10, remains.Single(x => x.GoodId == Goods.JackDaniels.Id).Count); Assert.AreEqual(8, remains.Single(x => x.GoodId == Goods.FamousGrouseFinest.Id).Count); } 

Summary

• Less impact of tests on architecture
• Less abstraction layers - less complexity and easier to read code
• More confidence in the tests that actually read and insert data into the database
• Faster writing and easier support

useful links