The Builder design pattern is a simple yet powerful creational pattern. In this post, I will demonstrate how to use it to encapsulate the complexity needed to create an object.

The problem

Our application uses dynamic connection strings to access various databases. The following list shows some of the last connection strings generated by the app :

• Data Source=myServerAddress;Initial Catalog=myDatabase;MultipleActiveResultSets=True;Trusted_Connection=True;
• Data Source=myServerAddress;Initial Catalog=myDatabase;Integrated Security=SSPI;Password=myPassword;User ID=myDomain\myUsername;
• Data Source=myServerAddress;Initial Catalog=myDatabase;Packet Size=4096;Password=myPassword;Trusted_Connection=False;User ID=myUsername;

What can we see here ? The connection strings contains some parameters (ordered by name), but not all of these strings contains the same set of parameters. The following table provides a simple view over the differences between the 3 connection strings.

Somewhere in the source code, a function which purpose is connection string creation has been written. Here is the implementation of this function.

public static string GetConnectionString(string dataSource,
                                            string initialCatalog,
                                            bool? trustedConnection = null,
                                            bool? multipleActiveResultSets = null,
                                            string integratedSecurity = null,
                                            string userId = null,
                                            string password = null,
                                            int? packetSize = null)
{
    if (dataSource == null)
        throw new ArgumentNullException(nameof(dataSource));
    if (initialCatalog == null)
        throw new ArgumentNullException(nameof(initialCatalog));

    if (trustedConnection.HasValue && trustedConnection.Value && (userId != null || password != null))
        throw new ArgumentException($"{nameof(userId)} and {nameof(password)} cannot be set when {nameof(trustedConnection)} is set to True.");


    string result = $"Data Source={dataSource};Initial Catalog={initialCatalog};";

    if (integratedSecurity != null)
        result += $"Integrated Security={integratedSecurity};";
    if (multipleActiveResultSets.HasValue)
        result += $"MultipleActiveResultSets={(multipleActiveResultSets.Value ? "True" : "False")};";
    if (packetSize.HasValue)
        result += $"Packet Size={packetSize.Value};";
    if (password != null)
        result += $"Password={password};";
    if (trustedConnection.HasValue)
        result += $"Trusted_Connection={(trustedConnection.Value ? "True" : "False")};";
    if (userId != null)
        result += $"User ID={userId};";

    return result;
}

The major issue in this function is in its signature : at that point, the parameter list is made of 8 values, 6 of them being optional. This leads to a poor readability, and adds complexity at the call sites. Furthermore, the function has too much responsibilities : it validates data, make choices regarding the process, and builds the string accordingly.

From a higher point a view, the resulting connection string is tailored for SQL Server. What if we wanted to extend the application to support MySQL or Oracle ?

A better solution with the Builder pattern

First, let’s try to define this pattern appropriately : the Builder pattern is used to control the creation of objects needing complex initialization and configuration steps. In other words, it orchestrates the initialization and the assembly of the object constituents, and hides this process under simple configurations steps, such as properties setting or method calls.

What does it means for our example ? The Builder we’re about to write will expose a few properties (mapped to the expected result keywords) and a Build method which will produce the connection string from the property values.

Here is a naive implementation of our Builder type :

public class SimpleConnectionStringBuilder
{
    public string DataSource { get; set; }
    public string InitialCatalog { get; set; }
    public bool? TrustedConnection { get; set; }
    public bool? MultipleActiveResultSets { get; set; }
    public string IntegratedSecurity { get; set; }
    public string UserId { get; set; }
    public string Password { get; set; }
    public int? PacketSize { get; set; }

    private void CheckDataValidity()
    {
        if (DataSource == null)
            throw new ArgumentNullException(nameof(DataSource));
        if (InitialCatalog == null)
            throw new ArgumentNullException(nameof(InitialCatalog));

        if (TrustedConnection.HasValue && TrustedConnection.Value && (UserId != null || Password != null))
            throw new ArgumentException($"{nameof(UserId)} and {nameof(Password)} cannot be set when {nameof(TrustedConnection)} is set to True.");
    }

    public string Build()
    {
        CheckDataValidity();

        string result = $"Data Source={DataSource};Initial Catalog={InitialCatalog};";

        if (IntegratedSecurity != null)
            result += $"Integrated Security={IntegratedSecurity};";
        if (MultipleActiveResultSets.HasValue)
            result += $"MultipleActiveResultSets={(MultipleActiveResultSets.Value ? "True" : "False")};";
        if (PacketSize.HasValue)
            result += $"Packet Size={PacketSize.Value};";
        if (Password != null)
            result += $"Password={Password};";
        if (TrustedConnection.HasValue)
            result += $"Trusted_Connection={(TrustedConnection.Value ? "True" : "False")};";
        if (UserId != null)
            result += $"User ID={UserId};";

        return result;
    }
}

The complexity is now hidden behind a few properties and a parameterless method. Much better ! Now, generating a connection string is as simple as creating a SimpleConnectionBuilder object, setting some property values, and call its Build method.

var builder = new SimpleConnectionStringBuilder();
builder.DataSource = "myServerAddress";
builder.InitialCatalog = "myDatabase";
builder.MultipleActiveResultSets = false;
builder.TrustedConnection = true;
builder.PacketSize = 4096;

var result = builder.Build();
// result equals "Data Source=myServerAddress;Initial Catalog=myDatabase;MultipleActiveResultSets=False;Packet Size=4096;Trusted_Connection=True;"

