Connection strings in LINQ to SQL classes.
Version : VS 2008 RTW
When you have a team working on a project that contains a LINQ to SQL class (dbml), you might see the following message when trying to add a Table entity or stored procedure in a dbml created by a fellow developer:
The objects you are adding to the designer use a different data connection than the designer is currently using. Do you want to replace the connection used by the designer?
The reason this happens is because the connection string in Server Explorer used to add the new stored procedure is different from what was originally used. In a team environment, this will occur if one developer checks in the dbml using one connection string and another developer checks out the dbml and tries to add a stored procedure or a table using a different connection string. Connection strings generally fall into one of the two shown below for SQL server:
Windows Authentication:
Data Source=.\sqlexpress;Initial Catalog=northwind;Integrated Security=True
SQL Authentication:
Data Source=.\sqlexpress;Initial Catalog=Northwind;Persist Security Info=True;User ID=northwind_web;Password=**
The connection string could easily vary by the type of authentication, the userid/password combination, instance name etc. This could become a source control nightmare with each developer checking in the dbml file with a connection string defined in their "Server Explorer". Therefore it is best, when possible, that all developers use the same connection string defined in "Server Explorer" when working with a dbml file for a given project.
This brings up the question of how to change connection string when the dbml class library is used in an ASP.net website. The solution is create a connection string in web.config with the same name as what is specified in app.config.
To understand why, we have to look at the class library dll. I have a sample project attached at the bottom of this post that contains a class library containing a dbml and the class library being referenced from an ASP.Net project. When the library gets built, a sealed class called Settings is generated internally. Here is the class with the help of reflector:
[CompilerGenerated, GeneratedCode("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "9.0.0.0")]
internal sealed class Settings : ApplicationSettingsBase
{
// Fields
private static Settings defaultInstance = ((Settings) SettingsBase.Synchronized(new Settings()));
// Properties
public static Settings Default
{
get
{
return defaultInstance;
}
}
[ApplicationScopedSetting, SpecialSetting(SpecialSetting.ConnectionString), DefaultSettingValue(@"Data Source=.\sqlexpress;Initial Catalog=northwind;Integrated Security=True"), DebuggerNonUserCode]
public string northwindConnectionString
{
get
{
return (string) this["northwindConnectionString"];
}
}
}
We see that the northwindConnectionString property has a DefaultSettingValue attribute containing the connection string. When Settings gets loaded at runtime, if it finds the same a connection string with name MyClassLibrary.Properties.Settings.northwindConnectionString in app.config or web.config, it will use that. If it does not find it, it reverts to the DefaultSettingValue. As long as our web.config has the same "name" as what the Settings class is looking for, that will get used in place of the default value.
What about the case where we want one connection string during development and another for production? In web.config, you can specify the configSource attribute in the connectionStrings section. This attribute specifies the fully qualified name and location of an external configuration file that contains the connectionStrings section. During development, you can have configSource point to the connectionString of your test server and when you are ready to deploy to staging or production, you can change the attribute to point to the appropriate external config file. You can also have this done automatically using Web Deployment Projects or MSBuild.
The sample project attached contains a class library with a connection string defined. This class library is used in an ASP.net website project containing a connection string in web.config with the same "name" as what was used in the class library thereby "overriding" the class library connection string value. The website project also has a configSource defined in web.config pointing to an external configuration file ConnectionStrings_Test.config. When deploying to production, the configSource will be set to point to ConnectionStrings_Prod.config which contains the production server connection string.
Comments are welcome. Thanks!