Testing Tips - adamfoneil/Dapper.QX GitHub Wiki

Inline SQL used thoughtlessly can introduce a lot of quality risk to an application. I mitigate this with an integration test strategy outlined here. This approach catches invalid SQL due to misspelled or missing objects, but does not assert any particular query result otherwise.

  1. Use TestableQuery<T> as the basis for your query classes. This requires you to implement the method GetTestCasesInner in your query class. For example, see the query classes here. The idea here when implementing GetTestCasesInner is to have one or more yield return statements that demonstrate your query used with various parameter options. This example shows several different parameter combinations being tested.
protected override IEnumerable<ITestableQuery> GetTestCasesInner()
{
	yield return new OpenWorkItems(-1);
	yield return new OpenWorkItems(1);
	yield return new OpenWorkItems(232) { LabelIds = new int[] { 1, 2, 3 } };
	yield return new OpenWorkItems(1) { Text = "this that other" };
	yield return new OpenWorkItems(1) { Page = 4 };
}

The parameter values you supply don't matter unless your query performs an INSERT, UPDATE, or DELETE, such as this example. I typically use negative numbers as test parameter values in this case because negative values normally aren't used as Id values. This enables me to test a statement that modifies data without actually modifying any data.

  1. Add a test project to your solution if you don't have one already. Add a class dedicated to query tests, like this one for example.

  2. Add a method that returns an IDbConnection -- in my scenarios it's always a SqlConnection. This is so the test methods you're about to write have a way of connecting to a real database. If your database is installed at (localdb)\mssqllocaldb, then I recommend my SqlServer.LocalDb.Testing library because you could then use a very simple method that would look like this:

private SqlConnection GetConnection() => LocalDb.GetConnection("MyDatabase");

If your database requires a specific connection string accessed through configuration, then you have more options and decisions to make about how to inject configuration into your test project. I like to use some version of this approach.

internal static IConfiguration Config => new ConfigurationBuilder()
    .AddJsonFile("appsettings.json", false)
    .Build();
  1. For each TestableQuery<T> in your project, add a test method like this. This uses the QueryHelper.Test method to iterate over the various cases you created within your query class via the GetTestCasesInner method. If any of the cases fail within the query, then the test will fail. This example tests the MyWorkTypes query.
[TestMethod]
public void MyWorkTypes() => QueryHelper.Test<MyWorkTypes>(GetConnection);

You may have unusual cases where a query has constructor arguments. In this case, you can write the test a little differently as in this example below where you use a factory method to create a query instance that is tested. This is slightly more verbose, but the reason for it, again, is to let you pass any required constructor arguments.

[TestMethod]
public void OpenWorkItemLabelCountsQuery() => QueryHelper.Test(() => new OpenWorkItemLabelCounts(-1), GetConnection);

Test Explorer example

Here's what a query failure looks like in Test Explorer. In this case, I've deliberately added a typo within the SQL of a query:

img

The test failure message indicates the cause of the problem:

img