Chapter 17 - Developing: Applications - Migrating Oracle Forms

Article
12/05/2007

Introduction and Goals

Oracle Forms are applications built using Oracle Developer, a Rapid Application Development (RAD) environment available from Oracle. Oracle Forms-based applications can retrieve and manipulate data in Oracle databases. These forms can also be deployed across the Web.

It is not possible to modify an Oracle Forms application for interoperation with a Microsoft® SQL Server™ database, and it is recommended that applications of this type are rewritten. Microsoft Visual Basic® .NET is an ideal choice for rewriting an Oracle Forms application because it is very similar in nature to Oracle Developer, both in terms of the development tools and wizards, as well as the end product itself.

This chapter is for developers who want to migrate their Oracle Forms application running on an Oracle database to Visual Basic .NET running on SQL Server. The tools, processes, and techniques required for a successful conversion are described.

In this guide, the Oracle Forms application is first analyzed for its components, and then a similar analysis of the Visual Basic .NET application environment is conducted. Strategies for migrating from Oracle Forms to Visual Basic .NET are then examined. A detailed discussion on how to perform the migration is beyond the scope of this guide. The goal of this chapter is to provide a blueprint for performing the migration.

Migration Approach

Migrating an Oracle Forms application to Visual Basic .NET is complex, and the treatment of the subject here should be seen as a design sketch from which a detailed analysis and plans for an application migration can be made.

The basic approach is feasible for the following two reasons:

Oracle Forms and Visual Basic user interfaces share many common elements.
The Oracle Forms Designer is capable to generate Visual Basic code.

The form is the basis for the user interface (UI) in both Oracle Forms and Visual Basic. The form is graphical in nature and is used to present data and accept user input, and the form can contain elements which are both graphical and non-graphical in nature. Oracle Forms and Visual Basic have several common elements between them. However, their organization, naming, and properties differ.

Figure 17.1 illustrates how different components of Oracle Forms map to components in Visual Basic .NET.

Figure 17.1 Mapping of Oracle Forms to Visual Basic .NET WinForm

This diagram shows how all the high-level components of an Oracle Forms application map to components in Visual Basic .NET. This mapping serves as a convenient guide for the work to be done and also helps maintain the logical composition of the application. The mapping also helps avoid introducing too many unintended side effects, such as firing of triggers at the wrong time and complicating the migration. This diagram is used in this chapter to document the source Oracle Forms application, define the target Visual Basic .NET application, and plan for performing the migration.

Examining Oracle Forms

Oracle Forms applications are Oracle database optimized transactional applications built using the tools provided by Oracle's Developer Suite. Oracle Forms Builder, a part of Oracle Developer suite, enables you to quickly and easily create database forms and business logic. The development is done using wizards, drag-and-drop components, and built-in functions. A visual editor can be used to refine the forms with graphics and other components. The coding language is PL/SQL. A client/server Forms application can run on the Web with very little or no modification.

Like most GUI applications, an Oracle Forms application is composed of two parts: the application logic and the user interface. Oracle Forms maintains this separation throughout its design. Hence the Forms Runtime is the same in both the two-tier and three-tier architectures.

An understanding of the basic concepts and components that make up a form is important to planning an application migration. Figure 17.2 shows a simplified view of the Oracle Forms environment.

Figure 17.2 High-level organization of Oracle Forms components

Figure 17.2 High-level organization of Oracle Forms components

An Oracle Forms application is an organization of items. The organization of the items has two perspectives: that of the developer, called the form view; and that of the user, called the visual (or presentation) view. In the form view, the items are organized into logical blocks and have no visual structure. The visual structure is provided by constructing the visual view where items are placed on a canvas and presented to the user in windows. It is very important to maintain the same visual view when an Oracle Forms application is migrated. The Visual Basic .NET developers need to look at the visual view to understand how many screen layouts they need in Visual Basic .NET. Each item in a canvas will correspond to one control in .NET.

The migration should be attempted by creating two models: one is based on the form view, called the Form Model; and the other is based on the visual view, called the Visual Model. Figure 17.1 shows the form view of the mapping from Oracle Forms to Visual Basic .NET. The equivalent visual view is shown in Figure 17.3.

Figure 17.3 Mapping of visual view of Oracle Forms to Visual Basic .NET

The form view is used by the developer to access the business logic and the data access. The visual view is used to build the user interface.

