Visual Database Tools
Database connectivity is almost essential in every application you create, regardless of whether it’s a Windows-based program or a web-based site or service. When Visual Studio .NET was first introduced, it provided developers with a great set of options to navigate to the database files on their file system and local servers, with a Server Explorer, data controls, and data-bound components. The underlying .NET Framework included ADO.NET, a retooled database engine that works most efficiently in a disconnected world, which is becoming more prevalent today.
Visual Studio 2005 took those features and smoothed out the kinks, adding tools and functionality to the IDE to give you more direct access to the data in your application. This chapter looks at how you can implement data-based solutions with the tools provided in Visual Studio 2005, collectively termed the Visual Database Tools.
Database Windows in Visual Studio 2005
There are a number of windows that specifically deal with databases and their components. From the Data Sources window that shows project-related data files and the Data Connections node in the Server Explorer to the Database Diagram Editor and the visual designer for database schema, you’ll find most of what you need directly within the IDE. In fact, it’s unlikely that you’ll need to venture outside of Visual Studio for most application solutions to edit database settings.
Figure 38-1 shows the Visual Studio 2005 IDE with a current database editing session. Notice how the windows, toolbars, and menus all update to match the particular context of editing a database table. The next few pages take a look at each of these windows and describe their purpose so you can use them effectively.
Chapter 38
Figure 38-1
Server Explorer
In the last chapter you saw how the Server Explorer can be used to navigate the components that make up your system (or indeed the components of any server to which you can connect). One component of this tool window that was omitted from that discussion is the Data Connections node, where the databases known to Visual Studio 2005 appear.
Contrary to Microsoft’s own current set of documentation, the Servers node does not display a child node containing SQL Servers running on each server. Instead, you’re forced to create a direct connection to the database and server you need through the Data Connections node.
Figure 38-2 shows the Server Explorer window with an active database connection open. The database icon displays whether you are actively connected to the database or not, and contains a number of child nodes dealing with the typical components of a modern database.
The database node provides you with a hierarchical view of your database schema. Each of the main sections not only lists the primary items, but also provides as detailed a view as possible. The screenshot in Figure 38-2 shows the Customers table expanded to show its fields. The Views, Stored Procedures, and Functions folders are similar, with extra nodes shown for each parameter and field in every component.
Visual Database Tools
Figure 38-2
To add a new database connection to the Server Explorer window, click the Connect to Database button at the top of the Server Explorer, or right-click on the Data Connections root node and select the Add Connection command from the context menu.
If this is the first time you have added a connection, Visual Studio will ask you what type of Data Source you are connecting to. Visual Studio 2005 comes packaged with a number of data source connectors, including Access and Oracle as well as a generic ODBC driver.
Two new additions with the introduction of Visual Studio 2005 and SQL Server 2005 are Microsoft SQL Server Database File and Microsoft SQL Server Mobile Edition. The Mobile Edition entry is self-explanatory; it connects to mobile device–based SQL Server databases.
However, the Database File option is a new concept for SQL Server that borrows on the easy deployment model of its lesser cousins, Microsoft Access and MSDE. With SQL Server Database File, you can create a flat file for an individual database. This means you don’t need to store it in the SQL Server database repository, and it’s highly portable — you simply deliver the .mdf file containing the database along with your application.
Once you’ve chosen the Data Source type to use, the Add Connection dialog appears. Figure 38-3 shows this dialog for a SQL Server Database File connection, with the settings appropriate to that data source type. You are taken directly to this dialog if you already have data connections defined in Visual Studio.
The Change button returns you to the Data Sources page, enabling you to add multiple types of database connections to your Visual Studio session. Note how easy it is to create a SQL Server Database File. Just type or browse to the location where you want the file and specify the database name for a new database. If you want to connect to an existing database, use the Browse button to locate it on the file system.
Chapter 38
Figure 38-3
Generally, the only other task you need to perform is to specify whether your SQL Server configuration is using Windows or SQL Server Authentication. The default installation of Visual Studio 2005 includes an installation of SQL Server Express, which uses Windows Authentication as its base authentication model.
The Test Connection button displays an error message if you try to connect to a new database. This is because it doesn’t exist until you click OK, so there’s nothing to connect to!
When you click OK, Visual Studio attempts to connect to the database. If successful, it adds it to the Data Connections node, including the children nodes for the main data types in the database, as discussed earlier.
If the database doesn’t exist and you’ve chosen a connection type such as SQL Server Database File, Visual Studio 2005 will also attempt to create the database file for you.
Table Editing
The easiest way to edit a table in the database is to double-click its entry in the Server Explorer. An editing window is displayed in the main workspace, consisting of two components. The top section is where you specify each field name, data type, and key information such as length for text fields, and whether the field is nullable.
Right-clicking a field gives you access to a set of commands that you can perform against that field, as shown in Figure 38-4. This context menu is actually the same as the Table Designer menu that is displayed while you’re editing a table, but it is usually easier to use the context menu because you can easily determine which field you’re referring to with the menu commands.
Visual Database Tools
Figure 38-4
The lower half of the table editing workspace contains the Column Properties window for the currently selected column. You can use this section to specify the same set of properties available in the top grid area along with additional database setting such as auto-number fields.
Shown in Figure 38-5 is a sample Column Properties window for a field that has been defined with an Identity clause that is automatically incremented by 1 for each new record added to the table.
Figure 38-5
Chapter 38
Relationship Editing
Most databases that are likely to be used by your .NET solutions are relational in nature, which means you connect tables together by defining relationships. To create a relationship, select one of the tables that you need to connect and click the Relationships button on the toolbar, or use the Table Designer Relationships menu command.
The Foreign Key Relationships dialog is displayed (see Figure 38-6), containing any existing relationships that are bound to the table you selected. Click the Add button to create a new relationship, or select one of the existing relationships to edit. Locate the Tables and Columns Specification entry in the property grid and click its associated ellipses to set the tables and columns that should connect to each other.
Figure 38-6
In the Tables and Columns dialog, first choose which table contains the primary key to which the table you selected will connect. Note that the Foreign Key table field is populated with the current table name and is disabled.
Once you have the primary key table, you then connect the fields in each table that should bind to each other. You can add multiple fields to the relationship by clicking in the blank row that is added as you add the previous field. When you are satisfied with the relationship settings, click OK to save it and return to the Foreign Key Relationships dialog.
Add any additional relationships for the selected table until you have met the requirements of your database design and then click the Close button to return to the main IDE.
Views
Views are predefined queries that can appear like tables to your application and can be made up of multiple tables. Use the Data Add New View menu command or right-click on the Views node in Server Explorer and choose Add New View from the context menu.
Visual Database Tools
The first task is to choose which tables, other views, functions, and synonyms will be included in the current view. When you’ve chosen which components will be added, the View editor window is displayed (see Figure 38-7).
Figure 38-7
This editor should be familiar to anyone who has worked with a visual database designer such as Access. The tables and other components are visible in the top area, where you can select the fields you want included. The top area also shows connections between any functions and tables.
The middle area shows a tabular representation of your current selection, and adds columns for sorting and filtering properties, while the area directly beneath the tabular representation shows the SQL that is used to achieve the view you’ve specified. The bottom part of the view designer can be used to execute the view SQL and preview the results.
Stored Procedures and Functions
To create and modify stored procedures and functions, Visual Studio 2005 uses a text editor such as the one shown in Figure 38-8. While there is no IntelliSense to help you create your procedure and function definitions, Visual Studio doesn’t allow you to save your code if it detects an error.
Chapter 38
Figure 38-8
For instance, if the SQL function in Figure 38-8 were written as shown in the following code listing, Visual Studio would display a dialog upon an attempted save, indicating a syntax error near the closing parenthesis because of the extra comma after the parameter definition:
alter function dbo.function1
(
@parameter1 int = 5,
)
returns table |
|
as |
|
return select |
myid, firstname, lastname, address, phone |
from |
mytable1 |
You can use the Edit Advanced menu in the IDE to format the text with commonly required tasks such as setting the code to all uppercase or lowercase, and to swap between using tab characters and spaces.
Database Diagrams
You can also create a visual representation of your database tables via database diagrams. To create a diagram, use the Data Add New Diagram menu command or right-click the Database Diagrams node in the Server Explorer and choose Add New Diagram from the context menu.
When you create your first diagram in a database, Visual Studio may prompt you to allow it to automatically add necessary system tables and data to the database. If you disallow this action, you won’t be able to create diagrams at all, so it’s just a notification, rather than an optional action to take.
The initial process of creating a diagram enables you to choose which tables you want in the diagram but you can add tables later through the Database Diagram menu that is added to the IDE. You can use this menu to affect the appearance of your diagram within the editor too, with zoom and page break preview functionality as well as being able to toggle relationship names on and off.
Because database diagrams can be quite large, the IDE has an easy way of navigating around the diagram. In the lower-right corner of the Database Diagram editor in the workspace is an icon displaying a four-way arrow. Click this icon and a thumbnail view of the diagram appears, as shown in Figure 38-9.
Just click and drag the mouse point around the thumbnail until you position the components you need to view and work with in the viewable area of the IDE (which is updated in real time).
Visual Database Tools
Figure 38-9
Data Sources Window
One more window deserves explanation before you move on to actually using the database in your projects and solutions. The Data Sources window, which shares space with the Solution Explorer in the IDE, contains any active data sources known to the project (as opposed to the Data Connections in the Server Explorer, which are known to Visual Studio overall). To display the Data Sources tool window, use the Data Show Data Sources menu command.
The Data Sources window has two main views, depending on the active document in the workspace area of the IDE. When you are editing code, the Data Sources window will display tables and fields with icons representing their types. This aids you as you write code because you can quickly reference the type without having to look at the table definition. This view is shown on the left-hand side of Figure 38-10.
When you’re editing a form in Design view, however, the Data Sources view changes to display the tables and fields with icons representing their current default control type (initially set in the Data UI Customization page of Options). On the right-hand side of Figure 38-10, you can see that both text fields and integer-based fields use TextBox controls, whereas the Date field will use a DateTimePicker control. The icons for the tables indicate that all tables will be inserted as DataGridView components by default.
