Tutorial: Web Application using SQL

This tutorial walks you through the creation of a Windows ASP.NET Web application that interacts with the BookDemo base application using SQL. The project includes a page that is generated in managed COBOL. The 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 a database connection, for any function triggered from the Web page. The intermediary program maps .NET data types onto COBOL data types, and then calls the existing COBOL program to perform the work and access the database.

Demonstration Solution

The SQLBookDemoWebApplication 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, the sqlbook.cbl program is recompiled to managed code without any changes. Recompiling the program exposes it as a class and exposes its main entry point as a static method. Its linkage section defines data as standard COBOL types, such as PIC X. Non-COBOL client programs do not understand these standard COBOL types. Therefore, you must map the standard COBOL types to .NET compatible types before communicating with the client program. This mapping is done by the SqlBookWrapper.cbl program.

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. In addition to acting as an interface between the client form and the business logic COBOL program, this program also wraps the business logic around a run unit for isolation of resources such as database connections.

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\SqlLegacyBook 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 SQLBookDemoWebApplication solution and project, which contains skeleton versions of the files listed in the Demonstration Solution section.

In Visual Studio, click File > New > Project.
In the Create a new project dialog box, select COBOL from the Language drop-down list.
At the top of the center pane, ensure that .NET Framework 4.5 is selected.
Select ASP.NET Web Application.
Complete the remaining fields as follows:
- Name - SQLBookDemoWebApplication
- Location - Full path to any local directory; for example, c:\VSTutorials
- Solution Name - SQLBookDemoWebApplication
If the location you specified doesn't exist, check Place solution and project in the same directory.
Click Create.

Add Existing Projects

These projects contain the BookDemo base application.

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

Add Project References

In the Solution Explorer, right-click the SQLBookDemoWebApplication project; then select Add Reference.
In the left pane, expand Solution; then click Project.
In the right pane, check SqlBookWrapper and SqlLegacyBook; then click OK.

Add Project Dependencies

In the Solution Explorer, right-click the SQLBookDemoWebApplication project; then select Build Dependencies > Project Dependencies from the context menu.
From the Projects drop-down list, select SQLBookDemoWebApplication.
Under Depends on, check both SqlBookWrapper and SqlLegacyBook; then click OK.

Declare a Data Item

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

Build the SQLBookDemoWebApplication Project

In the Solution Explorer, right-click the SQLBookDemoWebApplication project; then select Build Solution 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 SqlBookDemoWebApplication project; then select Add > Existing Item.
Browse to the %PUBLIC%\Documents\Micro Focus\Visual COBOL\Samples\SQL\ado.net\SqlBookDemoWebApplication 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 SqlBookWrapper.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 SqlBookWrapper.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 SqlBookWrapper.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 SQLBookDemoWebApplication Solution

In the Solution Explorer, right-click the SQLBookDemoWebApplication 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.