NSubstitute.Community.DbConnection 2.5.0

NSubstitute.DbConnection

A powerful and flexible extension to NSubstitute for mocking database queries

Getting started

Create a mock connection by calling .SetupCommands() on a regular NSubstitute IDbConnection mock:

    var mockConnection = Substitute.For<IDbConnection>().SetupCommands();

or, if you want async support, use DbConnection instead of IDbConnection - either works fine here:

    var mockConnection = Substitute.For<DbConnection>().SetupCommands();

Set up a simple query

Set up a query using .SetupQuery() and the expected query text, then specify the response using .Returns() and an array of anonymous types:

    mockConnection.SetupQuery("select * from MyTable").Returns(
        new { Foo = 1, Bar = "abc" },
        new { Foo = 2, Bar = "def" }
    );

That's all you need to do - executing this query against the connection will return the specified results:

    using var command = mockConnection.CreateCommand();
    command.CommandText = "select * from table";
    mockConnection.Open();
    using var reader = command.ExecuteReader();

    reader.Read();
    reader["Foo"].Should().Be(1);
    reader["Bar"].Should().Be("abc");

    reader.Read();
    reader["Foo"].Should().Be(2);
    reader["Bar"].Should().Be("def");

Works with async

You'll also get the correct mocked behaviour using the async API

    await using var command = mockConnection.CreateCommand();
    command.CommandText = "select * from table";
    await mockConnection.OpenAsync();
    await using var reader = command.ExecuteReaderAsync();

    await reader.ReadAsync();
    reader["Foo"].Should().Be(1);
    reader["Bar"].Should().Be("abc");

    await reader.ReadAsync();
    reader["Foo"].Should().Be(2);
    reader["Bar"].Should().Be("def");

Works with Dapper

Because Dapper uses the same calls to DbConnection under the hood, you'll get the correct mocked behaviour here too:

    var result = mockConnection.Query<(int Foo, string Bar)>("select * from table").ToList();

    reader[0].Foo.Should().Be(1);
    reader[0].Bar.Should().Be("abc");
    reader[1].Foo.Should().Be(2);
    reader[1].Bar.Should().Be("def");

Query parameters

Use .WithParameter() to also match on the value of a parameter passed to the query (by default, a query will match on just the command text, ignoring any parameter values):

    mockConnection.SetupQuery("select * from table where Id = @id")
        .WithParameter( "id", 1)
        .Returns(new { Id = 1, Name = "The first one"});

    mockConnection.SetupQuery("select * from table where Id = @id")
        .WithParameter( "id", 2)
        .Returns(new { Id = 2, Name = "The second one"});

You can add multiple parameters by chaining calls to .WithParameter() or by using a single call to WithParameters()

You can also force a query to match only if no parameters are passed by calling .WithNoParameters()

Query text matching

In addition to supplying just the query text to match against, you can also supply a delegate or a regular expression:

    mockConnection.SetupQuery(queryText => queryText.Contains("from MyTable"))
        .Returns(new { Foo = 1, Bar = "abc" });

    mockConnection.SetupQuery(new Regex("select .+ from"))
        .Returns(new { Foo = 1, Bar = "abc" });

Concrete result types

As well as anonymous types, you can also use concrete types (including record types) as result types:

public class KeyValue
{
    public int Key { get; set; }

    public string Value { get; set; }
}

public record KeyValueRecord(int Key, string Value);

    mockConnection.SetupQuery("select * from MyTable")
        .Returns(new KeyValue { Key = 1, Value = "abc" });

    mockConnection.SetupQuery("select * from MyTable")
        .Returns(new KeyValueRecord(1, "abc"));

Multiple result sets

Use .ThenReturns() to set up second and subsequent result sets for your query:

    mockConnection.SetupQuery("select * from MyTable")
        .Returns(new { Never = 1, Eat = 1 })
        .ThenReturns(new { Shredded = 3, Wheat = 4 });
    );

    using var command = mockConnection.CreateCommand();
    command.CommandText = "select * from table";
    mockConnection.Open();
    using var reader = command.ExecuteReader();

    reader.Read();
    reader["Never"].Should().Be(1);
    reader["Eat"].Should().Be(2);

    reader.NextResult();

    reader.Read();
    reader["Shredded"].Should().Be(3);
    reader["Wheat"].Should().Be(4);

Further examples

Check out the test fixtures in NSubstitute.DbConnection.Tests and NSubstitute.DbConnection.Dapper.Tests for working examples of the full set of supported functionality.