The connection string to a database should never be hard coded in your application. There is (almost) always a difference between the development and production database server. Besides that IT management should have an easy way to change the configuration. Part of this has been automated by the wizards of Visual Studio. When you add a dataset to a web site the wizard will create a web.config entry for the connection string. That's nice but, as I described in a another post, when you add database code directly to your web site your site will be (sooner or later) toast. My way is to isolate all database code in a class library. But after building the datasets in there it has become harder to change the connection string later on. You have become the sorcerers apprentice. (For those unknown with that story: In there the pupil of a great wizard starts playing around on his own. In the end he's almost killed by a bewitched broom, the wizard himself comes back just in time to save him. The story has been beautiful visualized in Disney's Fantasia where Mickey Mouse is the sorcerers apprentice).
There is a way out. You can change the connection string of any table adapter at runtime by setting it's Connection.Connectionstring to the desired value. Scott describes how to add this functionality to the code of a dataset component. Which is nice but you have to write this code for every table adapter and you have to set the connection after every instantiation of the table adapter. Which is tedious and a possible source of bugs.
What the wizards do is store the value of the connection string in a setting of the app. There is a settings page under the properties of a project
In here settings are stored in name-value pairs. The name is the name of the database; for sqlServer it's the DB name for FireBird (as in the screenshot) the name is the full name of the database file.
In a web application these settings are stored in the web.config, in a class libray in the app.config file.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="DataLibrary.Properties.Settings.C__Documents_and_Settings_Peter_PETERSGEKKO_My_Documents_My_Custoners_DirActivity_DacPro_DACPRO_FDBConnectionString"
connectionString="port number=3050;charset=ISO8859_1;dialect=3;server type=0;database="C:\Documents and Settings\Peter.PETERSGEKKO\My Documents\My Custoners\DirActivity\DacPro\DACPRO.FDB";data source=Farlowella;user id=gebruiker;password=project"
providerName="FirebirdSql.Data.FirebirdClient" />
</connectionStrings>
</configuration>
What could work is editing this app.config file. But the downside is that you have to maintain besides the web.config an app.config for every class library which works with a database. Which is prone to error.
To good thing is that you can override the values of these settings from code. When the library is loaded the settings are available in Settings, a property of the namespace of the library. In this scenario I have a class library which wraps up access to a Firebird database. I add one public method.
public static void SetConnectionString(string connString)
{
const string FBconnectionTEmplate = "port number=3050;charset=ISO8859_1;dialect=3;server type=0;database=\"{0}";
const string SettingsName = "C__Documents_and_Settings_Peter_PETERSGEKKO_My_Documents_My_Custoners_DirActivity_DacPro_DACPRO_FDBConnectionString";
Settings.Default[SettingsName] = string.Format(FBconnectionTEmplate, connString);
}
The method has a template for a Firebird connection string. It expects the filename of the Firebird database to use. The setting has a horrible name, that's the fault of the Firebird designer support. When you use sqlserver it will be a simple database name. The name is the indexer to the Settings.Default array. To this I assign the composed connectionstring.
Now I make one call to this method, passing it a value read form the web.config
string dbConnString = System.Configuration.ConfigurationManager.AppSettings["dbConnection"];
ConnectionStringSetter.SetConnectionString(dbConnString);
After that all table adapters will use the right connection. I have my database wrapped up in a separate layer and still have all configuration in the web.config.