5.7 Database Projects

There is a fundamental difference between working with databases and working with other software development artifacts in Visual Studio .NET. With programs and components, source code is of central importance. Although we must create DLL or EXE files in order for our programs to run, these are usually never checked into revision control systems—they are essentially disposable because they can always be re-created from the source code.

With databases, on the other hand, the model is different. The closest thing we might have to source code is some SQL script that creates a database with a particular schema. However, these are not really at the center of the development model—the database is typically the authoritative source of information. SQL creation scripts are often generated from the contents of the database, so they cannot necessarily be described accurately as containing "source" code. You can use a Database project to hold scripts that contain the master definition for the current database schema, but these scripts would not be run as part of the normal build process—you don't want to re-create your database from scratch every time you build your project.

The role of a Visual Studio .NET Database project is therefore somewhat different from that of most projects—it does not contain the authoritative information required to build the target. In fact, Database projects don't build any kind out output at all—they just act as a container for scripts. Moreover, you do not even need a project in order to use the visual database design tools—when you save any changes you have made in these designers, Visual Studio .NET always writes changes out to the database itself, so there is no need for a project file or source files.

Database projects are useful when you employ the common development practice of having separate database servers for development and production. (And there may also be separate staging and test servers.) Visual Studio .NET can create script files that capture any changes made to a development server. These scripts can then be applied to other servers later on in the development process to apply the same modifications that you applied to the development server.

5.7.1 Creating a Database Project

Database projects are created just like any other projects, using the New Project dialog. (Open this with File New Project (Ctrl-Shift-N).) The Database Projects category can be found underneath the Other Projects category. The Database Projects category contains only one kind of project: Database Project.

When you create a new Database project, Visual Studio will ask you to choose a database connection for the project, presenting the dialog shown in Figure 5-26. This lets you select the database for which you will be creating scripts. When you select a connection, this creates a new database reference in the project.

Figure 5-26. Add Database Reference dialog

5.7.2 Connections and References

The list of named database connections displayed in the Server Explorer provides a convenient way of looking at particular data sources—they save you having to navigate through the tree control the long way round. (They also allow non-SQL Server data sources to be used.) However, these connections are stored on a per-user basis, so although they are convenient for interactive use, they are not much use for representing connections in Database projects—project files may be opened by other users who do not have the same connections configured on their system. Visual Studio .NET Database projects therefore have a slightly different mechanism of their own called database references.

Database projects usually contain at least one database reference. (You can see one at the bottom of Figure 5-30.) A database reference is very similar to a database connection, except that all of the information is stored in the database project file instead of in the user's Visual Studio .NET settings. This means that anyone who opens the project file will be able to connect to the database even if he didn't have the relevant connection in his local configuration. (This is a good reason to use integrated security with database connections. If you use explicit credentials, they can get stored in the project as part of the connection information, visible to anyone with access to the project file. You might not want database credentials stored in the clear like this. You can prevent passwords from being stored—the Data Link Properties dialog shown in Figure 5-2 has a checkbox labeled Allow Saving Password. If you remember to make sure this is unchecked, the password will not be stored in the settings, and users will be prompted to type credentials in when they try to use the connection. However, if you use integrated security, anyone who opens the project will simply connect with his own credentials automatically.)

When you open a Database project with database references in it, Visual Studio .NET checks to see if the references match any of your database connections. If the project contains a database reference that is different from all of the connections listed in your Server Explorer, it will automatically add a new connection with the same settings as the reference.

When you create a new database project, Visual Studio .NET will ask you which database you wish to connect to. If your system already has an appropriate database connection configured, you can use that. A reference will then be created in your project that has the same settings as the chosen connection. Otherwise, choose the Add New Reference... button to connect to some other data source. This will open the Data Link Properties dialog, allowing you to configure the connection. This is the same window that is used to add data connections to the Server Explorer, as shown in Figure 5-2. This will add a new connection to your Server Explorer and a new database reference to your project, both with the same settings.

5.7.3 Scripts and Databases

