How to create a database and populate it during setup

Question

I would like to find a way to create and populate a database during asp.net setup.

So, what I'm willing to do is:

1. Create the database during the setup
2. Populate the database with some initial data (country codes or something like that)
3. Create the appropriate connection string in the configuration file

I'm using .NET 3.5 and Visual Studio 2005, and the Database is SQL Server 2005.

Thanks in advance.

Answer 1

If you are creating an installer I'm sure there is a way to do it in there, but I am not all that familiar with that.

Otherwise, what you might do is the following.

1. Add a application_start handler in the Global.asax, check for valid connection string, if it doesn't exist, continue to step two.
2. Login to the server using a default connection string
3. Execute the needed scripts to create the database and objects needed.
4. Update the web.config with the connection information

The key here is determining what the "default" connection string is. Possibly a second configuration value.

Answer 2

Not a real answer, but if you are using .net 3.5 you are for sure using VS 2008. Anyway can you clarify what database are you using?

Answer 3

Generally, you'll need to have SQL scripts to do this. I tend to do this anyway, as it makes maintaining and versioning the database much easier in the long run.

The core idea is, upon running the setup program, you'll have a custom action to execute this script. The user executing your setup will need permissions to:

• Create a database
• Create tables and other database-level objects in the newly-created database
• Populate data

Your scripts will take care of all of that, though. You'll have a CREATE DATABASE command, the appropriate CREATE SCHEMA, CREATE TABLE, CREATE VIEW, etc. commands, and then after the schema is built, the appropriate INSERT statements to populate the data.

I normally break this into multiple scripts, but YMMV:

• Create schema script
• "Common scripts" (one for the equivalent of aspnet_regsql for web projects, one with the creation of the Enterprise Library logging tables and procs)
• Create stored procedure script, if necessary (to be executed after the schema's created)
• Populate initial data script

For future maintenance, I create upgrade scripts where a single script typically handles the entire upgrade process.

When writing the scripts, be sure to use the appropriate safety checks (IF EXISTS, etc) before creating objects. And I tend to make mine transactional, as well.

Good luck!

Answer 4

Well, actually I found a tutorial on MSDN: Walkthrough: Using a Custom Action to Create a Database at Installation

I'll use that and see how it goes, thanks for your help guys, I'll let you know how it goes.

Answer 5

If you can use Linq to Sql then this is easy.
Just import your entire database into the Linq to Sql designer. This will create objects that describe all objects in your database, including the System.Data.Linq.DataContext derived class that encapsulate the entire database setup.

Now you can call DataContext.CreateDatabase() to create the database.

See here more information.

Answer 6

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