A powerful and flexible extension to NSubstitute for mocking database queries
If there's anything you need to do that's not covered below then raise an Issue and we'll see about adding it!
The package is available from nuget.org as NSubstitute.Community.DbConnection
Or, you can run install-package NSubstitute.Community.DbConnection
from within your IDE
To get 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();
You can then fluently add behaviour to your mock connection using the .SetupQuery()
methods below, starting with some relative "greedy" matching by default, simply on the query text passed in, but with the ability to match parameter values if you need and also match parts of the query text.
You can define single or multiple result sets, using anonymous types or concrete types, including record types.
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");
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");
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");
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()
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" });
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"));
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);
Use .Throws()
to simulate the scenario when an exception is expected.
mockConnection.SetupQuery("delete from TableWithTriggerPreventDelete")
.Throws(new Exception());
Check out the test fixtures in NSubstitute.DbConnection.Tests
and NSubstitute.DbConnection.Dapper.Tests
for working examples of the full set of supported functionality.