LINQ to SQL connectionstring

Question

I have an application that I want to be able to configure the connection string for my LINQ to SQL. I've tried so many different ways but cannot seem to get it working. I want to do this dynamically in the code when the app is run, the reason for this is that the user can change the connection settings.

If I delete the connectionString out of app.config the application still works OK (communicating) which makes me wonder where I should be changing the connection string?

When you say you're deleting the connection string from `app.config`, are you then running this from Visual Studio, or just straight from the executable? — Graham Clark, Feb 21 '11 at 17:12

score 9 · Answer 1 · answered Feb 21 '11 at 17:19

9

You can pass an override connection string into the DataContext constructor:

var db = new MyDataContext("Data Source=Something Else;")

answered Feb 21 '11 at 17:19

Rup

33,765
9
83
112

How would I go about setting the connection string in app.config in code? – Prisoner Feb 21 '11 at 20:08
Sorry I missed the extra question - no, I'm not proposing you change it in app.config at all. You can use this constructor to feed in any string obtained from anywhere. – Rup Feb 22 '11 at 14:07

Francisco Noriega · Accepted Answer · 2013-02-14T20:19:42.760

9

I think that the best way to do it is a combination of Albin's and Rup's answers. Have a value in the config file, and then read it at run time and feed it to the context constructor, something like this:

WEB.CONFIG:

<appSettings>
<add key="ConString" Value="The connection string" />

CODE:

//read value from config
var DBConnString = System.Configuration.ConfigurationManager.AppSettings("ConString");

//open connection
var dataContext= new MyDataContext(sDBConnString)

this way you can change the connection string even at runtime and it will work and change on the running program.

edited Feb 14 '13 at 20:19

answered Feb 21 '11 at 17:28

Francisco Noriega

13,725
11
47
72

How would I go about setting the connection string in app.config in code? – Prisoner Feb 21 '11 at 20:03
just like the connection string that you said you deleted, it would depend on the provider you are using. for mssql I use something like `server=ip;uid=user_id;pwd=passw;database=database_name` – Francisco Noriega Feb 21 '11 at 20:47
well you manually specify where you want it to connect in the config, then it will automatically connect there through code with the context constructor – Francisco Noriega Feb 21 '11 at 20:59
Yes, I realise this but I want to be able for the user to change the connection string that is in the app.config file whilst the program is running – Prisoner Feb 21 '11 at 21:13
1

oh! well in that case you would need to just get the string (or values and setup the string) from the user (like choosing from a combobox) and then use in in the context constructor. – Francisco Noriega Feb 21 '11 at 21:33
Hi, for creating a connectionString in the code use SqlConnectionStringBuilder class. After constructing it properly pass its connectionString property to the DataContext class at creating time as it was already suggested here. – ChristoD Feb 13 '13 at 09:04

score 2 · Answer 3 · answered Feb 21 '11 at 17:23

The DBML class (YourDataContext) has an overloaded constructor which takes ConnectionString, so try instantiating that instead of the default one.Get the connection string from app.config and use that to create the instance.

YourDataContext context = new  YourDataContext (ConfigurationManager.ConnectionStrings["ConnStringInAppConfig"].ConnectionString)

score 0 · Answer 4 · answered May 19 '17 at 20:14

By Default your constructor look like this

public dbDataContext() : 
            base(global::invdb.Properties.Settings.Default.Enventory_4_0ConnectionString, mappingSource)
    {
        OnCreated();
    }

You can change return value Instead of

//Original 
public string Enventory_4_0ConnectionString {
            get {
                return ((string)(this["Enventory_4_0ConnectionString"]));
            }
        }

this

//Modified code
public string Enventory_4_0ConnectionString {
            get {
                return (System.Configuration.ConfigurationManager.ConnectionStrings["Enventory_4_0ConnectionString"].ConnectionString);
            }
        }

score 0 · Answer 5 · answered Feb 21 '11 at 17:17

0

You should change it in app.config. The reason it works without is that the LINQ2SQL designer creates a fallback to the connection string used when designing the DBML. If you define a connection string in app.config that is used instead.

answered Feb 21 '11 at 17:17

Albin Sunnanbo

46,430
8
69
108

score 0 · Answer 6 · answered Jul 18 '19 at 13:44

Inside your dbml file designer.cs add this dynamic call to base class constructor. It will work for local, dev and prod automatically pulling from current web.config without need to pass connection every time;

    public HallLockerDataContext() : 
    base(ConfigurationManager.ConnectionStrings["MYDB1"].ConnectionString, mappingSource)
    {
        OnCreated();
    }

Usage:

    using (var db = new HallLockerDataContext())
    {

    }

LINQ to SQL connectionstring

6 Answers6

Linked