如何在 .net 中以编程方式设置连接字符串配置？

How do I set a connection string config programmatically in .net?

提问by ripper234

I'd like to set a connection string programmatically, with absolutely no change to any config files / registry keys.

我想以编程方式设置连接字符串，绝对不更改任何配置文件/注册表项。

I have this piece of code, but unfortunately it throws an exception with "the configuration is read only".

我有这段代码，但不幸的是它抛出了“配置为只读”的异常。

ConfigurationManager.ConnectionStrings.Clear();
string connectionString = "Server=myserver;Port=8080;Database=my_db;...";
ConnectionStringSettings connectionStringSettings = 
  new ConnectionStringSettings("MyConnectionStringKey", connectionString);
ConfigurationManager.ConnectionStrings.Add(connectionStringSettings);

Edit:The problem is that I have existing code that reads the connection string from the configuration. So setting the config string manually, or through a resource, don't seem like valid options. What I really need is a way to modify the configuration programmatically.

编辑：问题是我有从配置中读取连接字符串的现有代码。因此，手动或通过资源设置配置字符串似乎不是有效的选项。我真正需要的是一种以编程方式修改配置的方法。

回答by David Gardiner

I've written about this in a post on my blog. The trick is to use reflection to poke values in as a way to get access to the non-public fields (and methods).

我已经在我的博客上的一篇文章中写到了这一点。诀窍是使用反射来插入值作为访问非公共字段（和方法）的一种方式。

eg.

例如。

var settings = ConfigurationManager.ConnectionStrings[ 0 ];

var fi = typeof( ConfigurationElement ).GetField( "_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic );

fi.SetValue(settings, false);

settings.ConnectionString = "Data Source=Something";

回答by Rupert Davis

I was looking for the answer to the same qustion about allowing the user to amend the connection string in a click once application by selecting a local SQL Server.

我一直在寻找关于允许用户通过选择本地 SQL Server 在单击一次应用程序中修改连接字符串的同一问题的答案。

The code below displays a user form which contacts all the locally available SQL Servers and allows them to select one. It then constructs a connection string for that sever and returns it from a variable on the form. The code then amends the config files AND SAVES IT.

下面的代码显示一个用户表单，它联系所有本地可用的 SQL Server 并允许他们选择一个。然后它为该服务器构造一个连接字符串，并从表单上的一个变量中返回它。然后代码修改配置文件并保存它。

string NewConnection = "";
// get the user to supply connection details
frmSetSQLConnection frm = new frmSetSQLConnection();
frm.ShowDialog();
if (frm.DialogResult == DialogResult.OK)
{
    // here we set the users connection string for the database
    // Get the application configuration file.
    System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
    // Get the connection strings section.
    ConnectionStringsSection csSection = config.ConnectionStrings;
    foreach (ConnectionStringSettings connection3 in csSection.ConnectionStrings)
    {
        // Here we check for the preset string - this could be done by item no as well
        if (connection3.ConnectionString == "Data Source=SQL204\SQL2008;Initial Catalog=Transition;Integrated Security=True")
        {
             // amend the details and save
             connection3.ConnectionString = frm.Connection;
             NewConnection = frm.Connection;
             break;
        }
    }
    config.Save(ConfigurationSaveMode.Modified);
    // reload the config file so the new values are available

    ConfigurationManager.RefreshSection(csSection.SectionInformation.Name);

    return clsDBMaintenance.UpdateDatabase(NewConnection))
}

回答by NotNormal

Another way to approach this would be to operate on the collection directly:

解决此问题的另一种方法是直接对集合进行操作：

