CODEWITHSUNDEEP
c#sql-serverlinq-to-sqlado.netapp-config
Nano Taboada
Nano Taboada

Reputation: 4182

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

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.

Upvotes: 7

Views: 29301

Answers (3)

Sean Aitken
Sean Aitken

Reputation: 1207

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);
 } }

Upvotes: 11

Ostemar
Ostemar

Reputation: 5808

You're forgetting to send in the connectionstring to the DataContext constructor.

Example:

LINQTOSQLDataClassDataContext db = new LINQTOSQLDataClassDataContext(builder.ConnectionString);

Upvotes: 6

jason
jason

Reputation: 241611

You can force a DataContext to use a specific connection string with

DataContext db = new DataContext(myConnectionString);

The parameterless DataContext constructor will use a connection string from the App.config file first, then the connection string set at compile time.

Upvotes: 4

Related Questions