Linq to SQL Database Connection String Issue

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 :(

3 thoughts on “Linq to SQL Database Connection String Issue

  1. has there been any new developments on this? How do you tell the DataContext to not read from “appsettings”?? Thank you for your time.

  2. In your DataContext Designer (Linq-To-SQL Designer page), click on any white space and go to properties. At the bottom you should see the connection string related settings. Expand and clean them up.

    Now, your setting are read from your partial class and not from your appsettings.

    But, keep in mind, everytime you modify (update your designer with another entity addition), you will have to clean this property!

    Hope it helps.
    Budigelli

Leave a Reply to Todd Cancel reply

Your email address will not be published. Required fields are marked *