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:
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.
Other Posts you might also like
The Voices of Reason
# re: LINQ To SQL and the Web.Config ConnectionString Value
# re: LINQ To SQL and the Web.Config ConnectionString Value
Bla-Bla-DataContext dataContext = Bla-Bla-DataContext.GetInstance();
# re: LINQ To SQL and the Web.Config ConnectionString Value
# re: LINQ To SQL and the Web.Config ConnectionString Value
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
# re: LINQ To SQL and the Web.Config ConnectionString Value
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
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
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
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
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
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
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
# re: LINQ To SQL and the Web.Config ConnectionString Value
# re: LINQ To SQL and the Web.Config ConnectionString Value
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
# 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
# re: LINQ To SQL and the Web.Config ConnectionString Value
# re: LINQ To SQL and the Web.Config ConnectionString Value
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
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
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
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
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
# re: LINQ To SQL and the Web.Config ConnectionString Value
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
# re: LINQ To SQL and the Web.Config ConnectionString Value
# re: LINQ To SQL and the Web.Config ConnectionString Value
Thank You, the information is very good
# re: LINQ To SQL and the Web.Config ConnectionString Value
If I make any changes to my DataContext like adding table or anything I have to redo everything. I don't feel like I had this issue in older versions of visual studio like 2019 but maybe just me.
# re: LINQ To SQL and the Web.Config ConnectionString Value