Using Database Projects

Up to this point, we have discussed the use of the Visual Database Tools outside the context of a Visual Studio solution/project. Now let's investigate the role of the Database project type. Database projects hold a database connection (referred to as a database reference) and SQL scripts or queries that relate to that database. One benefit of housing scripts in a project like this is that they are available for storage in a source control system.

Note

Scripts are nothing more than SQL statements stored in a file. They are useful because they can be executed in batch to do such things as create tables in a brand-new database or add a canned set of stored procedures to a database. Because they are merely files, they can be transferred from computer to computer, enabling you to duplicate database structures across machines with ease.

The SQL scripts in the database project can create many of the database objects that we have already discussed: tables, views, triggers, stored procedures, and so on. Queries developed using the Query/View Designer can also be directly saved into a database project. In short, you use the Visual Database Tools in conjunction with a Database project to create and save SQL scripts and queries.

Creating a Database Project

To generate a database project, pick New Project from the File menu and then look under the Other Project Types category in the New Project dialog box (see Figure 15.23).

Figure 15.23. The database project type.

The project wizard will first prompt you for a database reference to add to the project; you do this by using the same set of dialog boxes used to add data connections in the Server Explorer (refer to Figure 15.3).

Once created, the default project will have the structure that you see in Figure 15.24.

Figure 15.24. Database project structure.

As you can tell from this project structure, it has predefined folders for holding scripts, queries, and database references.

Scripts are added to the project in two ways. You can use the traditional "add new item" process just as you would with any other project. Select the item type (see Figure 15.25), and a script (or query) will be added to the project with the standard template skeleton code added to the file for you. An easier way to create scripts, however, is to let the database tools do the work for you.

Figure 15.25. Database project items.

Auto-Generating Scripts

There are two major types of auto-generated scripts: create scripts and change scripts. As their names imply, create scripts are used to create a new database object of some sort: a table, a stored procedure, and the like. Change scripts are used to update an existing database object.

Create Scripts

Using Server Explorer, you can generate a create script for any object in the database. For example, to capture the create script for the BillOfMaterials table in the AdventureWorks database, you would right-click the table and select Generate Create Script to Project. All of the SQL necessary to create the table (and its associated objects such as keys, indexes, and so on) will be written to a file under the Create Scripts folder in the current database project.

You even can script an entire database with one click: Instead of selecting an individual table, select the database node in Server Explorer and then select the Generate Create Script to Project command.

When the script is created, double-clicking on its file in the project will launch the SQL Editor with the script's content.

Note

As an indication of the manual work that script generation saves, consider this: The create script for the BillOfMaterials table weighs in at more than 120 lines of SQL.

Change Scripts

The concept with change scripts is the same with two differences: Change scripts can be automatically generated only for tables, and the script will capture only changes made to the table instead of the creation of the table. The Table Designer provides easy access to change script generation. After making a change to the table within the designer, select Table Designer, Generate Change Script. The script will be constructed and placed within the Change Scripts folder in the current project.

Note

Because Visual Studio generates the change script by looking at your current edits and comparing them to the table structure as it exists in the database, you can only generate a change script before you have saved your changes down to the database.

Executing a Script

After a script is created, you have the option to run the script against a database. You kick off script execution in the Solution Explorer by right-clicking on the script file. There are two options in the pop-up menu for running the script: a Run command and a Run On command. The Run command will run the script against the database specified in the default database reference. The Run On command allows you to manually specify which database, from the list of database references, should be the target of the script.

Database References

If you recall from the Database Project Wizard, you are prompted for a database reference to include in the project. But database projects support the ability to have more than one reference. If you have more than one reference, you can specify which of the available references is the default reference by right-clicking on it in Solution Explorer and selecting Set As Project Default.