Changing SQL connection at runtime

[samcogan]

Im writing an app that needs to let the user choose which SQL server
they want to use froma drop down box, then connect to that server.
I've been searching for ages to find a way to do this, anyone have any
ideas?

Thanks

Sam

 

[Morten Wennevik]

Hi Sam,

What seems to be the problem? All you need to do is change the connection
string. If the server can be of different types (ie oracle and ms sql
server) then it gets slightly more complicated, but as long as you use
oledb objects in your code it still is a matter of changing the connection
string.

See http://www.connectionstrings.com for samples on connection strings.

 

[Jeff Allan]

There are a few ways I can think of. I would use option 1 but if you don't
have "default" table then you can use item 2.

1.> Store connection string data in a default table.
2.> Store the connection strings in the web.config file.

 

[Dave Sexton]

Hi Sam,

In the 2.0 framework it's common to store connection strings in the
project's default Settings file to be used by the 2.0 "Providers". Each
string must have a unique name. At runtime, your code can grab the
connection string from a property like this:

// C#
string connString = Properties.Settings.Default.ExampleConnectionString;

or if the user has selected the unique name from a list:

string namedConnString = lstConnectionStrings.SelectedText;
string connString = Properties.Settings.Default[namedConnString];

If you're using an earlier version than the 2.0 framework, then you could
fill a static (shared) Hashtable where the keys are unique names and the
values are the corresponding connection strings. Store the connection
strings in the AppSettings of your application's configuration file and load
them into the Hashtable when they are required. The singleton pattern will
be useful.