Note Providing a complete description of how to transform every item and every piece of code from an Oracle Forms application to a Visual Basic .NET application is beyond the scope of this guide.

In this section, the entire Oracle Forms application is examined, with an emphasis on understanding the database-related components.

An Oracle Forms application is built using components called modules. The following four types of modules are visible when a form application is opened up in Oracle Forms Builder:

Object Library
PL/SQL Library
Form Module
Menu Module

Figure 17.4 shows the details of an Oracle Forms application as seen in the Ownership View using Oracle Forms Builder. This tool will be employed in documenting the current application.

Figure 17.4 An Oracle Form as visible in Oracle Forms Builder

Figure 17.4 An Oracle Form as visible in Oracle Forms Builder

An application can be built using multiple forms, menus, and library modules. The Oracle Forms Builder can be used to browse the component model and understand the modules and their characteristics so that the Forms Model can be built. The elements offered by these modules account for the capabilities, features, and functioning of the application and should be the focus of the developer involved in migrating an application.

Table 17.1 is a compilation of the files that make up an Oracle Forms application.

Table 17.1: Oracle Forms File Extensions

Module	Extension	Characteristic
Form	.fmb	Form design file
	.fmx	Form executable runfile
	.fmt	Form text export file
Menu	.mmb	Menu design file
	.mmx	Menu executable runfile
	.mmt	Menu text export file
PL/SQL Library	.pll	PL/SQL library design file (can also be executed — contains both source and executable code)
	.plx	PL/SQL library executable runfile (contains no source code)
	.pld	PL/SQL library text export file
Object Library	.olb	Object library design file
	.olt	Object library text export file

Oracle Forms Builder can be used to view all the components in a Forms application by opening the form and clicking View, and then Show PL/SQL only.

Object Library

The object-oriented programming concept of reusing code is implemented in Oracle Forms by the object library and PL/SQL library modules. The Object library contains reusable objects and the PL/SQL library contains reusable code. Hence, these are examined first for a migration.

The Object library is a collection of forms objects that can be used in other form, menu, or library modules. The Object Library is used to store, maintain, and distribute standard forms objects, which can then be reused across the entire application. They also help conserve runtime memory and encourage reuse. The objects in an Object Library are commonly organized as folders. The organization may be by type of item or by application functional area.

A good example of the use of an object library is a control block used to enter complex search criteria in a query-only form. This control block is then used in multiple forms.

Oracle Forms Builder allows creation of a subclassed version of a library object for reuse in any desired module. To be able to tell whether an object is subclassed or not, look at the subclass information property of the data block.

The object-oriented features of Visual Basic .NET allow you to model library object inheritance closely to maintain the same structure. The object library can be viewed as a DLL.

PL/SQL Library

The PL/SQL library contains reusable code invoked by other form, menu, or library modules. The code, also called a program unit, can be user-named functions, procedures, or packages. The program unit is stored and executed on the client. The program units are not for database-related code alone. They may contain purely business logic. For example, the code could be used to check the validity of a user input in a form. Any SQL contained in these program units is passed to the database.

Libraries make all the PL/SQL code available to the form without being part of the form. The libraries are loaded dynamically. Multiple forms can attach the same PL/SQL library.

Table 17.1 identifies the files that are related to the PL/SQL Library that contain code that needs to be migrated.

Form Module

A form (or form module) is the main component that anchors an application and provides the necessary code to interact with the datasource and the user interface. The underlying database data is reflected in multiple items, including fields, check boxes, and radio groups. A form is logically organized into blocks.

Blocks

A block is a container for a group of related items such as text boxes, lists, and buttons. The block itself does not have a visual representation.

Oracle Forms Builder consists of two main types of blocks. These are:

Data blocks
Control blocks

These two kinds of blocks are very different from each other. The data block serves as a bridge to the underlying data and provides an abstraction for how the data is reached. The control blocks are more like programming units that can organize controls into logical groups forming part of the same user interface navigation cycle. Whether a block is a data block or a control block can be determined by verifying the Database Data Block property for the block. It is a data block if the setting is "Yes" and a control block if the setting is "No."

Data Block

A data block can be associated with a specific database table (or a view), a stored procedure, a FROM clause query, or a transactional triggers. By default, the association between a data block and the database allows for automatic access and manipulation of data in the database. Triggers are used when a data block needs access to tables not directly associated with it (non-base tables).

