Visual Studio .NET arrives with a set of visual tools for creating and modifying database objects and schemas, such as diagrams, indexes, triggers, and stored procedures. These database tools offer a single interface to multiple database server backbends. At the time of this writing, only SQL Server and Oracle databases are supported; however, support for additional databases is expected in the near future.
Tracking changes and coordinating versions of database objects within an application can become quickly overwhelming. Before Visual Studio .NET, database objects, which were created within SQL Server's SQL Enterprise Manager, could be added to a source control application only after generating scripts for all the objects and then saving them to a file. This made script generation time consuming. Fortunately, Visual Studio .NET's IDE lets you create database objects and queries then check them into source control. (You learn about source control in Chapter 9.)
Database Projects
When creating a new application, it is often necessary to create a new database. By creating a database project, you can build a new database while tracking stored procedure changes for version control of database objects. Versioning of database objects is possible because all Visual Studio .NET projects, including database projects, can be added to Source Control. You can create a database project in Solution Explorer to organize your database references, scripts, and queries. This allows you to work with an application's database aspects without having to recreate database references or cluttering up other projects in a solution with data related objects because while your presentation resides in one project, the database exists in another. This separation helps the application to be more modularized and easier to manage. To create a new database project, follow these steps:
1. From the File menu, choose New * Project.
2. Expand Other Projects and Database Projects and then select Database Projects. You can create a new database connection or select an existing one as shown in Figure 6-9.
Figure 6-9: The Add Database Reference dialog box.
The end result is a database project (as shown in Figure 6-10) with everything you need in order to begin adding and manipulating database schemas and objects.
Figure 6-10: A database project.
Database Designer
The Database Designer is a visual database schema editor for Microsoft SQL Server and Oracle databases, which enables developers to add, modify, and delete database objects. These objects include tables, views, indexes, and stored procedures.
Creating a database table is as simple as right-clicking on the Tables icon of the database connection in Server Explorer and selecting New Table. A designer (shown in Figure 6-11) is provided for entering the new table's attributes. Follow the same process to create or modify views, indexes, and stored procedures.
Figure 6-11: The Database Designer's table design page.
Query Designer
The Query Designer allows developers to build queries visually. Using a visual view of tables and columns, developers can decide what data will be returned and how that data will relate to other tables.
Selecting tables and columns in the Query Designer is a quick and dirty way to select data for retrieval, but most applications require queries that do more than just return data. Often the data returned must be sorted by a specific column or other criteria that limit the returned data. Because the visual configuration and design of queries can never meet all application
requirements for data retrieval, the Query Designer allows for complete control over the development of queries through direct editing of queries.
No query is complete until it has been tested to verify that it meets your application's requirements and that it is bug free. To test your query, once you are finished building it, simply execute it in the Query Designer and examine the results for errors.
To create a query with the Query Designer, follow these steps:
1. Select Add Query … from the Project menu. The Database Query item is automatically selected. Give your query file the desired name and press Open. 2. You are presented with the Add Table dialog box that is shown in Figure 6-12,
allowing you to select the tables involved in your query. Select authors then press Add then Close.
Figure 6-12: The Add Table dialog box.
3. Select fields by checking boxes next to the columns as shown in Figure 6-13. The columns you select will be listed so that you can select sort options and filtering criteria. Also, you can see the SQL query syntax automatically generated each time you select or configure an option.
Figure 6-13: The Query Designer, including selected fields and a selected sort order. 4. Select a sort option. If you select more than one sort option, you can modify the sort
order to determine the order in which columns are sorted. Notice that the au_lname field is selected for Ascending sort order in Figure 6-13.
To run the new query, right-click anywhere in the Query Designer and select Run (Figure 6- 14).
Figure 6-14: The Query Designer as the query is executed.
Once a tested query has been idle for some time, Visual Studio .NET will attempt to save SQL Server resources by releasing what it considers to be an unnecessary connection or lock on data resources. You'll see the dialog box shown in Figure 6-15.
Script Editor
The Script Editor allows you to edit any script including SQL Server stored procedures and triggers and Oracle PL/SQL. Color-coding is available; however, no Intellisense is provided. (Look for this feature in future releases of Visual Studio.)
The Script Editor breaks SQL code into logical blocks of execution. These blocks are not always executed linearly because in T-SQL (Transact-SQL, the language of SQL Server stored procedures), conditional criteria can be specified before it is evaluated. When you debug a stored procedure, you will notice that breakpoints are not enforced at the line level. This is the same reason why SQL code is broken into logical blocks; each block of code is allowed a single breakpoint.
Stored Procedures
Stored procedures are precompiled database objects that contain code for implementing business rules, data retrieval, and data modification.
With that said, there is a distinct difference between precompiled stored procedures and compiled components. Components, such as .NET components and windows forms, are compiled into byte code, which is a lower-level machine language. Stored procedures are not compiled in the same way, although the path that is required to execute the stored procedure (the query plan) is compiled and stored in memory.
With regard to business rules, there is no speed or language advantage to building business rules into a stored procedure. Business rules in the database should be built into database constraints or triggers, effectively preventing an application from bypassing the rule.
Visual Studio .NET provides for the creation, modification, and deletion of stored procedures through the Server Explorer toolbox. Once a stored procedure is created, it must be tested and debugged if necessary, which is historically a difficult and time-consuming proposition. SQL Server did not provide a means of stepping through stored procedures until the release of SQL Server 2000.
Unfortunately, developers must use a separate tools set, SQL Query Analyzer, to step through a stored procedure. Visual Studio .NET allows developers to continue using a single tool set for developing all aspects of an application including stepping through and debugging stored procedures.
Right-clicking on a stored procedure in the Server Explorer exposes a set of functions that can be performed against the stored procedure including running and debugging. When running a stored procedure the Visual Studio .NET IDE will prompt for the parameter values the stored procedure is expecting, as shown in Figure 6-16.
Figure 6-16: The Run stored procedure dialog box.
To save time, the IDE saves values that you've entered for the stored procedure parameters for use as default values the next time you run the stored procedure. The execution results are displayed in the output window.
Debugging a stored procedure is the same as running a stored procedure except that while debugging occurs, breakpoints can be added to blocks of code in the stored procedure as shown in Figure 6-17. While stepping through a stored procedure, you can change the values of local variables, allowing you to test a wider range of possible situations.
Figure 6-17: Displays a stored procedure in debug mode.