I have a piece of code (.NET Framework 4.5.2, Enterprise Library 5.0.505.0) where I need to connect to a SQL Server database. However, the DB name may keep changing depending on user's requirement. So, I have the following piece of code to write the connection string dynamically to the app.config file.
public void CreateNewConnectionStringInConfig(string initialCatalog)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = ConfigurationManager.AppSettings["Data Source"];
builder.PersistSecurityInfo = true;
builder.InitialCatalog = initialCatalog;
builder.UserID = ConfigurationManager.AppSettings["User ID"];
builder.Password = ConfigurationManager.AppSettings["Password"];
Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
ConnectionStringSettings csSettings = new ConnectionStringSettings("UserSelectedConnectionString", builder.ConnectionString, "System.Data.SqlClient");
ConnectionStringsSection csSection = config.ConnectionStrings;
csSection.ConnectionStrings.Add(csSettings);
config.Save(ConfigurationSaveMode.Modified);
ConfigurationManager.RefreshSection("connectionStrings");
}
I checked and the connection string is getting created fine in the vshost.exe.Config file while debugging. However, when I am trying to create the Database object, I am getting an error. The code used to create the DB object is as shown below.
public class MyDac
{
private readonly Database _db;
public MyDac()
{
DatabaseProviderFactory factory = new DatabaseProviderFactory();
_db = factory.Create("UserSelectedConnectionString");
}
}
I am getting the following error while trying to create the _db object.
Activation error occured while trying to get instance of type Database, key "UserSelectedConnectionString"<br />
<br />
Microsoft.Practices.ServiceLocation.ActivationException: Activation error occured while trying to get instance of type Database, key "UserSelectedConnectionString" ---> Microsoft.Practices.Unity.ResolutionFailedException: Resolution of the dependency failed, type = "Microsoft.Practices.EnterpriseLibrary.Data.Database", name = "UserSelectedConnectionString".<br />
Exception occurred while: while resolving.<br />
Exception is: InvalidOperationException - The type Database does not have an accessible constructor.
Any help will be much appreciated. Thanks!
What I have tried:
1) Upgrading the Enterprise Library version to 6.0.0.0 resolves the issue but that is not an option for me. I have to keep it to version 5.0.505.0.
2) When I hard code the connection string in the App.config file from before hand (rather than writing it during run time), the app works fine. However, I can't do that in real life because the database name will keep on changing.