Script Your Database #75
Chapter 9, Server Explorer Hacks
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.
Chapter 9, Server Explorer Hacks
#75 Script Your Database
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
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
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

Get Visual Studio Hacks now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.