如何创建动态数据库连接字符串C# [英] How to create dynamic database connection string C#

问题描述

我刚刚使用 localhost 数据库创建了一个桌面Winforms应用程序.我正在使用的连接字符串是这样的:

I just created a desktop Winforms application with localhost database. The connect string I am using is this:

SqlConnection connect = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Administrator\Desktop\learningsystem\LearningSystem\LearningSystem\LearningSystem.mdf;Integrated Security=True");

如果我想在其他计算机上运行我的应用程序，应该如何使它工作?

If I want to run my application on other computers, how should I make it work?

解决方案感谢所有的帮助！我尝试了以下步骤.我认为它现在正在工作.但是，如果我做了一些棘手的事情，请纠正我.1.在项目属性设置中添加一个新的设置项.App.config将自动更新:

EDIT：SOLUTION Thank for all the help! I tried the following steps. I think it is working now. But please correct me if I did something tricky. 1. add a new setting item in project property setting. App.config will automatically update:

<connectionStrings>
    <add name="LearningSystem.Properties.Settings.LearningConn" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\LearningSystem.mdf;Integrated Security=True;Connect Timeout=30"
        providerName="System.Data.SqlClient" />
</connectionStrings>

2.在我的程序中，只需添加以下语句即可连接到sql server

2. In my program, just add the following statement to connect to the sql server

        SqlConnection connect = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename=|DataDirectory|\LearningSystem.mdf;Integrated Security = True; Connect Timeout = 30");

其他问题如果其他人将在其计算机上(而不是在同一网络中)运行此应用程序，则他们只是进入项目设置并通过选择我提供给他们的数据库文件来更改值，connectionString会自动更改，对吗?

Further question If others will run this application on their computer(not in the same network), they just go into the project setting and change the value by selecting the database file I provide to them,the connectionString will automatically change, right?

谢谢！

推荐答案

在应用程序中对此类内容进行硬编码通常不是一个好主意.通常，应用程序设置和连接字符串放置在应用程序的配置文件中(在ConnectionStrings部分中).

It's generally a bad idea to hard code such stuff in your application. Normally, application settings and connection strings are placed in the application's configuration file (in the ConnectionStrings section).

就像所有字符串一样，您可以从动态部分(变量，设置等)构建连接字符串，然后将生成的连接字符串传递给SqlConnection构造函数.同样，要使这些单独的部分可配置而不在应用程序中对其进行硬编码，您可能希望将它们添加到应用程序的配置文件中(在"AppSettings"部分中).但是恕我直言，在大多数情况下，这是一个过于复杂的解决方案.将整个连接字符串放在ConnectionStrings部分中会更简单(更灵活).

Just like with all strings, you could build your connectionstring from dynamic parts (variables, settings, etc.) and then pass that generated connectionstring to the SqlConnection constructor. Again, to make those separate parts configurable without hard coding them in your application, you might want to add them to your application's configuration file (in the AppSettings section). But IMHO this is an overly complex solution in most scenarios. Putting the entire connectionstring in the ConnectionStrings section is more straightforward (and more flexible).

无论如何，再次要使您的应用程序可配置，您可以使用应用程序的配置文件(App.config或Web.config)，您需要在项目的.NET Framework依赖项中添加对System.Configuration的引用，并使用系统.Configuration.ConfigurationManager 类.

Anyway, again, to make your application configurable, you might use your application's configuration file (App.config or Web.config), you need to add a reference to System.Configuration in your project's .NET Framework dependencies and use the AppSettings and ConnectionStrings properties of the System.Configuration.ConfigurationManager class.

(当然，还有更多方法可以使您的应用程序可配置.但是使用应用程序配置文件是最简单的解决方案之一.)

(Of course, there are more ways to make your application configurable. But using the application configuration file is one of the most straightforward solutions.)

将应用程序部署到另一台计算机时，您也需要复制其数据库.如果要在多台计算机上使用该应用程序并使它们连接到同一数据库，则可能要离开LocalDB并将数据迁移到SQL Server(Express)实例和

