Reading Connection Strings in Web.Config and App.Config and Enterprise Library DAAB Settings
by David Hayden ( Sarasota Web Design ), Filed: ADO.NET 2.0, Enterprise Library
Someone asked a question in the forums about how to list all the connection strings located in the app.config or web.config so he could populate a control that would allow the user to choose the active connection at run-time. The person also was using the Enterprise Library Data Access Application Block and wanted to know how to find the name of the default connection string.
Both good questions. Let's start with the easy one first.
Reading Default Connection String Name in DAAB Configuration Settings
It is very easy to read the default connection string name used by the Enterprise Library Data Access Application Block. There are many ways to do it, but this is the simplest in my opinion if you use the application's web.config or app.config file to store your DAAB Settings:
DatabaseSettings settings =
DatabaseSettings.GetDatabaseSettings
(new SystemConfigurationSource());
string defaultConnectionStringName =
settings.DefaultDatabase;
There is a DatabaseSettings Class that you can call GetDatabaseSettings on to get the name of the default connection string. Pass it the IConfigurationSource where the settings are located. Use SystemConfigurationSource for the application's app.config or web.config.
Reading All Connection Strings in App.Config or Web.Config
Actually, reading all the connection strings in the app.config or web.config is pretty easy now that we have the wonderful System.Configuration Namespace and its rich functionality. The code is pretty straight forward. We can read all the connection strings as well as each name, provider, etc:
ConnectionStringSettingsCollection connectionStrings =
ConfigurationManager.ConnectionStrings;
foreach (ConnectionStringSettings connection in connectionStrings)
{
string connectionStringName = connection.Name;
string connectionString = connection.ConnectionString;
string providerName = connection.ProviderName;
Debug.Print(connectionStringName);
}
Rather than just printing the connection string name to the output window, you could of course populate a control and allow the user to select one.
Based on the name of the connection string the user selects, you can use the Enterprise Library DAAB to access the database:
Database database =
DatabaseFactory.CreateDatabase(connectionStringName);
If you don't want to use the DAAB, no problem. Use the DbProviderFactory to give you similar database agnostic code ( albeit not as easy to use as the DAAB ) :
DbProviderFactory factory =
DbProviderFactories.GetFactory(providerName);
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = connectionString;
// ...
}
Fun stuff to play with.
Conclusion
Hope this helps :)
Source: David Hayden ( Sarasota Web Design )
Filed: ADO.NET 2.0, Enterprise Library