Various relationships can exist between data blocks and their underlying base tables. A common relationship is the master-detail relationship where each row in the master table corresponds to one or more rows in the detail table. The master-detail relationship allows the primary key and foreign key values to be linked across data blocks. Oracle Forms Builder automatically generates the objects and the PL/SQL code needed to support the master-detail relationships when these blocks are built using the Data Block Wizard.

Figure 17.5 shows the different components that make up a master-detail block as created by the Data Block Wizard.

Figure 17.5 Code generated by Oracle Forms Builder Data Block Wizard in creating a master-detail block

Figure 17.5 Code generated by Oracle Forms Builder Data Block Wizard in creating a master-detail block

By capturing a query on a table, a block can also be designed to show one or more records at a time. These are called single-record blocks or multi-record blocks, respectively.

Visual Basic .NET developers can use Oracle Forms Builder to look at the generated code to find out about the relationships between the blocks. For example, in Figure 17.5, code in the ON-POPULATE-DETAILS trigger on the DEPT block suggests that it is the master block of the EMP block.

In Visual Basic .NET, the equivalent of an Oracle Form data block is a Dataset into which data can be read from the database, and on the visual side, a DataGrid control bound to the Dataset will be capable to display the data. Dataset is just one option. In addition to the Dataset, ADO.NET offers DataReader for read-only data access.

Additional properties convey the association between a data block and a table, such as the WHERE clause and ORDER BY clause that will be used to create SQL for the Visual Basic .NET Dataset. The Insert Allowed, Update Allowed, and Delete Allowed properties will tell the Visual Basic .NET developer whether the block will insert, update, or delete any records.

Figure 17.6 shows all the properties of a data block.

Figure 17.6 Properties of a data block

Figure 17.6 Properties of a data block

Control Block

The second type of block is the control block. The items in a control block are not associated with the database, and its items do not relate to any columns within any database table. The items in a control block are called control items. For example, buttons in a module can initiate certain actions and can be logically grouped in a control block. The control block is only a logical grouping and the physical or visual placement may differ. It is also a mechanism to segregate the control block items from the items that are dependent on the database. Control items are used to perform functions, such as accepting input from the user and displaying calculated values and look values.

Program Units

Program units are part of forms modules and they contain named procedures, functions, or packages. The program units are similar to the program units created in the libraries but are local to the form in which they are created. They are stored and executed on the client with any embedded SQL passed to the database for processing. If this code contains database interactions, then it needs to be converted to T-SQL. If it contains only non-database related code, then it needs to be converted to its equivalent in Visual Basic .NET.

Triggers

The trigger object is a PL/SQL block executed on an event. Triggers can be owned by the form module, a data block, or an item, depending upon their scope.

If this code contains database interactions, then it needs to be converted to T-SQL. If it contains only non-database code, then it needs to be converted to its equivalent in Visual Basic .NET.

When a trigger is activated, it executes the code it contains. Each trigger’s name defines what event will fire it; for example, a WHEN-BUTTON-PRESSED trigger executes its code each time the user clicks the button to which the trigger is attached.

A trigger can be replaced by an equivalent event in Visual Basic .NET. The equivalent of an Oracle Forms WHEN-BUTTON_PRESSED trigger in Visual Basic .NET is the Click event procedure. Double-click the page Button control in design view to open the code behind the page and create a Click event procedure.

List of Values (LOV)

A LOV is a pop-up window that provides the user with a selection of values. The values can be static or populated by querying the database. LOVs are populated using columns returned by record groups. Check the Record Group property of the LOV for the record group which is used to provide values. Visual Basic .NET offers pop-up windows that can be programmed to display a list of values obtained by the DataReader.

Record Groups

A record group is a data structure in the form module which behaves like a local table for data retrieved from the databases. Record groups are a way to share small List of Values (LOV). The values can be retrieved using a query or a subprogram. Look into the property Record Group Query to see the SQL statement upon which the record group is based. A record group can be replaced by DataReader in Visual Basic .NET.

The menu module consists of a hierarchy of menus. Each menu consists of the items that can be selected. Menu modules are usually attached to form modules. Every form module has a default menu that includes the commands for all basic database operations, such as insert, update, delete, and query. Visual Basic .NET offers a wide range of functionality involving menus.

