Contact   •   Products   •   Search

Rick Strahl's Web Log

Wind, waves, code and everything in between...
ASP.NET • C# • HTML5 • JavaScript • AngularJs

LINQ To SQL and the Web.Config ConnectionString Value


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.

Make Donation
Posted in ADO.NET  ASP.NET  LINQ  


Feedback for this Post

 
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Mike December 07, 2007 @ 10:22am
Oh I remember this from typed datasets, exactly the same story...
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Tim December 07, 2007 @ 11:19am
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?
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Art Shayderov December 10, 2007 @ 1:24pm
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();
# re: LINQ To SQL and the Web.Config ConnectionString Value
by bodya March 05, 2008 @ 4:31am
Thank You very very!!! Your article help me to fix problem with connection strings.
# Using the connection strings in your web.config for LINQ
by Arke Systems Blog March 05, 2008 @ 3:01pm
Using the connection strings in your web.config for LINQ
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Keith G May 15, 2008 @ 10:33pm
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.
# re: LINQ To SQL and the Web.Config ConnectionString Value
by TriTim May 26, 2008 @ 11:17am
Rick - This saves me much work. Many thanks.
# LINQ To SQL and the Web.Config ConnectionString Value
by DotNetKicks.com June 06, 2008 @ 7:48am
You've been kicked (a good thing) - Trackback from DotNetKicks.com
# LINQ to SQL, Web.config and Connection Strings
by Galin Iliev [Galcho] Blog! July 09, 2008 @ 1:50pm
# re: LINQ To SQL and the Web.Config ConnectionString Value
by jparlato August 07, 2008 @ 4:45pm
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.
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Travis October 04, 2008 @ 4:19pm
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
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Chris November 05, 2008 @ 11:13pm
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
# re: LINQ To SQL and the Web.Config ConnectionString Value
by MarkD March 28, 2009 @ 3:25pm
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.
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Sam Schutte May 18, 2009 @ 7:48am
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.
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Rick Strahl May 18, 2009 @ 11:04am
@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.
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Zyrus Khambatta May 29, 2009 @ 1:30am
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. =)
# Using Linq to Sql and connection strings in a class library
by Scott's e-commerce adventures June 04, 2009 @ 8:59pm
Using Linq to Sql and connection strings in a class library
# re: LINQ To SQL and the Web.Config ConnectionString Value
by khanhpt July 05, 2009 @ 9:36pm
thanks you very much.
# re: LINQ To SQL and the Web.Config ConnectionString Value
by anwar July 22, 2009 @ 11:20pm
Hey
I'm getting this error Object reference not set to an instance of an object
# re: LINQ To SQL and the Web.Config ConnectionString Value
by El Guapo September 02, 2009 @ 1:48pm
Good job, thanks
# re: LINQ To SQL and the Web.Config ConnectionString Value
by JPMattis September 14, 2009 @ 12:22am
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
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Chris January 26, 2010 @ 5:38am
Thanks Rick! Just saved me some MSDN searching :)
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Mark February 18, 2010 @ 6:48am
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.
# re: LINQ To SQL and the Web.Config ConnectionString Value
by pepe August 24, 2010 @ 5:50am
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
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Tom Wayson October 08, 2010 @ 11:38am
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.
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Luis November 09, 2010 @ 9:38am
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
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Phuoc November 21, 2010 @ 12:22am
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
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Murray December 16, 2010 @ 3:15pm
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.
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Narling Ximena December 29, 2011 @ 7:49am
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
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Rick Strahl December 29, 2011 @ 12:02pm
@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.
# re: LINQ To SQL and the Web.Config ConnectionString Value
by Chrys August 09, 2013 @ 2:48pm
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?
 


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2014