Do Yourself a Favor When Writing Connection Strings In Configuration
Tags: csharp, dapper, dotnet, entityframework
Published: 2022-11-12 20:56:06

> Originally published @ **[hashnode](https://webjose.hashnode.dev/do-yourself-a-favor-when-writing-connection-strings-in-configuration)**.

> Heredia, Costa Rica, 2022-11-12

90% or more of the developers will just take the usual, simplistic approach of having a `ConnectionStrings` section in the configuration `appsettings.json` file and go with that.  Why am I calling it simplistic?  Because there's a better way:  The **DRY** way.

You see, having to specify the entire connection string on your various environment configuration files makes you repeat data such as the database name or your service account name.  Since non-**DRY** advocates pretty much go to hell and deserve no forgiveness, let me show you a **DRY** approach to database connection strings, so you can all save your souls from eternal damnation.

## The `IDbConnectionFactory` Service Definition

In order to also comply with dependency injection, I'll explain this in a dependency injection-compliant manner.  To do this, we will create a special service:  The `IDbConnectionFactory` service will be in charge of creating database connections whenever they are needed.

Here's the definition:

```csharp
using System.Data;

/// <summary>
/// Service capable of creating database connections on demand.
/// </summary>
public interface IDbConnectionFactory
{
    /// <summary>
    /// Creates a database connections and returns it ready for use (in an open state).
    /// </summary>
    /// <returns>The newly created database connection.</returns>
    Task<IDbConnection> CreateAsync();
}
```

Great.  The `CreateAsync()` method takes no parameters, so it is very easy to use (the consumer doesn't have to know about anything database-related), and that means the connection factory must know everything that is database connection-related:  The connection string.

Let's implement this interface for the arguably most popular RDBMS:  **Microsoft SQL Server**.  Before this, though, we must address the **DRY**-ness of the configuration files.

## DRY-ing Connection Strings Up

Knowing the problem makes it easy to work on a solution, and the solution is pretty simple.  We will rearrange the elements in the connection string  in a manner that can be easily targetted for override by the **.Net Configuration** system.  Something like this for `appsettings.json`:

```json
{
  "SqlServer": {
    "Server": "my.sqlserver.enterprise.example.com",
    "Port": 1433,
    "Database": "my-db",
    "IntegratedSecurity": false,
    "Username": "sys_username",
    "Password": "undisclosed",
    "Mars": true,
    "ConnectionTimeout": "00:00:10"
  }
}
```

**NOTE**:  SQL Server's default port number is **1433**.

Now that we have this in our all-environment configuration file, we only need to work out the per-environment overrides.  For example, usually the `Development` environment is for running the project locally in the developer's PC.  Usually this means that our database server is `localhost` and that we will use *Windows Authentication* (integrated security) to connect.  So `appsettings.Development.json` would look like this:

```json
{
  "SqlServer": {
    "Server": "localhost",
    "IntegratedSecurity": true
  }
}
```

See?  We will still have full port, MARS and connection timeout specifications for `Development`, and we did not have to **repeat** said values because they have already been specified in the master configuration file.  **DRY** matters.

Now, in order to read this and inject it to our implementation of the `IDbConnectionFactory` service for **SQL Server**, we will create a simple POCO class that matches what we have written in configuration.

### Configuring .Net Configuration To Meet Our Purposes

This would be the POCO class we would use to read configuration:

```csharp
public class SqlServerConnectionOptions
{
    #region Properties
    public string Server { get; set; }
    public int Port { get; set; }
    public string Database { get; set; }
    public bool IntegratedSecurity { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public bool Mars { get; set; }
    public TimeSpan? ConnectionTimeout { get; set; }
    public int? MaxPoolSize { get; set; }
    #endregion
}
```

I even threw a bonus property there to control the maximum number of SQL connections in the ADO.net connection pool.

Now configure it.

```csharp
IConfigurationSection section = builder.Configuration.GetSection("SqlServer");
builder.Services.Configure<SqlServerConnectionOptions>(section);
```

Just like that the options are ready to be consumed via dependency injection anywhere by requesting a service of type `IOptions<SqlServerConnectionOptions>`.

## The `SqlServerConnectionFactory` Service

This would be our culmination of preparations.  This will be a class that implements the `IDbConnectionFactory`interface.

```csharp
using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;

public class SqlServerConnectionFactory : IDbConnectionFactory
{
    #region Fields
    private Lazy<string> _connString;
    #endregion

    #region Constructors
    public SqlServerConnectionFactory(
        IOptions<SqlServerConnectionOptions> options
    )
    {
        _connString = new Lazy<string>(() =>
        {
            var opts = options.Value;
            // We use a special string builder.
            SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
            sb.DataSource = $"{opts.Server},{opts.Port}";
            sb.InitialCatalog = opts.Database;
            sb.IntegratedSecurity = opts.IntegratedSecurity;
            if (!opts.IntegratedSecurity)
            {
                sb.UserID = opts.Username;
                sb.Password = opts.Password;
            }
            sb.MultipleActiveResultSets = opts.Mars;
            if (opts.ConnectionTimeout.HasValue)
            {
                sb.ConnectTimeout = (int)opts.ConnectionTimeout.Value.TotalSeconds;
            }
            if (opts.MaxPoolSize.HasValue)
            {
                sb.MaxPoolSize = opts.MaxPoolSize.Value;
            }
            // Return the resulting connection string.
            return sb.ToString();
        });
    }

    #endregion

    #region IDbConnectionFactory
    public async Task<IDbConnection> CreateAsync()
    {
        SqlConnection conn = new(_connString.Value);
        await conn.OpenAsync();
        return conn;
    }
    #endregion
}
```

This implementation takes the kind offer from Microsoft and makes use of the `SqlConnectionStringBuilder` class.  With this class, we simply specify individual values, that so happen to coincide with what we did in our configuration section, and finally generate the connection string.

The whole connection string building, while it doesn't take much processing time, is here presented inside a `Lazy<>` object to make sure we don't pay for this processing unless it is really needed.

## Connecting `IDbConnectionFactory` With Your ORM

This depends on the choice of ORM.  With my beloved **Dapper**, all you need is a connection and it works.  This means that I usually program Dapper using the **Repository Pattern**.  So my repositories would require an instance of the `IDbConnectionFactory` and simply append **Dapper** to the newly created connection.

With EF, I personally would still use the Repository Pattern, but the connection factory is actually injected to the db context, and it is the db context that is injected to the repository.

### Dapper Example

Ok, so let's start with full quality:

```csharp
using System.Data;
using Dapper;

public class PerfectRepository<TEntity>
{
    #region Properties
    private IDbConnectionFactory ConnFactory { get; set; }
    #endregion

    #region Constructors
    public PerfectRepository(
        IDbConnectionFactory connFactory
    )
    {
        ConnFactory = connFactory;
    }
    #endregion

    #region Methods
    public async Task<TEntity> GetAsync(int id)
    {
        using IDbConnection conn = await ConnFactory.CreateAsync();
        return await conn.QuerySingleAsync<TEntity>("select * from myTable where Id = @id;", new { id });
    }
    #endregion
}
```

Easy stuff, uncomplicated, to the point, etc. etc.  **Dapper** is, in one word, heavenly.

Now let's see about the incarnation of the devil itself:  *Entity Framework*

### Entity Framework Example

So, we must start with the DB context.  It so happens that the DB context can be initialized with a SQL connection object, so let's take advantage of that.

```csharp
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

public class MyDbContext : DbContext
{
    #region Properties
    private IDbConnectionFactory ConnFactory { get; set; }
    #endregion

    #region Constructors
    public MyDbContext(
        IDbConnectionFactory connFactory
    )
    {
        ConnFactory = connFactory;
    }
    #endregion

    #region Methods
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // For starters, this is not an asynchronous method.  So hack away or make the 
        // IDbConnectionFactory.CreateAsync() method synchronous.  Your choice.
        // I'll hack here.
        var connTask = ConnFactory.CreateAsync();
        connTask.RunSynchronously();
        // We also must cast here.  Right away two things I dislike.
        optionsBuilder.UseSqlServer((SqlConnection)connTask.Result);
    }
    #endregion
}
```

The above is a DB context class that requests via dependency injection an instance of the `IDbConnectionFactory` interface.  This is then used to obtain a connection to the database.

There are two issues I dislike:

1. The `OnConfiguring` method is synchronous.  This means that we must create the connection synchronously.  This is less than ideal for reasons I won't discuss here.
2. The `SqlServerDbContextOptionsExtensions.UseSqlServer()` extension methods accepts an instance of the `DbConnection` class, so it forces a cast. 95% of all casts mean we are doing something wrong.

**IMPORTANT**:  All statistical percentage values in this article came out of a very serious survey I made to the programming population in my head. :-)

It is what it is, this spawn from hell called database context.  Let's move on.

Now we should be able to create a repository based on the DB context:

```csharp
using Microsoft.EntityFrameworkCore;

public class TaintedRepository<TEntity>
    where TEntity : class
{
    #region Properties
    private DbSet<TEntity> DbSet { get; }
    #endregion

    #region Constructors
    public TaintedRepository(
        MyDbContext context
    )
    {
        DbSet = context.Set<TEntity>();
    }
    #endregion

    #region Methods
    // See about Model Features to actually make this work.
    public Task<TEntity> GetAsync(int id)
        => DbSet.Where(x => x.Id.Equals(id)).SingleOrDefaultAsync();
    #endregion
}
```

**About Model Features**:  To actually make the above work, you need **[Model Features](https://webjose.hashnode.dev/use-model-features-to-speed-up-backend-development-in-a-dry-manner)**.

---

## Conclusion

So there it is.  Now you have successfully **DRY**'ed the connection string up and all is good once more in the **DRY** world.  Most importantly, your soul is now saved.

Now you can also override specific values like database name or server or port without copying and pasting the entire connection string around.  This also makes it super simple to provide passwords via environment variables, either manually or via *K8s Opaque Secrets*.  The environment variable name for the password for the example we worked on would be **SQLSERVER__PASSWORD**.

Let me know in the comments section if you have any doubts or concerns.

---

## OPTIONAL:  Making it Better for Entity Framework

I gave **EF** a hard time in this article, I know.  I may have purposedly set it up for failure a bit.  For example, if we do not enforce opening the connection to the database inside the connection factory, then the `CreateAsync()` method can be made synchronous.  I purposely do this because of my Dapper preference.  This way I don't have to open the connection everywhere.  I know, I'm a bad person.

As for the forced cast, I think Microsoft could have made an effort and define the parameter to be of type `IDbConnection`.  Honestly, I don't know why they didn't.  Maybe there is a good reason for it.  Since I am not motivated enough to go through the trouble of finding out, I guess we'll live in the darkness on this one.  What one can do is probably make an `ISqlServerDbConnectionFactory` service that returns a `SqlConnection` object from `CreateAsync()`.  This way no casting is necessary.  It is probably more sane too, if you foresee the need of connecting to other types of database servers, like PostgreSQL or MariaDB.  In those cases, it is best to have a connection factory service definition per database engine.