Stan Schultes is an IT and project manager, and a VB/Web enterprise application architect and developer in Sarasota, Fla. Stan is an MCP in VB and spoke on VB development at Microsoft's DevDays conference. He is a contributing editor for VBPJ and writes regularly for the magazine. Reach Stan at stan@vbexpert.com.

May 2001

Getting Started

Build a WinForms App With ADO .NET

Create a WinForms app that lets you execute ad-hoc SQL queries against an Access or SQL Server database.

by Stan Schultes

Reprinted with permission from Visual Basic Programmer's Journal, May 2001, Volume 11, Issue 5, Copyright 2001, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange.

The latest version of Visual Basic, VB.NET, takes a powerful new approach to developing Windows and Web-based software that will revolutionize application development. You must overcome the initial hurdle of learning the new language and tools, however, and the magnitude of changes might make you uncomfortable at first. In this article, I'll give you a quick walk-through of the VB.NET language and development environment to put you more at ease. I'll also show you the basics of the new ActiveX Data Objects.NET (ADO .NET) objects to get you started with database development.

Don't try converting an existing application for your first experience with VB.NET. All but the most trivial projects will need substantial modifications to work in the new version, and you'll likely find yourself extremely frustrated from the beginning. Instead, take time to visit the newsgroups and familiarize yourself with the changes. Start with the Microsoft walk-throughs to get some experience with the new environment.

The sample project in this column takes you a step further and shows you how to build a data-driven WinForms app using the new ADO .NET objects. ADO .NET is the data access technology behind VB.NET; you'll use it in many of your own database apps. Although the changes to ADO .NET are evolutionary, you need to become familiar with several new objects to get started.

After you've visited a few newsgroups and built up a base of knowledge, you're ready to sit down and face VB.NET. I'm working with Visual Studio .NET (VS.NET) beta 1, but Microsoft should have released beta 2 or later by the time you read this. Expect differences between the betas—new features, changed features, and some features that have disappeared. A warning: Don't install the VS.NET beta versions on any machine you use for production application development. If you don't have a spare machine available, wait for the release version.

When you run VB.NET for the first time, you'll see the initial setup screen on the Visual Studio (VS) home page in the Integrated Development Environment (IDE). The default profile is VS Developer, with the VS default keyboard and screen layouts. If you're primarily a VB developer and familiar with previous versions, start with the VB default keyboard layout and the VS default screen layout. Once you make your selections, click on the Get Started link.

On the Start page, click on the Create New Project link to create a new VB.NET project. In the New Project dialog, choose the Windows Application template, fill in the project name as ADONET, and choose a location where you want to save your project. When you click on OK, the New Project Wizard creates the directory you specified and generates and saves your template project code.

Jump Into the Code
View the code behind your new form by clicking on the View Code button in the Solution Explorer; you'll see that the wizard added some code already to the project. This is where you start to see what's different about VB.NET. The wizard-generated code looks more like VC++ than what you're used to seeing in VB6.

In your new project, change the default form's name to frmMain by clicking on Form1.vb in the Solution Explorer window and changing it to frmMain.vb. Change the name of the Form1 class in the code window by using Find and Replace (Ctrl-H) to replace all instances of Form1 with frmMain. You should be able to do this in the View | Class View panel, but it doesn't seem to work in beta 1. Right-click on the ADONET project in the Solution Explorer and choose Properties. In ADONET's property pages, change the Startup Object to ADONET.frmMain, and click on the OK button.

The toolbox pops out when you hover over the Toolbox tab on the IDE's left edge. Click and drag TextBox, Button, CheckBox, StatusBar, and DataGrid controls to the frmMain designer surface and arrange them as shown (see Figure 1). In the Properties window, find the Name property for each and name the new controls txtSQL, btnSQL, chkRequery, sbrStatus, and grdAdHoc, respectively. Set the Text property of the button to Execute SQL and the Text property of the checkbox to Requery on Update. (The Text property was named Caption in previous VB versions.)

	Figure 1 | Execute Ad-Hoc SQL Statements With ADO .NET. Click here.

You use the checkbox to control whether data from the affected table is displayed after you execute an update SQL query (either Insert, Update, or Delete). In the Properties window, set the chkRequery control's Checked property to True to requery by default. Make btnSQL the default control on the form so you can hit the Return key after typing in a SQL statement. Set frmMain's AcceptButton property to btnSQL to reset the button's Default property.

If you resize the form in design mode, all the controls stay where you placed them. The Anchor property, which is new to all WinForms controls, aids in form resizing. Change the Anchor property of the textbox and button to TopRight, the checkbox to TopLeftRight, and the data grid to All. Now resize the form; each control moves with the form according to how you set its Anchor property.

Next, double-click on btnSQL to jump into the btnSQL_Click event in the code window behind the button. Add this line of code to display a message box:

        Protected Sub btnSQL_Click _
   (ByVal sender As Object, _
   ByVal e As System.EventArgs)
   MsgBox("Hello from VB.NET!")
