Examining Visual Studio Database Developer Tools

Microsoft's Visual Studio (VS) 6.0, Enterprise Edition contains a collection of development systems and tools for building components, multitier database applications, and database-enabled Web sites. The collection includes the Visual InterDev Web development system and the Visual Basic (VB), Visual C++ (VC++), and Visual J++ (VJ++) application development systems.

The Web development system and application development systems feature an integrated set of database development tools called Visual Database Tools. Microsoft initially integrated these tools only in Visual InterDev but is now integrating Visual Database Tools into other VS products because of the tools' increasing popularity. Because of this integration, Visual InterDev, VB, VC++, and VJ++ now offer many useful database development tools such as Project Explorer, Data View, Property Pages, Query Designer, Table Designer, and Database Diagrammer.

Although Visual InterDev, VB, VC++, and VJ++ have common development tools, their development environments still differ. For example, when you're developing a database in VB, you use a data project template that adds controls and objects to a VB project. When you're developing a database in VC++, VJ++, and Visual InterDev, you use a database project template and apply the Visual Database Tools.

Microsoft is working toward implementing Visual InterDev's development environment in all the application development systems in the next release of VS. Because Microsoft is making Visual InterDev's development environment the common environment in VS, I'll show you how to use the Visual Database Tools in the Visual InterDev development environment. With these tools, you can view and create database projects and design the queries, stored procedures, diagrams, and tables that are a part of those projects.

Viewing an Existing Database Project

If you want to explore the contents of an existing database project, you begin by opening a database project. Multiple open windows appear. You use the tree nodes in the Project Explorer window to examine the project's contents. To view a project object's properties, you can use Property Pages. To get to the Property Pages window, click an object in Project Explorer and hit Shift+F4. Select the View Property Pages menu option, or right-click the object and select the Properties menu option. To view the project's data objects, you use the tree nodes in the Data View window. To view a data object's properties, right-click the object in the Data View pane and select the Properties menu option. The Stored Procedures window displays the database's stored procedures (i.e., compiled blocks of SQL code and optional procedural logic), and the Database Diagram window displays the database diagram (i.e., a graphical representation of the structure of a database and the relationships of the items in it).

Creating a Database Project

Visual InterDev lets you create database projects and Visual InterDev projects. When you create a database project, Visual InterDev initializes project properties and opens a Data View pane and Project Explorer pane containing a Connection object. When you create a Visual InterDev Project, Visual InterDev includes a global.asa object that has a DataEnvironment node. You add a database connection by clicking the DataEnvironment node and filling in the dialog boxes.

Whether you create a database project or Visual InterDev project, you get dialog boxes that prompt you for information about the ODBC or OLE DB data source, its driver or provider, and connection attributes. Some of your responses, such as whether to use client-side or server-side cursors, can affect performance and other behaviors of an application. The ODBC driver and OLE DB providers include online Help files that can help you set up the data source. (You can also check out Database Magic with Ken North, Prentice Hall.) After you establish a database connection, you can design queries and stored procedures.

When developers create applications, components, and scripts, they use ad hoc queries, prepared queries, and stored procedures. An ad hoc query is an SQL statement that you typically execute one time. A prepared query, which you typically use for executing repetitive queries, is a statement that has a query execution plan. Developers often design prepared queries that have parameters; before users can execute the prepared query, they must substitute their values for those parameters. For example, you might prepare a statement such as insert into office (city, region) values (?,?) and substitute Los Angeles, Western at execution time.

Because Visual InterDev uses multiple data-access technologies, you can use ad hoc queries, prepared queries, or stored procedures in your database project. The Visual Database Tools let you design queries with or without parameters and create and edit stored procedures.

Designing Queries

To create a query, you right-click a connection in Project Explorer and select the Add Item menu option. You have the option of adding a SQL script, database query, view script, stored procedure, table script, or trigger script. If you select query, Visual InterDev launches the Query Designer. This tool lets you create queries by using the iterative process of designing a query, running it, viewing the results, and modifying the query, if necessary. When you've finished with this process, you save the query as a .dtq file.

