2
\$\begingroup\$

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>();
\$\endgroup\$

1 Answer 1

5
\$\begingroup\$

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 }));
\$\endgroup\$
5
  • 2
    \$\begingroup\$ The using should be closing the connection when the scope of GetAsync is exited. Should be no need for an explicit Close(). \$\endgroup\$ Apr 4 at 16:29
  • \$\begingroup\$ @JesseC.Slicer and/or Peter : Do you have any suggestions for a "null check" for databaseOperation param in the GetAsync method ? \$\endgroup\$ Apr 4 at 17:01
  • 1
    \$\begingroup\$ There are a number of ways to go about it, but if you want to treat the method like a general-purpose API, if (databaseOperation is null) { throw new ArgumentNullException(nameof(databaseOperation)); } at the top of the method will do in a pinch. \$\endgroup\$ Apr 4 at 17:41
  • 1
    \$\begingroup\$ @LonelySemicolon ArgumentNullException.ThrowIfNull(databaseOperation); this should be the very first statement of the GetAsync. \$\endgroup\$ Apr 4 at 17:59
  • 2
    \$\begingroup\$ @JesseC.Slicer Correct, removed the unnecessary Close call. \$\endgroup\$ Apr 4 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.