Change SQL Server Database Connection Dynamically

Question

I have a connection class that I use to connect to my database, every time I do a select, insert, etc... statement. I have the connection string hard coded in the class.

The issue with this is, if I have to change database servers, then I have to change the string on every application, and republish.

Here is my connection string:

var cnnString = string.Format("user id=sa;" +
                                          "password=pw;server=database\\instance;" +
                                          "database=dbase; " +
                                          "connection timeout=10");

I thought about creating a table and storing the database info in there, but that doesn't help if can't use the connection class to select from the database.

Is there a way of doing this dynamically?

Try to put the database connection string in Web.config file and read it in your code. — JGV, May 27 '15 at 18:44
@VimalanJayaGanesh interesting thought, how would I update the web.config dynamically? — nate, May 27 '15 at 18:45
Why do you need to update the web config dynamically? How often and frequently are you planning on changing servers? The advantage of using your web config is that the connection string information is ONE location. Then when you need to change it, you change the web config and all your code uses the new information. — Sean Lange, May 27 '15 at 18:50
[Change a web.config programmatically with C# (.NET)](http://stackoverflow.com/questions/2260317/change-a-web-config-programmatically-with-c-sharp-net) — Steve, May 27 '15 at 18:51
Just some pointers, when I used to embed a connection string in a web.config, it was a best practice then to encrypt it (see [How to Encrypt connection string in web.config](https://chiragrdarji.wordpress.com/2008/08/11/how-to-encrypt-connection-string-in-webconfig/) ). Also, changing your web.config will recycle your app pool, so it's best to do during off-hours if you can. — Arin, May 27 '15 at 19:01
I don't think he needs to change web.config programmatically. All he has to do is, put each connection strings separately in the web.config file and read the appropriate connection string in C# code based on the scenario. — JGV, May 27 '15 at 19:12

score 2 · Answer 1 · answered May 27 '15 at 19:05

Place your connection strings in .config file and use ConfigurationManager class: https://msdn.microsoft.com/en-us/library/system.configuration.configurationmanager%28v=vs.110%29.aspx

Load all you connection strings into array / Dictionary then once you need to create connection using the appropriate connection string.

score 1 · Answer 2 · answered May 27 '15 at 19:19

You can use your Appconfig file

<appSettings>    
    <add key="dbserver" value="IP_SERVER" />
    <add key="dbname" value="DB_NAME" />
    <add key="dbuser" value="sa" />
    <add key="dbpass" value="PASSWORD" />
 </appSettings>

then use this to create the string conection

string strConexion = "Data Source='" + ConfigurationManager.AppSettings["dbserver"] + "';" +
                                "Initial Catalog='" + ConfigurationManager.AppSettings["dbname"] + "';" +
                                "User Id='" + ConfigurationManager.AppSettings["dbuser"] + "';" +
                                "Password='" + ConfigurationManager.AppSettings["dbpass"] + "';";

And you can use a form to change the configuration values using this

// Open App.Config of executable

    System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

        config.AppSettings.Settings.Remove("dbserver");
        config.AppSettings.Settings.Remove("dbname");
        config.AppSettings.Settings.Remove("dbuser");
        config.AppSettings.Settings.Remove("dbpass");

        // Add an Application Setting.
        config.AppSettings.Settings.Add("dbserver", txtDBServer.Text);
        config.AppSettings.Settings.Add("dbname", txtDBName.Text);
        config.AppSettings.Settings.Add("dbuser", txtDBUser.Text);
        config.AppSettings.Settings.Add("dbpass", txtDBPassword.Text);

Save the changes in App.config file.

 config.Save(ConfigurationSaveMode.Modified);

Force a reload of a changed section.

ConfigurationManager.RefreshSection("appSettings");

Change SQL Server Database Connection Dynamically

2 Answers2