Visual Studio Hacks [Electronic resources] نسخه متنی

Hack 75. Script Your Database

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 databases. 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.

9.4.1. 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 executable 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
simply 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
validfor 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.

9.4.1.1 Create a Database Project

The first step in the process is to create a Database 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
dialog is shown in Figure 9-16.

Figure 9-16. Creating a new Database Project

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.)

Figure 9-17. Add Database Reference dialog

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.

Figure 9-18. Northwind Project in the Solution Explorer

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

9.4.1.2 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 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.

Figure 9-19. Choosing Generate Create Script

After clicking Generate Create Script, you will see the dialog shown
in Figure 9-20.

Figure 9-20. Generate Create Scripts dialog

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 generated. After generating the create script, you
will see a number of scripts in the Solution Explorer, as shown in
Figure 9-21.

Figure 9-21. Solution ExplorerCreate Scripts

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 database, using the options that you prefer.
You should end up with a Database Project including scripts for your
entire database. (You can use folders to organize all these
scripts.)

9.4.1.3 Create command file

Now that you have a project filled with the
scripts for your entire database, how can you take these scripts and
create a new database from it? This is where command files can be
used. If you right-click on the Create Scripts folder, you will see
an option for Create Command File, as shown in Figure 9-22.

Figure 9-22. Choosing Create Command File

You will then see the Create Command File dialog shown in Figure 9-23.

Figure 9-23. Create Command File dialog

From this dialog, you can select the scripts that you would like to
include in the command file; then when you click OK, the command file
will be created. Here is an example command file:

@echo off
REM: Command File Created by Microsoft Visual Database Tools
REM: Date Generated: 10/17/2004
REM: Authentication type: Windows NT
REM: Usage: CommandFilename [Server] [Database]
if '%1' =  = '' goto usage
if '%2' =  = '' goto usage
if '%1' =  = '/?' goto usage
if '%1' =  = '-?' goto usage
if '%1' =  = '?' goto usage
if '%1' =  = '/help' goto usage
osql -S %1 -d %2 -E -b -i "dbo.Customers.tab"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "dbo.Customers.kci"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "dbo.Customers.fky"
if %ERRORLEVEL% NEQ 0 goto errors
osql -S %1 -d %2 -E -b -i "dbo.Customers.ext"
if %ERRORLEVEL% NEQ 0 goto errors
goto finish
REM: How to use screen
:usage
echo.
echo Usage: MyScript Server Database
echo Server: the name of the target SQL Server
echo Database: the name of the target database
echo.
echo Example: MyScript.cmd MainServer MainDatabase
echo.
echo.
goto done
REM: error handler
:errors
echo.
echo WARNING! Error(s) were detected!
echo --------------------------------
echo Please evaluate the situation and, if needed,
echo restart this command file. You may need to
echo supply command parameters when executing
echo this command file.
echo.
pause
goto done
REM: finished execution
:finish
echo.
echo Script execution is complete!
:done
@echo on

If you look at the text of the command file, you can see that it uses
the osql utility to execute each of the scripts.
You can run this command file by simply right-clicking on the command
file and clicking Run, or you can also incorporate this command file
into your build process.

The Database Project is a valuable tool that can be used to make the
managing of your databases easier and less prone to error.