Express Editions

Get a Lean, Mean Dev Machine with the Express Editions of Visual Basic and SQL Server 2005

Brian A. Randell

This article is based upon prerelease versions of Visual Basic 2005 Express Edition and SQL Server 2005 Express Edition. All information contained herein is subject to change.

This article discusses:

  • What's inside these new versions
  • Building a Windows Forms application
  • Powerful tools and Starter Kits
  • Creating and accessing data
  • Calling a Web service asynchronously
This article uses the following technologies:
Visual Basic and SQL

Code download available at:ExpressEditions.exe(578 KB)

Contents

New Lightweight Development Tools
Creating a Database
Queries
Tables and Relationships
Using Visual Basic 2005 Express
Data Binding
Creating a UI
Calling the Web Service
Wrapping It All Up

Ionly vaguely remember the first time I attempted to write a Windows®-based application. I was using C and writing for the 286. Actually I've tried to block the whole experience from my mind. Considering that I started programming on an HP 3000 mini using COBOL and RPG, programming with Windows was painful by comparison.

It wasn't until I first tried Visual Basic® 2.0 that I fell in love with programming in Windows. I have to say, I still love what I do. With the introduction of the common language runtime (CLR) and the Microsoft® .NET Framework, I really felt there wasn't anything I couldn't do. While some people enjoy programming with Windows using arcane editors such as EMACS or even editors that really aren't editors, like Notepad, I really enjoy the productivity of a powerful tool like Visual Studio®. However, the biggest problem with Visual Studio is its relatively large footprint. Wouldn't it be nice to have the core benefits of Visual Studio (a debugger, edit and continue, and IntelliSense®) in a nice lightweight version that lets me write Windows-based programs without the large overhead of an enterprise development tool?

New Lightweight Development Tools

Well, it seems Microsoft was reading my mind. As part of the new Visual Studio 2005 family of products, Microsoft will be releasing the following five new developer tools:

  • Visual Basic 2005 Express Edition
  • Visual C#® 2005 Express Edition
  • Visual C++® 2005 Express Edition
  • Visual J#® 2005 Express Edition
  • Visual Web Developer 2005 Express Edition

At their core, these products have many of the same features that their professional brethren do. Professional developer features such as full IntelliSense support, local debugger, Add Web Reference, and the improved Visual Data Tools will all be available in the Express products. For all the details visit Visual Studio 2005 Express Beta Products for feature lists, articles, and updated information on the entire family of Express developer tools.

In addition to these new development tools, the SQL Server team is providing an upgrade to the Microsoft SQL Server™ 2000 Desktop Engine (MSDE 2000). This new product is called SQL Server 2005 Express Edition and is based upon the SQL Server 2005 core engine. It is included as an optional component with the various Express editions of Visual Studio 2005 that I listed previously.

In addition to supporting the new features common to all editions of SQL Server 2005 (see the February 2004 issue of MSDN®Magazine for more information), the Express edition benefits from streamlined setup and deployment, integration with the various language-specific Express editions, and a reduced download size.

Just like MSDE 2000, SQL Server 2005 Express will have some limitations compared to its full-featured siblings. The biggest internal change from MSDE 2000 to SQL Server 2005 Express is that there will no longer be a workload governor. From reading the newsgroups and talking to developers, I know this has been a major point of confusion about MSDE 2000. The absence of a workload governor is a huge win for everyone building applications using MSDE 2000 but who wants to move to SQL Server 2005 Express. The chart in Figure 1 summarizes some of the requirements and major differences between MSDE 2000 and SQL Server 2005 Express.

Figure 1 MSDE 2000 vs. SQL Server 2005 Express

MSDE 2000 SQL Server 2005 Express
Can run on dual-processor machine Only utilitizes one processor
Can have up to 16 instances Can have up to 50 instances
Can utilize up to 2GB of RAM Can utilize up to 1GB of RAM
2GB database size limit 4GB database size limit
No GUI tools GUI tools as a separate download
Includes workload governor No workload governor
Deployment via MSI and merge modules Microsoft Installer (MSI) file only
.NET Framework is not required .NET Framework 2.0 is required
Supports Windows 98, Windows 98 Second Edition, Windows Me, Windows NT 4.0 SP5a, Windows 2000, Windows XP, and Windows Server 2003 Supports Windows 2000 SP4, Windows XP SP1, and Windows Server 2003 (including Web Edition)

