How to distribute SQL Server applications?

Question

I have a VERY simple C# Winforms project that I made using Visual Studio 2010. It uses SQL Express 2008. This is my first time using SQL, so I wanted a hands-on approach to learning SQL, and this project allowed me to do that.

I want to post my project on my website for people to see. However, I have stored procedures, constraints, and other things (such as what columns to put in each table) that need to be setup the first time my program is run.

I was told that this could be done using the built-in feature of VS2010 to make MSI installers, however this turned out to be very complex and I gave up on it quickly.

To me, the ideal solution would be to simply ZIP up the executable and have it automatically create the database, tables, columns, stored procedures, and everything else in-code. This seems rather primitive but it also seems simple and very direct. The only thing is, I don't know if this is possible.

My question for the SO community is what would you recommend? Please keep in mind that I do not want the distribution model to outweigh the complexity of my application itself, which as I stated is a very simple portfolio type project. Also if you could provide examples and/or instructions on how to proceed with your suggestion, that is much appreciated as well.

Thanks in advance!

Answer 1

You may want to look at Visual Studio database projects.

They will help keep track of all objects in the database and make deployment fairly easy without resorting to creation of databases and database objects with your own code.

Answer 2

As an easy solution, you could script your database and include the script file in your project (as a resource or as a separate file). On startup your application will check whether the database exists and run the script if it doesn't.
Of course, this implies that SQL Server itself is already installed (or somehow bundled with your app).

UPD. The easiest way to create a script is a script generation wizard from Management Studio (right-click on the database, Tasks -> Generate scripts). I hope, this wizard is available in Express edition.

See ths thread for an example of executing a script from .NET code.