Wrong database path after publishing the windows application

Question

I developed a WPF application with MS Access database. Everything is working fine. But when I tried to publish the app in order to create a setup file to use the application on other pcs, I am getting the following exception when I first run the app:

System.Data.OleDbException: Could not find file 'C:\Users\me\AppData\Local\Apps\2.0\77NYD08R.L6A\Y93TLQGV.2TD\pier..tion_b5378b5ea7b941ca_0001.0000_9b5c3ff3b52b7eb4\Data.

I'm accessing the database in the code as follows:

private void ConnectToDatabase()
{
     dBConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=MyProjectDB.mdb";
     dBConnection.Open();
}

And I added the database in bin/Debug folder. Why it's trying to get the database file from AppData\Local folder? Should I move the database file to another folder?

Answer 1

Basically there is what you should do:

Do not leave the database file in your project folder, copy it elsewhere, like to Environment.SpecialFolder.ApplicationData. You do it when the app is first launched and there no database file exists, so you copy the database from your project folder to your data folder.

There is quite a good answer, how to deal with the problem here.

If you need to write to the data deployed with your executable you should first copy it someplace you know the user will be able to write to, such as to Environment.SpecialFolder.ApplicationData, and write to the copy. Not only is DataDirectory not necessarily writable by users, it is part of the deployment and not part of the user data; if you repair or uninstall your executable then DataDirectory gets reinstalled or deleted. Users don't like it when you delete their data, so don't save it to DataDirectory

You can get the path of your ClickOnce executable via this command:

 var exePath = System.IO.Path.GetDirectoryName(
               new Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase).LocalPath);

See then this answer: Use something like that if the database should be used by multiple users of target computer:

AppDomain.CurrentDomain.SetData("DataDirectory", Path.Combine(System.Environment.GetEnvironmentVariable("public"), YOUR_FOLDER_NAME));

Or just one database for one user:

AppDomain.CurrentDomain.SetData("DataDirectory", Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),YOUR_FOLDER_NAME));

You should be aware that ClickOnce deployment method allows users that are not local admins to install your app, so it is better to choose such a folder on the target machine that should be by default writable for the end user.

Then you use |DataDirectory| in the connection string within your config file, like in the mentioned answer.

The DataDirectory would be a place where you copy the mdb file from exePath to your target data folder.

Your problem also is, that ClickOnce apps are installed in the user profile, so the database, if it is to be shared among different users of 1 computer, should be placed somewhere in c:\users\public folder. If it is to be used only by a single user, it can be placed in documents folder or somewhere in the current user's profile.

So for a shared database your db folder where you copy your mdb after the first deployment could be something like this:

Path.Combine(System.Environment.GetEnvironmentVariable("public"), YOUR_FOLDER_NAME)

for a non-shared database it would be

Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),YOUR_FOLDER_NAME)

---

There is usually a very good reason not to leave your data in your project folder as it is quite plainly recommended by Microsoft here:

When a ClickOnce application is uninstalled, its Data Directory is also removed. Never use the Data Directory to store end-user–managed data, such as documents.

But if it is OK with you then you might be just fine changing the file attributes in Visual Studio, see this answer.

But also when you udpate the mdb file within your project, the locally installed data file gets rewritten by the published ClickOnce updates - one more link here, just to see that this is quite common approach to have data copied elsewhere by ClickOnce app at startup.

Answer 2

You need to provide the location of you access database in the connection string

dBConnection.ConnectionString  = "Provider=MICROSOFT.ACE.OLEDB.12.0; " +
                "Data Source=|DataDirectory|MyProjectDB.mdb"

|DataDirectory| needs to be a full relative path this is just a placeholder. So you can use

var path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);

dBConnection.ConnectionString  = "Provider=MICROSOFT.ACE.OLEDB.12.0; " +
                "Data Source=" + path + "\MyProjectDB.mdb"