Here's what I do, which is similar to Tim's suggestion. This technique picks the connection string based on the machine. When you're ready to deploy, add a connection string to the App.Config for the target machine.
First the App.Config
<connectionstrings>
<add name="marois_work_1_spares" connectionstring="Data Source=MAROIS_WORK_1\SQLSERVER2008;Initial Catalog=Spares2;Integrated Security=True">
providerName="System.Data.SqlClient" />
<add name="ui-pc_spares" connectionstring="Data Source=(local);Initial Catalog=Spares2;Integrated Security=True">
providerName="System.Data.SqlClient" />
</connectionstrings>
Then, in my data access class:
private SparesDataContext getDataContext()
{
string machineName = Environment.MachineName.Trim().ToLower();
machineName = machineName + "_spares";
string connString = string.Empty;
try
{
ConnectionStringSettings conSettings = ConfigurationManager.ConnectionStrings[machineName];
connString = conSettings.ConnectionString;
}
catch (Exception ex)
{
string errorMessage = "Error reading the connection string from App.Config. " + Environment.NewLine +
"The machine name is '" + machineName.Trim() + "'";
throw new Exception(errorMessage, ex);
}
SparesDataContext dc = new SparesDataContext(connString);
try
{
dc.Connection.Open();
dc.Connection.Close();
}
catch (Exception ex)
{
string errorMessage = "Unable to acccess the database." + Environment.NewLine +
"The connection string is '" + connString.Trim() +
"'. The machine name name is '" + machineName.Trim() + "'";
throw new Exception(errorMessage, ex);
}
return dc;
}