sql server - How to create dynamic database connection string C#

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?

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:

    
        providerName="System.Data.SqlClient" />

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

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?

Thanks!

Answer

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).

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).

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.)

Edit:

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.

Edit 2 (regarding the recent edits in your post):

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

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).

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.

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.

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

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!)

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.

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

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