Sales Support: A Hybrid Visual Basic 6.0 & Visual Basic 2005 Application, Part 3

Scott Swigart

February 2008

Summary: This article, the third in a four-part series, shows how to include modern, simple-to-configure Microsoft Visual Basic 2005 reporting components within an existing Microsoft Visual Basic 6.0 application. (13 printed pages)

Contents

Introduction

The Sales Support Application

Reporting

Application Architecture

Visual Basic .NET Implementation

Interoperability with Visual Basic 6.0

Conclusion

Introduction

Do you want to modernize your Microsoft Visual Basic 6.0 applications? Would you like to take advantage of Web services, desktop search, and more, but you don't have the time or resources to upgrade (or rewrite) your production applications in Microsoft Visual Basic 2005?

In Part 1 of this article series, Enhancing an Application with Windows Desktop Search and Office Outlook Integratione, I showed that it is not necessary to migrate a Visual Basic 6.0 application to Visual Basic 2005 in order to take advantage of .NET functionality. Instead, you can integrate Microsoft Windows Desktop Search into a Visual Basic 6.0 application by using COM objects that have been created with Visual Basic 2005.

In Part 2,Enhancing a Visual Basic 6.0 Application with MapPoint Web Service, I demonstrated how to enhance the same application to display maps for locations in an existing database by calling into Microsoft MapPoint Web Service.

This hybrid approach lets you take advantage of all of the functionality of the .NET Framework without having to rewrite your application.

This article focuses on what is often an afterthought to developers, but is top-of-mind to users: reporting capabilities. Instead of struggling with the more limited reporting capabilities of Visual Basic 6.0, you will see how to include modern, simple-to-configure Visual Basic 2005 reporting components within an existing Visual Basic 6.0 application.

The Sales Support Application

"Sales Support" is a very simple sales-force support application that allows the user to look up accounts, contacts, and forecasted sales. The application is not intended to be a complete sales-force automation solution. Instead, it is designed to be a representative placeholder for an existing line-of-business Visual Basic 6.0 application. Also, it serves as the anchor for a number of logical application enhancements that are best implemented by using Visual Basic 2005.

Installation

The Sales Support installer has been tested on Microsoft Windows XP SP2 and on Windows Vista, and likely would work on older operating systems, too. Because this is a hybrid Visual Basic 6.0 and Visual Basic 2005 application, the Sales Support installer will install the.NET Framework 2.0 as a prerequisite. This application also stores data in a back-end database. For this reason, the installer will install Microsoft SQL Server 2005 Express Edition, which replaces Microsoft SQL Server 2000 Desktop Engine (MSDE 2000).

If you do not have these prerequisites installed, the installer will download them for you automatically. If they are already installed, the installer will detect their presence and skip to the application installation.

Operation

After the application is installed, you can launch it from theStart menu. Look for the Sales Support8_3 entry. The application will initially prompt you to log on. To simplify use, the user name and password fields default to working values, as shown in Figure 1. You can just click theLogin button to continue.

Figure 1. The Sales Support Login form

After you have logged on, the main application window appears, as shown in Figure 2. The application provides three different views of the sales information. TheAccounts tab provides the first view and shows each account and associated sales information. This view can be sorted differently by clicking on a column heading. TheContacts tab provides the second view and shows more detailed information about each individual contact. TheForecast tab provides the third view and displays future sales-forecast information.

Figure 2. The main application window

Again, the Sales Support application is not designed to be a working sales-force solution. Instead, it serves only as a basis for realistic application enhancements.

Reporting

Most line-of-business applications are "forms-over-data" applications that serve as a way to enter, edit, view, and visualize discrete pieces of information that is stored in back-end databases. Of course, users also want to see this data in the aggregate; for this reason, reports are a key component of most line-of-business applications (although they often are developed as an afterthought in version 1.0 of an application). Reporting has been available for Visual Basic through options such as Crystal Reports, the Data Environment, and a rich ecosystem of third-party reporting tools.

With Visual Studio 2005, Microsoft has released the powerful newReportViewer control, which allows you to create reports and view them in your application. TheReportViewer control supports interactive reports, printing, layout control, and exporting options.

To add new reports to a Visual Basic 6.0 application, you could continue to use the tools that shipped with the product in 1998. However, the users of our fictional Sales Support application demand the "latest and greatest," so that theReportViewer control was the best choice. This control can be added easily to a .NET form; also, by using the Interop Forms Toolkit 2.0, this form can be launched from the existing Visual Basic 6.0 application. Figure 3 shows the resulting report for accounts.

