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.

Answer 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))
{
   ...
}

Answer 2

I know this is old but here's how I do it (I quite like @Seba's way but I haven't tried that)

This assumes your DBML file resides in its own class library, which I've found it most convenient when sharing entities and data access across multiple websites, and other class libraries. It also assumes you've named your connection string the same in each project. I use NAnt to set this when I deploy to different environments.

I based this on the top answer above from @tvanfosson - kudos to that guy.

1. Create your own base class, which derives from LinqDataContext

Here's the VB code:

    Imports System.Configuration

Public Class CustomDataContextBase
    Inherits System.Data.Linq.DataContext
    Implements IDisposable

    Private Shared overrideConnectionString As String

    Public Shared ReadOnly Property CustomConnectionString As String
        Get
            If String.IsNullOrEmpty(overrideConnectionString) Then
                overrideConnectionString = ConfigurationManager.ConnectionStrings("MyAppConnectionString").ConnectionString
            End If

            Return overrideConnectionString
        End Get
    End Property

    Public Sub New()
        MyBase.New(CustomConnectionString)
    End Sub

    Public Sub New(ByVal connectionString As String)
        MyBase.New(CustomConnectionString)
    End Sub

    Public Sub New(ByVal connectionString As String, ByVal mappingSource As System.Data.Linq.Mapping.MappingSource)
        MyBase.New(CustomConnectionString, mappingSource)
    End Sub

    Public Sub New(ByVal connection As IDbConnection, ByVal mappingSource As System.Data.Linq.Mapping.MappingSource)
        MyBase.New(CustomConnectionString, mappingSource)
    End Sub

End Class

1. Open your DBML file, and in the Properties, add the above class name to the Base Class property.

Note, if you placed the custom data context class in the same assembly, simply include the class name, e.g. CustomDataContext.

If they are in different assemblies, use the fully qualified name, e.g. MyCo.MyApp.Data.CustomDataContext

1. To ensure the Designer stuff works properly, copy your connection string into the app.config file for the class library. This will not be used apart from in the IDE.

That's it.

You'll need to name your connection string the same

What you are essentially doing is forcing the data context to ignore the connection info set in the DBML file. Using the ConfigurationManager methods will mean that it will pick up the connection string from the calling assembly.

HTH

Answer 3

To keep it safe from anything in the auto generated code, override the connection info in the OnCreated() method of the data context:

using System.Configuration;
namespace MyApplication 
{
    partial void OnCreated()
    {
        // attempt to use named connection string from the calling config file
        var conn = ConfigurationManager.ConnectionStrings["MyConnectionString"];
        if (conn != null) Connection.ConnectionString = conn.ConnectionString;
    }
}

This way the dbml designer can do connection stuff its way (which isn't nice outside of a web project), but you grab final control of the connection when the application runs.

Answer 4

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>  

Answer 5

I had a bit of a struggle with this issue too. I found a solution by using c# partial class definition and extending the datacontext created by dbml designer. This solution quite is similar to tvanfosson's answer. What you have to do is to create partial datacontext class with default constructor getting ConnectionString from settings and in dbml designer DC properties set connection to None. That way connection string will not be the compiled into dll. Datacontext will automatically get connection string from web.config connectionstring settings. I have not tested if this works with app.config also, but I think it should work fine.

Here is sample of partial DC class:

namespace MyApplication {
    /// <summary>
    /// Summary description for MyDataContext
    /// </summary>
    /// 
    public partial class MyDataContext
    {
        public MyDataContext() :
            base(global::System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString, mappingSource)
        {
            OnCreated();
        }
    }
}

Answer 6

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

Answer 7

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.

Answer 8

Roll your own ConnectionFactory based on .config files:

• Define a custom config section to map key/connectionstring pairs
• Teach your ConnectionFactory to sniff into that config section using hostname or machinename as appropriate
• Populate key/connectionstring values for your various dev/qa/prod servers, and drop them into your various app.config, web.config etc. files.

Pro:

• All lives inside the project, so no surprises
• Adding additional deployment target is a copy/paste operation in a .config file

Con:

• Makes for big ugly XML sections, especially if you have a dozen production servers
• Needs to be duplicated between projects
• Needs code change & redeploy to add new target
• Code needs to know about the environment in which it will live

Answer 9

In a perfect world, I think you would refactor you data layer to pick up configuration settings via System.Configuration or relevant constructors/factories. Meaning, you either need to rewire its implicit configuration source, or explicitly set connections from its host/consumer. Another related pattern for centralizing these types of constants is to throw an readonly property into a static helper class and have that class manage the actual resolution from configs, etc.

One place you can look that I think shows good examples of how to do this elegantly is NHibernate and its configuration/mappings management. Granted, it's a bit of xml hell, and Fluent NHib is more sugary, but most of the real world samples will show you how to reconcile configuration from a supporting assembly vs. the executing assembly.

Answer 10

You could also have the web application provide the connection string when it needs to use the Data Access project. You could make it part of the constructor.

Also, you could could just write your own logic to load a connection string from an external file when the data access project makes it's calls.

Answer 11

Here's one way to look at it. Which component should make the decision about which database to use? It's possible that the database (or at least the connection string) could change in the future. Does the website decide which database to use? Or, does the DAL decide?

If you have dev, QA, UAT and prod databases, managing these connection strings is crucial.

If the website decides, it should pass the connection string from its web.config to the DAL. If the website isn't supposed to know or care where the data comes from, then the connection string belongs in the DAL.

Answer 12

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.

Answer 13

How about defining a ConnectionFactory object, that takes an enum as a parameter and returns a fully-formed connection object?