Did you know that by default LINQ to SQL defines your connection string in more than one place if you define your LINQ to SQL classes out from a web app, such as a data access layer class library?
- Under “Application Settings” stored as Settings.settings and compiled in with your code rendering it unchangeable without recompilation and making it very easy to be inspected upon via reflection.
- Stored in Web.config / App.config as a reference copy from “Application Settings” in making it very disconcerting as to the true location it is picked up from.
- Hard coded in the “.dbml” file of the
<Connection />node as clear text.
Three places?! For standalone web applications it’s not so bad, as it turns out it is still stored in the Web.config and hard coded to the .dbml file, but the connection string is changeable on the fly and it does not store any strings which are baked in.
However if you wish to setup a layered n-tier architecture with your LINQ to SQL classes residing in a separate project, you will suffer the compilation “gotcha”. Point 1 was a particular problem for me as I needed to update the connection string after compilation with an NAnt build script for deployment to a testing server. This obviously wasn’t acceptable, as it did not honour changes easily between different database configuration across multiple environments (dev, test, production) so I needed another solution.
Thankfully there was one, and it will shorten that list down (you will still be stuck with a local “.dbml” conn. string copy which appears to pose no deployment implication as it simply tells the LINQ to SQL designer which DB you are working with, which is good).
Remember the below instructions are if you have a separate project holding your LINQ to SQL classes or you your connection string is being compiled into application settings (potentially anything but a Web application?) What we want to do is first turn off application wide settings effectively freeing it from LINQ to SQL: -
- Open up the LINQ to SQL designer, and open the Properties tab of the designer (the schema itself), expand Connection and set Application Settings to False. Save. Don’t you feel better already?
- Close that down and open up your DataContext designer file (dbml_name.designer.cs) and alter the DataContext constructor. You will immediately notice how your connection string decided to jump in here as you turned off application wide settings. So the part to focus on here is altering the
base()inheritor. Renaming “MyConnString” below to suit your own. I also noticed aDatabaseAttributeon the class which I don’t think plays a big part and has any implications on the connection settings. You will also need a reference to System.Configuration:public dbDataContext() : base(ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString, mappingSource) - Open the App.config or Web.config featured in the project where your LINQ to SQL classes reside, and rename the connection string to what you defined as “MyConnString“.
- You now must Cut the entire
<connectionStrings />entry with name change and Paste it into either the App.config or Web.config of the application which is to access the data, such as a web application, Silverlight, WPF, WCF etc. It is important that you alter the configuration file of the calling application which is to access the data, as the ConfigurationManager defined in your LINQ to SQL classes will look for the .config file from where the calling application is executing from, no matter where your LINQ to SQL classes have been defined. As you can see, it works a little differently from before. - Now Right Click and open the Properties on your DAL or project containing your LINQ to SQL classes and remove the connection string “Application Setting” reference on the Settings tab.
- Rebuild. You’re all done, now just do a Find in Files check for perhaps your database name that you know was featured in the connection string to check for any stragglers, there shouldn’t be any.
Now you can alter your configuration of App.config / Web.config at your discretion without fear that the connection string is embedded somewhere nasty and won’t be picked up!
33 Comments
What if i want to access the connectionString from more than one project?
How can i acess the config file of Project2 from Project1?
exactly the same issue i faced. thanks for the resolution!
@bhatty, no problem! Glad to help, it was a really annoying problem!
hey I tried your solution but as soon as I manually change the DBML in the designer any of the edits you mention are erased.
Instead I used a partial calss to the datac context:
namespace Campbells.Infrastructure.DAC
{
public partial class userDataContext : System.Data.Linq.DataContext
{
public userDataContext()
: base(ConfigurationManager.ConnectionStrings["Cam"].ConnectionString, mappingSource)
{
OnCreated();
}
}
}
@jon: and you don’t receive “Member with the same signature already declared”? I think you would as your other declaration would be:
public DB() : base("Data Source=..........", mappingSource)both taking string and MappingSource.
and I imagine if you have removed the auto-gen’d statement from your .designer.cs file, then you would be back to square one as you were saying if you manually adjust the .dbml file.
If you remove all the connection gook then there is no default parameterless constructor for the data context generated. The partial method implements the default empty constructor.
Forgot to say. thanks for your help!
Yes but what I am saying is if you remove it (the default generated one), allowing your new partial with new constructor to be invoked, it will fail when the old constructor is re-build next generation.
No probs
Hard to talk about this stuff eg? No I dont modify the designer.cs cant do that else you lose edits if you switch to design mode. But “connection gook: is not a technical term. what I implement was I followed your advice and removed that stuff from the properties of the data context and set the “useappsettings” to false. then the generated code did not have the parameterless constructor that my partial class declares. (it compiles and runs) and I can change my DC in design mode………
I was struggling with this problem and thanks to both Graham and jon I not only have a solution that works but using the partial class approach one that is elegant too. Many thanks.
Splendid solution, thanks alot for posting it!
Hi,
I’m working in VB.NET and the properties window will not let me get rid of the connection string. How can I implement Jon’s solution?
Thanks
R
I’ve used the constructor with the connection string as follows, solving my problem:
Dim ctx As WhateverDBDataContext
ctx = new WhateverDBDataContext(ConfigurationManager.AppSettings(“whateverDBConnectionString”))
Cool dude
I have been looking for a good solution for a couple of days (as i am new to this). This post took me through step by step. Thanks so much.
This is very useful. I try this and hit you back for results.
Thanks
When i change the datacontext read from appsettings. the designer.cs disappears.
Nice one mate, I’d been looking for something like this.
@jon (or anybody who got it to work) – I’m trying to implement the solution you proposed of putting the parameterless constructor in the partial class. I’ve tried a few different things, including stuff that I didn’t think would otherwise be a great idea, and I can’t get it to work. The auto-generated parameterless constructor always comes back and I get compiler errors.
Would somebody mind giving an updated version of the steps they took to make this work? I’ve tried everything mentioned in people’s comments as well as I could and it hasn’t worked.
Thanks!
@sean – Have you made sure that you have set Application Settings to “False” (under Connection section) in the Properties tab of the LINQ to SQL designer? (I also set Connection to “(None)” in the drop-down list to be sure) This is what should prevent Visual Studio auto-generating a parameterless constructor.
I am using VS 2008. If you do as you suggest, when you drag a new table onto the dbml the parameterless constructor is regenerated. It appears after adding the new table the designer re-adds the Connection string in the properties. (I had it set to ‘None’)
Awesome. I had this problem before but couldn’t remember how to solve it.
Many thanks
why not just comment out the constructor for the DBML that has no connection string argument,
i.e.
Public Sub New()
MyBase.New(……)
OnCreated
End Sub
since it is the one that uses application settings and the other constructors use the connection string. instead of complicating your life.
Ritchieh.sd
Great step by step, my friend..! Just as what I need. Really helpful
Thanks for the original post and follow-ups. I was able to get it to work using the partial class method, with one additional step: every time I add a table to the designer, I have to go in and remove the static connection string from the DataContext properties window. A bit of a pain, but not a horrible one, especially given the benefits of having the connection string out of my data layer.
I am using this but sqlMetal stomps it. Anybody know where \ what file “Application Settings = Flase” is kept in?
Thank you for this solution. I had exactly the same problem with my 3-tier application.
On my local computer everything worked fine, but when I changed the connectionString on the test server it couldn’t make the connection (because test server was in different network).
Thanks again! I’ve saved this page to my favorites.
Jon’s solution is the best.
I have been using the solution at the top of this blog article for many months, but it became quite dangerous in a production environment, because occasionally I would rebuild and deploy the project without remembering to change the designer.cs file. Then suddenly production is down because it can’t find the database.
With Jon’s solution, the partial class remains totally intact permanently, even after you make any desired edits to the DBML designer. No problem, because the partial class is in a separate file and stays there regardless.
The trick is “removing all the connection gook” which eliminates the “member already declared problem”.
Thanks Graham for raising an important topic with a solid solution and Jon for putting the icing on the cake.
Now I have zero issues with this and can deploy to production any time fear free, my connection strings just *work* !
Hi,
Thanks a lot for this solution!!!
I have made my site working properly.
I have used only two steps of your solution and it is working fine now.
Over the past 3 days I’ve had a slew of issues deploying an MVC project to IIS because I naively expected the Linq to SQL class builder to play nice. I changed my web.config and app.config, but kept getting a cryptic “can’t connect to database” error. After 2+ days of fruitless Googling, I finally discovered that my local connection string (using my local machine name) was hard-coded into the SQL classes. No wonder the production server couldn’t establish a connection!
I fixed all of the hard-coded references, but doing a massive search/replace every time I built was a pain. Your solution is far more elegant. Now I just need to manage my application’s web.config and my DAL’s app.config, both of which can be handled with XDT transformations tied to my build process.
Thank you for putting together an excellent resource!
I’m not a LINQ to SQL pro, but why don’t you just use the DataContext constructor which takes the connection string as a parameter? It’s already auto generated by the designer in the Whatever.designer.cs
public WhateverDataContext(string connection) :
base(connection, mappingSource)
{
OnCreated();
}
I mean, OK, you’ll probably forget to delete the ‘plain’ CS for your developing machine from time to time if sth has changed in your designer, but if you just need to use dynamic database credentials use the constructor provided for that.
There are 5 constructors for the DataContext, there has to be one that fits your needs.
Kind regards, Mariano.
Many thanks!
This was very helpful and saved a lot of headaches. Thanks!
Post a Comment