Two kinds of scripts—create scripts and change scripts—can be stored in a Database project. Create scripts contain all of the information required to create a new database from scratch, copying everything except the table contents. Change scripts contain just modifications—they assume that the target database will have the same schema that the development database did before the change was made.

The scripts generated by Visual Studio .NET 2002 and 2003 are designed to be applied to SQL Server databases. Although the visual database design tools work with other databases, database projects and the scripts they contain support only SQL Server.

5.7.3.1 Create scripts

Create scripts are not normally used on production servers. The only time you would use a create script on a production server would be the very first time the system goes live. Once a system is up and running, you will never want to recreate the database from scratch, because all of the data would be lost.

By default, create scripts check for the existence of items they are about to create and will DROP any existing items they find. You should therefore never run a create script on a live server. (It is possible to instruct Visual Studio .NET to omit the DROP statements, but it will still not be productive to run such a script against a live server.)

Create scripts are most likely to be useful in staging and test environments—these systems don't have any real live data. The ability to create a new, empty database instance with exactly the right schema, views, and stored procedures can be very useful in these environments. You can add a create script to a Database project by using the Server Explorer—the context menu will have a Generate Create Script... item on the appropriate nodes. You can either generate scripts for individual objects, such as tables and stored procedures, or Visual Studio .NET can make a set of create scripts for the entire database.

You must install the SQL Server Client Tools in order for the Generate Create Script... option to work.

When you ask Visual Studio .NET to generate a create script, you will see the dialog shown in Figure 5-27. This allows you to control exactly which database items the script will create. The available database objects are shown in the list on the left of the dialog. Figure 5-27 shows the dialog as it appears for the pubs sample database when generating a create script for the entire database.

Figure 5-27. Generating a create script

If you open this dialog from the context menu of a specific database item instead of for the entire database, only the selected item will be available, and the checkboxes will all be disabled. However, you can click the Show All button to bring all the other objects back into the list.

By default, no objects will be added to the script—the list on the right shows the items that will be added, and it is initially empty. You can use the Add >> button to add individual items to the list. However, you may find it less work to use the checkboxes toward the top of the dialog. These let you include entire categories of database objects in your create script. The Script All Objects checkbox will cause every database object to be represented, but you can also select just certain categories, such as tables or stored procedures.

The Formatting tab of the Generate Create Scripts dialog, shown in Figure 5-28, allows you to control certain aspects of the SQL script that Visual Studio .NET will generate. You can disable the creation of DROP statements here, which makes the scripts potentially less destructive.

Figure 5-28. Create script code generation options

The "Generate scripts for all dependent objects" checkbox will cause Visual Studio .NET to determine which other database objects your selected objects depend upon and generate scripts to create those too. For example, if you generate a create script for a view, selecting this checkbox will generate create scripts for all of the tables the view uses.

The remaining checkboxes allow you to control whether comments describing the file's purpose will be added to the start of the scripts, whether SQL Server 2000 extended properties will be copied across, and whether the script will be limited to using only SQL Server 7 features.

Further options can be set with the Options tab shown in Figure 5-29. Despite being under the Security Scripting Options category, the first option simply determines whether a CREATE DATABASE statement will be created, along with some associated configuration options. The next three determine whether user role, login, and permission settings will be transferred.

Figure 5-29. Create script options

The settings under the Table Scripting Options category control how much information will be stored in the script for each table. By default, all indexes, triggers, and keys will be created by the generated script.

The File Options category allows you to choose the text encoding of the script files, the default being Unicode. It also allows you to choose between generating a single script file that creates everything and splitting the scripts up so that each object has its own script. Figure 5-30 shows a Database project for a simple database for which the "Create one file per object" option was selected. (This is the default option.) This particular database was fairly small—it contained two tables, ContentText and Transforms, and two stored procedures, GetNewTransformAndContent and GetNewTransformAndNewContent.

Figure 5-30. Create scripts in a Database project

Create scripts for stored procedures are fairly straightforward—each procedure is represented by a single file with a .prc extension, containing a SQL CREATE PROCEDURE statement. Tables are a little more complex however. A table's SQL CREATE TABLE statement is stored in the .tab file. Indexing settings are stored in the .kci file. The .fky files add foreign key constraints, and the .ext files contain any extended properties, such as column description strings.