Creating a Database

When I got my hands on an early copy of SQL Server 2005 Express and Visual Basic 2005 Express, I decided to build something fun. For a while now, I've wanted to create a catalog of all the books I have, but I also wanted to avoid as much data entry as possible. Since I know Amazon.com has a Web service, I figured I could put these two beta products to work and build a simple personal library application. In addition, I wanted to see how much work I could get done without writing a huge amount of code.

One of the biggest complaints about previous versions of MSDE was the lack of GUI tools to manage an MSDE instance. SQL Server 2005 Express solves this problem. It will have a complete set of graphical tools, written in managed code, to design and maintain your SQL Server 2005 Express installations. In order to keep the overall package size of SQL Server 2005 Express small, the GUI tools will be a separate download and installation. Figure 2 shows an early image of the tools. As the early builds I worked with did not have them available, I used the Visual Data Tools that are a part of Visual Basic 2005 Express (as well the other Express editions and professional editions of Visual Studio 2005).

Figure 2 SQL Express Manager

Figure 2** SQL Express Manager **

SQL Server Express installation is integrated into the various Express product SKUs. In my early build, I wasn't asked any questions during the installation of SQL Server 2005 Express; the only instruction that I gave it was to install. Without any further options, SQL Server 2005 Express was then installed as a named instance called SQLEXPRESS.

The first step in getting my application to work was to define my schema. I figured I'd need four core tables to get things going. Later I might decide to add additional tables to track who had borrowed my books and so on, but for this project, I only cared about the books—namely title, author, publisher, and date of publication.

In order to create a database, I launched Visual Basic 2005 Express. The initial startup screen is different from the regular Visual Studio version. Its purpose is to make getting started as easy as possible by immediately providing a launch pad of options rather than just a blank screen. Figure 3 shows a Beta 1 version of the Getting Started screen.

Figure 3 Visual Basic 2005 Express Startup Screen

There are two ways to create a SQL Server 2005 Express database using Visual Basic 2005 Express and the Visual Data Tools. Using my past experience with the existing tools in Visual Studio .NET 2003, I opened the Server Explorer window (labeled Database Explorer in Visual Basic 2005 Express) and created a new database by right-clicking the Data Connections node and selecting Create New SQL Server Database. When using the integrated tools in Visual Basic 2005 Express, you only get to define the SQL Server instance and the name of the database (location, file names, and so on are inferred). Note that in the Express editions, the Visual Data Tools will only let you edit local instances. The database and log files were stored in the default location specified when SQL Server 2005 Express was installed (typically under C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data).

SQL Server 2005 Express and Visual Basic 2005 Express support an alternative mechanism similar to using a Jet MDB file. First create a Visual Basic project. With a project created, I used the Add New Item dialog to add a Blank Database object (a SQL Server 2005 Express MDF file). The database connection uses a new connection string setting called AttachDBFilename to link to the database file. This option will force the database to be attached to the server when a connection is opened (if the database is not already attached). In addition, if AUTO_CLOSE is enabled at database creation (which is the default for SQL Server 2005 Express databases), when an application shuts down, the database and log files can be managed as regular operating system files suitable for XCOPY deployment.

Finally, SQL Server 2005 Express ships with two command-line tools. The first, OSQL.EXE, is similar to the version that comes with MSDE 2000, updated to talk to SQL Server 2005 Express. The second is SQLCMD.exe, a new and improved version of OSQL. Among its many enhancements is support for variables to make it easier to write reusable management scripts. Using SQLCMD, you can control, among other things, exact database creation options such as the directory where the physical files are stored.

Queries