End Sub
      

Several aspects of this code will look unfamiliar to you. The Protected keyword on the Sub declaration is similar to VB6's Private keyword in that the Sub is private to the frmMain class. Protected also means classes that inherit from frmMain will inherit the btnSQL_Click event. See Dynamic Help for more information on the new sender and e event parameters. Also, the MsgBox call now has parentheses around the string argument; this is a new syntax element in VB.NET.

Run the application by pressing F5 (or use the Debug | Start menu item). If there are no compile errors, your app will run and show the message box when you click on the Execute SQL button. Any compile errors appear in the Task List (on a tab at the bottom of the IDE window), where you can jump to the problem location by double-clicking on an error message. Stop the debugger after you explore your new project.

Access Data With Class
In the Solution Explorer window, right-click on the ADONET project name and choose Add | Add Class from the context menu. In the Add New Item dialog, choose the Local Project Items category and the Class Template item. Name your new class CData.vb and click on Open. Add this statement at the top of the class file:

        Imports System.Data.ADO
      

The Imports statement is roughly equivalent to using Project | References in earlier versions of VB. Imports loads a namespace so you can refer to objects within that namespace directly. Assuming you used the preceding Imports statement in a class, these two declarations in the class are equivalent:

        Dim oCnn as ADOConnection
Dim oCnn as System.Data.ADO.ADOConnection
      

At the top of CData.vb, declare variables for an ADOConnection and three properties, and add property procedures:

        Imports System.Data.ADO
Public Class CData
   'class private variables
   Private mADOConn As ADOConnection
   'class member variables
   Private m_sReturnMessage As String
Public ReadOnly Property _
ReturnMessage() As String
Get
Return m_sReturnMessage
End Get
End Property
End Class

Property procedures have a new syntax with only Get and Set procedures; the Let procedure syntax has been dropped. You need to include the ReadOnly keyword on the procedure declaration when you provide only the property Get routine.

A powerful addition to the VB language is the parameterized constructor. A constructor is a routine called when you instantiate a class; it's similar to the Class_Initialize routine in VB4 through VB6. You can create multiple constructors and pass parameters to each constructor to set up the internal data in a class as it's created; you no longer need to create a separate class Init method.

The constructors for a class are one or more routines named New. If you provide more than one constructor, you need to declare each with the Overloads keyword to tell the compiler that you're creating alternate definitions. Each constructor for a class must have a unique signature; that is, the parameter list must be unique for each one. CData has two constructors; use this one to pass in a complete connection string:

Public Overloads Sub New _
(ByVal ConnectString As String)
'specifies complete ConnectString
mADOConn = New ADOConnection _
(ConnectString)
End Sub

CData's other constructor allows you to pass in discrete connection parameters (see Listing A).

Query Execution With ADO .NET
You query the database with CData's ExecuteSQL method (see Listing B), which returns a DataSet object. The DataSet is ADO .NET's new general-purpose data object you'll use for most ADO data access tasks. The DataSet is a disconnected object, completely separate from the actual data source. It provides a relational view of your data, no matter what form of data the data source actually is. ExecuteSQL checks parameters and calls the mExecuteSQL private function to do the query work; you now avoid instantiating the ADO objects if no SQL statement is passed to ExecuteSQL.

Within the mExecuteSQL method, you use the ADODataSetCommand object to connect the DataSet object with the data source. Note that in beta 2, the ADODataSetCommand object will be called a DataAdapter. The abbreviated ADO .NET code within mExecuteSQL looks like this, assuming you request a SELECT statement:

Private Function mExecuteSQL _
(ByVal SQL As String) As DataSet
Dim adoCM As New ADOCommand (SQL, mADOConn)
Dim adoDS As New DataSet()
Dim adoDSC As New ADODataSetCommand()
adoCM.ActiveConnection.Open()
adoDSC.SelectCommand = adoCM
adoDSC.FillDataSet (adoDS, "ad-hoc")
Return adoDS
End Function

In contrast to older versions of VB, it's fine to use the New keyword when declaring object variables in VB.NET (Microsoft fixed the previous inefficiencies). The New keyword lets you use the parameterized constructor syntax to initialize the adoCM object as it's created. You use the FillDataSet function's second parameter (arbitrarily named "ad-hoc") to identify a particular DataSet in the returned data. Use the new Return statement syntax to pass the function's return value back to the calling routine.

Finally, replace the MsgBox output in frmMain's btnSQL_Click event with code that calls the CData.ExecuteSQL method and sets the DataGrid's DataSource property to the returned DataSet (see Listing C). Note the structured error-handling syntax in the new Try Catch End Try construct. You write the error output in this routine to the debug window using the Console.WriteLine function.

Now you've gotten a peek at how the VB.NET language and new Visual Studio IDE operate, along with a quick introduction to ADO .NET. For my own applications, I'll probably leave complex code that won't change much in the VB6 environment. For new code or significant upgrades, however, I'm excited and looking forward to using the new VB.NET tools.