var settings = ConfigurationManager.ConnectionStrings;
var element = typeof(ConfigurationElement).GetField("_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);
var collection = typeof(ConfigurationElementCollection).GetField("bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);

element.SetValue(settings, false);
collection.SetValue(settings, false);

settings.Add(new ConnectionStringSettings("ConnectionStringName", connectionString));

// Repeat above line as necessary

collection.SetValue(settings, true);
element.SetValue(settings, true);

回答by Rebecca

I find that this works for me:

我发现这对我有用：

Configuration config = WebConfigurationManager.OpenWebConfiguration("~");
ConnectionStringsSection section = config.GetSection("connectionStrings") as         ConnectionStringsSection;
if (section != null)
{
    section.ConnectionStrings["MyConnectionString"].ConnectionString = connectionString;
    config.Save();
}

This overwrites an existing connection string.

这会覆盖现有的连接字符串。

回答by Jamal Hansen

I'm currently using dependency injection to handle different connection strings in dev/prod vs. test environments. I still have to manually change the webconfig if I want to move to between dev and prod, but for testing I have an IConnectionStringFactory interface with a default implementation that looks at the web config and an alternate testing configuration that returns static values. That way when I'm testing I simply set the factory to the testing implementation and it will return the testing connection string for the key I ask for. Otherwise it will look in the webconfig.

我目前正在使用依赖注入来处理开发/生产与测试环境中的不同连接字符串。如果我想在 dev 和 prod 之间移动，我仍然需要手动更改 webconfig，但是为了测试，我有一个 IConnectionStringFactory 接口，它有一个默认实现，它查看 web 配置和一个返回静态值的备用测试配置。这样，当我进行测试时，我只需将工厂设置为测试实现，它就会返回我要求的密钥的测试连接字符串。否则它将在 webconfig 中查找。

I could extend this to another implementation for dev vs. prod but I'm more comfortable having a single implementation of IConnectionStringFactory in my production assembly and the testing implementation in my testing assembly.

我可以将此扩展到 dev 与 prod 的另一个实现，但我更愿意在我的生产程序集中使用 IConnectionStringFactory 的单一实现，并在我的测试程序集中使用测试实现。

回答by Christian.K

In addition to the other answers given, and assuming the connection string is not simply another configuration variable or constant as a whole, you might consider using SqlConnectionStringBuilderclass instead of directly concatenating the string together.

除了给出的其他答案之外，假设连接字符串不仅仅是另一个配置变量或常量，您可能会考虑使用SqlConnectionStringBuilder类而不是直接将字符串连接在一起。

EDIT: Ups, sorry just saw that you basically want to read your connection string (complete I guess) from another source.

编辑：UPS，抱歉刚刚看到您基本上想从另一个来源读取您的连接字符串（我猜是完整的）。

回答by Robert S.

You could put it in a resources file instead. It won't have the built-in features of the ConfigurationManager class, but it will work.

你可以把它放在一个资源文件中。它不会具有 ConfigurationManager 类的内置功能，但它会起作用。

Assuming Resources.resx:

假设 Resources.resx：

Resources.Default.ConnectionString = "Server=myserver;" // etc

Resources.Default.ConnectionString = "Server=myserver;" // etc

Then in your code:

然后在你的代码中：

conn.ConnectionString = Resources.Default.ConnectionString

conn.ConnectionString = Resources.Default.ConnectionString

It's a hack, I know.

这是一个黑客，我知道。

回答by Andrew McClellan

Looks like the naming was changed as of .net Core 2.1 Modifying David Gardiner's answer This way should work for referencing new and old versions:

看起来命名已更改为 .net Core 2.1 Modifying David Gardiner's answer 这种方式应该适用于引用新旧版本：

var settings = ConfigurationManager.ConnectionStrings[ 0 ];

var settings = ConfigurationManager.ConnectionStrings[0];

var fi = typeof( ConfigurationElement ).GetField( "_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic );

if(fi == null)
{
  fi = typeof(System.Configuration.ConfigurationElementCollection).GetField("_readOnly", BindingFlags.Instance | BindingFlags.NonPublic);
}

fi.SetValue(settings, false);

settings.ConnectionString = "Data Source=Something";

回答by devio

ConfigurationManager is used to readfrom the config file.

ConfigurationManager 用于从配置文件中读取。

Your solution is to simply set conn.ConnectionString to the conn string you need.

您的解决方案是简单地将 conn.ConnectionString 设置为您需要的 conn 字符串。