Category Archives: Test

Manually Creating a Test Harness in .Net

Let me start by saying that this post isn’t intended to try to replace Selenium, or Cypress, or whatever UI testing tools you may choose to use. In fact, it’s something that I did for manual testing, although it’s not difficult to imagine introducing some minor automation.

The Problem

Imagine that you have a solution that requires some data – perhaps it requires a lot of data, because you’re testing some specific performance issue, or perhaps you just want to see what the screen looks like when you have a lot of data. Let’s also imagine that you’re repeatedly running your project for one reason or another, and adding data, or whatever.

My idea here was that I could create a C# application that scripts this process, but because it’s an internal application, I could give it access to the data layer directly.

The Solution

Basically, the solution to this (and to many things) was a console app. Let’s take a solution that implements a very basic service / repository pattern:

From this, we can see that we have a pretty standard layout, and essentially, what we’re trying to do is insert some data into the database. It’s a bonus if we can add some test coverage while we’re at it (manual test coverage is still test coverage – it just doesn’t show up on your stats). So, if you’re using a REST type pattern, you might want to use the controller endpoints to add the data; but for my purpose, I’m just going to add the data directly into the data access layer.

Let’s see what the console app looks like:

        static async Task Main(string[] args)
        {
            IConfiguration configuration = new ConfigurationBuilder()
                  .AddJsonFile("appsettings.json", true, true)
                  .Build();

            // Ensure the DB is populated
            var dataAccess = new TestDataAccess(configuration.GetConnectionString("ConnectionString"));
            if (dataAccess.GetDataCount() == 0)
            {
                var data = new List<MyData>();

	     // Generate 100 items of data
                for (int j = 0; j <= 100; j++)
                {
		var dataItem = CreateTestItem();
                      data.Add(dataItem);
                }
                dataAccess.AddDataRange(data);
            }

            // Launch the site            
            await RunCommand("dotnet", "build");
            await RunCommand("dotnet", "run", 5);

            System.Diagnostics.Process.Start(@"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe", @"https://localhost:5001");
        }

Okay, so let’s break this down: there’s essentially three sections to this: configuration, adding the data, and running the app.

Configuration

We’ll start with the configuration:

       IConfiguration configuration = new ConfigurationBuilder()
                  .AddJsonFile("appsettings.json", true, true)
                  .Build();

        // Ensure the DB is populated
        var dataAccess = new TestDataAccess(configuration.GetConnectionString("ConnectionString"));

Because we’re using a console app, we’ll need to get the configuration; you could copy the appsettings.json, but my suggestion would be to add a link; that is, add an existing item, and select that item from the main project, then choose to “Add As Link” (this is not a new feature):

This means that you’ll be able to change the config file, and it will be reflected in the test harness.

Creating the data

There’s not too much point in me covering what’s behind TestDataAccess – suffice to say that it encapsulates the data access layer; which, as a minimum, requires the connection string.

It’s also worth pointing out that we check whether there is any data there before running it. Depending on your specific use-case, you may choose to remove this.

Building, running, and launching the app

Okay, so we’ve now added our data, we now want to build the main application – thanks to the command line capabilities of .Net Core, this is much simpler than it was when we used to have to try and wrangle with MSBuild!

    // Launch the site            
    await RunCommand("dotnet", "build");
    await RunCommand("dotnet", "run", 5);

    await RunCommand(@"C:\Program Files (x86)\Google\Chrome\Application\chrome.exe", @"https://localhost:5001");

RunCommand probably needs a little more attention, but before we look at that, let’s think about what we’re trying to do:

1. Build the application
2. When the application has built, run the application
3. Once the application is running, navigate to the site

RunCommand looks like this:

        private static async Task RunCommand(string command, string? args = null, int? waitSecs = -1)
        {
            Console.WriteLine($"Executing command: {command} (args: {args})");

            Process proc = new System.Diagnostics.Process();
            proc.StartInfo.WorkingDirectory = @"..\..\..\..\MyApp.App";
            proc.StartInfo.FileName = command;
            proc.StartInfo.Arguments = args ?? string.Empty;

            proc.Start();

            if ((waitSecs ?? -1) == -1)
            {
                proc.WaitForExit();
            }
            else
            {
                if (waitSecs! == 0) return;
                await Task.Delay(waitSecs!.Value * 1000);
            }
        }

“But this looks inordinately complicated for a simple wrapper for running a process!” I hear you say.