The Query Designer displays two windows: the Project Explorer window and the Design window. The Design windows consists of a pane, two grids, and a text window. You can use the top pane to add existing tables to your query by dragging them from a Database Diagram or Data View window. (Being a visual development environment, Visual InterDev's Visual Database Tools offers drag-and-drop functionality.) You use the upper grid to specify the query specifics. This design grid includes columns for the table name, column name, sort type, sort order, and search criteria. Below the design grid is a text window for SQL statements. You can either use the SQL statements that the Query Designer generates, or you can enter custom SQL statements. After you execute a query, the Query Designer displays query results in the lower grid. If a pane, grid, or text window gets out of synch with another, the Query Designer greys out the others to provide a visual indication that a problem exists.

Using the Query Designer isn't difficult. You simply fill the Design window's pane, text window, and grids with the appropriate information. For example, here's how I completed the Design window for the query:

Top pane. I wanted to perform a keyword search in my database that contains magazine articles, so opened the existing Articles database project and selected the Data View pane. I clicked the Articles database tree node to open a list of tables in that database. I dragged the Author, Article, and Keyword tables from the Database Diagram window to the top pane of the Design window.

Upper design grid. First, I filled the Column and Table columns in the upper design grid by clicking the appropriate checkboxes in the tables in the top pane. For example, when I clicked the AuthorID checkbox in the Article table in the top pane, the Query Designer automatically fills in the design grid's Column and Table columns with the appropriate values (i.e., AuthorID and Article, respectively). Then, I entered the keyword value of 'SQL' as the search criterion for Idxkey to generate the where clause of the query.

Text window. As I was specifying the columns, tables, and search criteria in the design grid, the Query Designer was simultaneously generating a SQL statement in the text window. Because this SQL statement already included a where clause, I didn't have to edit the SQL statement or enter a new one.

Lower results grid. After I used the Run menu option to execute the query, the Query Designer displayed the results in the lower grid.

When I was satisfied with the query's design and results, I saved the query as by_keyword.dtq. The Query Designer subsequently displayed by_keyword.dtq as a node in the Project Explorer window.

Designing Stored Procedures

To create stored procedures, you start by selecting a database from the tree diagram in the Data View window. Click the database name (e.g., articles (BURNS)), and select Add to create a new procedure. (To edit an existing procedure, simply click the procedure.) Visual Database Tools opens an edit window that uses color-coded text. When you add a new procedure, the stored procedure editor provides a procedure template.

You can debug stored procedures without leaving the Visual Database Tools environment. Before exercising the option to remotely debug procedures, you must perform a Visual Studio installation for both clients and servers. When debugging, you can remotely monitor the procedure as it executes on the server. To debug a procedure, you first set breakpoints and watches and then click Execute. Visual Database Tools opens up two windows: Execute and Output. In the Execute window, you specify the values for the procedure parameters. The Output window displays the stored procedure's outcome.

Designing Diagrams and Tables

o facilitate designing SQL databases, Visual InterDev includes Table Designer and Database Diagrammer. You can use these design tools to create database diagrams, create tables, and specify constraints, indexes, and relationships.

To create a database diagram, you use the Database Diagrammer. In the Data View pane, click a database node. For example, click the articles (BURNS) database node in the Data View pane. The database node expands to show Database Diagrams, Tables, Views, and Stored Procedures. To use the Database Diagrammer, click the Database Diagrams node. You can use the Database Diagrammer to create a new diagram by right-click the Database Diagrams node, or you can use the Database Diagrammer to edit existing diagrams by double-clicking the diagram's name in the list under the Database Diagrams node. In either case, Visual InterDev opens a new pane containing the diagram.

To create a table, you use the Table Designer. In the Data View window, right-click a database node and select the New Table option to add a new table. The Table Designer prompts you for a table name and displays a grid. The grid contains columns that help you enter the necessary data, such as column name, type, length, precision, scale, default value, and nullability. You can also enter identity information and specify whether the column is a globally unique ID (GUID) for a row. When you visually mark a column as a primary key, Visual InterDev generates a primary key constraint and a primary key index. To create foreign key relationships, you use drag-and-drop techniques to create links between tables.

You can use the table's Property Pages to add indexes and constraints, such as check constraints. You can also set table and column properties by using Property Pages in the Database Diagrammer mode. When you use the Database Diagrammer to change tables, Visual Database Tools updates the appropriate diagrams and vice versa. Visual Database Tools optionally generates a change file that includes the text of SQL statements you used to create or update tables and triggers.

An Impressive Set

Visual InterDev offers an impressive collection of Visual Database Tools for client and server development. Project Explorer, Data View, Property Pages, Query Designer, Table Designer, and Database Diagrammer are a first-rate set of developer tools.