Unit Test Database Commands in C#

Question

I am currently trying to create unit tests for my method that is inserting data into my database. I know that I need to either mock or create a fake class so that my test data does not into added to my database, but I am not sure how to go about doing this. Does anyone have an idea?

Here is my code that my console "User Interface" touches. This is in my "Business" C# project:

/// <summary>
/// Gets the connection string.
/// </summary>
private static readonly string _connectionString = ConfigurationManager.ConnectionStrings["Database"].ConnectionString;

/// <summary>
/// Inserts a new admin into the Admins database table.
/// </summary>
/// <param name="admin">Admin record.</param>
public static void InsertNewAdmin(Admin admin)
{
    var adminDatabaseWriter = new AdminDatabaseWriter(_connectionString);
    adminDatabaseWriter.Insert(admin);
}

From there it goes into my AdminDatabaseWriter class and does the following in the method "Insert":

/// <summary>
/// Inserts a new admin into the Admins database table.
/// </summary>
/// <param name="admin">Admin record.</param>
public void Insert(Admin admin)
{
    using SqlConnection sqlConnection = new(_connectionString);
    sqlConnection.Open();

    using SqlCommand sqlCommand = DatabaseHelper.CreateNewSqlCommandWithStoredProcedure("InsertNewAdmin", sqlConnection);
    sqlCommand.Parameters.AddWithValue("@PIN", admin.PIN);
    sqlCommand.Parameters.AddWithValue("@AdminType", admin.AdminTypeCode);
    sqlCommand.Parameters.AddWithValue("@FirstName", admin.FirstName);
    sqlCommand.Parameters.AddWithValue("@LastName", admin.LastName);
    sqlCommand.Parameters.AddWithValue("@EmailAddress", admin.EmailAddress);
    sqlCommand.Parameters.AddWithValue("@Password", admin.Password);
    sqlCommand.Parameters.AddWithValue("@AssessmentScore", admin.AssessmentScore);

    var userAddedSuccessfully = sqlCommand.ExecuteNonQuery();
    sqlConnection.Close();

    if (userAddedSuccessfully < 0)
    {
        throw new AdminNotAddedToDatabaseException("User was unsuccessful at being uploaded to the database for an unknown reason.");
    }
}

Again, I am trying to unit test this last piece of code that I have attached. What would be the best way to test that my code actually added the object into the database without actually adding it to the database.

Answer 1

Before writing unit tests for the method Insert, you should ask yourself what exactly you wish to "unit test". Insert returns nothing (void), so no return value to test against. However, it throws an exception when some conditions are fulfilled, and it also has a side effect (calling ExecuteNonQuery to put some data in database).

No matter what scenario you decide to test (be it exception being thrown or side effect occurring), you should tell your testing framework that when it reaches the line where side effect takes place, it should replace it with another operation that actually does nothing (not only so that actual database won't get "dirty", but also because external dependencies might consume time or even worse, make your test fail because they fail themselves, and basically we don't care for such dependencies in the context of the object we test).

All standard testing frameworks/libraries provide tools for making assertions about exceptions and side effects, but please note that mocking your side effect (and generally mocking dependencies) is essential not only when you test against a return value or an exception, but also when you wish to verify that side effect itself occurs. Because either way we want to prevent external dependencies from interfering with out test.

Regardless of what testing framework you use, working with interfaces in your product code is generally preferred in order to facilitate flexibility and testability of your application, and it will also let you mock "side effect" methods such as ExecuteNonQuery (that is ISqlCommand rather than SqlCommand). This is because when you instruct to mock an interface method, the framework creates a new dummy class implementing that same interface.

To conclude, testing that object is "added" into database can be done by:

1. Changing your code to work with interfaces (ISqlConnection, ISqlCommand).
2. Mocking desired interfaces' methods (Open, ExecuteNonQuery, Close).
3. Executing Insert.
4. Verifying that ExecuteNonQuery has been invoked.

(Whether object has been properly added is a different story, which probably calls for integration tests.)

Answer 2

The piece of code presented in the question has three responsibilities:

1. create SqlCommand and fill it with parameters;
2. execute SqlCommand to have some side effect in the end;
3. check the execution result and throw if value isn't the expected one.

Obviously each of these responsibilities needs to be tested, the thing is that it makes little sense to use the same testing approach for every. While you're able to do unit testing for points 1 and 3 (more on that below), trying to do so for the 2nd one isn't beneficial. SqlCommand execution involves interaction with an external system, which is a database, i.e you need to implement some sort of database component isolation. And you don't have that many options to do so, while each of which is far from ideal.

The options are (at least the ones I'm aware of):

1. Write code against abstractions (like ISqlCommand suggested in another reply) and mock them (with sth like NSubstitute), so that you do nothing on attemtp to reach a real database. You just need to assert then on the mocks to see that proper methods were called in a proper order with expected arguments. This approach is bad in a few ways:
   • database interaction is left untested, while it might be hiding errors (like wrong stored procedure name or implementation or invalid parameters);
   • tests become fragile, as by using mocks your reveal the method implementation details. E.g such simple thing as method calls reordering or addition of additional arguments not visible at the tested method api level could make tests fail, while the changes themselves could be absolutely correct in regard to the business logic, therefore shouldn't cause any test failures.
2. Use some database replacement like Sqlite or EF Core in-memory.
   • Even though this approach is better imo than the previous one, as you have a database more or less similar to the real one, lack of features and behavior differences are still leaving database interaction not properly tested.

Another approach to address the issue is to write not unit but rather integration tests against a real database (not a production one, but specially prepared for tests). You just need to clean it up before/after tests execution. I personally think that this approach is the best to use to test database interaction, as you are testing your application behavior in the environment closest to the production one. Downsides are that the tests setup is a little bit more complicated and test runs will most likely need more time. Libraries like Reseed (I'm authoring it) or Respawn could be helpful here to simplify the setup and optimize execution speed.

Now back to the unit testing of the responsibilities 1 and 3. It should be possible to write unit tests for them by changing your method design a bit. You might extract a few additional methods each of which will be handling each of the responsibilities explicitly. So that initial Insert could now look as a composition of three methods:

1. CreateParameters to prepare SqlCommand parameters – you'll be able to assert that parameters are filled correctly by a pure unit test;
2. Execute to execute a command with a real database – this could be tested in an integration test;
3. AssertUserCreated to throw exception if user creation failed – again pure unit test is enough.

This will allow you to reach 100 percent test coverage, which isn't neccessarily needed. Points 1 and 3 could be left untested, if the parameters creation logic and stored procedure result validation logic are straighforward as they are now.

And of course design could be improved even further to become not only more testable, but also more readable, this is just an idea.