How can I make LINQ to SQL use a connection string which is being modified at runtime?

ado.net, app-config, c#, linq-to-sql, sql-server

Solution

It seems like you are trying to modify the connection string that is stored in the app.config file. When you use a no argument constructor for your data context, it reads what was configured at design time.

Try injecting your modified connection string into the constructor of the DataContext:

ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["LoremIpsum"];
SqlConnectionStringBuilder builder;
LINQTOSQLDataClassDataContext db;

if (null != settings) 
{   
    string connection = settings.ConnectionString;  
    builder = new SqlConnectionStringBuilder(connection);

   // passwordTextBox being the control where joe the user actually enters his credentials

    builder.Password =passwordTextBox.Text;  
    db = new LINQTOSQLDataClassDataContext(builder.ConnectionString);
 } }

Problem

I'm experimenting some difficulties trying to use Connection String Builders (ADO.NET) within LINQ to SQL. Let me show you guys what I'm trying to do: the app.config file: ``` <?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> </configSections> <connectionStrings> <add name="LoremIpsum" connectionString="Data Source=SomeServer;Initial Catalog=SomeDB;User ID=joe;" providerName="System.Data.SqlClient" /> </connectionStrings> </configuration> ``` and a snippet of the form: ``` ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["LoremIpsum"]; if (null != settings) { string connection = settings.ConnectionString; SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connection); // passwordTextBox being the control where joe the user actually // enters his credentials builder.Password = passwordTextBox.Text; } LINQTOSQLDataClassDataContext db = new LINQTOSQLDataClassDataContext(); // finally some rather anecdotic LINQ sentence here: var foo = db.Table.Single(bar => bar.Table == whatever); ``` On the other hand checking the Immediate Window: ``` ?builder.ConnectionString "Data Source=SomeServer;Initial Catalog=SomeDB;User ID=joe;Password=swordfish" ``` I'm always getting an exception: Login failed for user 'joe'. Any ideas? Thanks much in advance.

Original source