After inserting, updating, or deleting records from an Oracle form, changes are not made in the underlying database until the changes are committed with a save command. While rewriting this module in Visual Basic .NET, it is important to know whether inserts, updates, and deletes are permitted on a data block. The data block properties of Insert Allowed, Update Allowed, and Delete Allowed determine these.

Any item in a menu can also be referenced through PL/SQL code to enable/disable menu items, attach commands to menu items, initialize check or radio menu items, and change menu start-up code.

Windows and Canvases

Windows and canvases form the basis for the visual presentation of the form. The visual model is built by looking at the windows and canvases through Form Builder. The developer can then create an identical representation in Visual Basic .NET based on the visual model using forms.

Oracle Forms has two types of windows: document windows (the main application areas) and dialog windows (messages and other actions).The end user interacts with the application using these windows. In addition to incorporating all the functionality in the original application, it is also important to maintain the general look and feel of the application.

The document window is composed of work areas called canvases where visual objects such as graphics and items are arranged. Several canvases can be located in a form module. All canvases may appear in a single window (by default) or spread across multiple windows (to view simultaneously).

From the start, a new document window or dialog has a relationship with a form in which relationships to blocks tie the visual representation together with the underlying database data. Figure 17.7 shows a window which displays the master-detail view of the data. The Dept part is the header and the Employees part is the detail.

Figure 17.7 Sample application window

Figure 17.7 Sample application window

Frames

A frame is a visual object found on a canvas. Frames are used to arrange items in terms of blocks. The frame defines visual characteristics such as margins, offsets, and distances between items. Frames may also be defined in the Object Library to enforce standards across the application. The equivalent of frames in Visual Basic .NET is the panel.

Item

Items are interface objects that present the data values to the users. Items also enable the user to interact with the form. The type of interaction varies according to the type of item. Visual Basic .NET offers a wide range of equivalents for the form items called controls.

The type of an item can be examined by locating the Item Type property of the item. Items have both a visual representation and a forms representation (code) and will appear in both models. However, the properties captured in each of the models will differ.

Only the common items, such as text item, check box, and radio groups, which could be dependent on the database are discussed here. For each of the items, the visual and functional characteristics are attached to the visual model and the logical characteristics are captured in the form model. For example, the height, width, font size, and format mask are the visual characteristics. The database item (table and column) that is used to populate the text item is the form characteristic.

Text Item

Text items allow for text to be displayed and manipulated. Text items are called by the name TextBox in Visual Basic .NET. When examining text items, Visual Basic .NET developers need to determine whether this item corresponds to a column in a database table by looking at the Database Item property. If so, it needs to be tied to a dataset or other data source in Visual Basic .NET. The name of the text item is not necessarily the same as the name of the database column. Look at the Column Name property for the name of database column.

Check Box Item

A check box provides the user with a Boolean control that has just two values, such as true and false, or on and off. The check box values can be set by fetching from the database. When creating the equivalent Check Box control, Visual Basic .NET developers need to look at the following three properties of a check box item: Value When Checked, Value When Unchecked, and Check Box Mapping of Other Values.

Radio Group Item

A radio group provides the user with a fixed set of options that are mutually exclusive, Radio group item values can also be set using database columns. Under the radio button, the developer should look at the Radio Button Value property for the value assigned to the button. In Visual Basic .NET the radio group can be replaced by a combination of GroupBox and RadioButtons.

Buttons

Buttons are interface items that can display list of values, commit data in a form, query the database, or invoke PL/SQL blocks. The WHEN-BUTTON-PRESSED trigger holds the code for these actions. Buttons are also found in Visual Basic .NET.

Understanding Visual Basic .NET

Visual Basic.NET offers Windows® Forms (WinForms) Designer, which is very similar in nature to Oracle Forms Builder. The product of the Windows Forms Designer is a Windows-based form application that is similar in nature to an Oracle Forms application. Forms are the fundamental elements of the application on which controls (same as items in Oracle Forms) can be placed for user interaction. As in Oracle Forms, menus and toolbars provide a structure for the user to control the application. For readers who are unfamiliar with VB.NET, there is a lot of in-depth technical information available at https://msdn.microsoft.com/vbasic/using/

