Patrick Dengler
Microsoft Corporation
November 1999
Summary: Discusses the design of Database Projects, file-based projects included with the Microsoft Visual Studio suite. (5 printed pages) Includes:
Introduction
Getting Started
Source Control
Working with Code
The Editor
Building and Deploying
Summary
Microsoft® Visual Studio® version 6.0 gave us a lot of great RAD database support, but are you aware of the enterprise database development support? The Visual Studio development team talked with many real-world database developers, which lead us to the design of a simple yet little-known addition to the Visual Studio suite: Database Projects.
Database Projects are file-based projects created to store your database scripts, documentation, build files, and any other information associated with your database. As with any project in Visual Studio, your files are tightly and easily integrated into Microsoft Visual SourceSafe™. An additional database connection metaphor advances the project to a database build environment. From within a Database Project you can edit, test, build, and deploy your databases using scripts.
Using the new Visual Studio shell (which can be launched by opening Microsoft Visual InterDev® or Microsoft Visual J++®), create a new Database Project. These can be found in the Visual Studio folder in your program files. Immediately after creating a new database project you are prompted to add a database connection. This is not a required step, and you can later add and remove as many connections as you like; for illustration purposes, let's create a connection to a local SQL Server's pubs database. (Database Projects do not require SQL Server, but the tools are better oriented toward it.) The connection node appears in the project and can be treated like a folder (more later).
Figure 1. New database project
Notice that the Data View also shows the connection, and from here can you use all of the designers to create diagrams, tables, views, and stored procedures directly on the database. These are helpful and work directly against the database, but it is not considered a part of your project. It is the current view of your database. Your code is in the project.
The project is easily placed under source control. Your documents and files can all participate. Simply select Project/Source Control/Add to Source Control. Depending on your source-control system type and configuration, you may be asked to sign in or locate the system. Once this is completed, your project is now under source control and ready for team development and change control. (Note that other team members can easily enlist in this project by using the New Project dialog box, and clicking the Source Control.)
The primary role of a database project is to store and execute database scripts.
If you are building databases using scripts, you will already have a directory of folders and scripts. These can easily be added via the Project/Add Item dialog box using the existing tab. Just select the root of your project on disk and click Add Folder.
If you have not yet built a database and wish to start from scripts, you can use the Project/Add Item dialog box to add any kind of script. In the dialog box you will note that there are templates for scripts of type SQL, Stored Procedure, Table, Trigger, and View.
Figure 2: Add Item dialog box
These scripts are basic T-SQL templates for building each of these objects (the SQL Script being blank). For example, adding a table script opens the following template:
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Table_Name')
BEGIN
PRINT 'Dropping Table Table_Name'
DROP Table Table_Name
END
GO
/******************************************************************************
** File:
** Name: Table_Name
** Desc:
**
** This template can be customized:
**
**
** Auth:
** Date:
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- --------
**
*******************************************************************************/
PRINT 'Creating Table Table_Name'
GO
CREATE TABLE Table_Name
(
)
GO
GRANT SELECT ON Table_Name TO PUBLIC
GO
As noted in the template, it can be customized to meet your development methodologies. Adding and editing files in the installation directory of Visual Studio (usually \Program Files\Microsoft Visual Studio\), under the \Common\IDE\IDE98\Templates\Database Project Items\ folder, you can edit, remove, and add your own templates.
If you already have a database and you want to reverse-engineer the scripts, you can do so by using the Copy SQL Script functionality. In the Data View select any database item (table or stored procedure) or multi-select many or all items, and on the right-click context menu select Copy SQL Script. Next, select a folder in your database project and paste. It will create a file that has the script that represents the object or objects you copied. By default it includes the drop and permissions scripts, and for tables includes indexes, triggers, and keys, but this can be adjusted using Tools/Options and selecting the Data Tools.
The editor supports any text that can be executed against databases, but you will enjoy color coding for Transact SQL. When the SQL script is underneath the connection node a couple of interesting things can be done (remember that this is like a folder).
First, the query builder can be used to edit or create insert select, insert, update, or delete statements. Simply select the statement you wish to edit (or select nothing to create a new statement), and use the right-click context menu edit SQL (or insert SQL) and the query builder will be launched so you can visually edit your SQL.
Second, the script can be executed against the database. This can be done via the right-click context menu within the editor, or from the context menu on the file in the project explorer. The Execute command initiates the execution and the results are piped to the output window.
As with any of the editors in Visual Studio, you can use your favorite by selecting Open With from the context menu on the file in the project.
Multiple scripts can be executed by multi-selecting each of the files and then selecting Execute as before. The order in which you select them becomes the order in which they are executed. However, most of the database developers we talked to had complex build scripts not isolated to T-SQL. To support this we added the support of executing .bat files from the project tree. You can add the .bat file that builds your database to the project and execute it right from the IDE.
As mentioned earlier, you can create multiple connection nodes in the project by using Project/Add Data Connection; but, in this version the scripts only execute against the database connection node under which they reside. If you want your scripts to execute against a different database connection, you can change the connection properties of the node to point to a different database.
Database Projects are simple file-based projects, with some nice database features. These features allow enterprise database developers to develop, code control, build, and deploy SQL databases. Adding Database Projects to your Visual Studio solution integrates the entire team into the application building process.