Connection string hell in .NET / LINQ-SQL / ASP.NET

Question

I have a web application that comprises the following:

• A web project (with a web.config file containing a connection string - but no data access code in the web project)
• A data access project that uses LINQ-SQL classes to provide entities to the web project UI (this project has a settings file and an app.config - both of which have connection strings)

When I build and deploy, there is no settings file or app.config in the Bin directory with the data access .dll, but changing the connection string in the web.config file doesn't change the database accordingly - so the connection string must be compiled into the data access dll.

What I need is one config file for my entire deployment - website, data access dlls, everything - that has one connection string which gets used. At the moment there appear to be multiple connection strings getting used or hardcoded all over the place.

How do I best resolve this mess?

Thanks for any help.

Solution 1

I've never had a problem with the Data Access Layer (DAL) being able to use the connection strings from my web.config file. Usually I just copy the connection strings section from the DAL and paste it into the web.config. I'm using the DBML designer to create the data context.

If this won't work for you, you can specify the connection string in the data context constructor. In your web project have a static class that loads your settings, including your connection strings, and when you create your DAL object (or data context, if creating it directly) just pass it in to the constructor.

public static class GlobalSettings
{
    private static string dalConnectionString;
    public static string DALConnectionString
    {
       get
       {
           if (dalConnectionString == null)
           {
              dalConnectionString = WebConfigurationManager
                                      .ConnectionStrings["DALConnectionString"]
                                        .ConnectionString;
           }
           return dalConnectionString;
       }
    }
}
...

using (var context = new DALDataContext(GlobalSettings.DALConnectionString))
{
   ...
}

Solution 2

The configuration file for the startup project will define the configuration settings for all included projects. For example if your web project is the startup project, any reference to "appSettings" will look for settings from web.config, this includes any references to "appSettings" from your data access project. So copy any config settings from the Data Access project's app.config to the web project's web.config.

Solution 3

Roll your own ConnectionFactory based on the Registry:

• add a registry key for your application under SOFTWARE/[YOUR_COMPANY]/[YOUR_APP]
• add a string value for ConnectionString
• Teach your ConnectionFactory to crack open the appropriate registry key (in a static constructor, not every page load!).
• export the registry info as a .reg file, add it to source control, modify and apply it as necessary to set up additional machines.

Pro:

• Simple to set up
• Connectionstring lives in a single place
• Not in web/app.config, so no need to hardcode environment-specific settings.
• Not in web/app.config, so Junior Dev Jimmy can't accidentally tell your production server to look at the DEV database

Con:

• Not immediately obvious that important things are living in the registry, so new devs will need instructions.
• Extra step when configuring a new deployment machine
• Registry is oldskool. Junior devs will mock you.

Solution 4

Thanks for the responses.

Those of you who say the app will use the setting in the web.config are correct for instances where I reference it in my own code:

_connectionString = ConfigurationManager.AppSettings["ConnectionString"];

..but there is a different issue with LINQ-SQL datacontexts - I think they include connections strings in the compiled dll for use in the parameterless constructor. As tvanofosson says, I need to create datacontexts by passing in a reference to the connection string in the web.config. Which is where I was getting into a tangle :)

Solution 5

Your application will only use the config entries in the web.config file. You can put dll config setting in the web.config file as long as they are structure properly. My example is VB specific using the My Namespace, but it gives you the general idea.

In the configSections paret of the config file you will need an entry:

<configSections>
    <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
        <section name="YourAssembly.My.MySettings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    </sectionGroup></configSections>

Then in the applicationSettings part of the config file you put the entries for each dll:

    <applicationSettings>
      <YourAssembly.My.MySettings>
        <setting name="DebugMode" serializeAs="String">
            <value>False</value>
        </setting>
      </YourAssembly.My.MySettings>
    </applicationSettings>  

View more solutions

Comments

[Jitendra Pancholi]

It seems to be a bug in linq to sql, i worked a lot on linq to entities and this is first time i am working with linq to sql and facing this issue first time.

[Zhaph - Ben Duguid]

Yes, the datacontext will include the ConnectionString as a fall back if all other ways to find a ConnectionString with the correct name fail - you can always instatiate the context using the constructor that takes a connection string as a parameter.

[p.campbell]

@Jason: kudos for submitting 2 answers. Well thought out pros and cons. An unfortunate Con would be 'not web hosting' friendly.

[Frank Schwieterman]

Doesn't adding that constructor cause a compile issue, as the designer generated partial class already has the same constructor?

[Kyle Ryan]

Ah, I just realized that DAL = Data Access Layer. We newbie-types are a little slow with the syntax.

[Kieran]

Sorry for being stupid but where do i put this class in the web app project or in the Data Access project. And do you have any ideas on how to overide the DALDataContext() default constructor to default to the string in the web.config .? Thanks

[tvanfosson]

@Kieran -- Since it's only concerned with the web settings, I would put it in the web project.

[Seba Illingworth]

Thats right, depending on the designer properties you choose (Connection section), the generated code might create a parameterless constructor itself.

[stakx - no longer contributing]

(@tvanfosson: This is off-topic, but I thought I'd mention this anyway: If your GlobalSettings code is supposed to be implementing the Singleton pattern, then it isn't thread-safe. Jon Skeet wrote an article on how to implement thread-safe singletons; using the Lazy<T> type would also work well and nicely.)

[tvanfosson]

@Stakx - it's a read-only property. Even if another thread replaces the value, it will replace it with the same thing. You could wrap it in a lock statement, but I don't really think it would be a problem.

[stakx - no longer contributing]

(@tvanfossom, I agree; it's indeed not a problem in the present case. But if someone took the above code as an example for how to implement any singleton object, then it might become one. That's why I mentioned it, and why my comment is also off-topic. ;-)

[Mac]

This is my preferred solution. Setting the connection to "None" in the designer as Hihu mentioned will prevent the parameterless constructor from being auto-generated, so there is no compile issue. A similar solution, with more elaborate detail, is described here.