LINQ and Web Application Connection Strings

It’s usually a good idea to seperate Biz Layer Objects and Data Access Objects into seperate assemblies, but when you do that and use LINQ to SQL (.dbml) files the DataContext object default ctor uses the Settings file that is automattically generated as the default connection string instead of the configuration file connectionString setting.

When you drag a database object to the dbml file an app.config file is created, but there isn’t an option anywhere in properties to actually use it. We really don’t want to have to manage connection strings in multiple places and when dev’n a class library it’s usually best to leave that up to the client of the class library especially if you own dev of both projects. (not considering service interface here)

Here’s what I recommend to simplify the connection string management,

1) Create the dbml file in the class library project and drag a database object onto the design surface.

2) Find the default parameterless ctor for the DataContext something like this and remove it.


`
public DataClasses1DataContext() :


base(global::LINQConnStringLib.Properties.Settings.Default.ConnectionString, mappingSource)


{

OnCreated();

}


`

3) Add a reference to the System.Configuration DLL.

4) Create a new CS file to hold the Data Context partial class that will contain the default ctor and the following code to it (replacing class name and conn string name for your project)
<span style="color: blue; font-size: small;"> </span>
<span style="color: blue; font-size: small;">public</span><span style="font-size: small;"> </span><span style="color: blue; font-size: small;">partial</span><span style="font-size: small;"> </span><span style="color: blue; font-size: small;">class</span><span style="font-size: small;"> </span><span style="color: #2b91af; font-size: small;">DataClasses1DataContext</span>
<span style="font-size: small;"> </span>
<span style="font-size: small;">{</span>
`
public DataClasses1DataContext() : base (ConfigurationManager.ConnectionStrings[“ConnectionString”].ConnectionString)


{

OnCreated();

}

}`

5) Add the connection string to your application configuration file.
<span style="color: blue; font-size: small;"> </span>
<span style="color: blue; font-size: small;">&lt;</span><span style="color: #a31515; font-size: small;">configuration</span><span style="color: blue; font-size: small;">&gt;</span>
<span style="color: blue; font-size: small;"> </span>
<span style="color: blue; font-size: small;">&lt;</span><span style="color: #a31515; font-size: small;">connectionStrings</span><span style="color: blue; font-size: small;">&gt;</span>
<span style="color: blue; font-size: small;"> </span>
<span style="color: blue; font-size: small;">&lt;</span><span style="color: #a31515; font-size: small;">add</span><span style="color: blue; font-size: small;"> </span><span style="color: red; font-size: small;">name</span><span style="color: blue; font-size: small;">=</span><span style="font-size: small;">"<span style="color: blue; font-size: small;">ConnectionString</span><span style="font-size: small;">"
<span style="color: blue; font-size: small;"> </span>
<span style="color: red; font-size: small;">connectionString</span><span style="color: blue; font-size: small;">=</span><span style="font-size: small;">"<span style="color: blue; font-size: small;">x</span><span style="font-size: small;">"
<span style="color: blue; font-size: small;"> </span>
<span style="color: red; font-size: small;">providerName</span><span style="color: blue; font-size: small;">=</span><span style="font-size: small;">"<span style="color: blue; font-size: small;">System.Data.SqlClient</span><span style="font-size: small;">"<span style="color: blue; font-size: small;"> /&gt;</span>
<span style="color: blue; font-size: small;"> </span>
<span style="color: blue; font-size: small;"><span style="color: #a31515; font-size: small;">&lt;/connectionStrings</span><span style="color: blue; font-size: small;">&gt;</span></span>
<span style="color: blue; font-size: small;"><span style="color: blue; font-size: small;"> </span></span>
<span style="color: blue; font-size: small;"><span style="color: blue; font-size: small;"><span style="color: #a31515; font-size: small;">&lt;/configuration</span><span style="color: blue; font-size: small;">&gt;</span></span></span>


6) Delete the settings file if you don’t need it. It will be created again if you add new objects to the dbml file so I just leave it there.

7) Right click on the DBML file design surface and chose properties. Select “none” for the Connection property. That way the default ctor won’t be created in the generated file.

The down side to this approach is that you have to set the Connection property to none after you add new objects to the dbml file, but it’s not as bad as having to manage the connection string in multiple places.


Hope this helps,

Jon

Share