Is there a way to load an existing connection string for Linq to SQL from an app.config file?

Question

I'm running into a really annoying problem with my Linq to SQL project. When I add everything in under the web project everything goes as expected and I can tell it to use my existing connection string stored in the web.config file and the Linq code pulls directly from the ConfigurationManager.

This all turns ugly once I move the code into its own project. I’ve created an app.config file, put the connection string in there as it was in the web.config but when I try to add another table in the IDE keeps forcing me to either hardcode the connection string or creates a Settings file and puts it in there, which then adds a new entry into the app.config file with a new name.

Is there a way keep my Linq code in its own project yet still refer back to my config file without the IDE continuously hardcoding the connection string or creating the Settings file? I’m converting part of my DAL over to use Linq to SQL so I’d like to use the existing connection string that our old code is using as well as keep the value in a common location, and one spot, instead of in a number of spots.

Manually changing the mode to WebSettings instead of AppSettings works untill I try to add a new table, then it goes back to hardcoding the value or recreating the Settings file. I also tried to switch the project type to be a web project and then rename my app.config to web.config and then everything works as I’d like it to. I’m just not sure if there are any downfalls to keeping this as a web project since it really isn't one. The project only contains the Linq to SQL code and an implementation of my repository classes.

My project layout looks like this

Website
-connectionString.config
-web.config (refers to connectionString.config)

Middle Tier
-Business Logic
-Repository Interfaces
-etc.

DAL
-Linq to SQL code
-Existing SPROC code
-connectionString.config (linked from the web poject)
-app.config (refers to connectionString.config)

Update Here's an example of the code I'm talking about

Web Project

public DB() : 
        base(global::System.Configuration.ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString, mappingSource)
{
    OnCreated();
}

Non Web Project

public DB() : 
        base(global::SampleProject.Data.Properties.Settings.Default.SiteSqlServer, mappingSource)
{
    OnCreated();
}

Every time I try to make the default constructor look like the first way, it always auto changes back to the second way once I make a change to the file unless the DBML is inside of a web project.

Answer 1

If you mean: Can I use a particular connection string in Linq-2-Sql? Then the answer is yes.

When you instantiate the DataConext you can pass in a connection string which can come from where-ever you like.

If you wanted to use the connection string configured in your Linq project then you could create your own Settings class and add it there and reference it via code in your Linq project.

Answer 2

i use a trick for this:

public partial class DataContext
{
    partial void OnCreated()
    {
        this.Connection.ConnectionString =
                     global::System.Configuration.ConfigurationManager
                     .ConnectionStrings["SQLServer"].ConnectionString;
    }
}

this will read the connection string out of the current configuration manager, which (in the case of an ASP.Net website) will be the Web.config. I just add this partial class definition to the same project where my dbml is defined. Then any other project that uses the dbml project will just have to define their connection strings in their app.config/web.config.