It is a bit, but the essence is this: when running the build command, we need to wait for it to complete, when running the run command, we can’t wait for it to complete, because it never will; but we do need to move to the next thing; and when we launch the site, we don’t really care whether it waits or not after that.

Summary

I appreciate that some of you may be regretting spending the time reading through this, as all I’ve essentially done is script some data creation and run an application; but I imagine there are some people out there, like me, that want to see (visually) what their app looks like with different data shapes.

Calling a Web API from a Console App and Creating a Performance Test

While working on a proof of concept, I needed to create a dummy API, and then run a stress test on it. Whilst the activity itself may seem pointless (creating a templated API and stress testing it), I felt the process might be worth documenting.

Create the API

We’ll start with creating the API:

dotnet new webapi

If you just run this, you should see the following:

The next step is to call that from a console app.

Create a console app to call the API

Add a new console app project to your solution, and replace the code in Program.cs with the following:

    class Program
    {
        static HttpClient client = new HttpClient();
        static string path = "https://localhost:44356/weatherforecast";


        static async Task Main(string[] args)
        {
            Console.WriteLine("Press enter to start test");
            Console.ReadLine();
            string? data = await CallWeatherForecast();

            Console.WriteLine(data ?? "No data returned");
        }

        private static async Task<string?> CallWeatherForecast()
        {            
            HttpResponseMessage response = await client.GetAsync(path);
            if (response.IsSuccessStatusCode)
            {
                string data = await response.Content.ReadAsStringAsync();
                return data;
            }

            return null;
        }
    }

It’s worth noting that I switched on nullable reference types here (in the csproj file):

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <Nullable>enable</Nullable>
  </PropertyGroup>

To test this, you’ll need to start both the projects (either select Set Startup Projects… from the solution context menu, or run the API, and then right-click the console app and select Debug -> Start New Instance).

Once you’ve checked that this works, build the solution in release mode (remember, we’re going to be running a stress test, so debug mode will show skewed results).

Call the console app from JMeter

For the stress test, I’m going to use Jmeter. You’ll need to download it from here.

I won’t go into too much detail about how to set this up, but briefly, extract the downloaded zip somewhere, and run the jmeter.bat file. You should be presented with the following screen:

Add a thread group, so we can simulate multiple users:

Then add an OS Process Sampler to run the console app:

Remember to run the API first, then click the green play arrow. You’ll see the users ramp up:

We don’t have any listeners, so the results are, unfortunately lost. Let’s add a couple:

As you can see, we now have some information. How long these calls are taking on average, the error count, etc. The throughput we’re getting is around 3/second… In fact, running a stress test locally on the same machine, it’s difficult to break it, because as the resources get used up, the JMeter process itself suffers, too. This is a good reason to run JMeter from a VM in the cloud.

Whilst it’s quite difficult to kill the service, I certainly managed to slow it down considerably:

These figures are in milliseconds, which means that 90% of calls are taking over 2 minutes. This entire test took around 15 minutes, and around 10 requests per second was about the best it got to (I ran 10 loops of 1000 concurrent users).

There’s a few things you can do to identify where the performance starts to degrade, but I’m more interested in what happens to these figures if I add DB access.

Note: when you’re playing with this, the reports don’t automatically clear each run, so you have to select each, right-click and clear.

Add calls to a DB

Let’s add Entity Framework to our project.

We can then change the controller to allow adding new records and retrieval by date:

        public WeatherForecastController(
            ILogger<WeatherForecastController> logger,
            StressTestDbContext stressTestDbContext)
        {
            _logger = logger;
            _stressTestDbContext = stressTestDbContext;
        }

        [HttpGet]
        public IEnumerable<WeatherForecast> GetNew()
        {
            var rng = new Random();
            return Enumerable.Range(1, 5).Select(index => new WeatherForecast
            {
                Date = DateTime.Now.AddDays(index),
                TemperatureC = rng.Next(-20, 55),
                Summary = Summaries[rng.Next(Summaries.Length)]
            })
            .ToArray();
        }
        
        [HttpGet("/[controller]/GetByDate/{dateTime}")]
        public IEnumerable<WeatherForecast> GetByDate(DateTime dateTime)
        {
            var forecasts = _stressTestDbContext.Forecast.Where(a => a.Date.Date == dateTime.Date);
            return forecasts;
        }

        [HttpPost]
        public IActionResult Post(WeatherForecast weatherForecast)
        {
            DailyForecast forecast = new DailyForecast()
            {
                Date = weatherForecast.Date,
                Summary = weatherForecast.Summary,
                TemperatureC = weatherForecast.TemperatureC
            };

            _stressTestDbContext.Add(forecast);
            if (_stressTestDbContext.SaveChanges() != 0)
            {
                return Ok();
            }
            return BadRequest();
        }

