Integration Testing made Simple for CRUD applications with SqlLocalDB

I have had multiple discussions about which automated testing process is the best for a variety of applications I have come across in my career.

When you are starting a Greenfield application, the discussion isn't that hard.

However, for Brownfield application development, the discussion gets more heated, when your application is hard to test. Legacy components and bad architectures could cause this. Or maybe the application does not have to perform complex algorithms, does not have complex or clearly defined business rules, or is a complex (or simple) CRUD Application.

This tutorial is from the DotNetCurry(DNC) Magazine with in-depth tutorials and best practices in .NET and JavaScript. This magazine is aimed at Developers, Architects and Technical Managers and covers C#, Patterns, .NET Core, MVC, Azure, DevOps, ALM, TypeScript, Angular, React, and more. Subscribe to this magazine for FREE and receive all previous, current and upcoming editions, right in your Inbox. No Spam Policy.

In any of these cases, the application will be harder to test. This article is about that last case scenario – automated testing for CRUD application.

How do you test applications where TDD just does not feel right - when Unit Tests provide so little added value (or are so complex) that developers rule out automated testing (which is a shame!).

Because if you abandon automated testing, you abandon the positive and proven impact it has on your code.

First, some concepts – TDD, UnitTesting and Code Coverage

Not everyone reading this article has the same technical background. So, I'll go over some core concepts, making sure that everyone is up-to-speed.

 TDD (Test Driven Development): While this is not an article about TDD, you cannot write about Integration Testing (or automated testing of any matter) without writing about Test Driven Development (TDD).

TDD is a software development process that relies on the repetition of a very short development cycle. Requirements get turned into specific test cases. After this step, software is written or improved which will allow the new tests to pass.

TDD provides a short feedback loop. It leads to a better design (SOLID, DRY and KISS principles will be easier to implement) and less production bugs. You also feel much more confident when refactoring and introducing new team members gets much easier.

Unit Testing: This is an automated software testing method by which individual units of source code are tested to determine whether they are fit for use. You test particular pieces of valuable or critical code which results in better architecture, easier maintenance, and better documentation.

A fairly contested question is: how large is a unit? Well, it depends, but experts commonly agreed to keep Unit Tests small and simple. So, the Unit is as small as possible in your code base.

Code Coverage: This is a measure to describe the degree to which the source code of an application is executed when tests are run.

If you have a high code coverage (so a lot of unit tests that span across the lines of code of your application), you have a lower chance of running into bugs, compared to applications with lower code coverage.

A good deal of different metrics can be used to calculate code coverage, which is measured as a percentage. The most basic is the calculation of the percentage of application statements called during the execution of your tests.

What kind of tests are there?

Figure 1: Different categories of tests.

Figure 1 is a simplistic visualization of three different categories of tests. Depending on who you ask, there will be more. But I'm certain almost everyone can agree with these three concepts as a bare minimum.

Starting from bottom to top, tests are cheaper to run. You should have as many tests as possible (or viable).

Where Unit Tests cover smaller code bases, Integration Tests will test multiple modules or systems as they interact together. One Integration Test will result in higher code coverage as there will be more application statements called during the execution of these tests. They are often used to test external services, like databases, APIs, etc.

So, these tests are more expensive (take longer) to run.

End-To-End tests will test the application as a whole, by testing the UI (using frameworks like Selenium, etc.), or calling an API layer. These tests are the most expensive, and you should only write them if they are necessary.

Editorial Note: If you are developing ASP.NET Core applications, read this series of articles by Daniel which talks about an automated testing strategy involving unit tests, integration tests, end–to-end tests and load tests.

Integration Testing

As we go back to the example stated in the introduction, Integration Tests can be more valuable than Unit Tests. Applications with lots of legacy code, or CRUD applications, will benefit more from Integration Tests.

For the record, always work towards an architecture where TDD or Unit Tests are more viable. I am not disagreeing with that.

But sometimes you have to work with what you've got, when the architecture just doesn't allow high levels of Code Coverage and the project budget does not cover changes in that area.

In this article, I will address the issue of writing code to perform automated testing for CRUD applications, i.e. testing the database. This sample can be applied to brownfield application as well though.

To sum up, the definition of an Integration Test can be stated as follows:

• Like Unit Tests, source code is tested in an automated context.
• For Integration Tests the "Units" under test are larger.
• You test entire systems or multiple modules instead of small code units.
• Integration Tests have to run in an automated context (Continuous Integration Builds).
• Integration Tests take longer to run and can require (external) dependencies.
• Mocking is almost impossible. Because the system under test requires a lot of different modules, or because you want to test the actual results of an external service (like a database).

Can't you Mock your external services?

Well, sure you can!

But in an application as described earlier, the test results will not represent a real-life situation.

Let's say you have an AuditManager (that audits who changed an entity) or you work with “soft deletes” (when you don’t physically delete records from a database but use an “IsDeleted” flag to set the deleted state of an object).

You could Mock these scenarios, but should you?

Do Unit Tests really provide more code safety in these situations? How will you test these managers as they interact with your repository? Or with your Business Logic layer?

At a certain point, the mocking becomes so complex and is so arbitrary that Integration Testing just makes life easier.

Providing simple Integration Testing with SqlLocalDB

The first goal for this article is to test CRUD features of our database and keep that process simple.

The challenge lies in the latter - providing a Testing Database that can be reused, shared across developers, and deployed on the Build Server.

After reading this article you will be able to add Integration Tests to any project, with only a couple of code changes.

SqlLocalDB – Getting Started

What is SqlLocalDB? Well, according to Microsoft:

“LocalDB installation copies a minimal set of files necessary to start the SQL Server Database Engine. Once LocalDB is installed, you can initiate a connection using a special connection string. When connecting, the necessary SQL Server infrastructure is automatically created and started, enabling the application to use the database without complex configuration tasks. Developer Tools can provide developers with a SQL Server Database Engine that lets them write and test Transact-SQL code without having to manage a full server instance of SQL Server.”

This is perfect for what we are trying to achieve!

It is simple, it will run on a Continuous Integration (CI) build, and you don't have to create tables or change anything in your code.

SqlLocalDB Utility

There is a simple command-line tool available to enable you to create and manage instances of SQL LocalDB. I'll list the most important commands you can use below, but you can look at the Microsoft documentation too over here for a full list of features.

• sqllocaldb create|c ["instance name"] -s Creates a new LocalDB instance with the specified name. -s starts the new LocalDB instance after it's created.
• sqllocaldb delete|d ["instance name"] Deletes the LocalDB instance with the specified name.
• sqllocaldb start|s ["instance name"] Starts the LocalDB instance with the specified name
• sqllocaldb stop|p ["instance name"] -i -k Stops the LocalDB instance with the specified name. -i requests LocalDB instance shutdown with NOWAIT option. -k kills LocalDB instance process without contacting it.
• sqllocaldb info|i Lists all existing LocalDB instances.
• sqllocaldb info|i "instance name" Prints information about the specified LocalDB instance

The Sample

So, let’s do this!

First, create a new project in an existing solution. Call it "[YourProject].Tests.Integration". Add a ConnectionString in the app.config file to allow our Integration Tests to connect to a new SqlLocalDB instance, which looks something like this:

<connectionStrings>
<add name="SomeConnection" connectionString="Data Source=(localdb)\localtestdb; Database=application_Tests; Trusted_Connection=True; MultipleActiveResultSets=true" providerName="System.Data.SqlClient" />
</connectionStrings>

In our new Integration Test Project, add a Bootstrap class that registers all our modules (using Autofac as IoC container).

If you're not using an IoC container, you can skip this step. We will use MSTest as testing framework throughout the sample. Other popular frameworks like NUnit or xUnit will also work.

public static IContainer Bootstrap()
{
   //register the different modules
   var _container = new AutofacContainer(builder =>
   {
     builder.RegisterModule<IntegrationModuule>();
     builder.RegisterModule<CommonModule>();
     builder.RegisterModule<CoreModule>();
     builder.RegisterModule<DbModule>();
     builder.RegisterModule<ServiceModule>();
   });
        
   _container.Add(_container);
   
   //set up the ServiceLocator
   ServiceLocator.SetContainer(_container);
            
   return _container;
}

