Using the Data View Window | Ado Examples and Best Practices

Team-Fly

The Data View window is exposed by clicking on the yellow disk icon on the toolbar or by clicking View/Data View window. Opening the Data View window brings you directly into the Visual Database Tools world.

The Data View window is designed to help you construct and manage your data sources. You begin by creating a Data Link, which is simply a captured ADO connection string persisted to the Registry. A Data Link shares ancestry with the Universal Data Link (UDL) technology that persists connection strings to a file. However, you can't use a UDL to create a Data Link in the Data View window even though they both use the same ADO dialog boxes to create their links to your data source. Nor do UDLs appear in the list of known Data Links when you open your Data View window.

Creating Data Links

Creating a Data Link is intuitive. When Visual Basic starts, no Data Links exist—you have to create them yourself, even if you've registered ODBC DSNs or created OLE DB UDL files. Basically, you have to click the third icon from the left on the Data View window—Add a New Data Link (see Figure 12-1)—or right-click the Data Links folder and choose Add a Data Link. This opens the Data Link Properties page. This is where you specify the OLE DB provider and all of the other parameters needed to identify the server, database, and yourself to SQL Server. When you start Visual Basic 6.0, you should see all Data Links created in the past by whatever means, or you're supposed to. If you create a Data Link and Visual Basic crashes (or you crash it), Visual Basic won't have an opportunity to save your Data Link.

click to expand
Figure 12-1: Visual Basic 6.0 Data View Window—adding a new Data Link

Once a Data Link is created, you can use it to explore what your data provider can expose. Not all data providers are created equally—many expose very limited functionality through the Data View window. Consider that ADOX, which was designed to support DDL operations, was not released until well after Visual Basic 6.0 and these tools were developed. It's the provider itself and its functionality that know how to respond to requests to expose underlying database functionality.

Working with Your Database Schema

Both the SQL Server and Oracle providers know how to expose database diagrams, but this is new for Visual Basic 6.0. The database diagrams permit you to map the database schema and primary key/foreign key relationships. As a matter of fact, if the database exists, you can construct tables and relationships using nothing but the Data View window. The design table dialog box supports a full range of table-column criteria, including identity columns, datatype, length, precision, scale, identity seed, GUID designation, and nullability. It also supports the rather startling capability to insert columns between other columns. This can be particularly problematic for existing applications that have hard-coded programs based on SELECT * queries, as they expect specific columns to be returned in a specific order. A misguided SA can really throw a wrench in the works by inserting columns between existing columns.

