LINQ To SQL and the Web.Config ConnectionString Value

December 07, 2007 • from Maui, Hawaii • 29 comments

On this page:

I just got had by an odd behavior in the LINQ to SQL model designer when working with a LINQ to SQL Model in a separate class library project. I use LINQ to SQL in a business layer which always lives in a separate assembly and so the model does not live in the same project as the Web (or other) application I'm working on.

When the model is created the DataContext has options on how the Connection is handled when you create your DataContext without specifying any parameters in the constructor - ie. you're instantiating the DataContext without a connection string or connection object:

TimeTrakkerContext context = new TimeTrakkerContext();

When you do this the settings are read from the configuration settings or the default connection string if no settings are available. You can see these settings in the DataContext properties under Connection:

DataContextPropertySheet

You can specify a default connection string, and whether you like to use Application Settings to read the value from your config file.

Now the behavior for this is different depending in which type of project you LINQ to SQL. If you use a Web Project the Application settings are directly read from the ConfigurationManager object:

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

This directly maps to the plain ConnectionStrings key specified as you would expect.

If you however use the model in a class library project you get code that maps to the Settings object:

public TimeTrakkerContext() : 
        base(global::TimeTrakker.Properties.Settings.Default.TimeTrakkerConnectionString, mappingSource)
{
    OnCreated();
}

and which is set up like this:

