When working with “expensive” resources, it is essential to call the Dispose method to tell .NET’s garbage collector that it’s time to clean up the instance and all its associated baggage. Disposing of instances is most important when dealing with finite resources on the hot path of your codebase, and for most .NET developers, the most crucial resource is a database connection. Your app is done for if you run out of database connections.

With database connections, we also have a supporting cast of commands and readers, all of which can be disposed of as well. This leads to an interesting question: “Do you HAVE TO dispose everything?”

In this post, we’ll explore the Microsoft.Data.Sqlite implementations of DbConnection, DbCommand, and DbDataReader and see what happens when we call Dispose on each type.

The Data Access Code

When working with ADO.NET, you’ll likely be using a Microsoft.Data.* package or a community library like Npgsql. Each library implements a database-specific version of DbConnection, DbCommand, DbReader, and other intrinsics. Let’s write a simple application that opens a connection, creates a command, and then reads the results.

using Microsoft.Data.Sqlite;  
  
await using SqliteConnection connection = new("Data Source=test.sqlite");  
await connection.OpenAsync();  
  
await using SqliteCommand command = connection.CreateCommand();  
command.CommandText = "SELECT * FROM sqlite_master";  
  
await using SqliteDataReader reader = command.ExecuteReader();  
Console.WriteLine($"Has Rows? {reader.HasRows}");

You may notice a few things happening in the previous code.

1. All implementations implement IDisposable, but more specifically IAsyncDisposable.
2. We can use scope-based disposal, which disposed of our instances as soon as they fall out of scope. The use of await using allows us to do that.
3. All instances are disposed of, but do we need it?

In the following sections, we’ll see what each dispose of and if we need to dispose of all instances in the code sample.

Disposing a SqliteConnection

What does disposing of an instance of SqliteConnection entail? Looking through the codebase using my trusty decompiler, I found that calling dispose does a lot.

protected override void Dispose(bool disposing)  
{  
    if (disposing)  
    {        
        Close();  
    }  
    base.Dispose(disposing);  
}

public override void Close()  
{  
    if (State != ConnectionState.Open)  
    {        
        return;  
    }
      
    Transaction?.Dispose();  
  
    for (var i = _commands.Count - 1; i >= 0; i--)  
    {        
        var reference = _commands[i];  
        if (reference.TryGetTarget(out var command))  
        {            
            // NB: Calls RemoveCommand()  
            command.Dispose();  
        }        
        else  
        {  
            _commands.RemoveAt(i);  
        }    
    }  
    Debug.Assert(_commands.Count == 0);  
  
    _innerConnection!.Close();  
    _innerConnection = null;  
  
    _state = ConnectionState.Closed;  
    OnStateChange(_fromOpenToClosedEventArgs);  
}

1. It closes the current connection and sets the state to closed.
2. It disposes of any active transactions to the database.
3. It also disposes of any DbCommand instances associated with the connection and removes them from being tracked.
4. If there are any inner connections, they get disposed of as well.

Hmm… commands get disposed of. Well, what does that do?

Disposing a SqliteCommand

No surprise, but disposing of a SqliteCommand does a lot too! Let’s take a look at the implementation.

protected override void Dispose(bool disposing)  
{  
    DisposePreparedStatements(disposing);  
  
    if (disposing)  
    {        
        _connection?.RemoveCommand(this);  
    }  
    base.Dispose(disposing);  
}

private void DisposePreparedStatements(bool disposing = true)  
{  
    if (disposing && DataReader != null)  
    {        
        DataReader.Dispose();  
        DataReader = null;  
    }  
    if (_preparedStatements != null)  
    {        
        foreach ((var stmt, _) in _preparedStatements)  
        {            
            stmt.Dispose();  
        }  
        _preparedStatements.Clear();  
    }  
    _prepared = false;  
}

1. The command is removed from the associated connection
2. Any reader associated with the command is disposed.
3. All statements associated with the command are disposed of and cleared from a tracking collection.

Interestingly, again, disposing of the command disposes the reader. Let’s keep going. What does that do?

Disposing a SqliteDataReader

We’re almost to the end. What happens when a SqliteDataReader gets disposed?

protected override void Dispose(bool disposing)  
{  
    if (!disposing || _closed)  
    {        
        return;  
    }  
    _command.DataReader = null;  
  
    _record?.Dispose();  
    _record = null;  
  
    if (_stmtEnumerator != null)  
    {        
        try  
        {  
            while (NextResult())  
            {            
            }        
        }        
        catch  
        {  
        }    
    }  
    _stmtEnumerator?.Dispose();  
  
    _closed = true;  
  
    if (_closeConnection)  
    {        
        _command.Connection!.Close();  
    }
}

Here, disposing of the reader disposes of all the record instances currently part of the reader. The method will also disconnect from the command it is associated with. Also, in this case, the reader will close the connection if a boolean is set to true.

Conclusion

Following the chain of calls, calling Dispose on the SqliteConnection should be enough for most folks to reclaim the resources allocated by the initial code. That said, if you want to reclaim your resources “faster”, you can undoubtedly call the Dispose methods as soon as you’re done with the resource. Generally, I recommend calling dispose on derived instances from SqliteConnection if you use object-pooling, where the root instance is long-lived. This is a common practice in ORMs, which can help improve performance. Finally, if you’re using a dependency injection library, it already handles object disposal automatically for you. As always, check your code, use the decompiler, and learn how stuff works.

As always, thanks for reading and sharing my posts with friends and colleagues. Cheers :)