| 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:
-
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
-
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.
-
Instructs the system to set the value of
the variable.
SET
Value OF parameterID
TO sID
-
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!
|
