ADO.NET, COBOL and Stored Procedure

Overview

In a previous article we began exploring how ADO.NET and NetCOBOL for .NET could be used to access data in a database. We defined the process for connecting to the database, establishing a command and finally accessing and displaying the data. In this article we will access a Stored Procedure to perform a specific task. A stored procedure is basically a series of SQL statements that reside on the database.The procedure could create a new table, retrieve data from one or more tables, update one or more tables or perform many other tasks.

Our example will select employee data from the Employee table of the Pubs database. We will create a WinForm application that will use a drop-down listbox to present the employees in the database. When an employee is selected the information for that employee will be presented. Our WinForm will look as follows:

I would at this time like to thank Brad Bower, a Systems Engineer with Fujitsu Software for his assistance in creating this example. Brad has worked extensively with ADO, NetCOBOL for .NET and ASP.NET and compiled the example in this article.

Repository

In the repository of the program you will see the following statements:

From our previous sample you will remember these statements are used to employ the .NET Framework data access classes. Specifically we are going to use the SqlConnection class to connect to the database, the SqlDataAdapter class to access the data and the DataSet and DataTable to access specific fields in the returned data.

Bind Combo Box to Data

In order to populate the combo box with the employee ID we need to bind the control the specific data item.  In the properties for the combo box the property ValueMember has been updated to reflect that data from the column emp_ID will be used to populate the list.

This is an important step in the process. If this is not done the combo box will not be populated with the employees already in the database and we would have to use another method to select the employees.

OBJECT Working-Storage Section

In the WORKING-STORAGE SECTION of the OBJECT we need to define some variables that we will be using in the methods. By defining these items in the OBJECT's WORKING-STORAGE area they will be usable by other methods in the instantiated object. The variables we will define here will contain the data returned from the database and are defined as follows:

 

Notice that the last field is a date field.  In the table definition this column is defined as
a "DATETIME" data type. In order for us to display this information properly on the form we
will convert this to a string, but in order to do this we will need some things added to the REPOSITORY.
The following statements were added to make working with the DATETIME variable easier:

      CLASS CLASS-DATETIME         AS "System.DateTime"       

        PROPERTY PROP-DAY         AS "Day"

        PROPERTY PROP-MONTH       AS "Month"

        PROPERTY PROP-YEAR        AS "Year"

"NEW" Method

When the form is created the method "NEW" is called. It is here we will want to place our code to establish our connection to the database, retrieve some data and populate the drop-down box with employees. Remember we want the form to be displayed with the combo box already populated, not having to populate it after the form is displayed.

To begin with we need to create some WORKING-STORAGE variables to hold the objects we will be creating, or instantiating. Therefore, in the WORKING-STORAGE SECTION of the "NEW" method we have added the following:

We have created references for a connection, an adapter, a dataset and a data table. We have also created a connection string variable of 200 characters in length that will be used to pass the connection string to the connection invocation. It may seem like an unnecessary step but as we will see in the Procedure Division of the method in a minute, it does make the code a bit easier to read and follow.

In the original Procedure Division for the method "NEW" there was one line to invoke the method "InitializeComponent". That line has been left in place and additional coding has been implemented to establish connectivity with the database. All the new coding has been placed after the "InitializeComponent" line. This is because we must have the form and all of it's control and properties initialized and available before we can proceed. The updated "NEW" method thus appears as follows:

Notice after the connection has been established and the data retrieved the combo box is populated. Let's take a minute and look at how the combo box is populated. The first line of the code instantiates a new dataset. This will be to hold the data.

INVOKE CLASS-DATASET "NEW" RETURNING MySQLDataSet.

The next line of code, using the data adapter object that was created earlier, populates or fills the data set with data from the table "Employee" in the PUBS database (remember in the connection string we told it what table to access).

INVOKE MyDataAdapter "Fill" USING MySQLDataSet "Employee".

Up to now we have been working with complete tables. We now need to access one item in the table, a specific column, and bind it to the combo box. We do this by using the "get_Item" method of the data set. We access the property "Table" and tell it we want to create a data table of only the first data item in the table, no matter how many rows. We do this with the following statement:

INVOKE PROP-TABLES OF MySQLDataSet "get_Item" USING BY VALUE 0 RETURNING MySQLDataTable.

We invoke the property "Tables" of the data set MySQLDataSet calling the method "get_item" telling it to access the data in the first position (or column) and return a SQL Data Table. Remember, .NET is zero based so the first item in a table is Zero, not one. While the process may seem confusing at first, re-read this section a few times and look at the code in Visual Studio. Take the time to understand what each line is doing and why. As always check the Help section for further information on the statements and review the class members for each of the classes mentioned. This is new and does take a bit of getting used to.

Now that we have a data table of just employee ID's we need to inform the combo box where to look to get it's data. The following line of code updates the property of the combo box to tell it the data source is the SQL Data Table populated in the previous line of code.

SET PROP-DATASOURCE OF cboEmp TO MySQLDataTable.

Combo Box Updates

If you were to select the combo box control and double click on it you would create a new method called "cboEmp_SelectedIndexChanged". This method is executed when the user selects a different employee from the list presented. This is the method we will use to populate the other data fields on the form.

In the WORKING-STORAGE SECTION for the method the following lines have been added:

The SelID will be used to obtain the "Selected value" property from the combo box. The easiest way to obtain this field is to use an object reference and then convert the object reference to a string. The field sID will be used to receive the employee ID.

