SQL database deployment for VB.NET app

Question

I have read many articles here, but haven't quite found the solution. I have a SQL Server Express database that is used by my VB.NET application. I have packaged and deployed the application via an MSI file and everything works great except I cannot figure out how to include my database file with the package. I understand there are three general ways to do this (copy the files over manually, custom actions, and SQL scripts). I didn't need anything fancy here, just a quick way to put the DB on the client machine so my app can access it.

I decided copying over the DB manually was the quickest option. I tried putting it in the working directory and in the \DATA directory of the client's SQL Server Express install, but my app wouldn't connect. I also tried changing my connection in the project to .\SQLEXPRESS instead of [my_computer_name]\SQLEXPRESS followed by a rebuild of the deployment project and reinstall on the client machine, but no soup for me. Same issue. I tried changing the "UserInstance" property in the project to "True" but my project would not let me save that action.

Am I correct that a manual copy is the quickest and easiest way to get this done?

Answer 1

You need to attach your database file to the running SQL Server on the client machine. This could be easily done using this variation on the connection string stored in your configuration file (app.config or web.config)

Server=.\SQLExpress;AttachDbFilename=where_you_have_stored_the_mdf_file;
        Database=dbname; Trusted_Connection=Yes;

in alternative, you could use the |DataDirectory| substitution string.
This shortcut eliminates the need to hard-code the full path.
Using DataDirectory, you can have the following connection string:

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|\yourfile.mdf”
         Database=dbname; Trusted_Connection=Yes;

Answer 2

You should to attach your file to the Sql Server instance.

CREATE DATABASE YourDatabaseName 
    ON (FILENAME = 'C:\your\data\directory\your_file.mdf'), 
    (FILENAME = 'C:\your\data\directory\your_file_Log.ldf') 
FOR ATTACH;