If the conversion of the forms is done correctly, then the WinForm is simply the presentation layer with all of the database access logic and rules put into a business object layer. The conversion to a Web Form solution, as a result, only requires associating the appropriate Web server control to an object holding the data retrieved from the business object layer.

The controls on a form simply provide an interface to the event-driven model that is the equivalent of the triggers system in Oracle Forms. As changes are made to textbox input, the pressing of buttons, or the selection of items in a list, additional code is triggered (see "Triggers" in the "Form Module" section earlier in this chapter). The code is in the form of subroutines that handle the call from the raised event to provide some specific action. The PL/SQL routines will map to these subroutines. The action is typically defined as an algorithm or a business rule. In most applications, the business rules extract information from the database.

A summary of the entire array of controls in WinForms is available at https://msdn.microsoft.com/library/en-us/vbcon/html/vboriControlsForWinForms.asp.

The .NET Framework is so extensible that numerous Independent Software Vendors (ISVs) have created unique controls or have enhanced the functions in existing .NET controls. Developers themselves can generate their own controls for personal needs.

Many of the most common controls emulate functionality that has been used in visual forms for years, whether in classic Visual Basic, Delphi, or Oracle. One of the most beneficial properties of most controls is the capability to bind them to data sources. Data sources do not have to be ADO.NET objects. Sources can come from arrays and other collection-type objects.

Scenario 1: Rewriting to Visual Basic .NET

Converting from Oracle Forms to Visual Basic .NET is non-trivial. There is no tool that automates the conversion to VB.NET. In Oracle Forms, an application can be separated into a visual or presentation component and a code component. This separation makes the migration easier and is the approach used in this guidance.

Two methods are available to convert Oracle Forms to Visual Basic.NET:

Case 1: Using Oracle Designer
Case 2: Manually Redesigning the application

Case 1: Using Oracle Designer

Visual Basic code is generated from the Oracle Form application using the Visual Basic Generator, which is a function of Oracle Designer. The Visual Basic code is then upgraded to Visual Basic.NET using the guidance available from Microsoft at https://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconupgradingapplicationscreatedinpreviousversionsofvisualbasic.asp.

At the time of publication, this method is not perfect. Many manual changes will still need to be made after the automated conversion. Using Oracle Designer is still a useful exercise because it provides a valuable template structure of the migrated Visual Basic application even though subsequent changes to the code will be necessary.

Case 2: Manually Redesigning the Application

In this method, the entire Oracle Forms application is documented and the new application is built in an orderly fashion.

To document and redesign the application, follow these steps:

Create a list of forms found in the application and note the relationship between them.
Create two models for each form.

The first model, which is called the Forms Model, will contain all the forms components. The Forms Model will document the four modules: forms module, menu module, object libraries, and PL/SQL libraries. The second model, called the Visual Model, will document all the layout-related components: windows, canvases, frames, and items
Document the Forms Model.

Open a form in the Ownership View using Oracle Forms Designer and note the components under each module with their hierarchy from module down to items. Perform the following actions:
1. Start with documenting all the modules: forms modules, menu modules, object libraries, and PL/SQL libraries using Figure 17.1 as reference.
2. For each module, incrementally refine the model with the next level of detail using multiple passes.
3. Document all the supporting components, such as LOVs, record groups, and program units, found under the form module.
4. Based on the size of the form, submodels or documents may be prepared for subsections of the model.
5. A block document may be created for just the data blocks and control blocks. Use Oracle runtime in debug mode to understand the flow. When you run Oracle Forms in debug mode, the runtime environment displays the name of each trigger when it fires. Operate the form as a user and document the flow of execution in the blocks document.
6. A procedures document containing a compilation of PL/SQL code or program unit found in triggers, user-named subprograms, packages, PL/SQL-type menu items, and menu startup code. All places in a form which contained PL/SQL code can be conveniently viewed by switching the form view to Show PL/SQL Only. For each program unit, note the existence of SQL or calls to database stored programs.
Document the Visual Model.

Open the form in the Visual View using Oracle Forms Designer and note the components under each module, capturing the hierarchical relationship as shown in Figure 17.3. Document all the visual characteristics for each of the components.
Convert the shared code.

Start the migration by converting the shared components that are under the object libraries and PL/SQL libraries. While migrating from Oracle Forms to Visual Basic .NET, continue to maintain the same logical grouping and organization of the various forms components, including the shared libraries.