The script files are just series of SQL statements. If you double-click one, it will open in a Visual Studio .NET editor window. You can execute the scripts using the Run or Run On... items from the script's context menu in the Solution Explorer. (Run On... lets you choose which database connection to use.) Remember that, by default, a create script will drop any existing tables before creating new ones, so don't do this on a database if you care about its current contents. If you elected to generate a create script for each object, you will need to be careful about the order in which you execute these files—you must create the tables first, since the keys, indexes, extended properties, and stored procedures all refer back to tables. If you generated a single create script for the whole database, it will create items in the correct order automatically.

Create scripts are useful for building a new database with the required schema from scratch, but they are of no use for modifying an existing database. As projects evolve, schema changes must be applied to the database nondestructively. For certain kinds of changes, it will be possible to use a create script—adding a new table for example. But for any change that modifies an existing object in the schema, you will need to use a change script instead.

5.7.3.2 Change scripts

Every time you modify a database using the visual database design tools described earlier in this chapter, Visual Studio .NET is able to generate a script containing the changes you made. The intended use of this feature is for you to start from a position in which your development server and production server both have the same schema. You will then make changes to the development server, saving those changes in change scripts. When you are ready to apply the changes to the production server, you can simply run the change scripts on that server. It will then have the same changes applied that you made to the development server.

Visual Studio .NET will offer to create change scripts only if you have a Database project open. If you don't have a Database project open, you will not be prevented from making changes to the database, but no script file will be generated. (This is often the most convenient way to prototype a design—having Visual Studio .NET generate a change script for every little modification you make can be obtrusive in early experimental stages of development.) Because databases do not normally provide any way of retrieving a change log, you will not be able to recover this information later on, so be sure that you have a Database project open if you need change scripts. Alternatively, you can ask VS.NET to create a change script for you—if you are editing the table, the Diagram Generate Change Script... item will create a change script even if you don't have a Database project open.

If you have a Database project open, you will be offered the chance to create a change script each time you save your changes to the database. For example, after adding a new column to a table and creating a relationship between that column and one in another table, selecting File Save causes the dialog shown in Figure 5-31 to appear.

Figure 5-31. Saving a change script

This dialog allows a change script to be created and shows the SQL that will be generated. This one contains an ALTER TABLE statement to add the new column. If you click on the Yes button, a normal Save File dialog will appear asking where you want to save the file. (By default, it will suggest the project's Change Scripts folder.)

The default name for the change script will usually be the name of the item being changed. So, in this example, Visual Studio .NET suggested Books.sql. If you make a series of changes to the same table, you will need to store each set of changes in a separate script file. (A series of changes cannot be merged automatically into a single file.) Fortunately, if you use the same filename every time you save a change, Visual Studio .NET will add a number to the end of the file in order not to overwrite previous changes. So, if you save four changes with the name Books.sql, they will be saved into Books.sql, Books1.sql, Books2.sql, and Books3.sql. (It does not indicate that it is going to do this in the Save dialog—it always shows the name without the number.)

As with create scripts, change scripts just contain SQL statements. And again, you can execute them with the Run or Run On... items from their context menus in the Solution Explorer.

If you attempt to modify a database item that you do not have permission to change, you will not be allowed to save your change into the database. However, Visual Studio .NET will still be able to save these changes into a change script, even if you are not allowed to modify the database itself. You could use this feature to design the changes you want using the visual database design tools, generate a change script, and then submit that script to a DBA.

Unfortunately, you cannot open change scripts with the visual design tools—you will only be able to edit their text. For example, suppose you used the table designer to modify a table and then saved your changes to a change script but not the database. If you then closed the table designer window, you would not be able to reopen the table designer to show your modified view. When you open a table designer window, it will always show what is currently in the database and unfortunately cannot incorporate any work in progress stored in a script file. When you open a script file, you always get the SQL editor view.

5.7.4 Query Files

If you have a Database project, you can write a standalone query that is saved as a .dtq file. You edit these using the query and view designer described earlier in this chapter. This allows you to write a query in the designer and execute it without having to store it in the database.