How to create dynamic database connection string C#

Question

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:

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

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

Answer 2

If you want to work on other PCs, rather than building it dynamically make the connection string more generic:

Server=(localdb)\\mssqllocaldb;Database=LearningSystem;Trusted_Connection=True;MultipleActiveResultSets=true

This should create the mdf file under 'mssqllocaldb' in %appdata% for each user. You might need LocalDb installed (which you tick during SQL Server installation)

Answer 3

Your question is not clear! you need work with one Database on 2 or more PC?!

OR

you need work with 2 separate programs? if you need 2 separate programs :

you must copy .mdf file to other PC at same address or keep mdf address in app.config and read it before connect to SQL.

How to Read From app.config

if you need work with one Db you must connect to dataBase Server such as SQL Server and keep connection string in app.config in connectionStrings tag.

Get connection string from App.config