Reload web.config after updating connectionstring programmatically in C#

Question

I have created Connectionstring in web.config at run time. Now I am saving some data into my DB but it is still reading old Connectionstring. How can I solve this? below is my code:

public string EditConnectionString(string userId, string password, string host, string dbName)
{
    try
    {
        System.Configuration.Configuration Config = WebConfigurationManager.OpenWebConfiguration("~");
        ConnectionStringsSection conSetting = (ConnectionStringsSection)Config.GetSection("connectionStrings");
        string providerName = "System.Data.EntityClient";
        string conString = @"metadata=res://*/OWordpress.csdl|res://*/OWordpress.ssdl|res://*/OWordpress.msl;provider=System.Data.SqlClient;provider connection string=" + "\"data source=" + host + ";initial catalog=" + dbName + ";user id=" + userId + ";password=" + password + ";MultipleActiveResultSets=True;App=EntityFramework\"";
        ConnectionStringSettings StringSettings = new ConnectionStringSettings("OWordpressContainer", conString,providerName);
        conSetting.ConnectionStrings.Remove(StringSettings);
        conSetting.ConnectionStrings.Add(StringSettings);
        Config.Save(ConfigurationSaveMode.Modified);

        return MessageFamily.Success.ToString();
    }
    catch(Exception ex)
    {
        return ex.Message.ToString();
    }
}

After creating Connectionstring I am saving some data as below but it still read old connectionstring values:

public string SaveDbInfo(string userId, string password, string host, string dbName)
{
    try
    {
        var optionEntity = new Options();

        optionEntity.key = "DBInfo";
        optionEntity.value = "{userId:'" + userId + "',password:'" + password + "',host:'"+host+"',dbName:'"+dbName+"'}";

        objContext.Options.Add(optionEntity);
        objContext.SaveChanges();
        return MessageFamily.Success.ToString();
    }
    catch(Exception ex)
    {
        return ex.Message.ToString();

    }
}

After changing the connection string, when old connection string is being used, is your values getting saved properly? — Manik Arora, May 28 '15 at 09:55
How and *where* did you save the changes? Modifying a web application's `web.config` causes the application to restart — Panagiotis Kanavos, May 28 '15 at 11:34
If you're changing the ConnectionString at runtime why save it in the web.config at all? You can pass the whole connectionstring into the DbContext constructor without it being a named connection in web.config. — kjbartel, May 28 '15 at 11:40
Did your code actually work, or did it result in an exception without saving? Did you check the return value of `EditConnectionString` ? — Panagiotis Kanavos, May 28 '15 at 11:42

score 2 · Answer 1 · answered May 28 '15 at 09:47

2

Try the following code after you are changing the connection string and let me know if it helped or not-

ConfigurationManager.RefreshSection("connectionStrings");

answered May 28 '15 at 09:47

Manik Arora

4,702
1
25
48

Ohk, did you added this line after - Config.Save() ? – Manik Arora May 28 '15 at 09:53
yes I did after Config.Save(ConfigurationSaveMode.Modified); – Parveen May 28 '15 at 09:54
You can try recycling the app pool after you make the changes in the web.config, using this answer - http://stackoverflow.com/a/249942/3748701. NOTE: this will erase all the server session data, try this for testing purposes first, then let me know what happened. – Manik Arora May 28 '15 at 10:39
Then I don't know my friend, how to help you, in one of my project I did the same thing and had used ConfigurationManager.RefreshSection which had worked perfectly :) – Manik Arora May 28 '15 at 10:52
None of this helps because modifying `web.config` forces an application restart anyway. If the changes were actually saved, the application would have restarted with the new settings – Panagiotis Kanavos May 28 '15 at 11:36

score 1 · Accepted Answer · edited May 28 '15 at 11:48

1

I solve this issue by using EntityConnectionStringBuilder

var ecsBuilder = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["OWordpressContainer"].ToString());

var sqlCsBuilder = new SqlConnectionStringBuilder(ecsBuilder.ProviderConnectionString)
{
    InitialCatalog = dbName,
    UserID=userId,
    DataSource=host,
    Password=password
};

var providerConnectionString = sqlCsBuilder.ToString();                
ecsBuilder.ProviderConnectionString = providerConnectionString;    
string contextConnectionString = ecsBuilder.ToString();   

using (var db = new DbContext(contextConnectionString))
{
    OWordpressContainer objContext = new OWordpressContainer(contextConnectionString);    
    var optionEntity = new Options();    
    optionEntity.key = "DBInfo";    
    optionEntity.value = "{userId:" + userId + "',password:'" + password + "',host:'" + host + "',dbName:'" + dbName + "'}";    
    objContext.Options.Add(optionEntity);    
    objContext.SaveChanges();
}

edited May 28 '15 at 11:48

Amnesh Goel

2,617
3
28
47

answered May 28 '15 at 11:26

Parveen

652
1
9
27

This isn't saving anything. If all you wanted was to connect to a different database, why were you asking about saving connection strings? – Panagiotis Kanavos May 28 '15 at 11:39
I think he wanted to connect to different database using same DBContext of EntityFramework and ConnectionName but different Database having same schema but doing it dynamic and on the fly. The answer seems to be good solving his purpose. – Manik Arora May 28 '15 at 11:43
This code has issues - it opens two DbContexts, closes the unused one and leaves `OWordpressContainer` open – Panagiotis Kanavos May 28 '15 at 11:48

Reload web.config after updating connectionstring programmatically in C#

2 Answers2