Vyoms OneStopTesting.com - Testing EBooks, Tutorials, Articles, Jobs, Training Institutes etc.
OneStopGate.com - Gate EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopMBA.com - MBA EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopIAS.com - IAS EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopSAP.com - SAP EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopGRE.com - of GRE EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
Bookmark and Share Rss Feeds

ADO.NET, COBOL and Stored Procedure | Articles | Recent Articles | News Article | Interesting Articles | Technology Articles | Articles On Education | Articles On Corporate | Company Articles | College Articles | Articles on Recession
Sponsored Ads
Hot Jobs
Fresher Jobs
Experienced Jobs
Government Jobs
Walkin Jobs
Placement Section
Company Profiles
Interview Questions
Placement Papers
Resources @ VYOMS
Companies In India
Consultants In India
Colleges In India
Exams In India
Latest Results
Notifications In India
Call Centers In India
Training Institutes In India
Job Communities In India
Courses In India
Jobs by Keyskills
Jobs by Functional Areas
Learn @ VYOMS
GATE Preparation
GRE Preparation
GMAT Preparation
IAS Preparation
SAP Preparation
Testing Preparation
MBA Preparation
News @ VYOMS
Freshers News
Job Articles
Latest News
India News Network
Interview Ebook
Get 30,000+ Interview Questions & Answers in an eBook.
Interview Success Kit - Get Success in Job Interviews
  • 30,000+ Interview Questions
  • Most Questions Answered
  • 5 FREE Bonuses
  • Free Upgrades

VYOMS TOP EMPLOYERS

Wipro Technologies
Tata Consultancy Services
Accenture
IBM
Satyam
Genpact
Cognizant Technologies

Home » Articles » ADO.NET, COBOL and Stored Procedure

ADO.NET, COBOL and Stored Procedure








Article Posted On Date : Monday, March 26, 2012


ADO.NET, COBOL and Stored Procedure
Advertisements

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.






Sponsored Ads



Interview Questions
HR Interview Questions
Testing Interview Questions
SAP Interview Questions
Business Intelligence Interview Questions
Call Center Interview Questions

Databases

Clipper Interview Questions
DBA Interview Questions
Firebird Interview Questions
Hierarchical Interview Questions
Informix Interview Questions
Microsoft Access Interview Questions
MS SqlServer Interview Questions
MYSQL Interview Questions
Network Interview Questions
Object Relational Interview Questions
PL/SQL Interview Questions
PostgreSQL Interview Questions
Progress Interview Questions
Relational Interview Questions
SQL Interview Questions
SQL Server Interview Questions
Stored Procedures Interview Questions
Sybase Interview Questions
Teradata Interview Questions

Microsof Technologies

.Net Database Interview Questions
.Net Deployement Interview Questions
ADO.NET Interview Questions
ADO.NET 2.0 Interview Questions
Architecture Interview Questions
ASP Interview Questions
ASP.NET Interview Questions
ASP.NET 2.0 Interview Questions
C# Interview Questions
Csharp Interview Questions
DataGrid Interview Questions
DotNet Interview Questions
Microsoft Basics Interview Questions
Microsoft.NET Interview Questions
Microsoft.NET 2.0 Interview Questions
Share Point Interview Questions
Silverlight Interview Questions
VB.NET Interview Questions
VC++ Interview Questions
Visual Basic Interview Questions

Java / J2EE

Applet Interview Questions
Core Java Interview Questions
Eclipse Interview Questions
EJB Interview Questions
Hibernate Interview Questions
J2ME Interview Questions
J2SE Interview Questions
Java Interview Questions
Java Beans Interview Questions
Java Patterns Interview Questions
Java Security Interview Questions
Java Swing Interview Questions
JBOSS Interview Questions
JDBC Interview Questions
JMS Interview Questions
JSF Interview Questions
JSP Interview Questions
RMI Interview Questions
Servlet Interview Questions
Socket Programming Interview Questions
Springs Interview Questions
Struts Interview Questions
Web Sphere Interview Questions

Programming Languages

C Interview Questions
C++ Interview Questions
CGI Interview Questions
Delphi Interview Questions
Fortran Interview Questions
ILU Interview Questions
LISP Interview Questions
Pascal Interview Questions
Perl Interview Questions
PHP Interview Questions
Ruby Interview Questions
Signature Interview Questions
UML Interview Questions
VBA Interview Questions
Windows Interview Questions
Mainframe Interview Questions


Copyright © 2001-2018 Vyoms.com. All Rights Reserved. Home | About Us | Advertise With Vyoms.com | Jobs | Contact Us | Feedback | Link to Us | Privacy Policy | Terms & Conditions
Placement Papers | Get Your Free Website | IAS Preparation | C++ Interview Questions | C Interview Questions | Report a Bug | Romantic Shayari | CAT 2018

Fresher Jobs | Experienced Jobs | Government Jobs | Walkin Jobs | Company Profiles | Interview Questions | Placement Papers | Companies In India | Consultants In India | Colleges In India | Exams In India | Latest Results | Notifications In India | Call Centers In India | Training Institutes In India | Job Communities In India | Courses In India | Jobs by Keyskills | Jobs by Functional Areas

Testing Articles | Testing Books | Testing Certifications | Testing FAQs | Testing Downloads | Testing Interview Questions | Testing Jobs | Testing Training Institutes

Gate Articles | Gate Books | Gate Colleges | Gate Downloads | Gate Faqs | Gate Jobs | Gate News | Gate Sample Papers | Gate Training Institutes

MBA Articles | MBA Books | MBA Case Studies | MBA Business Schools | MBA Current Affairs | MBA Downloads | MBA Events | MBA Notifications | MBA FAQs | MBA Jobs
MBA Job Consultants | MBA News | MBA Results | MBA Courses | MBA Sample Papers | MBA Interview Questions | MBA Training Institutes

GRE Articles | GRE Books | GRE Colleges | GRE Downloads | GRE Events | GRE FAQs | GRE News | GRE Training Institutes | GRE Sample Papers

IAS Articles | IAS Books | IAS Current Affairs | IAS Downloads | IAS Events | IAS FAQs | IAS News | IAS Notifications | IAS UPSC Jobs | IAS Previous Question Papers
IAS Results | IAS Sample Papers | IAS Interview Questions | IAS Training Institutes | IAS Toppers Interview

SAP Articles | SAP Books | SAP Certifications | SAP Companies | SAP Study Materials | SAP Events | SAP FAQs | SAP Jobs | SAP Job Consultants
SAP Links | SAP News | SAP Sample Papers | SAP Interview Questions | SAP Training Institutes |


Copyright ©2001-2018 Vyoms.com, All Rights Reserved.
Disclaimer: VYOMS.com has taken all reasonable steps to ensure that information on this site is authentic. Applicants are advised to research bonafides of advertisers independently. VYOMS.com shall not have any responsibility in this regard.