Visual Studio Hacks

Script Your Database #75

Chapter 9, Server Explorer Hacks

|

309

HACK

HACK

#75

Script Your Database Hack #75

Most applications involve some sort of data, and where you have data you

probably have a database. Script the creation of your database with the

Database Project and the Server Explorer.

Managing databases is always a difficult task; you have to manage not only

the tables but also the views, stored procedures, functions, and triggers. In

an enterprise environment, you are most likely working with multiple data-

bases. Keeping those databases synchronized with one another is a daunting

task. All of this is not easy, but using the Database Project and the Server

Explorer can help make these tasks much more manageable.

The Database Project

The Database Project is different than most of the projects you are used to

using in Visual Studio. The project does not have an output like an execut-

able or assembly, but instead is just something that can be used to collect

and store SQL scripts. The idea behind the Database Project is that, instead

of trying to use one of your many databases as the “master” database, you

should instead maintain a store of create scripts that can be used to create

your database at any time. If you need a new copy of the database, you sim-

ply run this collection of scripts, instead of trying to make a copy of the

database. This has a number of benefits:

• You can ensure that all databases are using the same objects.

• You can always create a new copy of the database from scratch.

• By using scripts, you can catch any objects that might no longer be

valid—for example, stored procedures that reference columns or tables

that no longer exist. When a script for that stored procedure is run, it

will throw an error if the referenced column or table no longer exists.

• You can control these scripts using source control.

• You can incorporate the creation of the database into the build process

to catch problems quickly. (You might not want to actively create the

development database, but instead create a test database.)

• You can script required data to be added to the database, such as values

for metadata or lookup tables.

• You can script test data to be used for unit testing.

Maintaining a list of scripts can be a cumbersome task though; this is where

the Database Project and the Server Explorer come into play.

310

|

Chapter 9, Server Explorer Hacks

#75 Script Your Database

HACK

Create a Database Project. The first step in the process is to create a Data-

base Project. To do so, you simply need to go to File

➝ New Project and

then expand the Other Projects node and select Database Project. This dia-

log is shown in Figure 9-16.

Next, you will be asked to pick the database that you want to manage with

this project. You will see the dialog shown in Figure 9-17. (If you do not

have any database references created, instead you will see the Data Link

Properties dialog to choose a server and specify the login information.)

From this dialog, you can select an already configured reference or create a

new reference using the Add New Reference button. After creating the

project and configuring a database reference, you will see the project loaded

in the Solution Explorer, as seen in Figure 9-18.

The next step is to populate your project with the scripts to build your

database.

Create scripts. Creating scripts can be a tedious task. The Server Explorer

makes this process a little easier. You can create your objects in the normal

fashion using the Server Explorer or Enterprise Manager, then, using the

Server Explorer, you can generate create scripts by simply right-clicking on

Figure 9-16. Creating a new Database Project

Script Your Database #75

Chapter 9, Server Explorer Hacks

|

311

HACK

the object and choosing Generate Create Script, as shown in Figure 9-19.

(You can also right-click on the entire database to script the entire database.)

To use the Create Scripts functionality, you must be using

SQL Server 7 or 2000 and have the client tools for SQL

Server installed on your development machine.

After clicking Generate Create Script, you will see the dialog shown in

Figure 9-20.

Using this dialog, you can use the Formatting and Options tabs to configure

how the script should be created; then click OK and the script will be gener-

ated. After generating the create script, you will see a number of scripts in

the Solution Explorer, as shown in Figure 9-21.

In the figure, you see four different scripts that create the table, its foreign

keys, indexes, and so forth. Using the Generate Create Scripts command,

you can go through your database and create scripts for every object in the

Figure 9-17. Add Database Reference dialog

Figure 9-18. Northwind Project in the Solution Explorer