public static void SetupLocalDb()
{
    // Use a ProcessStartInfo object to provide a simple solution to create a new LocalDbInstance
    var _processInfo =
    new ProcessStartInfo("cmd.exe", "/c " + "sqllocaldb.exe create localtestdb -s")
       {
           CreateNoWindow = true,
           UseShellExecute = false,
           RedirectStandardError = true,
           RedirectStandardOutput = true
        };

        var _process = Process.Start(_processInfo);
        _process.WaitForExit();

        string _output = _process.StandardOutput.ReadToEnd();
        string _error = _process.StandardError.ReadToEnd();

        var _exitCode = _process.ExitCode;

        Console.WriteLine("output>>" + (String.IsNullOrEmpty(_output) ? "(none)" : _output));
        Console.WriteLine("error>>" + (String.IsNullOrEmpty(_error) ? "(none)" : _error));
        Console.WriteLine("ExitCode: " + _exitCode.ToString());
       _process.Close();
 }

We provide an IntegrationTestBase class like so:

[TestClass]
public abstract class IntegrationTestBase
{
    //The Entity Framework DBContext that we will use in our Integration Tests.
    //Set to protected so that child classes can access it. 
    protected IApplicationDbContext TestDbContext;

    [TestCategory("Integration")]
    [TestInitialize]
    public void Init()
    {
        try
        {
        
    #if DEBUG
            Bootstrapper.SetupLocalDb();
    #endif
            Bootstrapper.Bootstrap();
            
            TestDbContext = ServiceLocator.Resolve<IApplicationDbContext>();

            TestInit();
        }
        catch (Exception _e)
        {
            TestContextInstance.WriteLine(_e.InnerException);
            throw;
        }
    }

    public abstract void TestInit();
}

If you are using Autofac, you can use the ServiceLocator pattern to get your DB Context. Otherwise you can use Init Method to build up your context, services you might need, etc.

If the application is run in DEBUG mode, we will also create a new SqlLocalDB TestInstance. You could use the default instance, but following this step ensures you that the instance is available, can be cleaned up, etc.

Using ProcessStartInfo for creating a SqlLocalDB Instance is not ideal!

But to keep everything simple, striving for the least amounts of required code changes, this solution is good enough, for now. Providing a LocalDB abstraction could prove useful, providing more control and abstraction to the programmer.

But, that's basically it! Now you can start writing tests!

Like so:

[TestCategory("Integration")]
[TestMethod]
public void Test_User_Seeded()
{
    var _userRepo = ServiceLocator.Resolve<IRepository<User>>();
    var _users = _userRepo.GetAll().ToList();

    Assert.AreEqual(_users.Count, 1);
}

This test will call the actual (local) database using a Repository. It fetches all the Users and ensures that exactly one user is present in the database. Since everything is already set up, we could just as easily resolve a (business) service, a controller, anything you want!

The code above assumes that all Migrations have been run prior to testing, and that the database has been seeded. If you are using Entity Framework, you can make this happen like so:

public class ApplicationDbContext : DbContext, IApplicationDbContext
{
    static ApplicationDbContext()
    {
        // Provides automatic migration and Seeding
        var _dbMigrator = new DbMigrator(new ApplicationDbContextConfiguration());
        _dbMigrator.Update(null);
    }
}

You can run this code in the IntegrationTestBase class as well.

If you are not using an IoC Container and you want to test your context directly, you could write this:

[TestCategory("Integration")]
[TestMethod]
public void CheckUserSeeded()
{
    using (var context = new ApplicationDbContext())
    {
       var _users = context.Users.ToList();
       Assert.AreEqual(_users.Count, 1);
    }
}

Or create the context in your TestInit and dispose it in the TestCleanup. This allows you to use different ConnectionStrings if needed. Don't forget to run your migrations, scripts and seeds if you use this method:

[TestCategory("Integration")]
[TestInitialize]
public void Init()
{
    TestDbContext = new ApplicationDbContext();
}
 
[TestCleanup]
public void TestFinish()
{
    TestDbContext.Dispose();
}

And that is it, you can now perform actual testing against your local database!

Viewing your Test Explorer gives you that automatic feeling of victory when everything starts turning into green!

Figure 2: Test Explorer after successful Integration test run

Since we use the TestCategory Attribute, we can group these results by trait:

Figure 3: Group test results by Trait

Some additional thoughts

