How to read a connectionString WITH PROVIDER in .NET Core?
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
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);