The Procedure Division for the method will first obtain the selected value from the combo box and store it in the SelID object. After that has been obtained the method "ToString" from the class "CONVERT" will be invoked using the SelID as an input parameter and returning a string to the sID parameter. The sID parameter will then be used as an index in the call to the GetEmployeeData method to retrieve the data for that employee.  The code to do this is:

Stored Procedure

If you will remember from our earlier discussion we will call a stored procedure to get our data.  The stored procedure defined below is not a part of the PUBS database. You will need to add this stored procedure to the pubs database in SQL. ( We will not review how to add a Stored Procedure to a table. For information on how to accomplish this please refer to MSDN or work with your DBA).

Now we will create the new method to retrieve the data using sID.  This method will be called GetEmployeeData.

GetEmployeeData

The GetEmployeeData method will access the stored procedure and return the columns for the selected index. To begin, we need to establish the method and then some WORKING-STORAGE items that we will use to create the connection, the command and the parameter list of data to be retrieved. The method definition and WORKING-STORAGE section is presented in the following code:

These declarations will allow us to define the parameters of the parameter collection. The parameter collection is as it's name implies, a collection of parameters to be used to interact with the database. The collection has to be built however, it is not an automatic process. You as the designer have to define the parameters based on the task at hand. What do you need to retrieve in order to accomplish your task?

The PROCEDURE DIVISION of the method contains comments about what each line or section of code is doing. We will not replicate the entire method here but rather point out issues that are important to the successful execution of the code.

The first issue is the need to build the parameter list. We have told the stored procedure to return a number of variables to us. These will be returned as parameters within the SQL Command structure. In order to ensure we obtain the necessary information back we need to add each of the returned data items to the parameter list. They are not included in the parameter list by default and must be added individually.

Let's look at two different examples, an input parameter (one which will be used as selection criteria) and an output parameter (one that will be used to return data from the database to the source code):

The general composition of all the parameter blocks is as follows:

1. Set the data type to the proper type. Review the data types available within the .NET Framework help system if you have any questions.
   
        SET MySqlDbType TO SqlDbType-Char OF SqlDbType
   
2. Create a new SQL Parameter. The "USING" phrase is comprised of the following parameters: Host variable, data type, and length of the data being returned. The "RETURNING" phrase establishes the connection between the WORKING-STORAGE variable you defined and the SQL Parameter.
   
    INVOKE CLASS-SQLPARAMETER "NEW" USING BY VALUE "@ID"     MySqlDbType 9 RETURNING parameterID.
   
3. Instructs the system to set the value of the variable.
   
        SET Value OF parameterID TO sID
   
4. Add the parameter to the parameter list of the command you created in preceding code. You can view the actual instantiation of the command object in the source code provided.
   
   INVOKE Parameters OF MyCommand "Add" USING BY VALUE parameterID.

The only difference between the input section of code described above and an output section of code is the following line:

SET SqlParam-Direction OF parameterEmpID TO ParamDirection-Output OF ParameterDirection.

This line of code establishes the parameter being defined as an "OUTPUT" parameter and data will only flow from the database and thus no updates will be allowed on this field, at least in this procedure.

Once the parameter collection is built we are ready to execute the command object which will populate this collection.  We will open the connection then call the ExecuteNonQuery method of the command and then close the connection. The ExecuteNonQuery method does not return any rows, but populates any output parameters or return values mapped to parameters. 

     INVOKE MyConnection "Open".

     INVOKE MyCommand "ExecuteNonQuery".

     INVOKE MyConnection "Close".

After successful execution of the command the parameter collection is populated.  Now we need to retrieve the values and move them to the WS-EMPLOYEE Group level. As before we will not reproduce the entire section of code as the process is repetitive. Instead we will present one selection of code and detail what it is doing.  

The first line of code is setting the object parameterValue to the value of the Employee ID as defined in the parameter list. The parameterValue object was defined in the WORKING-STORAGE section as an "OBJECT REFERENCE CLASS-OBJECT". Instead of creating multiple fields of multiple types to perform this task we used an object reference and then invoked a "ToString" conversion to obtain the data. This was much cleaner coding and reduced the number of variables we defined.

     SET parameterValue TO PROP-VALUE OF parameterEmpID.

The next line of code performs a conversion of the parameterValue object to a string and places it in the proper WORKING-STORAGE field.

     INVOKE CLASS-CONVERT "ToString" USING BY VALUE parameterValue RETURNING WS-EMPID.

These two lines of code will need to be repeated for each parameter being returned in order to display the data.

The final step is to display the data from the WORKING-STORAGE variables onto the form itself. Each of the fields is presented below showing how to set the text property of the individual text box to the associated WORKING-STORAGE variable.

     SET PROP-TEXT   OF txtEmpID    TO WS-EMPID.

     SET PROP-TEXT   OF txtFName    TO WS-FNAME.

     SET PROP-TEXT   OF txtMInit    TO WS-MINIT.

     SET PROP-TEXT   OF txtLName    TO WS-LNAME.

     MOVE WS-JOB-ID  TO WS-VALUE.

     SET PROP-TEXT   OF txtJobID    TO WS-VALUE.

     MOVE WS-JOB-LVL TO WS-VALUE.

     SET PROP-TEXT   OF txtJobLvl   TO WS-VALUE.

     SET PROP-TEXT   OF txtPubID    TO WS-PUB-ID.

     SET PROP-TEXT   OF txtHireDate TO WS-HIRE-DATE-A.

By doing this exercise you can see how easy it can be to use the power of SQL and COBOL together to return data to a group level item thus allowing you to use existing code that may have been written for a different data source such as indexed files.  This will help you to maintain existing, tested code while only having to write a thin access layer to handle your file I/O.

Happy Coding!