Data Access With ADO.NET In Micro Focus Visual COBOL

This will be a multi-part article dealing with data access in the Microsoft .NET arena, using ADO. We will begin with a bit of history of ADO, what it is, why it's so important and finally how to utilize it in a COBOL environment. This first article will provide a brief history of the data access as well as provide the basic flow for using ADO to access the data. The sample included with the article will provide a basic access to the Northwinds database of SQL Server.

The second sample will expand on the principles and provide more detail of using a stored procedure to retrieve the data from a file. Please read these articles in the order in which they were written. Remember, you have to learn how to walk before you can run a marathon. Take your time and review the concepts, terms and samples presented here.

This series of articles assumes that you have Microsoft SQL Server installed and it is available to you. It also assumes that you have the demonstration databases installed, particularly the Northwinds database.

What is ADO?

ADO is the acronym for ActiveX Data Object and is based on Microsoft technology. The chart given below details the evolutionary progress in the data access technology from Microsoft.



Each of the above technologies has their good and bad points, but in every evolutionary cycle, the technology matured and became more 'industrial strength'. It became better and is able to handle the issues of scalability and performance. Hence, what is ADO? According to Mahesh Chand;

"ADO is a set of ActiveX controls that provide programmatic access to Microsoft's latest underlying data access technologies."

Basically, it's a COM wrapper around a set of the standard access mechanisms for the databases.

ADO.NET

Hence, what is ADO.NET? ADO.NET is the next evolutionary step in data access technology. Employing the aforementioned ADO technology, ADO.NET expands this by incorporating XML into a standard model to not only the relational data models but also the text based XML data. The API used in .NET is a managed code within .NET Framework and this means that any 'first-class' language within .NET can take the advantage of the classes, already defined, without having to reinvent the wheel, as it relates to the data access. Basically, it's a standard set of access and manipulation procedures that a developer can use to access his/her data. The best part is you as a developer don't have to install anything as it's already there in .NET Framework.

Basic ADO Flow

Microsoft created this unique library of the data access procedures for me to use, now what? Where do I begin? What do I look for? We will begin to answer these questions by looking at the namespace that controls the data access for SQL Server. We will discuss other data providers at the end of this article but for now, we will concentrate on SQL Server. For SQL Server access, the namespace you need to remember is System.Data.SqlClient.

Remember this namespace. It is important!

Within SQLClient namespace, there are currently 16 classes. These classes provide a programmatic manner in which the connections to the databases can be established, permissions verified, exceptions handled and other tasks relevant to the data access. You can go to this link to further research and explore this namespace. Let’s start on how to use ADO.

The basic flow to access a data object is to:

1. Create a connection to the data object.
2. Create a command for the data object to act upon.
3. Read the data returned by the data object.

In order to accomplish the above steps, we will need to use the following classes:

1. System.Data.SqlClient.SqlConnection
2. System.Data.SqlClient.SqlCommand
3. System.Data.SqlClient.SqlDataReader

Preparation

I used a managed console Application for this project. To begin, we need to declare some variables we’ll be using. We need to create a variable to define the connection properties, one to define the SQL commands that we’ll be issuing; another to hold the data. We read from the database and finally note any exceptions we may encounter. All of these are defined in the working-storage section and appear as shown below:



These are the main objects that will not only enable our connection and the usage of ADO but also provide some information back from the database engine on the processes we execute there.

Our next step will be to create some fields to hold the data we'll be retrieving from the table as well as a few miscellaneous working fields. The completed working-storage section contains the following:



Notice that I have the ability to mix the type of the data definitions. I defined some variables using the standard COBOL syntax of the PIC clause, while with other variables, I’ve used standard .NET data types. The ability to enable this functionality greatly increases the usefulness of some of your original source code without having to worry about the conversion to the new data types.

Finally, we've set up all of the different divisions and the references that will be necessary for us to utilize ADO and read some data from a table. We now need to follow the steps we outlined above.

Create a Connection

The first step is to create a connection to the database we are interested in. If you have ever used ODBC and embedded SQL, you have to establish the connection in the ODBC administrators panel in the Control Panel and then issue the SQL connect statement. The connect statement was similar to "EXEC SQL CONNECT TO {database} END-EXEC".

In ADO, the connection is much simpler. We use the SQLConnection object to establish the connection to the database. One of the benefits of ADO is, we no longer have to set up the connection in the ODBC administrators panel and this reduces the setup and the distribution issues. Instead, we merely build our connection string to the database, instantiate a connection to the database and invoke the 'Open' method on the new object. In our example, the connection is established via the following code:



What the statement is doing is creating a "NEW" instance of the SQLConnection class and naming it sqlConnection. We then provide the parameters for the connection with the connection string property. The next statement then invokes sqlConnection's "Open" method to actually establish the connection. The tricky part of this process is the connection string. Notice, we have set the User ID, password, and the database we are attempting to connect to. It should be noted that even though the connection string for this example was 'hard-coded' you can create a Working-Storage variable and pass the connection string to a method to establish the connection, or you can build the connection string in the method that establishes the connection. There are many, many ways to define the connection string. This is just one way to show you the syntax. (For questions on the connection strings, please discuss them with your DBA, IT personnel or check MSDN.)

