sql - Add *.mdf file to C# Project

Question

I'm using Visual Studio 2005. I create a project, not Web Project, just Windows application.

I remember that Access Database File can be added into a project. I don't need connection to server, data can be retrieved. And I want to do the same thing with SQL Database file.

I did the following steps:

1. Right-click on project.
2. Choose Add An Existing Item
3. Browse for *.mdf file.

4. DataSource Config Wizard appears and it displays this Message

   An error occurred while retrieving the information from the database: Failed to generate a user instance of SQL Server due to a failure int starting the process for the user instance. The connection will be closed.

I need help to add mdf file into my project.

Answer 1

To start with, and MDF file can be read only by an instance of SQL Server. If you deploy MDFs, then your application must either connect to a SQL Server provided by your end-user during setup, or it must deploy its own instance, in the later case a SQL Server Express Edition instance. See How to: Install SQL Server Express. With Visual Studio 2008 you can add a prerequisite to your own application setup MSI, see "Installing" the SQL Server 2008 Express ClickOnce Bootstrapper for Visual Studio 2008 SP1.

A second issue is that, despite the wide belief of the contrary, distributing the MDF alone without the LDF can land you into a world of pain. You can end up distributing an inconsistent MDF that needs the LDF to finish recovery and get into a consistent state.

But a more serious issue is your plan to deploy binaries (MDFs) instead of scripts for database deployment. This is doomed to fail. As soon as you'll plan to release v. 1.1 of your application you'll face the non-trivial problem of how to replace the user MDF (which now contains data added by the user) with your new MDF. This is why is much much better to deploy upgrade scripts always, and forget about the MDF in your project.