Monday 18 February 2008

How to set the connection string in your LINQ dbml file dynamically (based on web.config)

I've used this technique in my last 3 projects. Ideally, you want your LINQ database connection strings defined outside your compiled code and to be held in the Web.Config instead.

Unfortunately, by default, they are added to your Settings.cs file which is then compiled away into your code. This does the following:



  1. reduces your ability to easily configure your applications for different environments (ie you would need to have different compiled dlls for dev, test and production).

  2. It is also LESS secure because you can easily use reflection to examine your dlls for passwords - whereas you can encrypt the web.config so it is only viewable to people who have permissions to the IIS console.This is more difficult to break.

The recommendations to remedy this issue in the following MSDN blog http://blogs.msdn.com/jongallant/archive/2007/11/25/linq-and-web-application-connection-strings.aspx is slightly wrong. He suggests that you remove the default constructor in the designer. This is bad because you would have to fix up the file every time you regenerate your dbml file. Instead, you should use what is provided to you and set the property on the designer for "Application Settings" to false.






This then allows you to define a default constructor in your own partial class that extends your dbml context designer classes:



//

// Copyright (C) 2007 ddkonline

// All rights reserved.

// http://www.ddkonline.com

//

// File - InvestmentManagementDb.cs

// Author – David Klein

// Date Created – 18/02/2008

//

// History of changes

// ---------------------------------------------------------------------------------------

// Version Author Date Jira No.

// 1.1 David Klein 18/02/2008 LPP-59

//

// Description of changes

// ---------------------------------------------------------------------------------------

// Version Description

// 1.1 DBML now reads from Web.Config via ConfigManager.ConnectionStrings

// ---------------------------------------------------------------------------------------



using System.Configuration;

using ddkonline.InvestmentManagement.Domain.Properties;

using System;



namespace ddkonline.InvestmentManagement.Domain.Entity

{

public partial class InvestmentManagementDataContext

{

public InvestmentManagementDataContext()

: base(ConfigurationManager.ConnectionStrings["InvestmentManagementConnectionString"].ConnectionString, mappingSource)

{



}







}

}

27 comments:

Unknown said...

This looks nice but I can't clear the Connection property (or set to none).
As long as there is something set the default constructor is always generated.

Unknown said...

Hi,

I have used the constructor like you did in your example. And then the connection string in the designer is set to the connection string that is read from the web.config file. Is this just for disply purpose or what is this used for? If the connection string changes runtime I assume that it is read from the web.config file when the constructor runs... And that it not used a connection string that is set desgined time, E.g. like the Connection string in the designer?

Unknown said...

Hi,

It is not setting the Application Settings to False that removes the genereated constructor when generating the designer file. Its setting the Connection to None.

Big Al said...

Cheers for this David, it has really saved us some time (took over a day to finally get to your post). All please beware that the error you get will relate to remote connections and named pipes which sends you off on a bit of a red herring!

Have a deadline to meet for the end of May and could still have been pondering over this by then had we not found your post!

Rob Packwood said...

This method doesn't work for me. How do you prevent the default constructor from generating? I know you mentioned the Applications Property set to "false", which is what I have tried with no luck. Whenever I define a default constructor on the empty partial class it conflicts with the generated default constructor.

Rob Packwood said...

As a follow up comment to my previous one... the default constructor is added whenever I add a table. How do you prevent that from occurring?

David Klein said...

Guys, it is a pain - but looks like you just have to set the connection string to "None" to remove the default constructor each time you add tables through the designer.

I get around the issue with the designer by not using it at all. Instead, we use SQLMetal to completely regenerate the file each time and a powershell script to fix up any naming that we don't like as per http://ddkonline.blogspot.com/2008/06/using-power-of-powershell-to-configure.html.

This also resolves the issue when developing in teams of not knowing who has the 'master' version of the dbml - it always comes straight from the database. Additional modifications are done in the partial classes only.

Unknown said...

So, I don't get it (well, I guess I could say that "I get it" because the implementor of this fiasco is Microsham).



Who does Microsham think they are in changing how WE manage OUR code, and propagating cleartext userid and password information in this new website file (.dbml)?



This is outrageously ridiculous to be forced upon programmers who have been programming with the config connString info in the web.config file, encrypted at that, for 7+ years. Sheesh!

Anonymous said...

grt..it worked for me....saved a lot of time..many thanks

fevster said...

I might be a complete n00b here but I can't even get to the Application Settings in the properties window! Any help?

Anonymous said...

fevster

Haha i had the same problem!
Open up the dbml designer and in the properties window you should have 3 "section headings":

- Code Generation
- Data
- Connection

If you click the + next to connection you will see the Application Settings setting you are after.

Anonymous said...

Great work Dave. Worked like a charm for me.

Anonymous said...

This is a conceptual problem. VS assumes that you use the same connection in design-time and in run-time. Ridiculous, especially when code is shared among more developers.
Why can't the DBML just point to an app.config or settings file entry to use for editing the DBML schema? Or simply store the local connection string in the .suo file or somewhere else, that is not under source control?

Best World of Warcraft Gold Guide said...

great post man :)

Anonymous said...

in dbml you can specify a different BaseClass

public class DataContextBase : System.Data.Linq.DataContext
{
private static string overrideConnectionString = ConfigurationManager.ConnectionStrings["CString"].ConnectionString;
public DataContextBase() : base(overrideConnectionString) { }
public DataContextBase(string connectionString) : base(overrideConnectionString) { }
public DataContextBase(string connectionString, System.Data.Linq.Mapping.MappingSource mappingSource) : base(overrideConnectionString, mappingSource) { }
public DataContextBase(IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base(connection, mappingSource) { }
}

Anonymous said...

Help needed. I can set the ApplicatonSettings to False in VS 2008; however, I cannot clear the existing ConnectionString property to None.

So it puts a static string into the contructor instead:

public SeMsReportsDataContext() :
base("Data Source=sql02;Initial Catalog=sems;Integrated Security=True", mappingSource)
{
OnCreated();
}

Anonymous said...

Thank you very much!

Funka! said...

Your site's design is cropping your code; it is too narrow to read the complete line in a few places. Consider setting overflow on your main-wrapper div to auto instead of hidden!

Anonymous said...

Cheers

Englestone said...

Thanks,

This was really starting to bug me.

-- Lee

Anonymous said...

It is extremely interesting for me to read this blog. Thank you for it. I like such themes and anything connected to them. I would like to read more on that blog soon.

Anonymous said...

Nice to meet you!!!
[URL=http://superjonn.50webs.com/town-nyc-restaurant-week-menu.html]town nyc restaurant week menu[/URL]

Srividhya said...

Hi, i wanted to try the same using machine.config. But getting errors while trying to do so.Do i need to remove the code from designer.cs file??

Anonymous said...

I ran across this and had to do one additional thing. Since the settings file was already created, I had to delete it. Then it worked perfectly, thanks!

No Spam Please said...

How about leaving everything as they are generated by VS, but instead of using the constructor with no parameters, call the one which passes the connection string as the parameter?

You can then use ConfigurationManager to retrieve it from web.config before calling this constructor.

SAlman said...

Really helpful post

Dustin N Pierce said...

The look and feel of your website greatly appeal to me. It's quite pleasing to the sight, which makes coming here and visiting more frequently much more enjoyable for me. I recently went to a profile regarding how to react fast in games ? Visit reaction time test and share your thoughts if you wish to read the entire article.