In Visual Basic .NET, create a class library project, one each for the object libraries and the PL/SQL libraries.
- Migrate the PL/SQL libraries.
  
  The procedures document contains the required information for migrating the PL/SQL libraries. For each PL/SQL library, create a Visual Basic .NET class in the target project. Each program unit in a library can be migrated into methods with the equivalent functionality.
- Migrate the object libraries.
  
  The migration of the object libraries can be performed using the Forms Model. For each object library, create a Visual Basic .NET class in the target project. Each object in a library can be migrated into an equivalent method in the class.
Convert the Visual Model.

Create a Visual Basic .NET Windows application project. The goal in this step is only to build the shell visually identical to the Oracle Forms application. In this step, you are concerned with the form and not the function. The functionality will be addressed in the next step. For example, a DataGrid control will be added to a form in this step while the tasks to connect to SQL Server and access data are performed in the next step.

For each window found in the Visual Model, create a form in the project. In Visual Basic .NET, the concept of canvas is handled by the form itself. The frames in the Oracle Forms application should be converted to panels in Visual Basic .NET. The items in a frame should be replaced with its equivalent controls. Also add the menu controls to match the Oracle Forms application.

For steps on adding forms to a project, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskaddingformtoproject.asp.

For more information on how to add Windows Forms Controls, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vboriwinformscontrols.asp.

For more information about learning the techniques for arranging the various controls on a Windows Form, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconformsdesigner.asp.

For guidance on adding menus, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconwindowsaccessories.asp.
Convert the Form Model.

At this stage, all the pieces are in place to code the business logic and application functionality. The majority of the work is related to converting the code in the blocks and items, which was documented in the block document. Because an almost one-to-one mapping of the shared libraries and its objects has been performed, the code in Visual Basic .NET can be written as a parallel to the code in the source application.
1. Start by converting the components, such as control blocks, program units, record groups, LOVs, and triggers that could be tied to multiple controls.
2. Code all the data components in the form because these could be possibly associated with multiple controls. A detailed account on how to perform this is provided in the "Adding Data Components to a Windows Form" section later in this chapter.
3. Complete the migration of the data block by adding code to read data and bind the grid.
4. Although the grid is bound to the dataset you created, the dataset itself is not automatically filled in. Instead, you must fill the dataset by calling a data-adapter method. Double-click the page to display the page's class file in the Code Editor. In the form's event handler, call the data adapter's Fill method, passing it the dataset you want to populate: SqlDataAdapter1.Fill(dsDept).
5. For more details on data binding and Windows Forms, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vboriwindowsformsdataarchitecture.asp.
6. The number of lines of code generated by the Data wizard is far in excess of what would be required if using Data Access Application Block (DAAB) for .NET.
7. Examine every control and add code to the control and its event.
8. Achieve the proper flow of execution through navigation rules similar to that seen when executing the Oracle Form application in the debug mode.

Adding Data Components to a Windows Form

You do not directly add a dataset to a page. Access to the dataset can be created in one of several ways. There are two preferred methods. One method is to use the set of visual objects available in the Toolbox under the Data tab. The other is to take advantage of the Microsoft Data Access Application Block for .NET.

Using the Visual Studio .NET Data Wizards

Visual Studio® .NET Data Wizards are ADO.NET code generators for creating access to SQL Server, OLEDB, Oracle, and ODBC data solutions. There is a small series of data objects needed to generate datasets to sources under ADO.NET. They are DataAdapters, connections, commands, and the datasets themselves. Connections provide the necessary information about data sources, authentication, and pooling options. Commands define whether the source is queried through stored procedures, direct table access, or through T-SQL Statements. DataAdapters provide a specific pipe for accessing data and transforming it back to the source by creating the Select, Update, Insert, and Delete commands; creating stored procedures; or using existing stored procedures. DataSets act as the virtual database to the client.

To use the wizards to access SQL Server, follow these steps:

Create the SqlAdapter.

From the Toolbox within VS.NET, choose the Data tab and drag the SqlDataAdapter onto the form area. If other data providers are needed, choose the appropriate OLEDB or ODBC Data Adapter. The wizard will start, and a SqlDataAdapter1 object will be created in the Component Tray area beneath the form.
Reference the connection.

The next step of the wizard is to choose from a drop-down list of pre-existing connections used with VS.NET or to generate a new connection. Clicking New Connection brings up the DataLink Properties dialog box to create the new connection object.
Choose a query type.

