Visual Basic .NET Database Tools
This chapter is from the book
This chapter is from the book
This chapter is from the book
Tools, tools, tools. It's funny, but if I were asked to name the one thing that has changed the most since the 1980s, as far as programming is concerned, I would have to say it is the tools available to the programmer. As far as the programming itself goes, we really aren't doing much more real work than we were 15 years ago. We're basically making screens, updating data, creating reports, and doing background (now called server-side) processing. Yes, it's a lot cheaper and a lot faster and a lot more complicated. In 1985, $40,000 got me a COBOL compiler for the Data General Minicomputer I programmed, and it was just that, a compiler. No editor (one came with the operating system), no IDE, no tools, just a command-line compiler and a bunch of thick books. No database tools either. If you wanted a database you had to shell out another $20,000 to $40,000, depending on the number of users. Moreover, all you got was indexed sequential access method (ISAM) which came with a database manager and some backup and integrity-checking utilities. There was also a very primitive query tool.
The first IDEs came with some of the MS-DOS PC-based software, such as dBase by Ashton-Tate and Boland's Paradox. I remember seeing my first real IDE in 1987 when I picked up a copy of Borland's Turbo-Pascal (which came on a single 5-inch diskette shrink-wrapped with the book). It had a color-coded editor (which I didn't even know about with my monochrome monitor), on-the-fly syntax checking, and built-in compiler. I was impressed. By the way, this is the tool that would evolve into Delphi a few years later. Even though I was impressed by the IDE, I could never quite swallow Pascal, with its weird block syntax.
It wasn't until VB 1.0 for Windows appeared that I felt that the IDE had finally found its stride. Since then it seems to be more about tools than programming. Originally intended to boost programmer productivity, the tools have become an entity unto themselves, causing programmers to spend more time learning the tools than the actual language. This has created a knowledge gap, where many of today's programmers are just tool users. I have read articles recently advocating dumping the IDE and returning to the simpler days of text editors and command lines. 1 I would not go that far, but I agree that the IDE can mask enough of the intricacies of programming that it can create a tendency toward laziness. However I also believe that the benefits of the IDE far outweigh the liabilities. Imagine trying to manage one of today's multimodule, multiproject solutions without an IDE with integrated source control. What a nightmare!
Some of my projects have literally hundreds of source files. The real-time syntax checking saves a lot of compile-edit-compile iterations. The dynamic online help is a lifesaver. Interactive debuggers have saved me thousands of hours scouring for bugs. This is not to mention the myriad other aids like spy++, the OLE tools, and so forth. Tools should enhance, not mask, a programmer's abilities. This is what the integrated database tools do in Visual Studio .NET. Without ever leaving the IDE, a programmer can create databases, tables, stored procedures, database diagrams, and views. In this chapter, we will look at the IDE and the tools provided to help with database programming.
A Roadmap of the IDE
With this version of Visual Studio, Microsoft finally achieved its goal of unifying the development environments into one IDE. Until this version, VB had its own IDE separate from the standard IDE used for C++ and other languages. One reason for this was because VB was an interpreted language while the others were compiled. Another reason was because VB developed along a separate evolutionary path than the other Microsoft development tools. The VB debugger was proprietary, but had an edit-and-continue feature that made C++ programmers jealous. There were many integrated tools, such as the database manager and data environment designer, that were not available in the other IDE.
The new IDE has some equivalents to the tools in the old IDE, but many have been combined into the Server Explorer, and some are made obsolete by the radical changes in VB itself. (See Figure 3.1.) Others have been incorporated into the various designers.
)
Figure 3.1. The opening screen of the Visual Studio IDE.
Take a look at the IDE startup screen. Notice that nowhere does it say anything about VB. This is because the same IDE is now used for VB, C#, and C++, as well as any number of other third-party languages supported by .NET, including COBOL. By default, the Start page shows in the main workspace at startup. This is just an embedded web browser window. It doubles as the display for the online help, which is all in HTML now, and also as a portal to the various .NET web sites that Microsoft thought you might be interested in. I will leave it up to you to explore the pages. If you really hate this, you can turn it off through the Options dialogs. I initially turned it off, but I found some of the resources rather useful, and it is a quick way of opening your last project with a single mouse click, so I leave it on now. The IDE with a Windows application open is shown in Figure 3.2.
)
Figure 3.2. The IDE with a new Windows application open.
VB 6—I don't know if you are one of these holdouts, like I was. In VB 6 and prior versions, the IDE supported a single-document interface (SDI) display mode. This was a holdover from the 16-bit Windows IDEs. In this mode most of the windows, including the designers, displayed as top-level floating windows, allowing the desktop to show through. Since I started with VB 1, I tended to prefer this mode to the newer multiple-document interface (MDI) style used in VB 5 and VB 6. The SDI mode is no longer supported, so we had best get used to the new tabbed view. If you can afford a larger monitor, use a high screen resolution, such as 1280x1024. Otherwise you will have to get used to scrolling the designers around if your forms cannot fit into the workspace.
The main workspace is the center of attention. This is where the various designers and code windows appear, surrounded by the many controls and tools available to you. The user interface (UI) uses a modified version of MDI layout called a tabbed view. All of the MDI child windows appear as tabs in the main workspace.
Two items of the IDE stand out: the Dynamic Help window, rollaway ancillary windows, and the solution explorer.
Dynamic Help takes F1 Help one-step further by automatically displaying all relevant Help topics each time the cursor is positioned over a keyword or a designer is open. I found this tool especially handy when first learning VB .NET and left it turned on because it did not slow down my typing, even on my slower laptop (300mhz PII).
Rollaway ancillary windows first appeared in the standard Microsoft IDE in a prior version. However, this is the first time the feature is available with VB so it is probably new to most of you. With the rollaway feature, some of the less frequently used windows roll out of the way and collapse into a tab when not needed, much like a window shade. When the user hovers the mouse pointer over the window's tab, the window rolls out. Most of the dockable windows have this option. It is enabled by clicking the pushpin icon.
In previous versions of VB the solution explorer was called the project explorer. It retains that function except that group projects are now called solutions. It also has a direct connection to the underlying file system. If you rename a file in VB, the IDE renames it on disk. If you delete a file from a project, it deletes it from disk. The good news is that it sends the file to the recycle bin so it can be recovered. If you want only to remove the file from the project, use the Remove From Project menu item instead. The solution explorer also allows you to act recursively on projects and solutions for things like source control functions. The solution now has properties that you can use to customize build types and other items.
The output window displays the results of program compiles. The task window displays compile-time errors that need to be fixed. These are linked directly to the source code windows so that when an error is fixed, it disappears from the task list. You can also jump directly to an error by double-clicking on the task. You can also use the task list to make notes to yourself, and so forth. This list persists between sessions.
If you have source code control, such as Visual SourceSafe, there is now a window that shows the status of all checked-out files. SourceSafe comes with the Enterprise Version of Visual Studio and higher. If you have SourceSafe Version 6.0 installed as a separate product, it will work with Visual Studio .NET Professional. A handy addition in this version is the inclusion of dynamic checkout. With this feature, if you attempt to edit a file that is checked-in, the environment will either prompt you for a checkout or silently check the file out, depending on your preferences. If you ever made substantial changes to a file only to discover that it was not checked out and somehow the read-only attribute got toggled off, you will appreciate this feature.
Working our way around, we come to the toolbox. The toolbox contains icons for Windows controls, database tools, and other components. It changes its contents based on the context. For example, if we are designing a web page, it will display the web controls. One annoying feature is that the Control tab doesn't show if you are in a code window. If you want to see what controls are available, you have to open a designer window first. I've seen more than one programmer mystified as to where all the controls went.
There are two main tools for managing databases in Visual Studio. Server Explorer allows you to create databases, manage tables and views, and create stored procedures and triggers. The database project allows you to manage scripts associated with the solution you are creating. With the database project you can create scripts to perform data definition language (DDL) and data manipulation language (DML) functions. DDLs are transact-SQL scripts that manage the database schema. DMLs are transact-SQL scripts that manage the data in the database. DDL and DML can be (and often are) combined in the same script.