Finally, we can change the main program to call those functions:

        static async Task Main(string[] args)
        {
            Console.WriteLine("Press enter to start test");
            Console.ReadLine();

            ConsoleTitle("CallGetNewWeatherForecast");
            string? dataGet = await CallGetNewWeatherForecast();
            Console.WriteLine(dataGet ?? "No data returned");

            ConsoleTitle("AddForecast");
            string? dataAdd = await AddForecast(_rnd.Next(30),
                DateTime.Now.AddDays(_rnd.Next(10)), _summaries[_rnd.Next(_summaries.Length)]);
            Console.WriteLine(dataAdd ?? "No data returned");

            ConsoleTitle("CallGetWeatherForecast");
            string? dataGetDate = await CallGetWeatherForecast(DateTime.Now.AddDays(_rnd.Next(10)));
            Console.WriteLine(dataGetDate ?? "No data returned");
        }

        private static void ConsoleTitle(string title)
        {
            Console.ForegroundColor = ConsoleColor.Blue;
            Console.WriteLine(title);
            Console.ResetColor();
        }

        private static async Task<string?> CallGetNewWeatherForecast()
        {            
            HttpResponseMessage response = await client.GetAsync($"{path}");
            if (response.IsSuccessStatusCode)
            {
                string data = await response.Content.ReadAsStringAsync();
                return data;
            }

            return null;
        }

        private static async Task<string?> CallGetWeatherForecast(DateTime dateTime)
        {
            string dateString = dateTime.ToString("yyyy-MM-dd");

            HttpResponseMessage response = await client.GetAsync($"{path}/GetByDate/{dateString}");
            if (response.IsSuccessStatusCode)
            {
                string data = await response.Content.ReadAsStringAsync();
                return data;
            }

            return null;
        }

        private static async Task<string> AddForecast(int temperature, DateTime date, string summary)
        {
            var forecast = new WeatherForecast()
            {
                TemperatureC = temperature,
                Date = date,
                Summary = summary
            };

            HttpResponseMessage response = await client.PostAsJsonAsync($"{path}", forecast);
            if (response.IsSuccessStatusCode)
            {
                string data = await response.Content.ReadAsStringAsync();
                return data;
            }

            return null;
        }

To get a sensible reading, you’ll need to do this from an empty database:

For the first run, we’ll do 500 users, and 3 iterations:

The output from the first run is:

And let’s just check that that’s created the records we expected:

EF async calls vs non-async

To satisfy a curiosity that I’ve had for a while, I’m now going to change the update API method to async:

        [HttpPost]
        public async Task<IActionResult> Post(WeatherForecast weatherForecast)
        {
            DailyForecast forecast = new DailyForecast()
            {
                Date = weatherForecast.Date,
                Summary = weatherForecast.Summary,
                TemperatureC = weatherForecast.TemperatureC
            };

            _stressTestDbContext.Add(forecast);
            if (await _stressTestDbContext.SaveChangesAsync() != 0)
            {
                return Ok();
            }
            return BadRequest();
        }

Again, 1500 records were created:

Here’s the report:

What an interesting result. Making the update async seems to have slightly reduced the throughput. This is running locally, and I only have a 4 core machine, but I would have expected throughput to slightly increase here, rather than decrease.

Manually Adding DbContext for an Integration Test

In EF Core, there is an extension method that allows you to add a DBContext, called AddDBContext. This is a really useful method, however, in some cases, you may find that it doesn’t work for you. Specifically, if you’re trying to inject a DBContext to use for unit testing, it doesn’t allow you to access the DBContext that you register.

Take the following code:

services.AddDbContext<MyDbContext>(options =>
                options.UseSqlServer());
         

I’ve previously written about using UseInMemoryDatabase. However, this article covered unit tests only – that is, you are able to instantiate a version of the DBContext in the unit test, and use that.

As a reminder of the linked article, if you were to try to write a test that included that DBContext, you might want to use an in memory database; you might, therefore, build up a DBContextOptions like this:

var options = new DbContextOptionsBuilder<MyDbContext>()
                .UseInMemoryDatabase(Guid.NewGuid().ToString())
                .EnableSensitiveDataLogging()
                .Options;
var context = new MyDbContext(options);

