This article explains how to create a database access class that allows to implement unit test with XUnit and Moq using ADO.NET.
Introduction
In this article, we explain how to create a database access class that is unit test friendly and is done with plain ADO.NET classes without the necessity of more complex framework. The tests will be implemented using XUnit and Moq. The examples are implemented using C# and NET 5, but can be implemented also in other versions of NET, for example NET Core 3.1
Background
Traditionally, developers that use ADO.NET, create the Data
class by direct implementation on it the objects designed to manage the database access, and normally we use the concrete implementation of the connection object (for example, SqlConnection
) to implement the data access class.
This form does not allow to create a mock of the class that depends on the existence of an interface. The interface allow us to create a fake object to implement the mock.
I found that many developers think that is not possible do a mock of the DB class because the lack of the interface in the concrete implementation of the ADO.NET class (such as SQLCommand
, or SQLConnection
), The fact is that there exists a generic interface that allow us to do this.
https://docs.microsoft.com/en-us/dotnet/api/system.data.idbconnection?view=netcore-3.1
IDbConnection
allow us to use it to inject it in the class instead a concrete implementation of the connection, or create it with new
inside the code.
In our code, because, actually using the same object injected in all instances of the database access class may create some concurrence problem, we use a delegate to pass a function to the db class instead directly an instance of an object derived from IDbConnection
. This ensures that the object used in our instantiation of the class is unique for the class, avoiding concurrence problems.
Implementing the Class as Unit Testable
How we implement it, well for use in the real program to access the database we need to follow three simple steps.
First Step
Configure the function to be injected to the object in the startup.cs class.
public void ConfigureServices(IServiceCollection services)
{
string connectionStr = Configuration.GetConnectionString("Wheater");
services.AddScoped<IMoqReadyService, MoqReadyService>(
x => new MoqReadyService(() => new SqlConnection(connectionStr)));
}
Observe in this snip of code that we get the connection string from configuration, and the factory function is coded to create a new SqlConnection
object when it will be invoked.
Second Step
Create the Data access class and inject the function as parameter in the constructor.
private Func<IDbConnection> Factory { get; }
public MoqReadyService(Func<IDbConnection> factory)
{
this.Factory = factory;
}
As you see, we inject the function inside the class in the constructor and store it in a private
variable.
Third Step
Invoke the factory and create the rest of the needed objects.
As the last step, invoke the factory inside out method to create our instance the SqlConnection
(as is configured in this example) and create the rest of the ADO.NET objects:
public async Task<List<WeatherForecast>> GetForecastMoqableAsync(DateTime startDate)
{
var t = await Task.Run(() =>
{
using IDbConnection connection = this.Factory.Invoke();
using IDbCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM WeatherInfo WHERE Date = @date";
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@date", SqlDbType.DateTime)
{ Value = startDate });
That can be different depending on what operation we use in the method, but the creation of the IDbConnection
implementation is the same using the instruction:
using IDbConnection connection = this.Factory.Invoke();
In resume to create our class testable, the operations are the following:
Implement the Test Code
Implementing the test code is pretty straightforward now. We only need to change the factory implementation for a Mock
object and replace and configure all the objects based in this initial mock.
The main step in the code of the XUnit is the creation of the IdbConnection
mock object as is shown in the next code segment:
public class MoqSqlTest
{
readonly MoqReadyService service;
readonly Mock<IDbConnection> moqConnection;
public MoqSqlTest()
{
this.moqConnection = new Mock<IDbConnection>(MockBehavior.Strict);
moqConnection.Setup(x => x.Open());
moqConnection.Setup(x => x.Dispose());
this.service = new MoqReadyService(() => moqConnection.Object);
}
In this code segment, you can observe how the moq
object is created based in the IDbConnection
and part of the configuration of the test. After creating this base object, the creation of the rest of the test depends on what type of data access function you want to test. Let's see this in the following section.
Using the Code
The code presents two examples of test classes that test methods that read and insert information from the database.
Testing a read operation with Data Reader.
[Trait("DataReader", "1")]
[Fact(DisplayName = "DataReader Moq Set Strict Behaviour to Command Async")]
public async Task MoqExecuteReaderFromDatabaseAsync()
{
var moqDataReader = new Mock<IDataReader>();
moqDataReader.SetupSequence(x => x.Read())
.Returns(true)
.Returns(false);
moqDataReader.SetupGet<object>(x => x["Date"]).Returns(DateTime.Now);
moqDataReader.SetupGet<object>(x => x["Summary"]).Returns("Sunny with Moq");
moqDataReader.SetupGet<object>(x => x["Temperature"]).Returns(32);
var commandMock = new Mock<IDbCommand>();
commandMock.Setup(m => m.Parameters.Add
(It.IsAny<IDbDataParameter>())).Verifiable();
commandMock.Setup(m => m.ExecuteReader())
.Returns(moqDataReader.Object);
this.moqConnection.Setup(m => m.CreateCommand()).Returns(commandMock.Object);
List<WeatherForecast> result =
await this.service.GetForecastMoqableAsync(DateTime.Now);
Assert.Single(result);
commandMock.Verify(x => x.Parameters.Add(It.IsAny<IDbDataParameter>()),
Times.Exactly(1));
}
Testing an Insert
operation using Mock behaviour Strict.
[Trait("ExecuteNonQuery", "1")]
[Fact(DisplayName = "Moq Set Strict Behaviour to Command Async")]
public async Task MoqExecuteNonQueryStrictBehaviourforCommandAsync()
{
WeatherForecast whetherForecast = new()
{
TemperatureC = 25,
Date = DateTime.Now,
Summary = "Time for today"
};
var commandMock = new Mock<IDbCommand>(MockBehavior.Strict);
commandMock.Setup(c => c.Dispose());
commandMock.Setup(c => c.ExecuteNonQuery()).Returns(1);
commandMock.SetupSequence(m => m.Parameters.Add(It.IsAny<IDbDataParameter>()));
commandMock.Setup(m => m.Parameters.Clear()).Verifiable();
commandMock.SetupProperty<CommandType>(c => c.CommandType);
commandMock.SetupProperty<string>(c => c.CommandText);
this.moqConnection.Setup(m => m.CreateCommand()).Returns(commandMock.Object);
var result = await service.SetForecastAsync(whetherForecast);
Assert.Equal(1, result);
commandMock.Verify(x => x.Parameters.Add
(It.IsAny<IDbDataParameter>()), Times.Exactly(3));
}
Observe that in this case, we are creating the mock objects using strict behaviour, we can also create it using Loose behaviour, the use of the behaviour depends on what you want to test in your class.
The loose behaviour allows you to create more short tests, but you can lose information about what you want to test in the class under test.
Here is an example of a loose behaviour using the same class as the last code example:
[Trait("ExecuteNonQuery", "2")]
[Fact(DisplayName = "Moq Set Loose Behaviour to Command Async")]
public async Task MoqExecuteNonQuerySetLooseBehaviourToCommandAsync()
{
WeatherForecast whetherForecast = new()
{
TemperatureC = 25,
Date = DateTime.Now,
Summary = "Time for today"
};
var commandMock = new Mock<IDbCommand>(MockBehavior.Loose);
commandMock.Setup(c => c.ExecuteNonQuery()).Returns(1);
commandMock.SetupSequence(m => m.Parameters.Add(It.IsAny<IDbDataParameter>()));
this.moqConnection.Setup(m => m.CreateCommand()).Returns(commandMock.Object);
var result = await service.SetForecastAsync(whetherForecast);
Assert.Equal(1, result);
commandMock.Verify(x => x.Parameters.Add
(It.IsAny<IDbDataParameter>()), Times.Exactly(3));
}
Points of Interest
I found in some developers the tendency to use in simple operations with the database, very massive frameworks as Entity Frameworks, and the justifications are the following:
- ADO.NET class cannot be unit test
- ADO.NET cannot make Asynchronous operation
The simple example code that you can download allows you to make asynchronous the call to the DB and also do unit tests over the class without the overhead of the EF.
I am not opposed to EF, it is very useful in large and complex interfaces with the DB, but I prefer the simple ADO.NET operation when all the interaction with the DB are a few requests or insert
operations.
I normally working with Microservices, and that is the situation that I deal with day by day with the Db.
You can also see a version in the video of this article at:
History
- 16th May, 2022: First version