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
– JGV, Commented May 27, 2015 at 18:44
@VimalanJayaGanesh interesting thought, how would I update the web.config dynamically? — nate
– nate, Commented May 27, 2015 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
– Sean Lange, Commented May 27, 2015 at 18:50
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 ). Also, changing your web.config will recycle your app pool, so it's best to do during off-hours if you can. — Arin
– Arin, Commented May 27, 2015 at 19:01

Riad Baghbanli · Accepted Answer · 2015-05-27 19:05:03Z

2

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.

answered May 27, 2015 at 19:05

Riad Baghbanli

3,3441 gold badge17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Herbey · Accepted Answer · 2015-05-27 19:19:52Z

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");

Collectives™ on Stack Overflow

Change SQL Server Database Connection Dynamically

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related