Create a Command

We've established our connection and now we have to tell the database; what we are attempting to do. We do this via a command structure. Again, if you have used embedded SQL. Before this, it would be your "EXEC SQL SELECT * FROM EMPLOYEES END-EXEC" statement. As a matter of fact, this is what we are going to attempt to do. We are going to select some information from the Employee database and then display it on the console Window. The statements to establish the command are the following:



Notice, for this part of the process, I created a WORKING-STORAGE variable and moved my Select statement into it. I can easily build a Select statement, based upon the information supplied to me from the user. Again, there are many different ways to accomplish a task, with this being just one among many. Once we have the command string built, we next need to instantiate the SQLCOMMAND class. The SQLCOMMAND class requires two parameters in order for it to successfully complete its task, the connection object (you need to tell it where to execute the command) and the command to execute (once you have the connection, now what do you want to do). After you instantiated the SQLCOMMAND, the method you will use to actually return the data to you is the "executereader" command. This will return a data reader object from which you will access your data. How do you know if it returns any data or not?

Read the Data

OK! We have established a connection to a database. We have built and executed a command against the connection and hopefully returned the data. How do you know if you've returned any data? There are several ways to check but here is a little trick that is easy to implement. The method we will execute to access the data in the data reader object is the 'Read' method. The Read method returns a Boolean value, saying whether or not, it has read any data. If there is data, the value will be true or B"1". If no data was read then the value will be false or B"0". We will add a 'returning' phrase to our read statement to set a Boolean variable (dataFound) and we can then check if we have data to process or not.

Since, we are not sure of how much data we have to process, we use a Perform statement to iterate through the dataset returning the information we requested. The method we will use will be "GetString" and it will return a string based on an ordinal position within the dataset to a variable we have defined in WORKING-STORAGE. Always remember, .NET uses offset zero when working with the arrays so our first field that we will access will be at zero, subsequently 1 and finally 2.

The code to read through the data is as follows:



Notice, we have done a 'primer' read of the data, set prior to executing the Perform loop. This is interesting to see if we have the data to process or not, then we will not execute the Perform loop. Within the Perform loop, there are three "GetString" invocations to return the First Name, Last Name and Home Phone Number of the employee. We keep a count of how many records we've read and then display the record on the console. Finally, we read to determine if there is more data to process and then if there is; repeat the process or exit the Perform loop.

When you execute the sample program, you should see the data displayed as follows,



Do you notice anything out of place? Perhaps not lined up correctly? There is a lot of ‘space’ between the last name and the phone number but yet the correct amount of the space exists between the first and last names. Why? One of the advantages of working in a managed environment and using managed code types is the ability of the environment to manage to present the information in a much clearer, formatted way. In the Working-Storage section, we defined the first name as a ‘string’ entity and the last name as standard COBOL data type of ‘PIC X(20)’. When we display the data, the managed code type is being automatically sized, based on the number of non-space characters present. COBOL data type displays the number of characters, it has defined and in this case, it is ‘20’. Try playing with the definitions in the Working-Storage section. Change the last name to a ‘string’ data type and see the effect on the display.

Housekeeping

Afterwards, we have processed all of our data. Therefore, we exit the Perform loop. Prior to exiting the program, we need to close the data reader and the connection. We display a message on the console and wait for the user to press the enter key to exit. We execute the “Accept” statement to show you the screen display. The housekeeping code appears as follows:



Other Data Providers

This article has presented a way to access the data via ADO for SQL Server. Oracle and IBM have both provided the managed namespaces that enable similar functionality to their respective database environments.

If you’re working with Oracle, you will need to use the Oracle Data Provider for .NET or ODP.NET. You can find additional information about ODP.NET. Basically, the namespace you will be using is Oracle.DataAccess.Client. Consult the Oracle documentation for further information.

For IBM, you will need to install the IBM Data Server Provider for .NET. The namespaces you will be working with for IBM DB2 are IBM.Data.DB2 and if you are using Informix; it will be IBM.Data.Informix.

Wrap-Up

Data access in Microsoft .NET environment can save time, enable more automation of processing and help to present the data in a clear manner. The steps we outlined in the article should help you to understand the processing requirements. This article showed you how to manually create an ADO based program. Micro Focus has provided additional assistance in the form of an ADO Connection Editor and a utility called OpenESQL to create SQL enabled source code. To see how these tools can save you time and effort, please see the following video created by one of our Senior Support Managers, Chris Glazier: Visual COBOL In a Nutshell: Database Access Using ADO.NET.

The attached zip file has a complete solution for you to review and utilize. Feel free to use it as a template for your ADO experiments!

If you'd like to try out the samples and don’t have a copy of Micro Focus Visual COBOL, you can download the personal edition of Visual COBOL for free from the link.