But in a scenario where you’re writing an integration test, you may need to register this with the IoC. Unfortunately, in this case, AddDbContext can stand in your way. The alternative is that you can simply register the DbContext yourself:

var options = new DbContextOptionsBuilder<MyDbContext>()
                .UseInMemoryDatabase(Guid.NewGuid().ToString())
                .EnableSensitiveDataLogging()
                .Options;
var context = new MyDbContext(options);
AddMyData(context);
services.AddScoped<MyDbContext>(_ => context);

AddMyData just adds some data into your database; for example:

private void AddTestUsers(MyDbContext context)
{            
    MyData data = new MyData()
    {
        value1 = "test",
        value2 = "1"                
    };
    context.MyData.Add(subject);
    context.SaveChanges();
}

This allows you to register your own, in memory, DbContext in your IoC.

Designing and Debugging Database Unit Tests

There are many systems out there in the wild, and some new ones being written, that use database logic extensively. This article discusses how and why these pieces of logic should be tested, along with whether they should exist at all.

In general, for unit tests, it’s worth asking the question of what, exactly, is being tested, before starting. This is especially true in database tests; for example, consider a test where we update a field in a database, and then assert that the field is what it has been set to. Are you testing your trigger logic, or are you simply testing Microsoft SQL Server works?

The second thing to consider is whether or not it makes any sense to use testable database logic in new code. That is, say we have a stored procedure that:
– Takes a product code
– Looks up what the VAT is for that product
– Calculates the total price
– Writes the result, along with the parameter and the price to a new table

Does it make sense for all that logic to be in the stored procedure, or would it make more sense to retrieve the values needed via one stored procedure, do the calculation in a testable server-side function, and call a second procedure to write the data?

FIRST

Unit testing a database is a tricky business. First of all, if you have business logic in the database then it, almost by definition, depends on the state of the data. You obviously can simply run unit tests against the database and change the data, but let’s have a look at the FIRST principles, and see where database tests are inherently difficult.

Fast

It depends exactly what is meant by fast, but in comparison to a unit test that asserts some logic in C# code, database tests are slow (obviously, in comparison to conducting the test manually, they are very fast). Realistically, they are probably going to be sufficiently slow to warrant taking them out of your standard unit test suite. A sensible test project (that is, one that tests some actual code) may contain a good few hundred tests, let’s assume they all take 200ms – that means that 300 tests take a total of 60 seconds!

One thing that conducting DB tests does give you is an idea as to how fast (or slow) they actually are:

Isolated

It’s incredibly difficult to produce a database unit test that is isolated because, by its nature, a database had dependencies. Certainly, if anything you’re testing is dependent on a particular data state (for example, in the case above, the product that we are looking for must exist in a table, and have a VAT rate) then, unless this state is set-up in the test itself, this rule is broken.

Repeatable

Again – this isn’t a small problem with databases. Should I change Column A to test a trigger on the table, am I then able to change it again. What if the data is in a different state when I run the unit tests from the last time – I might get rogue fails, or worse, rogue passes. What happens if the test crashes half way through, how do we revert?

Self-verifying

In my example before, I changed Column A in order to test a trigger, and I’ll maybe check something that is updated by the trigger. Providing that the assertion is inside the test, the test is self-verifying. Obviously, this is easier to do wrong in a database context, because if I do nothing, the data is left in a state that can be externally verified.

Timely

This refers to when a test is written. There’s nothing inherent about database tests that prevent them from being written before, or very shortly after the code is written. However, see the comment above as to whether new code written like this makes sense.

Problems With A Database Test Project

Given what we’ve put above, let’s look at the outstanding issues that realistically need to be solved in order to use database tests:

1. Deployment. Running a standard code test will run the code wherever you are; however, a database test, whichever way you look at it, needs a database before it runs.

2. Rollback. Each test needs to be isolated, and so there needs to be a way to revert to the database state before the tests began.

3. Set-up. Any dependencies that the tests have, must be inside the test; therefore, if a table needs to have three rows in it, we need to add those rows within the test.

4. Assertion. What are we testing, and what makes sense to test; each test needs a defined purpose.

Example Project

In order to explore the various possibilities when setting up a database project, I’m going to use an example project:

Let’s start with some functionality to test. I’m going to do it this way around for two reasons: having code to test better illustrates the problems faced by database tests, and it is my belief that much of the database logic code is legacy and, therefore, already exists.

Here’s a new table, and a trigger that acts upon it:

CREATE TABLE [dbo].[SalesOrder]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [ProductCode] NCHAR(10) NOT NULL, 
    [NetAmount] DECIMAL(18, 2) NULL, 
    [Tax] DECIMAL(18, 2) NULL, 
    [TotalAmount] DECIMAL(18, 2) NULL, 
    [Comission] DECIMAL(18, 2) NULL
)
GO
 
CREATE TRIGGER SalesOrderAfterInsert ON SalesOrder
AFTER INSERT, UPDATE
AS
BEGIN
	DECLARE @CalcTax Decimal(18,2) 
	DECLARE @CalcComission Decimal(18,2) 
     
	SELECT @CalcTax = INSERTED.NetAmount * 0.20 FROM INSERTED
	SELECT @CalcComission = INSERTED.NetAmount * 0.10 FROM INSERTED
	 
    UPDATE S
    SET S.Tax = @CalcTax,
		S.Comission = @CalcComission,
		S.TotalAmount = S.NetAmount + S.Tax
	FROM INSERTED, SalesOrder S
    WHERE S.Id = INSERTED.Id
END
GO

This is for the purpose of illustration, so obviously, there are things here that might not make sense in real life; however, the logic is very testable. Let’s deploy this to a database, and do a quick manual test:

Once the database is published, we can check and test it in SSMS:

Quick edit the rows:

And test:

At first glance, this seems to work well. Let’s create a test:

[TestMethod]
public void CheckTotalAmount()
{
    using (SqlConnection sqlConnection = new SqlConnection(
        @"Data Source=TLAPTOP\PCM2014;Initial Catalog=MySqlDatabase;Integrated Security=SSPI;"))
    {
        sqlConnection.Open();
        using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
        {
            sqlCommand.CommandText = "INSERT INTO SalesOrder (Id, ProductCode, NetAmount) " +
                "VALUES (2, 'test', 10)";
            sqlCommand.ExecuteNonQuery();
        }
 
        using (SqlCommand sqlCommandCheck = sqlConnection.CreateCommand())
        {
            sqlCommandCheck.CommandText = $"SELECT TotalAmount FROM SalesOrder WHERE Id = 1";
            decimal result = decimal.Parse(sqlCommandCheck.ExecuteScalar().ToString());
 
        }
    }
}

Okay – there are a number of problems with this test, but let’s pretend for a minute that we don’t know what they are; the test passes:

Let’s run it again, just to be sure:

Oops.

Let’s firstly check this against the test principles that we discussed before.
1. Is it fast? 337ms means that we can run 3 of these per second. So that’s a ‘no’.
2. Is it Isolated? Does is have a single reason to fail – and can it live independently? If we accept that the engine itself is a reason to fail, but ignore that, then we can look specifically at the test, which asserts nothing. What’s more, it is doing two separate things to the DB, so both can fail realistically.
3. Is it Repeatable? Clearly not.
4. Is it self-verifying? No – it isn’t, because we have no assertions in it. Although we know that on the first run, both queries worked, we don’t know why.
5. Timely – well, we did write it directly after the code, so that’s probably a tick.

So, we know that the second run didn’t work. A quick look at the DB will tell us why:

Of course, the test committed a transaction to the database, as a result, any subsequent runs will fail.

The Solution

What follows is a suggested solution for this kind of problem, along with the beginnings of a framework for database testing. The tests here are using MSTest, but the exact same concept is easily achievable in Nunit and, I imagine, every other testing framework.

Base Test Class

The first thing is to create a deployment task:

The deployment task might look a little like this:

public static bool DeployDatabase(string projectFile)
{
    ILogger logger = new BasicFileLogger();
 
    Dictionary<string, string> globalProperties = new Dictionary<string, string>()
    {
        { "Configuration", "Debug" },
        { "Platform", "x86" },
        { "SqlPublishProfilePath", @"MySqlDatabase.publish.xml" }
    };
 
    ProjectCollection pc = new ProjectCollection(
        globalProperties, new List<ILogger>() { logger }, ToolsetDefinitionLocations.Registry);
        
    BuildParameters buildParameters = new BuildParameters(pc);            
    BuildRequestData buildRequestData = new BuildRequestData(
        projectFile, globalProperties, null, new string[] { "Build", "Publish" }, null);
 
    BuildResult buildResult = BuildManager.DefaultBuildManager.Build(
        buildParameters, buildRequestData);
 
    return (buildResult.OverallResult == BuildResultCode.Success);
}

Publish Profiles

This uses a publish profile. These are basically XML files that tell the build how to publish your database; here’s an example of one:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>MySqlDatabase</TargetDatabaseName>
    <DeployScriptFileName>MySqlDatabase.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=TLAPTOP\PCM2014;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

