How to read a connectionString WITH PROVIDER in .NET Core?

Question

I added

.AddJsonFile("Connections.json", optional: true, reloadOnChange: true)

in

 public Startup(IHostingEnvironment env)

Connections.json contains:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=DATABASE;Trusted_Connection=True;MultipleActiveResultSets=true",
    "COR-W81-101": "Data Source=DATASOURCE;Initial Catalog=P61_CAFM_Basic;User Id=USERID;Password=PASSWORD;Persist Security Info=False;MultipleActiveResultSets=False;Packet Size=4096;",
    "COR-W81-100": "Data Source=DATASOURCE;Initial Catalog=Post_PS;User Id=USERID;Password=PASSWORD;Persist Security Info=False;MultipleActiveResultSets=False;Packet Size=4096;",
    "MSEDGEWIN10": "Data Source=DATASOURCE; Initial Catalog=COR_Basic; Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Packet Size=4096;Application Name=\"COR_Basic\"",

    "server": "Data Source=DATASOURCE; Initial Catalog=COR_Basic; Persist Security Info=False;User Id=USERID;Password=PASSWORD;MultipleActiveResultSets=False;Packet Size=4096;Application Name=\"COR_Basic\""
  },


  "conStrings": [
      {
        "name": "COR-W81-101",     
        "connectionString": "Data Source=DATASOURCE; Initial Catalog=COR_Basic; Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Packet Size=4096;Application Name=\"COR_Basic\"",
        "providerName": "System.Data.SqlClient"
      }

    },

    {
      "name": "server",
      "connectionString": "Data Source=DATASOURCE; Initial Catalog=COR_Basic; Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Packet Size=4096;Application Name=\"COR_Basic\"",
      "providerName": "System.Data.SqlClient"
    }
  ],



  "conStringDictionary": {
    "COR-W81-101": {
      "connectionString": "Data Source=DATASOURCE; Initial Catalog=COR_Basic; Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Packet Size=4096;Application Name=\"COR_Basic\"",
      "providerName": "System.Data.SqlClient"
    },

    "server": {
      "connectionString": "Data Source=DATASOURCE; Initial Catalog=COR_Basic; Persist Security Info=False;Integrated Security=true;MultipleActiveResultSets=False;Packet Size=4096;Application Name=\"COR_Basic\"",
      "providerName": "System.Data.SqlClient"
    }

  }
}

Now I wanted to read the connectionStrings:

public class ConnectionString
{
    public string name { get; set; }
    public string connectionString { get; set; }
    public string providerName { get; set; }
}

like this:

//Microsoft.Extensions.DependencyInjection.OptionsConfigurationServiceCollectionExtensions.Configure<ConnectionString[]>(services, Configuration.GetSection("conStrings"));

// https://stackoverflow.com/questions/31929482/retrieve-sections-from-config-json-in-asp-net-5
//var objectSections = Configuration.GetSection("conStringDictionary").GetChildren();
//foreach (var x in objectSections)
//{
//    System.Console.WriteLine(x.Key);
//    var cs = new ConnectionString();
//    ConfigurationBinder.Bind(x, cs);
//    System.Console.WriteLine(cs);
//}

// http://andrewlock.net/how-to-use-the-ioptions-pattern-for-configuration-in-asp-net-core-rc2/
        Microsoft.Extensions.DependencyInjection.OptionsConfigurationServiceCollectionExtensions.Configure<Dictionary<string, ConnectionString>>(services, Configuration.GetSection("conStrings"));

But I can't get it to read the array or the dictionary. I need the providerName for each connectionString, and I want to have it in the same entry as the connection string, but not as a joined string.

Answer 1

You were basically there, all you have to do is make a few strongly typed classes to match the old ConnectionStringSettings and utilize some collection serialization logic.

Here's how I would suggest to format them in json. Rather similar to how you would specify a connection string the old XML app/web.config way. The name of the connection string being the key.