Figure 3. Accounts report

You can access this report in the sample application via the newReports menu item that includes Account, Contacts, and Forecast reports.

Application Architecture

The core of the Sales Support application is a Visual Basic 6.0 application that communicates with a SQL Server 2005 Express database. When the user selects a report from the newReports menu, the Visual Basic 6.0 application creates an instance of the .NET report form as though it were a COM object. The Visual Basic 6.0 application passes the report data to the .NET report form as an ADORecordset object. The .NET report form converts theRecordset object to aDataSet object. This is necessary because theReportViewer control cannot bind to an ADORecordset object, but it can bind to an ADO.NETDataSet object. The report is then displayed as shown in Figure 3. (For more information, please see "Sharing ADO Recordsets Between Visual Basic 6.0 and Visual Basic 2005," in the MSDN Library.)

Figure 4. Application data flow

Visual Basic .NET Implementation

Extending the Visual Basic 6.0 Sales Support application requires a Visual Basic .NET Framework Class Library project that is called VBNET_Extensions. This project accepts aRecordset object from the Visual Basic 6.0 Sales Support application and converts it to aDataSet object that is suitable for reporting. TheDataSet object provides a visual designer that allows you to create table definitions to hold the data. By looking at the designer, you can see that thisDataSet object is configured with tables that map to the data that the Sales Support application will provide.

Figure 5. DataSet designer

If you are completely new to the .NET Framework, aDataSet object is a big change from the four main ADO objects—namely,Recordset objects. ADataSet object could be described as a collection of hierarchically relatedRecordset objects (DataTables), disconnected from the database, but accompanied by associatedCommand objects to handle data manipulation within the database. What is yet more interesting, aDataSet object can be filled with data from sources other than a database. In this example, theDataSet object is filled with data from ADORecordset objects.

The Contacts, Account, and Forecast tables hold data that is supplied from the Sales Support Visual Basic 6.0 application. The Visual Basic 6.0 application passes inRecordset objects that contain this information, and the VBNET_Extensions Class Library copies that data from theRecordset object into theseDataTable objects. The TopAccounts and TopForecast tables are designed specifically for charts that appear in some of the reports. These charts show the top 10 accounts, in terms of recent sales and forecast sales.

After theDataSet is created, you're ready to create the actual reports. Many reporting options have been available for Visual Basic; however, with Visual Studio 2005, Microsoft has introduced theReportViewer control, which is an all-in-one reporting tool that simplifies the building of powerful and interactive reports. This is a .NET control, of course, so that it has to be placed on a .NET form that is launched from the existing Visual Basic 6.0 application.

Creating a .NET form that is accessible from Visual Basic 6.0 requires the Interop Forms Toolkit 2.0, which is a free and supported Visual Basic 2005 PowerPack that makes .NET forms appear as COM objects in Visual Basic 6.0 applications. After you install it, a Visual Basic 6.0 InteropForm is added to the Visual Basic .NET Framework Class Library, as shown in Figure 6.

Figure 6. Adding a Visual Basic 6.0 InteropForm

The InteropForm is just like any other .NET form, except that it contains a few extra items that make it COM-callable. After the form has been added to the project, place aReportViewer control from the Data section of the toolkit onto the form. TheReportViewer control has a small smart tag in the top-right corner that is used to dock theReportViewer control in the form. Docking a control will cause the control to fill the available space, and resize automatically as the form is resized—a challenging task in Visual Basic 6.0. The smart tag is used also to create a new report definition. You can see the smart tag accessed in Figure 7.

Figure 7. Creating a new report from the smart tag

With the report designer open, the toolkit updates to contain a number of items that are specific to reports. For this application, tables and charts are the two main items that are used. Drag a table from the toolkit onto the report-design surface. As soon as the table is in place, drag individual columns from the Data View window onto the table to bind table cells to theDataSet columns, as shown in Figure 8. You can also drag columns onto the chart to specify from where the data values and category names should come.

Figure 8. Building the report

To make the report more readable, set theBackgroundColor property of the table cells to the expression=iif(RowNumber(Nothing) Mod 2, "PaleGreen", "White"), which will alternate the background color of each line of the table. Right-click the chart, and then selectProperties to display the custom property pages for the chart, as shown in Figure 9. These property pages allow you to customize the chart—for example, by setting the chart title, or by formatting the x-axis labels as currency.

Figure 9. Chart-property pages

The final step is to connect theReportViewer control to theSalesSupportDataSet.