You can get Visual Studio to generate this for you, by selecting to “Deploy…” the database, and then selecting “Save Profile As…”:

Database Connection

Now that we’ve deployed the database, the next step is to connect. One way of doing this is to configure the connection string in the app.config of your test project:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="MySqlDatabase" 
         connectionString="Data Source=TLAPTOP\PCM2014; Initial Catalog=MySqlDatabase; Integrated Security=true" />
  </connectionStrings>
</configuration>

You can then connect using the following method:

ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
 
_sqlConnection = new SqlConnection(connectionString.ConnectionString);
_sqlConnection.Open();

This sort of functionality could form the basis of a base test class; for example:

[TestClass]
public class BaseTest
{
    protected SqlConnection _sqlConnection;
 
    [TestInitialize]        
    public virtual void SetupTest()
    {
        ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
        _sqlConnection = new SqlConnection(connectionString.ConnectionString);
        _sqlConnection.Open();
    }
 
    [TestCleanup]
    public virtual void TearDownTest()
    {
        _sqlConnection.Close();
    }
}

Transactions

So, we now have a deployment task, and a connection, the next step is to run the tests in a way in which they are repeatable. The key here is to use transactions. Going back to the base class, we can wrap this functionality into a method that can simply be inherited by all unit tests.

public class BaseTest
{
    protected SqlConnection _sqlConnection;
    protected SqlTransaction _sqlTransaction;
 
 
    [TestInitialize]
    public virtual void SetupTest()
    {
        ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
        _sqlConnection = new SqlConnection(connectionString.ConnectionString);
        _sqlConnection.Open();
        _sqlTransaction = _sqlConnection.BeginTransaction();
    }
 
    [TestCleanup]
    public virtual void TearDownTest()
    {
        _sqlTransaction.Rollback();
        _sqlConnection.Close();
    }
}

Refactor The Base Class

Let’s put all this together, and remove some parts that can be separated into a common helper class:

public class ConnectionHelper
{
    SqlConnection _sqlConnection;
    SqlTransaction _sqlTransaction;
 
    public SqlConnection OpenTestConnection()
    {
        ConnectionStringSettings connectionString = ConfigurationManager.ConnectionStrings["MySqlDatabase"];
 
        _sqlConnection = new SqlConnection(connectionString.ConnectionString);
        _sqlConnection.Open();
        _sqlTransaction = _sqlConnection.BeginTransaction();
 
        return _sqlConnection;
    }
 
    public SqlCommand UseNewTestCommand()
    {
        SqlCommand sqlCommand = _sqlConnection.CreateCommand();
        sqlCommand.Transaction = _sqlTransaction;
        return sqlCommand;
    }
 
    public void CloseTestConnection()
    {
        _sqlTransaction.Rollback();
        _sqlConnection.Close();
    }
}

The base test now looks like this:

[TestClass]
public class BaseTest
{
    protected ConnectionHelper _connectionHelper;
 
    [ClassInitialize]
    public virtual void SetupTestClass()
    {
        DatabaseDeployment.DeployDatabase(@"..\MySqlDatabase\MySqlDatabase.sqlproj");
    }
 
    [TestInitialize]
    public virtual void SetupTest()
    {
        
        _connectionHelper = new ConnectionHelper();
        _connectionHelper.OpenTestConnection();
    }
 
    [TestCleanup]
    public virtual void TearDownTest()
    {
        _connectionHelper.CloseTestConnection();
    }
}

In Summary

We now have a base test class that will deploy the database, establish a new connection, and transaction; and then, on completion of the test, will roll back the transaction. Here’s what the above test now looks like:

[TestClass]
public class UnitTest2 : BaseTest
{
    [TestMethod]
    public void CheckTotalAmount3()
    {
 
        // Arrange
        using (SqlCommand sqlCommand = _connectionHelper.UseNewTestCommand())
        {
            sqlCommand.CommandText =
                "INSERT INTO SalesOrder (Id, ProductCode, NetAmount) " +
                "VALUES (2, 'test', 10)";
            sqlCommand.ExecuteNonQuery();
        }
 
        // Act
        using (SqlCommand sqlCommand = _connectionHelper.UseNewTestCommand())
        {                
            sqlCommand.CommandText = $"SELECT TotalAmount FROM SalesOrder WHERE Id = 2";
            decimal result = decimal.Parse(sqlCommand.ExecuteScalar().ToString());

            // Assert
            Assert.AreEqual(12, result);
        }
    }
}