With the database created, I needed to define the schema. Naturally, I could have fired up SQLCMD and entered T-SQL commands, such as CREATE TABLE, but the Visual Data Tools were calling my name. In poking around to see what was new, I had a pleasant surprise. Right-clicking on a database exposes a context menu with various commands including a New Query command. Selecting this command opens a query designer window, which is similar in form and function to the New View window that is available in Visual Studio .NET 2003. This new edition has been specifically enhanced to let you do more than just query data. You now have full access to all the T-SQL commands you might have run from the command line. If you look at Figure 4, you'll see that the context menu in the query designer even exposes an explicit Make Table option.

Figure 4 New Query Designer

Figure 4** New Query Designer **

Tables and Relationships

After exploring the Query Designer, I tried the Table Designer, available when right-clicking the tables node in the Database Explorer via the Add New Table command. With the New Table Designer, the act of creating tables is similar to previous editions of the Visual Data Tools (as well as SQL Enterprise Manager and Microsoft Access). The designer operates as expected, and defining the tables for the application's small schema was quick and easy. To define the primary key, indexes, and relationships, there is a Table Designer menu item on the main menu bar and a corresponding toolbar. I defined all four tables, and also created a primary key for each table.

The next step is to define the relationships between the tables, adding additional indexes as necessary. To define a relationship using the Visual Data Tools, a table needs to be opened in design view via the Open Table Definition command (available when right-clicking on a table in the Database Explorer window). Once opened, the Relationships command is available from the Table Designer menu (or its corresponding toolbar). Selecting the command opens the Foreign Key Relationships dialog. A table can have any number of relationships and constraints defined. Clicking the Add button creates a new constraint. In order to define a relationship, the Tables and Column Specification property must be set. Clicking the Editor button next to the property name opens the Tables and Columns dialog. This is where the actual columns are matched up for the relationship.

Closing the dialogs and then saving the table will cause the "Save changes to your database" warning message to appear, allowing the changes to be applied, canceled, or saved to a script file to be executed later. In addition to a relationships editor, there is also the Indexes/Keys dialog which provides a UI to add, modify, or remove indexes and keys on a table-by-table basis.

At this point, I want to create a database diagram of my schema. In my early build, it wasn't implemented (although as you'll see, the DataSet designer can fill in). So, the last step was to input some sample data. Naturally, I could use the query designer to execute INSERT statements. I could also right-click a table and select the Show Table Data command, which opens a DataGrid window for entering data. Now it was time to build the main program.

Using Visual Basic 2005 Express

Visual Basic 2005 Express provides blank project templates: Windows Application, Windows Control Library, and Console Application. In addition, there's a new feature in the Express editions: Starter Kits. Visual Basic 2005 Express comes with a Starter Kit for a DVD Collection. Choosing this Starter Kit opens a new project with a ready-to-run application. Figure 5 shows the DVD Starter Kit's Getting Started document. There's also an Add New Online Template option to download Starter Kits from a community site. Although this feature isn't implemented yet, it's a great idea. The released version of the product is planned to include information on how to convert your project to a Starter Kit and how to publish it to the Web for others to share using the New Project dialog. All of the Express editions will support this feature. For my simple book manager, I selected a new Windows Application.

Figure 5 Visual Basic 2005 Express DVD Starter Kit

Another great feature of Visual Basic 2005 Express, which is shared by its full-featured siblings, is Zero Impact Projects. When you select File | New Project, the New Project dialog does not ask for a location. It only asks for the project type and a name. It isn't until you save the project that a decision needs to be made about where to save it. In fact, you can discard a project and not have to worry about finding WindowsApplication368 on your hard drive the next time you start Visual Basic 2005 Express.

There are many improvements to the Windows Forms designer and the included controls and components, but my favorite feature so far is the snap lines. Using dynamically aligning lines, snap lines help you size and align one control with another control on the form. Figure 6 shows a blue snap line helping me align a textbox with a label. In addition to border alignment, the snap lines will help you align the text of a control with the text baseline of another control. To learn more about additional enhancements, you can read the article, ".NET Framework 2.0: Craft a Rich UI for Your .NET App with Enhanced Windows Forms Support" by Michael Weinhardt and Chris Sells in the May 2004 issue of MSDN Magazine.

Figure 6 Snap Lines in the Windows Forms Designer

Figure 6** Snap Lines in the Windows Forms Designer **