When deploying your app to another computer, you need to copy its database over too. If you want to use the application on multiple machines and let them connect to the same database, you might want to leave LocalDB and migrate the data to a SQL Server (Express) instance and make it accessible over the (local) network.

编辑2(关于帖子中的最新编辑):

我在步骤1中看到您现在正在解决方案中使用应用程序设置(称为LearningConn).没关系.但是，请务必在步骤2中使用该设置，就像这样:

I see in step 1 that you are using an application setting (called LearningConn) in your solution now. That's fine. However, it is important that you also use that setting in step 2, like this:

SqlConnection connect = new SqlConnection(Properties.Settings.Default.LearningConn);

如果在Visual Studio中更改设置，它将更新连接字符串.由于该设置可能具有应用程序范围，因此无法在运行时(由用户)更新应用程序中的设置/连接字符串.

If you change the setting in Visual Studio, it will update the connection string. Since the setting will probably have application scope, it will not be possible to update the setting/connection string within your application in runtime (by the user).

我不确定您的连接字符串是否使用| DataDirectory |.在所有情况下都将始终如期工作.我只在ASP.NET Web应用程序中使用过它.如果它在WinForms应用程序中确实有效，则您可以阅读此文档，以了解如何进行设置.但是我个人对这种方法有些怀疑.

I'm not sure if your connection string using |DataDirectory| will always work as expected in all scenarios. I have only been using it in ASP.NET webapplications. If it does work in WinForms applications, you might read this document to learn how to set it up. But personally I am somewhat sceptical about this approach.

我个人会选择在连接字符串中使用占位符的解决方案，在将其传递给SqlConnection构造函数之前，将其替换为.mdf文件的完整路径.

I personally would opt for a solution where you use a placeholder in your connection string, which you replace with the full path to the .mdf file before you pass it to your SqlConnection constructor.

例如，当您使用"{DBFILE}"作为占位符时，您的LearningConn设置的值将如下所示:

When you use "{DBFILE}" as the placeholder, for example, the value of your LearningConn setting would look like this:

数据源=(LocalDB)\ MSSQLLocalDB; AttachDbFilename = {DBFILE};已集成安全性=真；连接超时= 30

Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename={DBFILE};Integrated Security=True;Connect Timeout=30

(请注意，该值应该是一行，没有任何换行符！)

(Note that this value should be a single line without any line breaks!)

您可以在应用程序中创建一个名为 DbFile (类型为字符串)的单独设置，以存储应在连接字符串中代替{DBFILE}的实际值.当您将范围用户"用于该设置时，用户可能会在应用程序中更改该值.保存后，它可能不会直接保存在应用程序的配置文件中，而是保存在用户Windows用户配置文件中某处隐藏的其他配置文件中.您可能会阅读此文档了解有关应用程序设置的更多信息.

You might create a separate setting in your application called DbFile (of type string) to store the actual value that should be put in place of {DBFILE} in your connection string. When you use scope "user" for that setting, the value might be changed from within the application by the user. When saved, it might not be saved directly in the application's configuration file, however, but in an additional configuration file hidden somewhere in the user's Windows user profile. You might read this document to learn more about application settings.

您在步骤2中的代码最终可能看起来像这样:

Your code in step 2 might eventually look something like this:

string connectString = Properties.Settings.Default.LearningConn;
string dbFile = Properties.Settings.Default.LearningSystemDb;
connectString = connectString.Replace("{DBFILE}", dbFile);
SqlConnection connect = new SqlConnection(connectString);

要让应用程序的用户选择并存储要使用的数据库.mdf文件，您可以在应用程序中的某处包括以下代码(或其变体):

To let your application's users select and store the database .mdf file to use, you might include (a variation of) the following code in your application somewhere:

using (var dlg = new System.Windows.Forms.OpenFileDialog())
{
    dlg.Title = "Select database file to use";
    dlg.Filter = "Database Files (*.mdf)|*.mdf";
    dlg.CheckFileExists = true;

    if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        Properties.Settings.Default.DbFile = dlg.FileName;
        Properties.Settings.Default.Save();
    }
}

这篇关于如何创建动态数据库连接字符串C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！