{
  "ConnectionStrings": {
    "Test1": {
      "ConnectionString": "server=localhost;database=db;username=user;password=pass;",
      "ProviderName": "MySql.Data.MySqlClient"
    },
    "Test2": {
      "ConnectionString": "server=localhost;database=db2;username=user2;password=pass2;",
      "ProviderName": "MySql.Data.MySqlClient"
    }
  }
}

Now for the classes to bind to. First is the simple ConnectionStringSettings class itself, implements your basic equality/hashing methods (will be necessary as we intend to stick this in a Dictionary).

public class ConnectionStringSettings
{
    public String Name { get; set; }
    public String ConnectionString { get; set; }
    public String ProviderName { get; set; }

    public ConnectionStringSettings()
    {
    }

    public ConnectionStringSettings(String name, String connectionString)
        : this(name, connectionString, null)
    {
    }

    public ConnectionStringSettings(String name, String connectionString, String providerName)
    {
        this.Name = name;
        this.ConnectionString = connectionString;
        this.ProviderName = providerName;
    }

    protected bool Equals(ConnectionStringSettings other)
    {
        return String.Equals(Name, other.Name) && String.Equals(ConnectionString, other.ConnectionString) && String.Equals(ProviderName, other.ProviderName);
    }

    public override bool Equals(Object obj)
    {
        if (ReferenceEquals(null, obj)) return false;
        if (ReferenceEquals(this, obj)) return true;
        if (obj.GetType() != this.GetType()) return false;
        return Equals((ConnectionStringSettings) obj);
    }

    public override int GetHashCode()
    {
        unchecked
        {
            int hashCode = (Name != null ? Name.GetHashCode() : 0);
            hashCode = (hashCode * 397) ^ (ConnectionString != null ? ConnectionString.GetHashCode() : 0);
            hashCode = (hashCode * 397) ^ (ProviderName != null ? ProviderName.GetHashCode() : 0);
            return hashCode;
        }
    }

    public static bool operator ==(ConnectionStringSettings left, ConnectionStringSettings right)
    {
        return Equals(left, right);
    }

    public static bool operator !=(ConnectionStringSettings left, ConnectionStringSettings right)
    {
        return !Equals(left, right);
    }
}

Next is the collection of ConnectionStringSettings. This is only necessary because the Name of the connection string is the key in the JSON notation. In order to keep that name consistently attached we need to override Dictionary's Add method (but you can't do that because its not virtual). So all we are REALLY doing is just wrapping a Dictionary internally with that extra bit in our own Add implementation. Again this looks like a lot of code, but you'll see it's very monotonous boring stuff.

public class ConnectionStringSettingsCollection : IDictionary<String, ConnectionStringSettings>
{
    private readonly Dictionary<String, ConnectionStringSettings> m_ConnectionStrings;

    public ConnectionStringSettingsCollection()
    {
        m_ConnectionStrings = new Dictionary<String, ConnectionStringSettings>();
    }

    public ConnectionStringSettingsCollection(int capacity)
    {
        m_ConnectionStrings = new Dictionary<String, ConnectionStringSettings>(capacity);
    }

    #region IEnumerable methods
    IEnumerator IEnumerable.GetEnumerator()
    {
        return ((IEnumerable)m_ConnectionStrings).GetEnumerator();
    }
    #endregion

    #region IEnumerable<> methods
    IEnumerator<KeyValuePair<String, ConnectionStringSettings>> IEnumerable<KeyValuePair<String, ConnectionStringSettings>>.GetEnumerator()
    {
        return ((IEnumerable<KeyValuePair<String, ConnectionStringSettings>>)m_ConnectionStrings).GetEnumerator();
    }
    #endregion

    #region ICollection<> methods
    void ICollection<KeyValuePair<String, ConnectionStringSettings>>.Add(KeyValuePair<String, ConnectionStringSettings> item)
    {
        ((ICollection<KeyValuePair<String, ConnectionStringSettings>>)m_ConnectionStrings).Add(item);
    }

