Visual studio app.config file database path for installed apps

Question

I am using local database in my app and when I generate installation file (By Installer Package), after installing program it gives database path errors.

Eample

an attempt to attach an auto-named database for file....
//OR
The given path format is not supported

I've tried to edit database path in app.config file but it failed every time, By default my code line is like this:

<add name="SampleDatabaseWalkthrough.Properties.Settings.SampleDatabaseConnectionString"
            connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\SampleDatabase.mdf;Integrated Security=True"
            providerName="System.Data.SqlClient" />

And my app installing in C:\Program Files (86)\App_Folder\Setup Please note that future users might install it in custom path so I need a way to get dynamic path of installed app.

My question is How can I get app installed path to replace with this part AttachDbFilename=|DataDirectory|\SampleDatabase.mdf?

Answer 1

In production mode |DataDirectory| refers to 'bin' directory, not 'app_data'. If you placed the .mdf file in the app_data directory, you can change it like this:

|DataDirectory|\SampleDatabase.mdf to |DataDirectory|\app_data\SampleDatabase.mdf

<add name="SampleDatabaseWalkthrough.Properties.Settings.SampleDatabaseConnectionString"
        connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\app_data\SampleDatabase.mdf;Integrated Security=True"
        providerName="System.Data.SqlClient" />

Update1:

I'm sending some code. I just want to give you an idea. You can change it for your situation.

private void Form1_Load(object sender, EventArgs e)
{
    if (!IsExist())
    {
        CreateDatabase();
        CreateTables();
    }
}

// Create the Database
private void CreateDatabase()
{
    string basePath = Environment.CurrentDirectory;
    string mdfFile = "TestDatabase.mdf";
    string ldfFile = "TestDatabase_Log.mdf";
    string mdfFullPath = System.IO.Path.Combine(basePath, "Data", mdfFile);
    string ldfFullPath = System.IO.Path.Combine(basePath, "Data", ldfFile);

    SqlConnection myConn = new SqlConnection("Server=.;Data Source=(LocalDB)\\MSSQLLocalDB;Integrated security=SSPI;database=master");
    string str = "CREATE DATABASE TestDatabase ON PRIMARY " +
            "(NAME = TestDatabase, " +
            $"FILENAME = '{mdfFullPath}', " +
            "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%)" +
            "LOG ON (NAME = MyDatabase_Log, " +
            $"FILENAME = '{ldfFullPath}', " +
            "SIZE = 1MB, " +
            "MAXSIZE = 5MB, " +
            "FILEGROWTH = 10%)";


    SqlCommand myCommand = new SqlCommand(str, myConn);
    try
    {
        myConn.Open();
        myCommand.ExecuteNonQuery();
        MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    finally
    {
        if (myConn.State == ConnectionState.Open)
        {
            myConn.Close();
        }
    }
}

// Create the tables and other stuff that you want
private void CreateTables()
{
    string conStr = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Data\TestDatabase.mdf;Integrated Security=True;Connect Timeout=30";

    SqlConnection myConn = new SqlConnection(conStr);
    string str = @"CREATE TABLE [dbo].[TestTable]
                    (
                        [Id] INT NOT NULL PRIMARY KEY, 
                        [Test] NVARCHAR(50) NULL
                    )";

    SqlCommand myCommand = new SqlCommand(str, myConn);
    try
    {
        myConn.Open();
        myCommand.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    finally
    {
        if (myConn.State == ConnectionState.Open)
        {
            myConn.Close();
        }
    }
}

// Check if there is the database
private bool IsExist()
{
    string basePath = Environment.CurrentDirectory;
    string mdfFile = "TestDatabase.mdf";
    string mdfFullPath = System.IO.Path.Combine(basePath, "Data", mdfFile);

    return System.IO.File.Exists(mdfFullPath);
}

Answer 2

You could try to use AppDomain.CurrentDomain.SetData method to change your mdf file path.

Since, I don't know how do you published the winform project.

I recommend that you use Clickonce to publish it.

First, please include your mdf file in your project.

Second, you could try the following code to change the installed path after you published it.

 private void Form1_Load(object sender, EventArgs e)
            {
                if(System.Deployment.Application.ApplicationDeployment.IsNetworkDeployed)
                {
                    string path = ApplicationDeployment.CurrentDeployment.DataDirectory; //Get installed path
                    AppDomain.CurrentDomain.SetData("DataDirectory", path);//set the DataDirectory 
                }
            }

Finally, based on my test, I can get the information from the mdf file after I publised it and installed it in another computer.