If you are using .NET Core, you can configure Entity Framework to run 'In-Memory', which would be even better and easier than the sample we just saw. But if you are not working with .NET Core, the solution above is extremely valuable.

You can change the ConnectionString in code. So, if you want to have a fresh database for different sub-sets of Integration Tests, you can. You could also run into problems when .mdf files already exist. This can be solved by manually deleting the files, or by attaching the database in the ConnectionString as shown below:

<add name="DataModel.Context" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=database;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\database.mdf" providerName="System.Data.SqlClient" />

You can view your local database using the Sql Server Object Explorer.

Figure 4: Using Sql Server Object Explorer to examine your local db

Continuous Integration (CI) Builds

The above code sample is a very simple example on how to set up Integration Testing. It is intended as bare-minimum, to keep the scope of this article as small as possible.

But, it provides system and database testing without having to change a lot of code to set it up. So, Goal #1 achieved!

But, we also have to be able to run our new tests in an automated context. We have to be able to configure and run a CI (or Continuous Integration) Build that has a SqlLocalDB instance available and runs the tests against it.

Let's take a look.

Changing existing CI Builds

Integration Tests are more expensive than Unit Tests. That's what we saw earlier. So, it would not be a good idea to run these tests in a "normal" CI Build process.

To prevent that, we need to exclude all Integration Tests in the Build Configuration. In the code samples earlier, you saw the following attribute: [TestCategory("Integration")]. This allows us to distinguish Integration Tests from other Unit Tests.

This is important because you commonly have two (or more) CI build definitions - one that runs after each check-in, and the other one that runs once a night.

In the first one, we only want to run the Unit Tests because they are quick in execution and work as a first line of defense. Integration Tests take longer, and you want to run them once a night i.e. in a "CI Nightly Build".

Run only Unit Tests

In the VSTS Build configuration, you can still use the standard "Visual Studio Test" task. You can specify to run all categories except the "Integration" category like so:

Figure 5: Exclude tests with “Integration” category

Configuring your Integration Nightly Build

For the Integration Build, we will need to do some more configuration to automate the process. The example provided below works on a hosted pipeline, so you can use it for free!

SqlLocalDB is available on VSTS build agents. So, all you need to do is make sure the instance you configured in your ConnectionString is available.

We use "Batch script" tasks to create and manage the SqlLocalDB instances. These are simple Command Line tasks that execute on your Build Agent.

Let's create the instance. Make sure you have the correct SQL Server path available. This may differ between hosted pipelines. If you are using the (free) Microsoft-hosted agents, you can check the installed software here. Depending on the installed version of SQL Server, you have to change the version number in the path “C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SqlLocalDB.exe”.

By specifying create localtestdb -s we create and start the instance that will be used for our DB tests.

Figure 6: Create Test Instance using Batch Script Task

After this task, we can add the "Visual Studio Test" task and leave it with the default settings. This will run our Unit and Integration Tests.

After this we should clean up our LocalDb Instance. This is not really required, but I recommend it all the same.

Create a new "Batch Script" task which executes SQLLocalDB and configure p localtestdb -k as argument. This will stop and kill the instance process without contacting it. After that you can include the last "Batch Script" with d localtestdb as argument. This deletes the instance.

A complete Nightly Integration Build configuration would look something like this:

Figure 7: Complete list of tasks for Integration Test Build

Conclusion:

A common myth in development teams is that any sort of automated testing is complex and time consuming, especially in Brownfield application development.

I hope that with this example I've shown you that not only is it not complex, you can add Integration Tests without modifications to your code.

The process is simple, there is almost no pain, and the gain is substantial. The feeling you get as a developer when you see your code coverage sky-rocket, all your tests shining in green, that's just awesome!

As described in the introduction, we now have a simple and automated way to run Integration Tests on any project. I hope you can integrate this way of working into your development process and experience the gains of having high code coverage!

This article was technically reviewed by Damir Arh.

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

My name is Tim Sommer. I live in the beautiful city of Antwerp, Belgium. I have been passionate about computers and programming for as long as I can remember. I'm a speaker, teacher and entrepreneur. I'm a Windows Insider MVP.But most of all, I'm a developer, an architect, a technical specialist and a coach; with 8+ years of professional experience in the .NET framework.