    void ICollection<KeyValuePair<String, ConnectionStringSettings>>.Clear()
    {
        ((ICollection<KeyValuePair<String, ConnectionStringSettings>>)m_ConnectionStrings).Clear();
    }

    Boolean ICollection<KeyValuePair<String, ConnectionStringSettings>>.Contains(KeyValuePair<String, ConnectionStringSettings> item)
    {
        return ((ICollection<KeyValuePair<String, ConnectionStringSettings>>)m_ConnectionStrings).Contains(item);
    }

    void ICollection<KeyValuePair<String, ConnectionStringSettings>>.CopyTo(KeyValuePair<String, ConnectionStringSettings>[] array, Int32 arrayIndex)
    {
        ((ICollection<KeyValuePair<String, ConnectionStringSettings>>)m_ConnectionStrings).CopyTo(array, arrayIndex);
    }

    Boolean ICollection<KeyValuePair<String, ConnectionStringSettings>>.Remove(KeyValuePair<String, ConnectionStringSettings> item)
    {
        return ((ICollection<KeyValuePair<String, ConnectionStringSettings>>)m_ConnectionStrings).Remove(item);
    }

    public Int32 Count => ((ICollection<KeyValuePair<String, ConnectionStringSettings>>)m_ConnectionStrings).Count;
    public Boolean IsReadOnly => ((ICollection<KeyValuePair<String, ConnectionStringSettings>>)m_ConnectionStrings).IsReadOnly;
    #endregion

    #region IDictionary<> methods
    public void Add(String key, ConnectionStringSettings value)
    {
        // NOTE only slight modification, we add back in the Name of connectionString here (since it is the key)
        value.Name = key;
        m_ConnectionStrings.Add(key, value);
    }

    public Boolean ContainsKey(String key)
    {
        return m_ConnectionStrings.ContainsKey(key);
    }

    public Boolean Remove(String key)
    {
        return m_ConnectionStrings.Remove(key);
    }

    public Boolean TryGetValue(String key, out ConnectionStringSettings value)
    {
        return m_ConnectionStrings.TryGetValue(key, out value);
    }

    public ConnectionStringSettings this[String key]
    {
        get => m_ConnectionStrings[key];
        set => Add(key, value);
    }

    public ICollection<String> Keys => m_ConnectionStrings.Keys;
    public ICollection<ConnectionStringSettings> Values => m_ConnectionStrings.Values;
    #endregion
}

A few simple extension methods to make things simpler.

public static class ConnectionStringSettingsExtensions
{
    public static ConnectionStringSettingsCollection ConnectionStrings(this IConfigurationRoot configuration, String section = "ConnectionStrings")
    {
        var connectionStringCollection = configuration.GetSection(section).Get<ConnectionStringSettingsCollection>();
        if (connectionStringCollection == null)
        {
            return new ConnectionStringSettingsCollection();
        }

        return connectionStringCollection;
    }

    public static ConnectionStringSettings ConnectionString(this IConfigurationRoot configuration, String name, String section = "ConnectionStrings")
    {
        ConnectionStringSettings connectionStringSettings;

        var connectionStringCollection = configuration.GetSection(section).Get<ConnectionStringSettingsCollection>();
        if (connectionStringCollection == null ||
            !connectionStringCollection.TryGetValue(name, out connectionStringSettings))
        {
            return null;
        }

        return connectionStringSettings;
    }
}

Finally the usage.

var configuration = new ConfigurationBuilder()
    .AddJsonFile("config.json")
    .Build();

var connectionStrings = configuration.ConnectionStrings();

foreach (var connectionString in connectionStrings.Values)
{
    Console.WriteLine(connectionString.Name);
    Console.WriteLine(connectionString.ConnectionString);
    Console.WriteLine(connectionString.ProviderName);
}

var specificConnStr1 = connectionStrings["Test1"];
Console.WriteLine(specificConnStr1.Name);
Console.WriteLine(specificConnStr1.ConnectionString);
Console.WriteLine(specificConnStr1.ProviderName);

