Tutorial: Object-Oriented Web Application using SQL

This tutorial walks you through the creation of an object-oriented Windows ASP.NET Web application that interacts with the BookDemo base application using SQL. The project you create includes a page that is generated in managed COBOL. This page calls an intermediary class method to funnel all requests from the Web page to a single routine. This encases the functionality within a run unit for isolation of resources, such as database connections, for any function triggered from the Web page. The intermediary class method then calls the business logic class method to perform the work and access the database.

Demonstration Solution

The OOSQLBookDemoWebApplication solution and project you create for this tutorial uses the following provided demonstration files in addition to the BookDemo base application:

sqlbook.cbl

In this tutorial, sqlbook.cbl is recompiled as a COBOL class that contains the business logic and database access code. This class is supplied and this tutorial adds it as a project reference to the Web application project.

SqlBookWrapper.cbl

In this tutorial, SqlBookWrapper program is recompiled as an intermediary class. This intermediary class is added as a project reference to the Web application project. The class creates a run unit, and passes the function code, along with the book details, to the business logic class defined in sqlbook.cbl to perform the work involving SQL.

Default.aspx

The main Web form. You modify it to contain user input fields, output display fields, and a trigger button used to call the client program. Input and output fields are defined as text boxes.

Default.aspx.cbl

This is the generated client COBOL program that is triggered by an end user click event initiated from the main Web form. When triggered, it:

Extracts the input data from text boxes on the form as System.Strings.
Calls the SqlBookWrapper program, passing it the string objects and receiving output as System.Strings.
Populates the text boxes on the form with the received System.Strings.

Web.config

The application configuration file.

Populate the BookDemo Database

If you have not already done so in another OpenESQL Managed Code tutorial, using SQL Server Management Studio or another preferred method, run the SetupSqlBooksSQLServer.sql script, located in %PUBLIC%\Documents\Micro Focus\Visual COBOL\Samples\SQL\ado.net\OOSqlLegacyBook directory (default location), against the BookDemo database.
This generates and populates the appropriate table.

Create a Solution and Project

In this procedure, you create the OOSQLBookDemoWebApplication solution and project, which contains skeleton versions of the files listed in the Demonstration Solution section.

Start Visual COBOL.
In Visual COBOL, click File > New > Project.
Under Installed > Templates, expand COBOL; then click Web.
At the top of the center pane, ensure that .NET Framework 4.5 is selected.
In the center frame, select ASP.NET Web Application.

Complete the remaining fields as follows:

Name	`OOSQLBookDemoWebApplication`
Location	Full path to any local directory; for example, c:\VSTutorials
Solution Name	`OOSQLBookDemoWebApplication`

If the location you specified doesn't exist, check Create directory for solution.
Click OK.

Add Existing Projects

In the Solution Explorer, right-click the OOSQLBookDemoWebApplication solution; then select Add > Existing Project.
Browse to the %PUBLIC%\Documents\Micro Focus\Visual COBOL\Samples\SQL\ado.net\OOSqlBookWrapper directory.
Double-click the OOSqlBookWrapper COBOL project.
In the Solution Explorer, right-click the OOSQLBookDemoWebApplication solution; then select Add > Existing Project.
Browse to the %PUBLIC%\Documents\Micro Focus\Visual COBOL\Samples\SQL\ado.net\OOSqlLegacyBook directory.
Double-click the OOSqlLegacyBook COBOL project.

Add Project References

In the Solution Explorer, right-click the OOSQLBookDemoWebApplication project; then select Add Reference.
In the left pane, expand Solution; then click Projects.
In the right pane, check OOSqlBookWrapper and OOSqlLegacyBook; then click OK.

Add Project Dependencies

In the Solution Explorer, right-click the OOSQLBookDemoWebApplication project; then select Project Dependencies from the context menu.
From the Projects drop-down list, select OOSQLBookDemoWebApplication.
Under Depends on, check both OOSqlBookWrapper and OOSqlLegacyBook; then click OK.

Declare a Data Item

In the Solution Explorer, open the Default.aspx.cbl file, located in the OOSQLBookDemoWebApplication project under the Default.aspx entry.
Declare the my-book data item in the working-storage section as follows:
```
       01 my-book   type OOSqlBookWrapper.SqlBook.
```
Save and close the file.

Build the SQLBookDemoWebApplication Project

In the Solution Explorer, right-click the OOSQLBookDemoWebApplication project; then select Build from the context menu.

Update the Web.config File

The default Web.config file added when you created the solution does not contain the configuration code needed to access the COBOL runtime and configure the project to run COBOL applications. Here, you replace the default file with a provided updated file.

In the Solution Explorer, right-click Web.config; then select Delete from the context menu.
Click OK to delete the file.
Right-click the OOSqlBookDemoWebApplication project; then select Add > Existing Item.
Browse to the %PUBLIC%\Documents\Micro Focus\Visual COBOL\Samples\SQL\ado.net\OOSqlBookDemoWebApplication directory.
Double-click Web.config.

Paint the Form

In the Solution Explorer, right-click Default.aspx; then select View Designer from the context menu.
At the bottom of the Form Designer, click Source.

