Database file not copied during publishing so installed application throws exception

Question

I am developing a C# windows form application containing a service based data based. when I test my application it's database works fine but after publishing and installing the program when program tries to open sqlconnection , this error appears:

System.Data.SqlClient.SqlException (0x80131904): An attempt to attach an auto-named database for file C:\Users\Behnam\AppData\Local\Apps\2.0\Data\5XVOVXV1.3VG\M5T04ZK7.QBJ\tahl..tion_45c3791d6509222d_0001.0000_be1c7cc05811ecf0\Data\AppData\TahlilGar.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

This is my ConnectionString:

<add name="BA" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\AppData\TahlilGar.mdf;
Integrated Security=True;"providerName="System.Data.SqlClient" />

I also tried : User Instance= True; but it's result is :

The user instance login flag is not allowed when connecting to a user instance of SQL Server. The connection will be closed.

How can I fix this issue?

---

Edit: I checked the mentioned path and there was not my .mdf file. so i copied it from my project and it worked fine after that. now why my mdf file is not copying when publishing and installing in the expected path.

Answer 1

When using click once for publishing the windows forms application, we can include or exclude the files along with your project. MSDN link explains how to add files

https://msdn.microsoft.com/en-us/library/kzy0fky2.aspx

Note: Database will appear in Application Files dialog box only if it is added to project

Answer 2

Your connection string mentions that SQL Server Express instance has been used in development machine:

<add name="BA" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\AppData\TahlilGar.mdf; Integrated Security=True;"providerName="System.Data.SqlClient" />

Assume your deployment machine uses full version of SQL Server instance, the automatic database generation from code has no effect in deployment context since User Instance feature only supported in Express version.

Here is the steps to resolve your issue:

1. Modify your connection string with removal of user instance part (remove User Instance=True if any).

2. Run aspnet_regsql.exe manually to attach and register database/MDF file on deployment machine (if you don't done it yet).

3. If you have migrated the database to SQL Server instance in deployment machine, make sure you should have change the data source path and add initial catalog (i.e. your DB name) like this one.

   <add name="BA" connectionString="Data Source=SERVERNAME;Initial Catalog=TahlilGar;Persist Security Info=True;User ID=DBUserID;Password=DBPassword;Integrated Security=True;"providerName="System.Data.SqlClient" />
   

The AttachDbFilename=|DataDirectory|\AppData\TahlilGar.mdf part works when database has exist in local machine and user account currently using the instance granted proper permission to attach your MDF file.

Additional notes from Adam Tuliper:

This is potentially an issue with the account you are running IIS under not having access to that file.

Assign full permissions to that folder for the Network Service account.

You can temporarily try 'everyone' and see if it resolves the issue, and work backwards from there.

Also ensure its not in use by the other web server (process explorer/sysinternals can help show you that).

References:

Attempt to attach an auto-named database for .mdf file failed

The user instance login flag is not supported on this version of SQL Server. The connection will be closed