Even though things went well until now, there is still an issue : our code cannot easily handle connection strings for multiple database systems. For instance, in SQL Server connection strings, we use the Data Source parameter to specify the database server address, whereas MySQL connection strings use the Server parameter. We cannot decently implement everything in the same type because of the Separation of concerns principle (building SQL Server connection strings is not the same as building MySQL connection strings).

To address this issue, we can build a class hierarchy with an abstract ConnectionStringBuilder type as the parent, and database-specific subclasses inheriting from it. To maximize the code sharing, the parent should be responsible for the building process. Consequently, it have to be able to operate on a dynamic list of parameters (see the _connectionStringKeywords and _connectionStringValues collections in the implementation hereunder). Then the subclasses almost only have to provide the parameter names for the common and specific properties.

public abstract class ConnectionStringBuilder
{
    private readonly Dictionary<string, string> _connectionStringKeywords = new Dictionary<string, string>();
    private readonly Dictionary<string, object> _connectionStringValues = new Dictionary<string, object>();

    public virtual string Server { get => GetValue<string>(); set => SetValue(value); }
    public string DatabaseName { get => GetValue<string>(); set => SetValue(value); }
    public string Login { get => GetValue<string>(); set => SetValue(value); }
    public string Password { get => GetValue<string>(); set => SetValue(value); }

    protected T GetValue<T>([CallerMemberName] string key = null)
        => (T)_connectionStringValues.GetValueOrDefault(key);

    protected void SetValue(object value, [CallerMemberName] string key = null)
        => _connectionStringValues[key] = value;

    public ConnectionStringBuilder() { }

    protected void SetParameterNameForProperty(string propertyName, string parameterName)
        => _connectionStringKeywords[propertyName] = parameterName;

    protected virtual void CheckDataValidity()
    {
        if (Server == null)
            throw new ArgumentNullException(nameof(Server));
        if (DatabaseName == null)
            throw new ArgumentNullException(nameof(DatabaseName));
    }

    public string Build()
    {
        CheckDataValidity();

        var keyValues = _connectionStringKeywords.Join(_connectionStringValues,
                                                        cskKvp => cskKvp.Key,
                                                        csvKvp => csvKvp.Key,
                                                        (csk, csv) => new { Keyword = csk.Value, Value = csv.Value });

        string result = string.Empty;
        foreach (var keyValue in keyValues.OrderBy(kv => kv.Keyword))
        {
            if (keyValue.Value == null)
                continue;

            result += $"{keyValue.Keyword}={keyValue.Value};";
        }

        return result;
    }
}

public class SqlServerConnectionStringBuilder : ConnectionStringBuilder
{
    public bool? TrustedConnection { get => GetValue<bool?>(); set => SetValue(value); }
    public bool? MultipleActiveResultSets { get => GetValue<bool?>(); set => SetValue(value); }
    public string IntegratedSecurity { get => GetValue<string>(); set => SetValue(value); }
    public int? PacketSize { get => GetValue<int?>(); set => SetValue(value); }

    public SqlServerConnectionStringBuilder()
    {
        SetParameterNameForProperty(nameof(Server), "Data Source");
        SetParameterNameForProperty(nameof(DatabaseName), "Initial Catalog");
        SetParameterNameForProperty(nameof(Login), "User ID");
        SetParameterNameForProperty(nameof(Password), "Password");
        SetParameterNameForProperty(nameof(TrustedConnection), "Trusted_Connection");
        SetParameterNameForProperty(nameof(MultipleActiveResultSets), "MultipleActiveResultSets");
        SetParameterNameForProperty(nameof(IntegratedSecurity), "Integrated Security");
        SetParameterNameForProperty(nameof(PacketSize), "Packet Size");
    }

    protected override void CheckDataValidity()
    {
        base.CheckDataValidity();

        if (TrustedConnection.HasValue && TrustedConnection.Value && (Login != null || Password != null))
            throw new ArgumentException($"{nameof(Login)} and {nameof(Password)} cannot be set when {nameof(TrustedConnection)} is set to True.");
    }
}

public class MySqlConnectionStringBuilder : ConnectionStringBuilder
{
    public bool? UseCompression { get => GetValue<bool?>(); set => SetValue(value); }
    public bool? UseUsageAdvisor { get => GetValue<bool?>(); set => SetValue(value); }
    public bool? UsePerformanceMonitor { get => GetValue<bool?>(); set => SetValue(value); }

    public MySqlConnectionStringBuilder()
    {
        SetParameterNameForProperty(nameof(Server), "Server");
        SetParameterNameForProperty(nameof(DatabaseName), "Database");
        SetParameterNameForProperty(nameof(Login), "Uid");
        SetParameterNameForProperty(nameof(Password), "Pwd");
        SetParameterNameForProperty(nameof(UseCompression), "UseCompression");
        SetParameterNameForProperty(nameof(UseUsageAdvisor), "UseUsageAdvisor");
        SetParameterNameForProperty(nameof(UsePerformanceMonitor), "UsePerformanceMonitor");
    }
}

Conclusion

The Builder pattern is a powerful way to handle complex object creation algorithms. Not only does it allow configurable object construction, but it helps handling similar scenarios with inheritance. Of course, our example has come from 44 lines to 96 lines, but this change had a lot of benefits :

• we improved readability by removing a lot of parameters at call sites
• we improved maintainability by moving (and hiding) the complexity in an independent type
• we added support for MySQL connection strings
• we added an extensibility point : adding support for a new provider is as easy as creating a class inheriting from ConnectionStringBuilder, and adding a new connection string parameter means creating a property and adding a call to SetParameterNameForProperty.