Debugging Unit Tests

The idea behind the framework described above is that the data is never committed to the database; as a consequence of this, the tests are repeatable, because nothing ever changes. The unfortunate side-effect here is that debugging the test is made more difficult as, if it fails, it is not possible to see directly which changes have been made. There’s a couple of ways around this. One of which is to simply debug the test, and then manually fire a commit, look at the data and continue. However, a SQL expert recently introduced me to a concept of “Dirty Reads”.

Dirty Reads

Dirty reads are achieved by issuing the following command the SQL Server:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

This allows you to see changes in the database which are still pending (that is, they have yet to be committed). What this means is that you can see the state of the data as it currently is, it also doesn’t place a lock on the data. One of the big issues with using this methodology is that you can see half committed transactions; of course, in this instance, that’s exactly what you want! Let’s debug our unit test:

Now let’s have a look at the SalesOrder table:

Not only does this not return anything, it doesn’t return at all. We’ve locked the table, and held it in a transaction. Let’s apply our dirty read and see what happens:

Instantly, we get the SalesOrder. If we now complete the test and run the query again, the data is gone:

References

https://pragprog.com/magazines/2012-01/unit-tests-are-first

http://stackoverflow.com/questions/13843990/how-can-i-programatically-publish-a-sql-server-database-project

https://social.msdn.microsoft.com/Forums/vstudio/en-US/ec95c513-f972-45ad-b108-5fcfd27f39bc/how-to-build-a-solution-within-c-net-40-?forum=msbuild

http://stackoverflow.com/questions/10438258/using-microsoft-build-evaluation-to-publish-a-database-project-sqlproj

https://msdn.microsoft.com/en-us/library/microsoft.build.framework.ilogger.aspx

http://stackoverflow.com/questions/10438258/using-microsoft-build-evaluation-to-publish-a-database-project-sqlproj

https://msdn.microsoft.com/en-us/library/hh272681(v=vs.103).aspx

Using MSTest DataRow as a Substitute for NUnit TestCase

I used to believe that Nunit’s TestCase test (that is, an ability to define a test and then simply pass it alternate parameters) was denied MSTest users. It appears that this is, at least now, fallacious.

The following article implies that this is a recent change:

Taking the MSTest Framework forward with “MSTest V2”

This particular example is in a UWP application:

        [DataTestMethod]
        [DataRow(1, 2, 3, 6)]
        [DataRow(8, 2, 3, 13)]
        [DataRow(8, 5, 3, 12)]
        public void AddNumbers(int num1, int num2, int num3, int total)
        {
            Assert.AreEqual(num1 + num2 + num3, total);
        }

Will result in a failing test, and:

        [DataTestMethod]
        [DataRow(1, 2, 3, 6)]
        [DataRow(8, 2, 3, 13)]
        [DataRow(8, 5, 3, 16)]
        public void AddNumbers(int num1, int num2, int num3, int total)
        {
            Assert.AreEqual(num1 + num2 + num3, total);
        }

Results in a passing one.

If you want additional information relating to the test, you can use this syntax:

        [DataTestMethod]
        [DataRow(1, 2, 3, 6, DisplayName = "First test")]
        [DataRow(8, 2, 3, 13, DisplayName = "Second test")]
        [DataRow(8, 5, 3, 15, DisplayName = "This will fail")]
        public void AddNumbers(int num1, int num2, int num3, int total)
        {
            Assert.AreEqual(num1 + num2 + num3, total);
        }

Given the constant problems that I have with finding the correct NUnit test adaptor, and trying to work out which are the right libraries, I think, despite coming late to this party, MS might actually drag people back to MSTest with this.

SQL Server quick insert test data

I recently came across a little known feature of SSMS that made me think that, for small amounts of data, it may be possible to create a procedure to insert test data into a table. Let’s start with the table:

CREATE TABLE [dbo].[Audit](
	[AuditDate] [datetime] NOT NULL,
	[AuditText] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

So, let’s create a statement that will inset data into this table:

INSERT INTO DBO.[Audit]
(AuditDate, AuditText)
VALUES
(GETDATE(), 'The sky is blue')

GO

sql1

Okay, so here’s the thing; try this:

INSERT INTO DBO.[Audit]
(AuditDate, AuditText)
VALUES
(GETDATE(), 'The sky is blue')

GO 10

sql2

So, that’s good, but what if you want some actual kosher data?

Randomising a date is relatively easy, using a combination of the RAND() function and the DATEADD() functions, you can do this in a single line. However, randomising text is more complex. If you just want random strings of letters, then you could try something like this. However, if you want something more sensible, then you can use a lookup table:

DECLARE @lookupTable TABLE (TextLookup VARCHAR(255))
DECLARE @lookupTableSize INT

INSERT INTO @lookupTable
(TextLookup)
VALUES
('red'), ('blue'), ('green'), ('orange'), ('purple')

SELECT @lookupTableSize = COUNT(1) FROM @lookupTable

INSERT INTO dbo.[Audit]
(AuditDate, AuditText)
VALUES
(DATEADD(day, -ABS(CHECKSUM(NewId())) % 30000, GETDATE()),
(SELECT TOP 1 TextLookup FROM (
    SELECT TextLookup, ROW_NUMBER() OVER (ORDER BY TextLookup) AS RowNum
    FROM @lookupTable
) AS DerivedAudit
WHERE DerivedAudit.RowNum >= (ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % @lookupTableSize) + 1))

GO 100


SELECT *
FROM dbo.Audit

It’s worth noting that GO in this case executes the entire script, not just the bit you want; however, I’m not proposing you use this for any serious volume – just for add a quick few records.

SQL Server xp_sprintf and xp_sscanf

I learned about these two functions accidentally last year, and I wanted to try them both out to see what they do. A long time ago, I used to be a C programmer, so the concepts of the two functions are familiar to me (assuming they are similar to their C cousins).

xp_sprintf

This is kind of like the C function sprintf… except it isn’t. Firstly, it only supports the %s placeholder. This means that you can’t represent, or format a float, like you can in C. Secondly, it looks like it only works with strings of 255 characters or less. Here’s an example:

DECLARE @myString varchar (255)
EXEC xp_sprintf @myString OUTPUT, 
    'There are %s fields in the table %s', '3', 'TestTable'

SELECT @myString

sql1

Admittedly, that seems a bit pointless; but what if we do this:

DECLARE @colCount INT
DECLARE @colCountStr varchar(255)
DECLARE @myString varchar(255)
DECLARE @tableName varchar(255) 

set @tableName = 'Audit'

SELECT @colCount = COUNT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_CATALOG = 'TestDB' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @tableName

SELECT @colCountStr = CONVERT(varchar, @colCount)

EXEC xp_sprintf @myString OUTPUT, 
    'There are %s fields in the table %s', @colCountStr, @tableName

SELECT @myString

sql2

It makes a bit more sense. However, there are a number of restrictions with the procedure. There is the 255 characters, you can only substitute strings and, because it’s a procedure, you can’t include it in a query.

A better way: FORMATMESSSAGE

FORMATMESSAGE is a function, but it supports all the standard placeholders:

DECLARE @colCount INT
DECLARE @tableName varchar(255) 

set @tableName = 'Audit'

SELECT FORMATMESSAGE('There are %d fields in the table %s', COUNT(COLUMN_NAME), @tableName) 
FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_CATALOG = 'TestDB' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @tableName

sql2

xp_sscanf

This looks like a far more useful function; as it allows parsing of a string. Whether or not you believe you should be storing your data in a manner that requires parsing, one day, you will need to do it. Consider this table:

scanf

So, I want to analyse this data; how about:

select *
from dbo.[Audit]

DECLARE @colour varchar(255)
DECLARE @text varchar(255)

SELECT @text = AuditText from dbo.[Audit]

EXEC xp_sscanf @text, 'The sky is %s today', @colour OUTPUT

select @colour

sql3

The eagle eyed amongst you might notice a slight issue here – because it’s a procedure, it can’t be used inside a query. It’s also hampered by the same restrictions of 255 characters, etc… I couldn’t find an xp_scanf equivalent of FORMATMESSAGE, so I rolled my own:

CREATE FUNCTION ScanFWrapper 
(	
	@inputText varchar(255),
	@formatText varchar(255)
)
RETURNS varchar(255)
AS
BEGIN	
	DECLARE @Result varchar(255)

	EXEC xp_sscanf @inputText, @formatText, @Result OUTPUT
		
	RETURN @Result

END
GO

Admittedly, it’s not very generic, but you can call it like this:

SELECT dbo.ScanFWrapper(AuditText, 'The sky is %s today') 
FROM dbo.[Audit]

sql4

Conclusion

There are a number of xp_* methods, and they seem to be one-off procedures, so I’m probably being unfair on them in trying to compare them to their C equivalents.