Set connection string for database access for production build Winforms app

Question

I am working on a Winforms project, and I am just setting up the setup file for the installation of the program.

Scenario

The connection string I have set for my use is like this:

 SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=F:\Application\MyAppv1.0\MyApp\DB.mdf;Integrated Security=True");
 return con;

Here, I use local DB from my personal location.

Question

On installation on the client's PC, the database is also attached, so the installed files will be on C:\Program Files\Author\MyAppv1.0. When I use the same connection string, the app shows an exception. So, should I update my connection string for that location during the setup creation? (I am using MSI).

Is there any commonly followed approach for this scenario?

My aim is to make the installed app use the DB that is also on the same location of installation named DB.mdf and is provided with setup.

Thanks guys in advance.

Answer 1

Do not use AttachDbFilename, it has many issues. Instead, attach your database normally using CREATE DATABASE...FOR ATTACH.

At startup of your app, you can connect to the server using master as the current DB, and check for existence of your DB. If it's not there you can create it.

private static void CheckDbExistence(string connectionString)
{
    const string query = @"
IF NOT EXISTS (SELECT 1
    FROM sys.databases
    WHERE name = 'MyDb')
BEGIN

    DECLARE @sql nvarchar(max) = N'
CREATE DATABASE MyDb ON
  (FILENAME = ' + QUOTENAME(@mdf, '''') + '),   
  (FILENAME = ' + QUOTENAME(@ldf, '''') + ')
  FOR ATTACH;
';

    EXEC (@sql);
END;
    ";
    var csb = new SqlConnectionStringBuilder(connectionString);
    csb.Initial Catalog = "master";
    using (var conn = new SqlConnection(csb.ConnectionString))
    using (var comm = new SqlCommand(query, conn))
    {
        comm.Parameters.Add("@mdf", SqlDbType.NVarChar, 255).Value = Path.Combine(Environment.CurrentDirectory, "MyDb.mdf");
        comm.Parameters.Add("@ldf", SqlDbType.NVarChar, 255).Value = Path.Combine(Environment.CurrentDirectory, "MyDb.ldf");
        conn.Open();
        comm.ExecuteNonQuery();
    }
}