Refactor to get one more layer of abstraction for less repeated code

Question

I might add more methods, but currently I have this design: So connectionString comes from IOptions pattern. Then for each method, I am repeating myself to open the connection in this part of the code, but I do need the connectionString for that:

using IDbConnection myConnection = new SnowflakeDbConnection(connectionString: _connectionString);
myConnection.Open();

Note: SnowflakeDbConnection is not mine. It is from the NuGet package for accessing Snowflake from .NET

I wanted to see if you can suggest a better design.

This is how I am doing it now:

public class MyDatabaseDataService: IMyDatabaseDataService
{
    private readonly string _connectionString;

    public MyDatabaseDataService(IOptions<ApplicationOptions> options)
    {
        this._connectionString = options.Value.MyDatabaseConnectionString;
    }
    
    public async Task<IEnumerable<T>> GetAllAsync<T>(string selectSqlCommand)
    {
        using IDbConnection myConnection = new SnowflakeDbConnection(connectionString: _connectionString);
        myConnection.Open();

        var result = await myConnection.QueryAsync<T>(selectSqlCommand).ConfigureAwait(false);

        myConnection.Close();

        return result;
    }

    public async Task<T> GetSingleOrDefaultById<T>(string sqlCommand, int id)
    {
        using IDbConnection myConnection = new SnowflakeDbConnection(connectionString: _connectionString);
        myConnection.Open();

        var result = await myConnection.QuerySingleOrDefaultAsync<T>(sqlCommand, new { id });

        return result;
    }
}

and the DI for MyDatabaseDataService is done in Program.cs like this:

builder.Services.AddSingleton<IMyDatabaseDataService, MyDatabaseDataService>();

Answer 1

One way to reduce repetitive code is through the usage of the template method pattern.

In your template method:

1. Establish a connection
2. Execute database operation (this is the customizable step)
3. Close the connection

private async Task<T> GetAsync<T>(Func<IDbConnection, Task<T>> databaseOperation)
{
    using IDbConnection myConnection = new SnowflakeDbConnection(connectionString: _connectionString);
    myConnection.Open();

    return await databaseOperation(myConnection).ConfigureAwait(false);
}

Then you can rewrite your publicly exposed methods:

public async Task<IEnumerable<T>> GetAllAsync<T>(string selectSqlCommand)
    => await GetAsync(conn => conn.QueryAsync<T>(selectSqlCommand));

public async Task<T> GetSingleOrDefaultById<T>(string sqlCommand, int id)
    => await GetAsync(conn => conn.QuerySingleOrDefaultAsync<T>(sqlCommand, new { id }));