The wizard then allows the developer to choose between SQL statements or stored procedures. As previously mentioned, it is best to use stored procedures when possible.
Generate the SQL statements, stored procedures, or bind the commands.

Depending on the choice in the previous step, the developer will be given the opportunity to provide or create an appropriate SQL statement. Inspecting the PL/SQL code is described earlier in the section "PL/SQL Library."
Finish the wizard.

Now the Component Tray contains both the SqlDataAdapter1 and SqlConnection1 objects. The information created from the wizard can be viewed in from the Properties window of the designer or by looking at the generated code. The Properties window of the DataAdapter shows the commands chosen (Delete, Insert, Select, Update) and their respective properties.
Generate the DataSet.

Now that the reference has been made to the source and the pipe has been created to work with the source, it is necessary to provide the client with the result. The container necessary for this is a dataset. Because the dataset is based on the information provided by the adapter, Visual Studio .NET has a wizard to take advantage of the information stored within the adapter. This is done by right-clicking the SqlDataAdapter1 inside the component tray, clicking Generate dataset, and then choosing most of the defaults (except when the object is renamed).

Using the Microsoft Data Access Application Block

The Data Access Application Block encapsulates performance and resource management preferred practices for accessing SQL Server databases. If used, it will reduce the amount of custom code needed to create, test, and maintain.

Specifically, the Data Access Application Block helps you:

Call stored procedures or SQL text commands.
Specify parameter details.
Return SqlDataReader, DataSet or XMLReader objects.

Instead of using the wizards (which create numerous references to adapter, command, and connection objects), the Data Access Application Block (DAAB) helps limit the creation of datasets to as little as one line of code.

The Data Access Application Block is available at https://www.microsoft.com/downloads/details.aspx?FamilyId=F63D1F0A-9877-4A7B-88EC-0426B48DF275&displaylang=en. After downloading, compile the assembly in the VB assembly. It will now be available to be referenced by any Visual Basic .NET application.

Calling T-SQL Stored Procedures from Windows Forms

To call a stored procedure, you first need to identify the stored procedure, and then create a DataReader or DataAdapter object. Each requires a database connection and a reference to the stored procedure name through the CommandText property. Next, you set the CommandType property to CommandType.StoredProcedure. Finally, any stored procedure parameters need to be defined for input and/or output in a collection of parameter objects. If the DataReader is used, then the developer must fill the appropriate controls by iterating though the collection of rows returned and applying the values to the controls. If the DataAdapter is used, then a DataSet must be filled and the controls must be bound through the DataSource or DataBinding properties.

Testing the Visual Basic .NET Application

The Visual Basic .NET application is a newly written application instead of a migrated application. Even though Oracle Forms and Visual Basic are architecturally and functionally similar, none of the code is reusable. Hence the application should undergo unit testing using a procedure that would be followed for new development. However, this newly developed Visual Basic .NET application does differ from other new development projects. Remember that the Visual Basic .NET application was designed to mimic the form and function of the Oracle Forms application, while most applications are developed based on documented requirements.

The objective of unit testing is to verify that the user interface closely resembles the Oracle Forms application and every component functions correctly. Start by visually comparing every frame in the Oracle Forms application to the corresponding window in Visual Basic .NET. Ensure that all panels are present. Ensure that all the controls (items) are accurately presented on the panel. Crosscheck all the visual components against the Visual Model.

The event models differ from Oracle Forms to Visual Basic .NET. Oracle Forms contains a nested hierarchy, with events being fired when moving through the window and its objects. These have to be duplicated in Visual Basic .NET. Verify the behavior by performing a walk-through of the entire application with tracing and debugging enabled. The logs can later be reviewed for errors and execution sequences. Visual Basic .NET provides a set of methods and properties that help you trace and debug the execution. Interactive debuggers are also available in the Visual Studio .NET SDK.

The application should then be tested for functionality. Start with testing the menus and then drilling down window-by-window. Test the functionality of every control. Use the same set of test cases in both applications. Populate the test data in the form and click all the controls. Verify that all the functional areas and navigation match the old application. Validate that the presentation domain screens are all called by the application.

For detailed guidance about all aspects of testing your Visual Basic .NET application and the available tools, refer to

https://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/vxoriTestingOptimizing.asp.