Add the following code immediately before the closing </asp:Content> element:

    <asp:Label ID="Label1" runat="server" Text="CATALOG SEARCH"></asp:Label>
    <asp:Label ID="catalogNumberLabel" runat="server" Text="Catalog Number"></asp:Label>
    <asp:TextBox ID="textBoxStockNo" runat="server"></asp:TextBox>
    <asp:Button ID="searchButton" runat="server" Text="Search" OnClick="searchButton_Click" />
    <asp:Label ID="errorLabel" runat="server" Text="Status" Visible="False"></asp:Label>
    <asp:TextBox ID="errorField" runat="server"></asp:TextBox>
    <asp:Label ID="Label2" runat="server" Text="RESULTS"></asp:Label>
    <asp:Label ID="titleLabel" runat="server" Text="Title"></asp:Label>
    <asp:TextBox ID="textBoxTitle" runat="server"></asp:TextBox>
    <asp:Label ID="authorLabel" runat="server" Text="Author"></asp:Label>
    <asp:TextBox ID="textBoxAuthor" runat="server"></asp:TextBox>
    <asp:Label ID="typeLabel" runat="server" Text="Type"></asp:Label>
    <asp:TextBox ID="textBoxType" runat="server"></asp:TextBox>
    <asp:Label ID="priceLabel" runat="server" Text="Price"></asp:Label>
    <asp:TextBox ID="textBoxPrice" runat="server"></asp:TextBox>
    <asp:Label ID="soldLabel" runat="server" Text="Sold"></asp:Label>
    <asp:TextBox ID="textBoxSold" runat="server"></asp:TextBox>
    <asp:Label ID="onHandLabel" runat="server" Text="On Hand"></asp:Label>
    <asp:TextBox ID="textBoxOnHand" runat="server"></asp:TextBox>
    <asp:Label ID="stockValueLabel" runat="server" Text="Stock Value"></asp:Label>
    <asp:TextBox ID="textBoxStockValue" runat="server"></asp:TextBox>

Note: This is a short-cut method to painting the form. Alternatively, you could paint the form using the Toolbox. For more information, see the Painting a Button and a Label section in Tutorial: Developing .NET COBOL Applications.

Save the form, but do not close the Form Designer.

Create a Click Event

At the bottom of the Form Designer, click Design.
Double-click the Search button on the form.
This opens Default.aspx.cbl in the COBOL editor, and inserts a searchButton_Click method into the code. This method is known as a Click Event. At this point in development, the method is empty.
Click (Save All).
Close the Default.aspx file in the Form Designer.

Code the searchButton_Click method

This calls the legacy program and provides input values.

In the COBOL editor, edit the code for the searchButton_Click method to read as follows:

       method-id searchButton_Click protected.
       working-storage section.
       01 book type OOSqlBookWrapper.SqlBook.
       01 anException type System.Exception.
       01 bookFunction string.
       local-storage section.
       
       procedure division using by value lnkSender as object by value lnkEvent as type EventArgs.
       try
            set book to type OOSqlBookWrapper.SqlBook::New()
            set book::StockNumber   to textBoxStockNo::Text  
            set bookFunction        to  "1"
            invoke book::CallLegacyWithRunUnit(bookFunction)
            invoke self::PopulateForm(book)
       catch anException 
            invoke self::DisplayException(anException)
       end-try
       end method.

Code the PopulateForm and DisplayException methods

In the COBOL editor, add code for the PopulateForm and DisplayException methods as follows:

       method-id PopulateForm final private.
       procedure division using aBook as type OOSqlBookWrapper.SqlBook.

           if aBook <> null
               set errorLabel::Visible to false
               set errorField::Visible to false
               set textBoxStockNo::Text    to aBook::StockNumber
               set textBoxTitle::Text      to aBook::Title
               set textBoxAuthor::Text     to aBook::Author
               set textBoxType::Text       to aBook::Type
               set textBoxPrice::Text      to type System.Convert::ToString(aBook::RetailPrice)
               set textBoxOnhand::Text     to type System.Convert::ToString(aBook::NumberOnHand)
               set textBoxSold::Text       to type System.Convert::ToString(aBook::NumberSold)
               set textBoxStockValue::Text to type System.Convert::ToString(aBook::StockValue)
           else
               set textBoxStockNo::Text    to "****"
               set textBoxTitle::Text      to "*************************************"
               set textBoxAuthor::Text     to "*************************************"
               set textBoxType::Text       to "****"
               set textBoxPrice::Text      to "****"
               set textBoxOnhand::Text     to "****"
               set textBoxSold::Text       to "****"
               set textBoxStockValue::Text to "*****"
           end-if

       end method.
       
       method-id DisplayException private.
       procedure division using by value lnkException as type System.Exception.
           set my-book to null
           set errorLabel::Visible to true
           set errorField::Visible to true
           set errorField::Text to lnkException::Message
           invoke self::PopulateForm(my-book)
       end method.

Save the file and close the editor.

Build the OOSQLBookDemoWebApplication Solution

In the Solution Explorer, right-click the OOSQLBookDemoWebApplication solution; then select Build Solution from the context menu.

Run the Application

From the main menu, click DEBUG > Start without debugging.
On the form, type 1111 into the Catalog Number field; then click Search.
Experiment further by searching for the 2222 and 3333 catalog records.
Close the form.