Adding a new table to a database can be done quite easily—assuming your UserID has sufficient permission. I added a table named Test to one of my sample databases by filling in the Data View design dialog box. The Data View window "table design" dialog box created the following script to be executed on demand. That is, no changes were made until the database diagram was saved. Notice how it manages the whole operation using transactions.

 BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO COMMIT BEGIN TRANSACTION CREATE TABLE dbo.TEst     (     Au_ID int NOT NULL,     YearMarried varchar(50) NULL,     Photo image NULL,     Bibliography text NULL     ) ON [PRIMARY]      TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE dbo.TEst ADD CONSTRAINT     PK_TEst PRIMARY KEY NONCLUSTERED     (     Au_ID     ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION ALTER TABLE dbo.Title_Author ADD CONSTRAINT     FK_Title_Author_TEst FOREIGN KEY     (     Au_ID     ) REFERENCES dbo.TEst     (     Au_ID     ) GO COMMIT

The schema diagram can be annotated to aid in documentation. It offers a great way to visualize the entire database or a selected subset of the tables and their relationships in a single glance. To establish or modify a primary key/foreign key relationship, simply drag from the primary key column of one table to the foreign key column of another. The Data View window takes care of the rest. A dialog box is exposed to verify the relationship, as well as to enable a number of update options.

The database diagram window indicates these relationships automatically by scanning the database's DDL information returned from the provider. Note the "key" symbol at the "one" end of the relationship line and the "infinity sign" at the "many" end of the relationship. For example, note that the Authors Au_ID column has a one-to-many relationship with the Title_Author.Au_ID column. This means there can be many titles for a specific author. The diagram also shows the Title_Author.ISBN column has a many-to-one relationship with the ISBN column of the Titles table—there can be many authors for a single ISBN (book).

Exploring Tables, Views, and Stored Procedures

One of the benefits of the Data Window is that it exposes the data interfaces of SQL Server. Once you click the + next to a Data Link, Visual Basic (and ADO) opens the connection and it queries for all of the DDL structures in the database. This exposes all of the database designs, tables, views, and stored procedures that are visible to your login ID. Yes, the database schemas you and the rest of your team create are all stored in the database where everyone can see them. As I said before, if you don't have permission to see portions of the database, they won't appear in the list—you won't even get connected unless you have permission to log on. Using the appropriate folder icons, go ahead and explode the lists one by one. I clicked on the Pubs folder. If you do, too, you should now see something similar to what is shown in Figure 12-2. Notice that I also (single) clicked on the Authors table and exploded the column names.

click to expand
Figure 12-2: Visual Basic 6.0 Data View window—a typical database diagram

Each table can be explored to view its columns, and if you right-click on a specific column, you can view the column properties including the ADO datatype. If you right-click on the table name, you can enter the design mode or simply dump the table to a grid to edit.

If your provider supports it, the Views folder is also accessible. It exposes any views created on the database, or you can create your own views as needed, using a separate dialog box.

SQL Server and Oracle both use stored procedures quite heavily, and the Data View window provides basic support for viewing, authoring, and modifying stored procedures. While the "debug" function is basically crippled, you do have the ability to examine both returned and input parameters from stored procedures.

What's Missing

There are a few things missing in the Data View window dialog boxes that might make it hard to get started and maintain your production database. You'll find that there's no way to create:

A new database. While the Data Link can make a connection to a data provider and select an initial catalog (default database), it provides no functionality to actually create the database in the first place. You'll have to use the tools that come with the database for that—assuming there are some.^[2]
A secondary index. You can specify a primary key—even a key with multiple columns for a new or existing table, and this operation creates a basic lookup index. However, there is no mechanism to create clustered or other secondary indexes to assist in locating or joining rows in a query.
Permissions. The Data View window assumes you have authority to make the changes you're making and that you'll have other tools for delegating that authority to your users.
UserIDs or passwords. Your ability to access the data might well be gated by the permission level of your UserID, and you should not be developing using SA.
Rules, defaults. While the queries you write depend on how these constraints behave, there is no mechanism for viewing or modifying them.
Triggers. Although you can create stored procedures, there is no way to assign these stored procedures to trigger duty.

You'll have to use your own database tools to perform any of these basic database functions. To help get you over the hump with some packages, I've included a sample application on the CD that can be used to create SQL Server databases. You'll have to find a way to create your own non-Microsoft databases. Yes, this is basically the same application I posted on my Web site (www.betav.com) some time ago. I made another pass on this code and spruced it up a bit for the CD.

There are also a few issues that make the Data View window (in Visual Basic) seem unfinished:

You can't use the Query Designer to help construct stored procedure code. While this is supported in the Visual InterDev version of this tool, it's not in Visual Basic, and it's sorely missed. Yes, you can type in the code by hand, but you're on your own when it comes time to code the joins.
The stored procedure code window editor has a number of, shall we say, interesting problems. For example, it can be easily confused while performing simple edits, or while scrolling, or while typing.
Although you can create a new DSN from the Data View window, you can't alter an existing DSN. This requires launching the ODBC applet from Windows.
There's no way to control other ODBC behavior from the Data View window. For example, you can't disable or tune connection pooling as you can from the ODBC applet dialogs.

Warning

If you select a table in the database diagram or the Data View window and press the Delete key, the Data View window will delete the table from the database quite unceremoniously.

^[2]MSDE is not shipped with any of the Visual Database Tools. You won't be able to create a database or perform any database maintenance functions unless you write TSQL queries or find another source of tools.

Team-Fly