To interact with the Sales Support Visual Basic 6.0 application, the application requires a way to pass aRecordset object into the AccountReportForm. This step is accomplished by exposing a property on the report form, as shown in Listing 1.

    Private mRecordset As Object    <InteropFormProperty()> _    Public Property Recordset() As Object        Get            Return mRecordset        End Get        Set(ByVal value As Object)            If (value IsNot mRecordset) Then                mRecordset = value                SalesSupportDataSet.Account.Clear()                Dim da As New OleDbDataAdapter()                da.Fill(SalesSupportDataSet.Account, mRecordset)                GenerateTopAccounts()                AccountReportViewer.RefreshReport()            End If        End Set    End Property

Listing 1. Recordset property on the Visual Basic .NET form

The property uses a .NET FrameworkOleDbDataAdapter object to copy the contents of theRecordset object into theDataSet's AccountDataTable object, which was created earlier in theDataSet designer. The magic of taking data from Visual Basic 6.0 and ADO and transferring it to an ADO.NETDataSet comes in one line of code:

da.Fill(SalesSupportDataSet.Account, mRecordset)

TheDataAdapter does all of the work for you.

Some of the reports also include charts, which show "top-10" information. You use a function to generate the data for these reports. For the Accounts report, you accomplish this by using theGenerateTopAccounts function that is shown in Listing 2.

    Private Sub GenerateTopAccounts()        Dim bs As New BindingSource(SalesSupportDataSet, _            SalesSupportDataSet.Account.TableName)        bs.Sort = SalesSupportDataSet.Account. _            sales_last_3_monthsColumn.ColumnName & _            " asc"        SalesSupportDataSet.TopAccounts.Clear()        For i As Integer = bs.Count - 11 To bs.Count - 1            Dim accountRow As SalesSupportDataSet.AccountRow            accountRow = bs.Item(i).Row            SalesSupportDataSet.TopAccounts. _                AddTopAccountsRow(accountRow.CompanyName, _                accountRow.sales_last_3_months)        Next    End Sub

Listing 2. GenerateTopAccounts function

TheGenerateTopAccounts function uses a .NET FrameworkBindingSource object to sort the accounts by sales. The first 10 rows are then copied from theBindingSource object into the TopAccountsDataTable. Because the chart already was bound to this table, the chart just populates itself and shows the top accounts.

Interoperability with Visual Basic 6.0

As noted earlier, the Interop Forms Toolkit 2.0 allows you to build forms in Visual Basic .NET that can be called from Visual Basic 6.0. The Interop Forms Toolkit 2.0 installs a Visual Basic 6.0 InteropForm item that was used to build the report forms. After the reports are created, selectTools |Generate Interop Wrapper Classes, as shown in Figure 10, to generate COM wrappers that are needed for Visual Basic 6.0 to interact with the .NET form.

Figure 10. Generating COM wrapper classes

After the wrapper classes are generated, compile the solution by using theBuild |Build Solution menu command.

With the Sales Support application open in Visual Basic 6.0, add a reference to the VBNET_Extensions COM object. Visual Basic 6.0 code can create an instance of this object and call into it, as though it were any other COM object. Listing 3 shows the Visual Basic 6.0 code that passes the account data into the Visual Basic .NET report form.

Private Sub mnuAccounts_Click()    Dim f As New VBNET_Extensions_AccountReportForm    If rsAccount Is Nothing Then        LoadAccountData    End If    f.Recordset = rsAccount    f.ShowEnd Sub

Listing 3. Passing a Recordset object into a Visual Basic .NET report form

You can see that only a tiny amount of code was required in Visual Basic 6.0 to trigger the new Visual Basic .NET functionality.

Conclusion

Visual Basic .NET is a great choice for many new application scenarios. Microsoft continues to pour its efforts into enhancing the .NET Framework and related technologies. At the same time, many organizations are maintaining significant Visual Basic 6.0 applications. As this article has shown, there is no reason that you cannot create hybrid applications by using the best of both technologies.

About the author

Scott Swigart spends his time consulting, authoring, and speaking about converging and emerging technologies. With development experience going back over 15 years, and by staying in constant contact with future software-development technologies, Scott is able to help organizations get the most out of today's technology while they prepare to leverage the technology of tomorrow. Scott is also the author of several .NET books, a certified Microsoft trainer (MCT) and developer (MCSD), and a Microsoft MVP. Feel free to contact the Scott atscott@swigartconsulting.com, or check out his latest musings atblog.swigartconsulting.com.