Click here to Skip to main content
Click here to Skip to main content

Tagged as

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

, 2 May 2011
Rate this:
Please Sign up or sign in to vote.
How to set the connection string in your LINQ dbml file dynamically based on web.config
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 has the effect of reducing your ability to easily configure your applications for different environments (i.e., you would need to have different compiled DLLs for dev, test and production).
 
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 and also do the following steps every time you will go for the drag and drop your stored procedure and user defined functions.
  1. Right click on any white space in dbml file.
  2. Click on properties option.
  3. Select drop down for Connection option in Properties window.
  4. Choose last option from drop down for Connection option which should be none.
  5. Close Properties window and press Save.
This allows you to define a default constructor in your own partial class that extends your dbml context designer classes like example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration; // For Reading Connection String from Web.Config
///
/// Th class for handling Common Connection String from Web.Config file by the Default Constructor for all DBML Files
///
namespace DMNxtGen.DataAccess
{
///
/// The class for handling Common Connection String from Web.Config file by the UserClassesDataContext Constructor.
///

public partial class UserClassesDataContext
{
public UserClassesDataContext()
: base(ConfigurationManager.ConnectionStrings["WebConnectionString"].ConnectionString, mappingSource)
{
OnCreated();
}
}
}
In the above example, UserClassesDataContext is the class name of the LINQ to SQL(DBML) file and WebConnectionString is the name of the Connection String in Web.Config.
 
Happy coding.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

maq_rohit a.k.a asthanarht
Technical Lead
United States United States
Rohit is a Lead Engineer from NCR. He started Embedded Programing in his college days and now he is a Software Developer by Profession. Mainly he interested in cutting edge technology offer by Microsoft (i.e Azure,MVC). He Loves coding and his passion is always been towards Microsoft Technologies. Apart from coding his other hobbies include reading books and hang out with friends is his most favorite past time hobby.
 

1. 20 Apr 2014: Best Mobile Article of March 2014 - First Prize
Follow on   Twitter

Comments and Discussions

 
QuestionWhat's wrong? Pinmembervahhab_samadi29-Nov-13 21:11 
QuestionPerfect Pinmemberbone krusher23-Oct-13 9:12 
AnswerRe: Perfect Pinmembermaq_rohit25-Nov-13 23:07 
GeneralMy vote of 5 PinmemberMember 96050076-Aug-13 4:21 
GeneralRe: My vote of 5 Pinmembermaq_rohit21-Aug-13 12:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 2 May 2011
Article Copyright 2011 by maq_rohit a.k.a asthanarht
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid