Disposing of a SqlConnection manually in XUnit tests

Question

I am writing SQL Server integration tests using xUnit.

My tests look like this:

[Fact]
public void Test()
{
    using (IDbConnection connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        connection.Query(@$"...");
        //DO SOMETHING
    }
}

Since I am planning to create multiple tests in the same class I was trying to avoid creating a new SqlConnection every time. I know that xUnit framework per se creates an instance of the class per each running test inside it.

This means I could make the creation of the SqlConnection in the constructor since anyway a new one will be created every time a new test run.

The problem is disposing it. Is it good practice or do you see any problem in disposing manually the SqlConnection at the end of each test?

Such as:

public class MyTestClass
{
    private const string _connectionString = "...";
    private readonly IDbConnection _connection;

    public MyTestClass()
    {
        _connection = new SqlConnection(_connectionString));
    }

    [Fact]
    public void Test()
    {
        _connection.Open();

        _connection.Query(@$"...");
        //DO SOMETHING
        
        _connection.Dispose();
    }
}

Answer 1

I was trying to avoid creating a new SqlConnection every time.

It's okay to create and dispose a new SqlConnection over and over. The connection instance is disposed, but the underlying connection is pooled. Under the hood it may actually use the same connection repeatedly without closing it, and that's okay. See SQL Server Connection Pooling.

So if that's your concern, don't worry. What you are doing in the first example is completely harmless. And however your code is structured, if you're creating a new connection when you need it, using it, and disposing it, that's fine. You can do that all day long.

If what concerns you is the repetitive code, I find this helpful. I often have a class like this in my unit tests:

static class SqlExecution
{
    public static void ExecuteSql(string sql)
    {
        using (var connection = new SqlConnection(GetConnectionString()))
        {
            using (var command = new SqlCommand(sql, connection))
            {
                connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }

    public static T ExecuteScalar<T>(string sql)
    {
        using (var connection = new SqlConnection(GetConnectionString()))
        {
            using (var command = new SqlCommand(sql, connection))
            {
                connection.Open();
                return (T)command.ExecuteScalar();
            }
        }
    }

    public static string GetConnectionString()
    {
        // This may vary
    }
}

How you obtain the connection string may vary, which is why I left that method empty. It might be a configuration file, or it could be hard-coded.

This covers the common scenarios of executing something and retrieving a value, and allows me to keep all the repetitive database code out of my tests. Within the test is just one line:

SqlExecution.ExecuteSql("UPDATE WHATEVER");

You can also use dependency injection with xUnit, so you could write something as an instance class and inject it. But I doubt that it's worth the effort.

If I find myself writing more and more repetitive code then I might add test-specific methods. For example the test class might have a method that executes a certain stored procedure or formats arguments into a query and executes it.

---

You can also do dependency injection with xUnit so that dependencies are injected into the class like with any other class. There are lots of answers about this. You may find it useful. Maybe it's the reason why you're using xUnit. But something simpler might get the job done.

---

Someone is bound to say that unit tests shouldn't talk to the database. And they're mostly right. But it doesn't matter. Sometimes we have to do it anyway. Maybe the code we need to unit test is a stored procedure, and executing it from a unit test and verifying the results is the simplest way to do that.

Someone else might say that we shouldn't have logic in the database that needs testing, and I agree with them too. But we don't always have that choice. If I have to put logic in SQL I still want to test it, and writing a unit test is usually the easiest way. (We can call it an "integration test" if it makes anyone happy.)

Answer 2

There's a couple different things at play here. First, SqlConnection uses connection pooling so it should be fine to create/dispose the connections within a single test. Having said that disposing of the connections on a per test class basis would also be doable. XUnit will dispose of test classes that are IDisposable. So either would work. I'd suggest it's cleaner to create/dispose them within the test.

public class MyTestClass : IDisposable
{
    const string ConnectionStr = "";
    SqlConnection conn;
    public MyTestClass()
    {
        this.conn = new SqlConnection(ConnectionStr);
    }

    public void Dispose()
    {
        this.conn?.Dispose();
        this.conn = null;
    }

    public void Test()
    {
        using (var conn = new SqlConnection(ConnectionStr))
        {
        }
    }
}