visual studio 2010 - deploying a database and application with ClickOnce

Question

I am new to ClickOnce. I created a WinForms application using C# and SQL Server Express. Currently my database is located in the mssql data folder. My connection string is located in an app.config file.

<add name="default"  providerName="System.Data.SqlClient"
  connectionString="Server=localhostSQLEXPRESS;Database=DBase;Trusted_Connection=True;" />

• How do I include my database file for it to deploy with the application?

• To which folder will the database be deployed and will my connection string (above) be still working?

Answer 1

You have a couple of options. First, if this is a simple database I recommend that you forego using SQL Express and use SQL Server Compact. A Compact SQL database is a file that can be bundled with your application installation. Below is a link guiding you through the creation process of a SQL Compact database.

http://technet.microsoft.com/en-us/library/ms173009.aspx

The following link discusses setting up the necessary dependencies and prerequisites for SQL Server Compact. (Note, you will need to scroll to the section entitled Private File–Based Deployment.)

http://msdn.microsoft.com/en-us/library/aa983326(v=VS.100).aspx

If you have a complex database, or one that may grow larger than the 3GB limit that is set for SQL Compact databases, the only alternative is to set SQL Server Express as a prerequisite to your application and once it is installed create a routine within your application that checks to see if your database exists in SQL Express and, if not, launches a database creation and updating routine.

In my case, I have a heavy, data-driven application that was written before SQL Compact was out and had to function in environments that were often disconnected from our servers. I had to use SQL Express so that we had to develop a mechanism to check for database updates when the server was available. If there are updates, encrypted script files are downloaded and executed, sequentially against the child database. After the database container is updated, a follow-up routine is called to check and synchronize the data.

--EDIT--

I wanted to quickly respond that, yes, you can bundle and attach a SQL Server database programatically to a local instance of SQL Server. There are many barriers to this and if you have a user base that uses multiple versions of Windows, PC architectures and multiple different security scenarios, you will probably have trouble consistently attaching your database to the user's SQL Server. Yes, it can be done. However, I'd recommend avoiding that scenario at all possibly costs.