Testing database calls C#

Question

First, let's address terminology here. Everything I search for says, "Unit tests don't touch the database!" I don't want a unit test. I want a test that when I send data to a database, I know that it correctly saves it (and testing of other crud operations). I have a repository layer that essentially accepts a DTO, then maps that DTO to an entity framework model, then saves that model to the database.

I need to be able to ensure that sending a DTO to these methods is in fact saving to the database.

An example method signature on the repository is:

public bool Save(SomeObjectDTO someObject)

I just need to test against whether or not this method call returns true.

What is the best way to set up tests where my methods being called are ones that save to the database?

Furthermore, is there a standard way to set up a blank testing database? It would be great if when I hit "Run tests" it constructs an empty database, fills it with initial data that is necessary, and then performs all the CRUD operations (all of my repository calls) to see that they all are saving like they should be.

I apologize if this is already answered, but everything I have searched either has someone saying you shouldn't be testing database calls, or people talking about mocking which is not really useful here.

I just want an example and/or the standard practice on how these types of tests should be set up.

Answer 1

What you're looking for is called integration testing, and is just as important as writing unit tests. There's a lot of potential bugs that are exposed by your underlying data provider that mocking your repository won't necessarily find (invalid foreign keys, null data for something marked as not null, etc).

I think it's also important that you test against the same database provider as your production system, otherwise there's a risk of missing implementation specific behavior. I use Azure SQL for a project, and instead of creating an in-memory SQL CE instance, I have a separate database on Azure that's only used for my integration tests.

If you use XUnit (and I'm sure it exists for other testing frameworks), there's a handy attribute [AutoRollback] that will automatically roll back your transaction after each test runs.

[Fact]
[AutoRollback]
public void AddProductTest_AddsProductAndRetrievesItFromTheDatabase()
{
    // connect to your test db
    YourDbContext dbContext = new YourDbContext("TestConnectionString")

    dbContext.Products.Add(new Product(...));

    // get the recently added product (or whatever your query is)
    var result = dbContext.Single();

    // assert everything saved correctly
    Assert.Equals(...);
}

After the test is finished, your database will be at a blank slate again (or whatever it was before you ran the test).