How to install a custom desktop application database to SQL Express?

Question

Hi, I have a WPF desktop application that uses a custom database for storage. I need to prepare a setup project (from Visual studio 2008) (full setup, not ClickOnce).

I can add the to the list of prerequisites to the application and it does install during the setup of the application.

My question is: How can I run a script during the setup to create the database that the application needs? OR how can I restore the database to the client machine during the setup?

Another related question, what would happen if already exists on the client machine? How to detect the instance name and connection data? And then how to be able -if needed- to change the Connection string used by Entity framework to connect to that database?

Thanks, Ed

Answer 1

SQL Server Express Edition is generally a really poor choice for a local database. It's a server-class engine that likes to use a lot of resources and runs as a service (so it's using up those resources even when your app isn't running). In other words, it belongs on a server.

The only place I've seen SQL Server Express used on a desktop that almost makes sense is as part of the Microsoft Small Business Accounting app, and in this case you generally install that program on a machine who's primary purpose is doing the accounting for your business.

What you should do is use a desktop or in-process class engine like SQL Server Compact Edition, Sqlite, or even Access. This will also greatly simplify your deployment.

If you insist on pushing through with this, know that the installer will create a new instance of sql server on the system. SQL Server will be fine with this. However, you'll need to account for that in the connection string of your app, and that can be a little more complicated. Additionally, to set up the database you have a couple options:

• Create it from client code on first start of the app
• Create it with a custom installer action (hard to get right because msi permissions)
• Distribute an pre-build *.mdf file and attach with custom installer action or on first start of the app.

Answer 2

See following to install database using custom action:
http://www.techbrij.com/145/install-sql-server-database-with-visual-studio-setup