[global::System.Configuration.ApplicationScopedSettingAttribute()]
[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
[global::System.Configuration.SpecialSettingAttribute(global::System.Configuration.SpecialSetting.ConnectionString)]
[global::System.Configuration.DefaultSettingValueAttribute("Data Source=.;Initial Catalog=TimeTrakker;Integrated Security=True")]
public string TimeTrakkerConnectionString {
    get {
        return ((string)(this["TimeTrakkerConnectionString"]));
    }
}

This is important for a couple of reasons: First using the Model in a class library project requires that you use this configuration settings class so you can't toss it as I often do in my projects. The compiler will let you know if you do and the model will recreate it for you. Heavy handed this, but I can live with that I suppose.

But maybe more confusing is the fact that even though I specify TimeTrakkerConnectionString as my key name the actual key that the DataContext is looking for is more complex - it's the full typename (namespace.class.key) when it actually is stored in the app.config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="TimeTrakker.Properties.Settings.TimeTrakkerConnectionString"
            connectionString="Data Source=.;Initial Catalog=TimeTrakker_Westwind;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

Not paying real close attention to the key created in App.Config I just created an entry for JUST TimeTrakkerConnectionString in my web.config file - only to find that the connection string was not respected at all.

It took a while of a going back and forth staring - and missing - the obvious to actually figure out that the damn key wasn't just TimeTrakkerConnectionString but the whole nine yards of TimeTrakker.Properties.Settings.TimeTrakkerConnectionString. Grumble, grumble, grumble...

Now, the ConnectionString key is only used if you use the default parameterless constructor on the DataContext. As an alternative you can also pass an explict connection string and if you want to avoid the business of having Application Settings generated just set the ConnectionString to None. This will cause the DataContext to be generated without the parameterless constructor and you have to pass a connection string instead. You can then subclass the DataContext (or use a wrapper like I do with the business object) to handle passing an application level connection string from whatever source you prefer.

However, having the default ConnectionString value there is nice because it makes it easier to write quick sample queries to test operation.

The Voices of Reason

Mike
December 07, 2007

# re: LINQ To SQL and the Web.Config ConnectionString Value

Oh I remember this from typed datasets, exactly the same story...

Tim
December 07, 2007

# re: LINQ To SQL and the Web.Config ConnectionString Value

I remember this from Typed Datasets as well ... used to drive me bananas. Work around for those was to hand edit the XML and change the connection string to reference the one in the web.config as well. Perhaps this also works for LinqToSQL?

Art Shayderov
December 10, 2007

# re: LINQ To SQL and the Web.Config ConnectionString Value

I think the easiest way to go is to add a partial class Bla-Bla-DataContext with one static method GetInstance() where you call appropriate constructor and supply connection string of you choice. You end up with something like this:

Bla-Bla-DataContext dataContext = Bla-Bla-DataContext.GetInstance();

bodya
March 05, 2008

# re: LINQ To SQL and the Web.Config ConnectionString Value

Thank You very very!!! Your article help me to fix problem with connection strings.

Arke Systems Blog
March 05, 2008

# Using the connection strings in your web.config for LINQ

Using the connection strings in your web.config for LINQ

Keith G
May 15, 2008

# re: LINQ To SQL and the Web.Config ConnectionString Value

Actually, I think I tried what you were saying:

I replaced the second line of the second version (1st override) of the DBContext method with this:

base(global::System.Configuration.ConfigurationManager.ConnectionStrings[connection].ConnectionString, mappingSource)

Now when I pass a "connection" instead of passing an entire connection string, I pass the key value from the web.config...and it gets it for me.

TriTim
May 26, 2008

# re: LINQ To SQL and the Web.Config ConnectionString Value

Rick - This saves me much work. Many thanks.

jparlato
August 07, 2008

# re: LINQ To SQL and the Web.Config ConnectionString Value

Rick, I'd like to ask if you have ever tried using linq to sql in a data layer with a wcf client?
I'm trying this, and I'm having great difficulty with the connection string not being found when I access the data layer via wcf. If I have the client instanciate a business layer object and call the data layer bypassing wcf, the connection is found and it works... but the same client when invoking the endpoint returns an error that the connection string cannot be found.

Any help would be greatly appreciated.
Thanks.

Travis
October 04, 2008

# re: LINQ To SQL and the Web.Config ConnectionString Value

Rick, just to confirm -

IF the multi-tiered solution is left as it would have been originally with the exception of adding the connection string line to your web.config :-

<add name="TimeTrakker.Properties.Settings.TimeTrakkerConnectionString"
connectionString="Data Source=.;Initial Catalog=TimeTrakker_Westwind;Integrated Security=True" providerName="System.Data.SqlClient" />

THEN will this override the connectionstring in the app.config if the DataContext object is instantiated with no parameters (i.e. I don't want to change app.config; I don't want to change any dbml code; I don't want to create any partial class)

Many thanks
Travis

Chris
November 05, 2008

# re: LINQ To SQL and the Web.Config ConnectionString Value

Rick, Thanks for the article...

What if you have situation where Integrated Security is not possible.

Any suggestions on encrypting the connection string while using LINQ to SQL?

Thanks,
Chris

MarkD
March 28, 2009

# re: LINQ To SQL and the Web.Config ConnectionString Value

After moving my code to a production server, I spent about 3 hours chasing down this error :
SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified.

It turned out to be this exact connection string problem!

Thanks for the post.

Sam Schutte
May 18, 2009

# re: LINQ To SQL and the Web.Config ConnectionString Value

The most annoying part of this to me is when you have references to multiple class libraries which reference a common database. Then, using the default implementation, you end up having to have multiple connection strings to the same database in your app.config.

For instance, if you have two class libraries called "UserLogic" and "WorkstationLogic", both of which use a "SecurityDB", you might end up with:

<add name="UserLogic.Properties.Settings.SecurityDBConnectionString" connectionString="Data Source=Server1;Initial Catalog=SecurityDB;Persist Security Info=True;User ID=sa;Password=password" providerName="System.Data.SqlClient" />

AND

<add name="WorkstationLogic.Properties.Settings.SecurityDBConnectionString" connectionString="Data Source=Server1;Initial Catalog=SecurityDB;Persist Security Info=True;User ID=sa;Password=password" providerName="System.Data.SqlClient" />

Just one extra thing to maintain. Sure, you can edit the generated classes and change things around, but if you have to update the object, it'll all get blown away.

Other people have commented on how this is similar to the typed datasets - it is - and that caused me endless problems. If you didn't have the configuration just right, the defaulting to the default connection string meant our production systems were always trying to pull data from our test systems, sometimes successfully ("it looks like it's working..."), which is a very hard defect to track down.

Rick Strahl
May 18, 2009

# re: LINQ To SQL and the Web.Config ConnectionString Value

@Sam - you can pass in your own connection strings to the DataContext and read them out of a custom Application Setting or other config store. For internal components I always do this exactly for this reason. I typically also add an application wide App.GetDataContext property so I consistently get the right connection string and set it on the DataContext.

Still I agree it would have been nicer if the setting was actually overridable where you can specify the appropriate connection string key in config.

Zyrus Khambatta
May 29, 2009

# re: LINQ To SQL and the Web.Config ConnectionString Value

I've been developing a Windows Forms application based on LINQ-to-SQL and I've always wanted to to be able to override the default connection parameters the app.config file. I came across a few solutions on the net, but there was no assurance that the references would not get blasted away each time I edited the DataContext object using the graphical editor. I didn't quite figure it out initially and had ignored it until now as it was not critical.

I knew that I could explicitly reference the connection as an argument to the data context in each instance but that would be, well, ugly. Instantiating a new DataContext object each time with no arguments just seemed so much more elegant!

I was trying to figure it out again today and I came across this page whilst googling for a solution to the problem. After a bit of reading and a bit of thought, I came up with the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

namespace MyNamespace
{
    public class MyDataContext : MyDataContextBase
    {
        private static class ConnectionParams
        {
            public static String ConnectionString {
                get {
                    return ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
                }
            }
        }

        public MyDataContext() : base ( ConnectionParams.ConnectionString )
        {
            
        }
    }
}

"MyDataContextBase" is the actual data context that I work with in the graphical editor, and "MyDataContext" the data context class I refer to in my code.

The solution is pretty simple really and I don't think the code needs much explanation. All I am doing is deriving "MyDataContext" from "MyDataContextBase" (the real data context), and passing in string read from the static property "ConnectionParams.ConnectionString". "ConnectionParams.ConnectionString" will return the connection string named "myConnectionString" from the app.config file.

So, I can call my data context like so:

    // some code

    using ( MyDataContext ctx = new MyDataContext() ) {
        // LINQ-to-SQL code and other goodies in here
    }

    // some more code

always being confident that I can edit the data context at will without breaking anything or referencing the incorrect database. =)

khanhpt
July 05, 2009

# re: LINQ To SQL and the Web.Config ConnectionString Value

thanks you very much.

anwar
July 22, 2009

# re: LINQ To SQL and the Web.Config ConnectionString Value

Hey
I'm getting this error Object reference not set to an instance of an object

El Guapo
September 02, 2009

# re: LINQ To SQL and the Web.Config ConnectionString Value

Good job, thanks

JPMattis
September 14, 2009

# re: LINQ To SQL and the Web.Config ConnectionString Value

re:Hey 
I'm getting this error Object reference not set to an instance of an object

Anwar,

Chances are that you aren't correctly referencing a valid SQL connection string (one that works). I recommend that you hardcode your connection string into your partial class constructor first, makes sure it works, then after that use that same connection string in your web.config where you can then reference with the ConfigurationManager.

Mattis

Chris
January 26, 2010

# re: LINQ To SQL and the Web.Config ConnectionString Value

Thanks Rick! Just saved me some MSDN searching :)

