c# - 如何让 LINQ to SQL 使用在运行时被修改的连接字符串？

Question

我在尝试使用 Connection String Builders (ADO.NET) 时遇到了一些困难在 LINQ to SQL 中。让我向你们展示我正在尝试做的事情:

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"

我总是遇到异常:用户“joe”登录失败。有任何想法吗？非常感谢。

Answer 1

您似乎正在尝试修改存储在 app.config 文件中的连接字符串。当您为数据上下文使用无参数构造函数时，它会读取设计时配置的内容。

尝试将修改后的连接字符串注入(inject)到 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);
 } }