Database Projects

Database projects are collections of one or more data connections that enable you to access your server database from within Visual InterDev. Database projects are also available within Visual C++ and Visual J++. Using database projects in addition to Web projects gives you the following benefits:

• Data connections aren't required in a database project until you want to run a SQL script against a database. In addition, you can rename data connections in a database project.
• You can use source control to ensure that the database and its structure are under the administrator's control.
• You can easily deploy the database to other projects.

One of the main benefits of working with database projects is that you can create and manage database objects using SQL scripts. You can also put these files under Microsoft Visual SourceSafe control.

To create a database project:

1. Choose File|New Project from the main menu.
2. Select the Database Projects folder in the New Project dialog box, as shown in Figure 12-20.
3. Enter the name of the new database project, and click Open.
4. Select the data source from the Select Data Source dialog box, and click OK.
5. If connecting to SQL Server, enter the Login ID and Password for the data connection and click OK.

Figure 12-20. The New Project dialog box showing the New Database Project icon.

Once you have created a database project, you can use SQL script files to create and manage your database objects and store other frequently used SQL commands.

To create a new SQL script file and add it to a project in Visual InterDev:

1. Expand the database project folder in the Project Explorer.
2. Right-click the data connection, and choose Add SQL Script from the context menu. The Add Item dialog box is displayed, as shown in Figure 12-21.
3. Select the SQL Script icon in the right pane. Enter a name for the SQL script in the Name text box, and click Open. The SQL Editor window is displayed, and the SQL script is added to the Project Explorer under the data connection node.
4. In the SQL Editor window, enter SQL statements that define the database objects you want to add to the database project.

Figure 12-21. The Add Item dialog box, where you can create a new SQL script or choose from an existing SQL template.

As you can see in Figure 12-21, you can use the SQL templates already supplied in addition to creating new SQL script files. You can select a SQL script file that creates a table, view, stored procedure, trigger, or query. If you select the Database Query option, Visual InterDev will launch the Query Designer. If you select any of the other options, Visual InterDev will launch the source code editor with the template SQL code ready for you to customize. Figure 12-22 shows the template code for creating a new table.

Visual InterDev makes it easy for you to generate SQL scripts for any existing database objects within your database project. You can select a single database object from within Data View or select multiple database objects.

To create a SQL script for creating a database object:

1. Within Data View, select one or more database objects for which you want to generate a SQL script to create the objects.
2. Once you have selected the database objects, simply right-click them and choose the Copy SQL Script command from the context menu.
3. From within Project Explorer, right-click the data connection object and choose Paste.

Your SQL script will now be added to the project. If you are using source control, you'll be prompted about whether you want to add the SQL file to source control on the server. The prompt is shown in Figure 12-23.

Figure 12-22. A database project showing the template SQL code for creating a new table.

Figure 12-23. The Push Add To Server dialog box asks you whether you want to add your SQL script to source control.

The following code shows an example of a SQL script that is generated for the Login table in the VI-Bank project. You can see that the script includes not only the CREATE TABLE statement but also the appropriate SQL syntax to add various constraints such as primary and foreign key constraints and check constraints.

If Exists (Select * From sysobjects Where      name = 'Login' And user_name(uid) = 'dbo')     Drop Table dbo.Login Go Create Table dbo.Login     (     ssn numeric(18, 0) Not Null,     userid varchar(8) Not Null,     password varchar(8) Not Null     ) Go Alter Table dbo.Login Add Constraint     PK_Login Primary Key Nonclustered     (     ssn     ) Go Alter Table dbo.Login Add Constraint     CK_Login Check (len(password) >= 4) Go Alter Table dbo.Login Add Constraint     FK_Login_Customer Foreign Key     (     ssn     ) References dbo.Customer     (     ssn     ) Go 

In addition to SQL Server database objects, the Copy SQL Script command also works with Oracle views, triggers, stored procedures, and functions, but not with Oracle tables and synonyms.

To execute a SQL script:

1. Within Project Explorer, select the script or scripts you want to execute. You can select more than one script at a time.
2. Right-click, and choose Execute from the popup menu.

Adding a Database Project to Source Control

Database projects consist of folders and SQL script files. You can add these files and folders to source control just as you do with other files, such as HTML and Active Server Pages Web pages.

To add a database project to source control:

1. In Project Explorer, right-click the database project name and choose Add To Source Control from the context menu.

2. Login to Visual SourceSafe by entering your Username and Password, as shown in Figure 12-24.

   

   Figure 12-24. The Visual SourceSafe Login dialog box.

3. Enter a project name in the Add To SourceSafe Project dialog box, and click OK.
4. Click OK on the Add To Source Control dialog box to add any existing files within the database project to source control.

Once you have placed your project under source control, you can check files in and out by selecting the file you want to work with, right-clicking to access the context menu, and then choosing the relevant menu option. In addition to being able to check files in and out, you can also undo the check-out status of any file. When a file is checked out for modification within your database project, you'll see a red check mark alongside the file within Project Explorer. When a file is checked in to Visual SourceSafe, you'll see a blue padlock icon alongside the file.

In addition to the right-click menu, you can access even more source control functionality by choosing Project|Source Control from the main menu. Figure 12-25 shows the available options on the Source Control menu.

In Figure 12-25, you'll see that the highlighted menu item is labeled SourceSafe. This menu item allows you to launch the Visual SourceSafe Explorer if you have it installed on your workstation. The Visual SourceSafe Explorer is a client tool that interacts with a Visual SourceSafe database in much the same way as a database project does. You do not need to have this tool installed on your workstation to place scripts under source control, but it can provide you with another interface for working with your Visual SourceSafe database. Figure 12-26 shows the Visual SourceSafe Explorer after it has been launched from Visual InterDev. You can see that the Account_Balance.sql script is currently checked out to user Admin under a check-out folder on the user's workstation.

Figure 12-25. The menu options displayed after choosing Project|Source Control within a database project.

Figure 12-26. Visual SourceSafe Explorer showing a SQL script named Account_Balance.sql that is checked out to the Admin user.