Mark
February 18, 2010

# re: LINQ To SQL and the Web.Config ConnectionString Value

The fact that DataSet Adapter or Linq to SQL have a reference to a global variable with connection is complete and utter insanity.

The whole Properties thing is. VS should not create it by default in a Class Library project.

The only time it's useful when you have Application/Main, or in a Web Application.

pepe
August 24, 2010

# re: LINQ To SQL and the Web.Config ConnectionString Value

Hello, I have this problem.
When I move the SPROC from DB to DBML file, autogenerating code from LINQ drop the empty constructor. For example

Before

 public DBFarmaciaDataContext() :
      base(global::System.Configuration.ConfigurationManager.ConnectionStrings  ["HospitalConnectionString"].ConnectionString, mappingSource)
    {
      OnCreated();
    }
 
    public DBFarmaciaDataContext(string connection) : 
            base(connection, mappingSource)
    {
        OnCreated();
    }

After

public DBFarmaciaDataContext(string connection) : 
            base(connection, mappingSource)
    {
        OnCreated();
    }

The firts method is gone, y ever must write again. You can help me, please.
Thank you, very match

Tom Wayson
October 08, 2010

# re: LINQ To SQL and the Web.Config ConnectionString Value

Thanks Rick! I was scratching my head on this one for a while.

I set the connection to (None) as you suggest, forcing clients of my .dll to provide a connection string, but I have found that each time you modify the data context it re-sets the connection properties. That is somewhat irksome, but I can live with it.

Luis
November 09, 2010

# re: LINQ To SQL and the Web.Config ConnectionString Value

this link is helpful as well:

http://goneale.com/2009/03/26/untie-linq-to-sql-connection-string-from-application-settings/

step by step to solve the issue of null exception

Phuoc
November 21, 2010

# re: LINQ To SQL and the Web.Config ConnectionString Value

Hi everybody
I used ClassLibrary project used LINQ to connect to database
I have also another project which are WPF application, in this I hava an App.config file with connectionString :

[code]
<configuration>
<connectionStrings>
<add name="FottecConnectionString"
connectionString="Data Source=MyComputer/MyDatabase;Initial Catalog=MyDatabaseName;User ID=sa;Password=myPassword"
providerName="System.Data.SqlClient"/>
</connectionStrings>

</configuration>
[/code]

I make an refference from my WPF project to LINQ project to read data
I need my LINQ project read ConnectionString from WPF project
Here is my code in LINQ project to get connetionString:
[code]
public AccountDataContextDataContext() :
base(global:: System.Configuration.ConfigurationManager.ConnectionStrings ["FottecConnectionString"].ConnectionString, mappingSource)
{
OnCreated();
}
[/code]
Is there any wrong with these code, it does not recognise the connectionString
Please help me to fix it.
Many thanks

Murray
December 16, 2010

# re: LINQ To SQL and the Web.Config ConnectionString Value

your first screenshot shows how you edit the Connection on the datacontext, I can't find this window in my VS2010 solution. I have a DBML file which doesn't have those properties.

Narling Ximena
December 29, 2011

# re: LINQ To SQL and the Web.Config ConnectionString Value

Hi Rick!

How could I connect two databases in SQL SERVER 2008 using linq to sql and c# in the file .dbml or my DataContext. I want to know if this is possible.

Also I have all my classes in a folder that includes the project.

I appreciate your help.

From: Narling

Rick Strahl
December 29, 2011

# re: LINQ To SQL and the Web.Config ConnectionString Value

@Narling - you need two separate DataContexts and .dbml files for that to work I think. You can possibly run raw data commands over ADO.NET on that same connection, but I don't think you can use LINQ in this scenario.

Chrys
August 09, 2013

# re: LINQ To SQL and the Web.Config ConnectionString Value

Ok so what do I put if I want my dbml inside my class library to use the connectionstrings in my web application web configs?

Alex Roosvelt Douglas Quispe Cuadros
May 02, 2019

# re: LINQ To SQL and the Web.Config ConnectionString Value

Thank You, the information is very good

Rick Strahl's Weblog