I have written a C# application that uses a SQL Server database. The intended users of this program will be running SQL Server 2008 R2. I have created a setup program (deployment project in visual studio) that installs my program but I would also like it to install the database. How do I go about accomplishing that?
You could write a custom install action. Take a look at the following article.
simply you have to add deployment project in your developed project. say named it proj_deploy then right click on this project (proj_deploy) and choose properties being in VS 2008 / 2010 then go to prerequisites and select which items you want to be installed on client machine when someone install your developed project.
prerequisites are bootstraps by which you can install third party tools and etc.
It is better that you let the users install SQL Server 2008 themselves. This gives better flexibility with regards to versions of the database server, and also gives them the possibility to install it on a different machine if they need it. SQL Setup lately also has gotten quite complex and not very straightforward. If you need an embedded SQL Server then you can use SQL Server Compact instead.
Within your application you can create the necessary DB on SQL Server 2005+ using the following code. Keep in mind it requires SA privileges on the SQL server. You need to replace %db_name% with the actual name of the database.
IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'%db_name%') BEGIN DECLARE @data_path nvarchar(1024), @db_path nvarchar(1024), @log_path nvarchar(1024) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Setup', N'SQLDataRoot', @data_path OUTPUT SET @db_path = @data_path + N'\Data\%db_name%_Data.MDF'; SET @log_path = @data_path + N'\Data\%db_name%_log.ldf'; EXECUTE (N' CREATE DATABASE [%db_name%] ON (NAME = N''%db_name%_Data'', FILENAME = N''' + @db_path + N''', SIZE = 3, FILEGROWTH = 10%) LOG ON (NAME = N''%db_name%_Log'', FILENAME = N''' + @log_path + N''', SIZE = 3, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS') END
You dont. Well, what you normally do is that your setup msi file has a bootstrapper, a .exe program that triggers install of the MSI. This one will ALSO check prerequisites and trigger installs of those if needed and available.
Check http://wix.mindcapers.com/wiki/Bootstrapper for a more detaoled explanation and some examples of what is available to you.
If it is a client/server application then the SQL Server most likely will already exist and won't be on the same pc as your application.
If it is a single-user database application, I would recommend using SQL Server Compact. Then the "installation" consists of just copying some dlls.