For my application, I had two main requirements. First, I needed a way to query the Amazon.com Web service with an ISBN number to retrieve a book's pertinent information so it could be added to my database. Second, I needed a way to browse my extensive library of books. Since the database was built and I had already entered some sample data by hand, solving the second requirement seemed like a good idea.

Data Binding

Data binding using the built-in tools keeps getting better and better in each version of Visual Studio. Visual Basic 2005 Express has a new window called the Data Sources window. Docked with the Solution Explorer by default, the Data Sources window lets you manage all of your application's data sources from a central location, whether they're objects, Web services, or DataSets filled from a database. Adding a new Data Source starts the Data Source Configuration Wizard. The first page, displayed in Figure 7, asks for the source of the data. Visual Basic 2005 Express, as well as the other Express editions, only supports local databases, objects, or Web services. The Professional (and higher) editions of Visual Studio 2005 will support databases on other machines on your network.

Figure 7 Data Source Configuration Wizard

Figure 7** Data Source Configuration Wizard **

Choosing the Local Database File option requires that you provide the path to a Jet MDB file or a SQL Server 2005 Express MDF file. Once the database is selected, the wizard enumerates the database and allows you to specify which objects to reference in a DataSet that you name, and which objects the wizard will create upon completion. You can select Tables, Views, Stored Procedures, and Functions in this release. I picked all of my tables and finished the wizard. The wizard churned away for a bit and, once complete, I had a DataSet with all four of my tables mapped in as DataTables with relationships intact, and TableAdapter objects configured for getting data into the DataSet. The TableAdapter is a typed version of the DataAdapter classes used in previous versions of the .NET Framework. It exposes typed methods for accessing data instead of only exposing the underlying command objects. Figure 8 shows my DataSet in a graphical fashion similar to Database Diagrams.

Figure 8 DataSet Created by the Data Source Configuration Wizard

Creating a UI

With the DataSet completed, building the UI for the library browser was a matter of dragging and dropping. With a form open in the Windows Forms designer, I opened the Data Sources window to drag and drop data sources onto the form. By default, list sources such as DataTables are rendered using the new DataGridView control but this can be changed using the dropdown menu on the data source. You can adjust how the individual columns are rendered by expanding a data source, such as the Publishers table, selecting a column, and using the dropdown to change the control type. Note that you can drag an entire data source or just individual columns to the form's design surface.

When I dragged and dropped the Publishers table onto the form a DataGridView control and some additional components and controls were created. Three components were added: a DataSet, a DataConnector, and a TableAdapter. A new hybrid component/control called the DataNavigator was also included. This item has an object on the component tray as well as a UI portion on the form. Its whole purpose is to provide a standard implementation for the navigation of a data source. The UI is based upon the new ToolStrip control and supports docking to any edge of the screen at run time via the four RaftingContainer controls.

The DataSet object is pretty self-explanatory. It's the form's runtime instance for accessing data in the database. The purpose of the DataConnector component is to make the process of binding controls to an underlying data source easier. The DataConnector component is the binding object between the data source and the controls that need binding.

With all the components added, I was prepared to write a line of code to fill the table, but even that was done for me. In the form's load event, a Fill command was issued:

Me.PublishersTableAdapter.Fill(Me.MyAmazon_DataDataSet.Publishers)

The code is commented by the IDE to let you know that if you don't want this behavior, you can just delete it or comment it out. I built the master-detail relationship between tables using the Configure Master Details command from the data menu. This command displays a dialog from which you choose the master source and specify the corresponding child data source (fed from the list managed by the Data Sources window). You have the choice to add the new controls as either a grid or as a set of individual controls.

To make the form work correctly with my schema, I did have to add a view in order to do a join. Currently, the Configure Master Details windows doesn't handle relationships based upon a intermediary linking table, such as the BooksToAuthors table in my schema (see Figure 8). So I added the view using the Visual Data Tools. At this point, I wanted it to be a data source in my project's Data Sources windows. There are two ways to accomplish this. One is to open the DataSet designer and drag and drop the view from the Visual Data Tools onto the design surface. The other is to rerun the wizard. For a single object, it was easiest to just perform the drag and drop gesture. For adding and removing multiple items, the wizard provides a nice interface.

