Linq to SQL Database Connection String Issue

Linq to SQL Database Connection String Issue

By budigelli on Jun 19, 2008 in .NET, LINQ

As i am moving in to hosting my application on an external server, i am finding some interesting and annoying (time consuming for research) things. i will try to address them as they come along…

One of the main thing was the way i handle my Data Layer. My Data Layer is a class library project and I have separate Business, Entity, and UI layers. My Data Layer is done using LINQ to SQL with stored procedures (All my interaction with Database are using Stored Procedures). When i dragged and dropped required tables and stored procedures in to the designer, behind the scenes, the DBML file, (specifically, the DataContext class) hasa default constructor that stores the connection string. To Re-iterate, we have this connection string defined in Web.Config, and app.Config (gets created for class library projects), and this hard coded value in “.dbml” File.

I googled for few min to find out couple of good posts on this issue and here is the summary and the steps to only have one connection string in “web.Config” file.

You can find more info on this by Rick, by Jon Gallant, and by David Klein.

I went ahead with what was posted on David klein’s article. on the “.dbml” designer properties, first made the “Application Settings” to false and tried to compile with partial class. But that didn’t compile. I read the comments David’s post and made the “Connection” to “Null”. When i saved and compiled the project it compiled. So, i deleted the Settings File, App.Config File and compiled and ran the application with out any problem.

Now, i only have one place to manage my connection string (in web.config file).

Recap.

Set the “Connection” Property for the “.dbml” designer file to “Null”

Create a partial class for your DataContext class and have the

constructor with base constructor reading the connection string from web.config file

public partial class DataClasses1DataContext

{

public DataClasses1DataContext() : base(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)

{

OnCreated();

}

}

 

Make sure you have Connection string in your web.config.

Thats it. Simple !!! Happy Coding…

Update: The next time i tried to add a Stored Procedure or Table to the DataContext Designer, i still have to go to the properties of the DataContext and tell it not to read from “appsettings” and set the connection string to null/empty string. I wish there is a permanent way to say, do not populate these values