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.

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.

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

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

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

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.