Calling the Web Service

The last piece of the puzzle was to call the Amazon Web service to get the data about a particular book. As I write this article, Amazon is beta testing a new major release of their API. The code download for this article (available from the link at the top of this article) includes the code to access the current non-beta version of the Amazon Web service. However, I'll leave it as an exercise for you to poke through the code. Even when Amazon releases their new API, the existing API will still be supported. I used the well-known Add Web Reference command to connect to Amazon.com's Web service interface to generate the proxy classes.

The proxy classes that Visual Studio or the WSDL.exe utility generate have always exposed asynchronous versions so that the calling thread is not blocked while the Web service call is executing. The problem with using these methods carelessly is that when a method completes and then signals its completion via an event, the event handler would be executing on the worker thread. When using a Web service from a Windows-based application, there is often a need to take data from the Web service in the event handler and update the user interface. The problem is that modifying the UI from a background thread is not allowed. In previous versions of the .NET Framework, you could do it and be subject to possible problems once your application was released into the wild. In the .NET Framework 2.0, the runtime will raise an exception if you try to perform an update to a UI element from the wrong thread. Naturally, techniques that are available today to switch from the worker thread to the UI thread using ISynchronizeInvoke (Control.Invoke, for example) are still supported.

The new BackgroundWorker object provides a way to safely execute long-running tasks on a worker thread while requiring less code and effort on the part of the developer. To use it, I dragged and dropped an instance onto my form. An icon representing the instance was added to the component tray. The BackgroundWorker exposes three events: DoWork, ProgressChanged, and RunWorkerCompleted. In addition, you can specify that the worker object should support status events and/or cancellation. The UI thread starts a long-running task by calling the RunWorkerAsync method of the BackgroundWorker object. This method is overloaded to allow a single state object to be passed (if desired) to the worker thread without having to worry about synchronization and locking code. Calling RunWorkAsync causes the DoWork event to be fired. In my application, I needed to pass the ISBN number from a TextBox on the form to the worker thread. This method was called from a button's click event handler.

The DoWork event handler is the entry point for the worker thread, which comes from the built-in CLR thread pool. For my application, the call to the Amazon.com Web services is put here. While the code is running inside DoWork, it can optionally call the BackgroundWorker instance's ReportProgress method, which in turn fires the ProgressChanged event. ReportProgress provides a parameter for an integer value specifying the percentage of how much work has been completed as well as an optional state object. Code that is handling the ProgressChanged event will run on the UI thread so that progress bars or other UI elements can be safely updated. Once the DoWork handler completes, the RunWorkerCompleted event runs. This event is also processed by the UI thread, making it safe to update the UI. In the case of my application, I populate the UI with information about the book requested.

It should be noted that most of the Express functionality described in this article is also available in the Professional versions of the products. For a brief look at some of the feature differences between the two, see Figure 9.

Figure 9 Visual Basic Express vs. Professional

Feature Express Professional
Database design tools: "Data Connections" node Local All
Data access Web services and local databases All
Documentation 10MB core 200MB optional Full MSDN Library
Extensibility Tools menu All
Class designer and object test bench No Yes
Debugging Local Local/Remote
XML designer support XML only XML/XSLT
Deployment support No Yes
Mobile development support No All
Reporting No Reporting Services, Crystal Reports
64-bit compiler support No Yes
Office development support No Yes
Server Explorer: "Servers" node No All
Source code control No SCCI
User experience Streamlined Full
Size 80MB Multiple CDs

Wrapping It All Up

Both SQL Server 2005 Express and Visual Basic 2005 Express make it fun and easy to create apps. The small footprint and full feature set of these products is going to make you want to give up Notepad, command-line tools, and compilers. What's more, the addition of the Starter Kits will make it easy to share and exchange applications with other developers using the version of Express that fits your programming language and application style.

Brian A. Randell is a senior consultant with MCW Technologies, a Microsoft Certified Partner specializing in custom apps development with .NET, SQL Server, and Office. He can be contacted via his blog at https://sqljunkies.com/WebLog/brianr.