var specificConnStr2 = configuration.ConnectionString("Test2");
Console.WriteLine(specificConnStr2.Name);
Console.WriteLine(specificConnStr2.ConnectionString);
Console.WriteLine(specificConnStr2.ProviderName);

Answer 2

An old question, but I was looking at this issue today and thought I'd share ...

A Simple Alternative to Including ProviderName

Here is a simple alternative that avoids custom extensions and altering the default ConnectionStrings configuration structure. It's based on how Microsoft includes a ProviderName for apps on Azure.

The solution is to add a context related key in the ConnectionStrings section that specifies the ProviderName.

AppSettings.json with SQLite provider:

{  
  "ConnectionStrings": {
    "MyContext": "Data Source=c:\\MySqlite.db;Version=3;",
    "MyContext_ProviderName": "System.Data.SQLite",
  }
}

And in the C# code read the values with the GetConnectionString() method:

var connectionString = Configuration.GetConnectionString("MyContext");
var providerName = Configuration.GetConnectionString("MyContext_ProviderName") ?? "";

if (Regex.IsMatch(providerName, "SQLite", RegexOptions.IgnoreCase)) 
{
    builder.UseSqlite(connectionString);
}
else if (Regex.IsMatch(providerName, "Oracle", RegexOptions.IgnoreCase)) 
{    
    builder.AddOracle(connectionString);
}
else if (... 

Bonus - Connection String Prefixes

Microsoft includes predefined prefixes for SQLClient and MySQL which will automatically include the provider name in the above format. However, these prefixes only work when added as environment variables, i.e., not in appsettings.json. For example, defining the connection string in launchSettings.json with the MYSQLCONNSTR_ prefix would populate both the connection string and provider name. For details, see Configuration in ASP.NET Core and scroll down to Connection string prefixes

launchSettings.json

{
  "profiles": {
    "Development": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "launchUrl": "",
      "environmentVariables": {
       "ASPNETCORE_ENVIRONMENT": "Development",

       // The prefix
       "MYSQLCONNSTR_MyContext": "Server=myServerAddress;Database=Green;Uid=myUsername;Pwd=myPassword;"

      }
    }
}

Answer 3

First off, Nicholi's answer inspired me! Thanks Nicholi.

Second, I have a "List" solution rather than a IDictionary solution. It's not as smooth as the IDictionary solution.

This can also be dubbed "how to create a collection list for dot net core configuration"

Here we go:

first a shameless theft!

public class ConnectionStringEntry
{
    public String Name { get; set; }
    public String ConnectionString { get; set; }
    public String ProviderName { get; set; }

    public ConnectionStringEntry()
    {
    }

    public ConnectionStringEntry(String name, String connectionString)
        : this(name, connectionString, null)
    {
    }

    public ConnectionStringEntry(String name, String connectionString, String providerName)
    {
        this.Name = name;
        this.ConnectionString = connectionString;
        this.ProviderName = providerName;
    }
}

second, a "wrapper". I wanted to track a DefaultConnectionStringName...along side my List (collection) of Entries.

public class ConnectionStringWrapper
{
    public string DefaultConnectionStringName { get; set; } = "";
    public List<ConnectionStringEntry> ConnectionStringEntries { get; set; } = new List<ConnectionStringEntry>();
    //public Dictionary<string, ConnectionStringEntry> ConnectionStringEntries { get; set; } = new Dictionary<string, ConnectionStringEntry>();

    public ConnectionStringEntry GetDefaultConnectionStringEntry()
    {
        ConnectionStringEntry returnItem = this.GetConnectionStringEntry(this.DefaultConnectionStringName);
        return returnItem;
    }

    public ConnectionStringEntry GetConnectionStringEntry(string name)
    {
        ConnectionStringEntry returnItem = null;
        if (null != this.ConnectionStringEntries && this.ConnectionStringEntries.Any())
        {
            returnItem = this.ConnectionStringEntries.FirstOrDefault(ce => ce.Name.Equals(name, StringComparison.OrdinalIgnoreCase));
        }

        if (null == returnItem)
        {
            throw new ArgumentOutOfRangeException(string.Format("No default ConnectionStringEntry found. (ConnectionStringEntries.Names='{0}', Search.Name='{1}')", this.ConnectionStringEntries == null ? string.Empty : string.Join(",", this.ConnectionStringEntries.Select(ce => ce.Name)), name));
        }

        return returnItem;
    }
}

Now, my reading the json and mapping to a concrete settings object code:

            IConfiguration config = new ConfigurationBuilder()
                    .SetBasePath(System.IO.Directory.GetCurrentDirectory())
                    .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
                    .Build();


            ConnectionStringWrapper settings = new ConnectionStringWrapper();
            config.Bind("ConnectionStringWrapperSettings", settings);
            Console.WriteLine("{0}, {1}", settings.DefaultConnectionStringName, settings.ConnectionStringEntries.Count);
            ConnectionStringEntry cse = settings.GetDefaultConnectionStringEntry();

My nuget packages:

\.nuget\packages\microsoft.extensions.configuration\2.1.1
\.nuget\packages\microsoft.extensions.configuration.binder\2.1.1
\.nuget\packages\microsoft.extensions.configuration.json\2.1.1

BONUS MATERIAL BELOW:

One of the things I am (trying) to so support a code base that can be deployed as DotNet 4.x ("classic" ?? as the term now??) and dotnet core.

To that end, I've written the above to provide an abstraction from the way DotNet(Classic) handles connection strings (xml, our old long time friend) and now the new cool kid on the block : DotNetCore with json.

To that end, I've written an interface:

public interface IConnectionStringWrapperRetriever
{
    ConnectionStringWrapper RetrieveConnectionStringWrapper();
}

and I have an implementation for dotnetcore:

public class ConnectionStringWrapperDotNetCoreRetriever : IConnectionStringWrapperRetriever
{
    public const string ConnectionStringWrapperSettingsJsonElementName = "ConnectionStringWrapperSettings";

    private readonly IConfiguration config;

    public ConnectionStringWrapperDotNetCoreRetriever(IConfiguration cnfg)
    {
        this.config = cnfg;
    }

    public ConnectionStringWrapper RetrieveConnectionStringWrapper()
    {
        ConnectionStringWrapper settings = new ConnectionStringWrapper();
        this.config.Bind(ConnectionStringWrapperSettingsJsonElementName, settings);
        return settings;
    }
}

Oh yeah, the all important JSON setup:

{
  "ConnectionStringWrapperSettings": {
    "DefaultConnectionStringName": "abc",
    "ConnectionStringEntries": [
      {
        "Name": "abc",
        "ConnectionString": "Server=myserver;Database=mydatabase;Trusted_Connection=True;MultipleActiveResultSets=true",
        "ProviderName": "SomeProvider"
      },
      {
        "Name": "def",
        "ConnectionString": "server=localhost;database=db2;username=user2;password=pass2;",
        "ProviderName": "SomeProvider"
      }
    ]
  }
}

.............

For DotNet(classic), all you need to do is implement a second concrete for IConnectionStringWrapperRetriever, and do the magic.

Remember the below xml? (ha ha, its not that old yet!)

<?xml version="1.0" encoding="utf-8"?>  
<configuration>  
  <connectionStrings>  
    <add name="ConnStr1" connectionString="LocalSqlServer: data source=127.0.0.1;Integrated Security=SSPI;Initial Catalog=aspnetdb"  
      providerName="System.Data.SqlClient" />  
  </connectionStrings>  
</configuration>  

(from https://learn.microsoft.com/en-us/dotnet/api/system.configuration.connectionstringsettingscollection?view=netframework-4.7.2)

Remember this stuff from EnterpriseLibrary?

<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null"/>
</configSections>

<dataConfiguration defaultDatabase="ConnStr1"/>

I'll leave the DotNet(Classic) implementation to the reader.

But now I inject IConnectionStringWrapperRetriever into my DataLayer classes.
I'm using Dapper, so I can fish a connection string using IConnectionStringWrapperRetriever.

If my project is "house" DotNet(Classic), I inject one version of IConnectionStringWrapperRetriever (not seen here, left to the reader). If my project is "housed" in DotNetCore I inject a second (shown above) version of IConnectionStringWrapperRetriever.

Outside the scope of this post, but by "housed", I mean I have 2 csproj's sitting side by side.

MyApp.DataLayer.classic.csproj and MyApp.DataLayer.csproj

I find it easier to leave the default csproj to house the DotNetCore stuff. And I use the "classic.csproj" file to house the DotNet(classic). My assembly name and default namespace remain "MyApp.Datalayer"......the .classic is ONLY for the csrproj filename to distinquish.

I create two solution sln files too. MySolution.classic.sln and MySolution.sln.

It seems to be working.....with this ConnectionString abstraction I wrote above.

The ONLY conditional I have is on the (classic) AssemblyInfo.cs files.

#if(!NETCOREAPP2_1)

using System.Reflection;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;

/* all the other stuff removed here */

// Version information for an assembly consists of the following four values:
//
//      Major Version
//      Minor Version
//      Build Number
//      Revision
//
// You can specify all the values or you can default the Build and Revision Numbers
// by using the '*' as shown below:
// [assembly: AssemblyVersion("1.0.*")]
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]

#endif

APPEND:

Ok, here is the DotNet(Classic) version:

public class ConnectionStringWrapperDotNetClassicRetriever : IConnectionStringWrapperRetriever
{
    public ConnectionStringWrapper RetrieveConnectionStringWrapper()
    {
        ConnectionStringWrapper returnItem = new ConnectionStringWrapper();

        foreach(ConnectionStringSettings css in System.Configuration.ConfigurationManager.ConnectionStrings)
        {
            ConnectionStringEntry cse = new ConnectionStringEntry(css.Name, css.ConnectionString, css.ProviderName);
            returnItem.ConnectionStringEntries.Add(cse);
        }

        if(returnItem.ConnectionStringEntries.Count == 1)
        {
            /* if there is only one, set the default name to that one */
            returnItem.DefaultConnectionStringName = returnItem.ConnectionStringEntries.First().Name;
        }
        else
        {
            /*
            <packages>
              <package id="EnterpriseLibrary.Common" version="6.0.1304.0" targetFramework="net45" />
              <package id="EnterpriseLibrary.Data" version="6.0.1304.0" targetFramework="net45" />
            </packages>                 
             */

            /* using Microsoft.Practices.EnterpriseLibrary.Data.Configuration; */
            /* You can write you own way to handle a default database, or piggyback off of EnterpriseLibrary.  You don't necessarily have to use EnterpriseLibrary.Data, you are simply piggybacking on their xml/configuration setup */
            DatabaseSettings dbSettings = (DatabaseSettings)ConfigurationManager.GetSection("dataConfiguration");
            returnItem.DefaultConnectionStringName = dbSettings.DefaultDatabase;
        }

        return returnItem;
    }
}

And the app.config xml:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

  <configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data"/>
  </configSections>

  <connectionStrings>
    <clear/>
    <add name="MyFirstConnectionStringName" connectionString="Server=.\MyServerOne;Database=OneDB;Trusted_Connection=True;MultipleActiveResultSets=true"
      providerName="System.Data.SqlClient" />

    <add name="MySecondConnectionStringName" connectionString="Server=.\MyServerTwo;Database=TwoDB;Trusted_Connection=True;MultipleActiveResultSets=true"
      providerName="System.Data.SqlClient" />
  </connectionStrings>


  <dataConfiguration defaultDatabase="MyFirstConnectionStringName" />

  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
</configuration>

Key words:

DotNet DotNet .Net Core Classic Json Configuration ICollection scalar and collection support both dotnet and dotnetcore

Answer 4

Something like:

  var configurationRoot = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json", false)
                .Build();

 var conString = configurationRoot["ConnectionStrings:MyConnection"]);