| Overview
In a previous article ("Data Access with ADO.NET", dated November 4,
2002) we discussed the use of the SqlClient class to access data in
ADO.NET. The SqlClient class was created by Microsoft and is used
primarily to access a SQL Server database. What about other databases?
How would you access them? The answer is OleDB! Microsoft has provided
a Framework to enable you to access other databases as well as SQL
Server. Through the use
of OleDB you can access other databases that are .NET compliant. This
is a major point to make here. The database MUST be .NET enabled for
OleDB to work in .NET. One database we have been seeing quite a bit of
interest in is Pervasive SQL. The latest version of this database, V8,
is fully .NET enabled and we will utilize it in our example today.
I have installed the PervasiveV8 SDK and
the Workgroup data engine. Both of these can be downloaded from the
Pervasive Developer Zone (http://www.pervasive.com/developerZone/
) and are free for development purposes. The sample we will be using a
simple Console Application written in NetCOBOL that will access the
DemoData database and the Person table specifically, displaying the
data to the console. The version utilized returns 1500 rows.
Let's begin!
Ole!
We will be using the Ole class within
the .NET Framework. The full Namespace specification can be found by
performing a search in the Help system for System.Data.Oledb. (
I would recommend taking a few minutes and browsing through the online
help files for OleDB. Microsoft has done a great job at providing good
background and detail information) As with all of the Framework
classes Microsoft has created a very extensive Namespace with many
subclasses to assist you in developing your applications. Our
application will be a basic connect, issue a command, read the data
and handle any exceptions that occur. For this purpose we will utilize
the following classes: OleDbConnection, OleDbCommand,
OleDbDataReader, and OleDbException.
First Steps
Prior to beginning our Ole process we
need to do some housework. We need to create references in our
Repository to the Classes we mentioned above so we can access their
methods and properties. To accomplish this we create four entries in
the Repository, one for each of the aforementioned classes:
Notice the first four lines in the
Repository reference the four Ole classes we are going to be using in
our example. On the fifth line there is a Property statement. Within
the OleDbException class their is a specific property we will be
referencing and that is the Message. This property will contain a
message whenever an exception is thrown in the OleDb namespace.
"Great!" you may say, "So it gives me a message. Now what do I do with
it?" Well every good COBOL programmer codes for exceptions and handles
them accordingly, right? We're no exception (no pun intended) and have
created a Declaratives section to handle any exceptions that occur.
The Declarative section is called any
time an exception is generated. The Exception-Object is identified and
an instance created (myException). Next the message is obtained from
the instance, converted to a string and displayed on the console. A
prompt has been added to halt execution of the application until the
operator has responded.
In the Working-Storage Section we have
created instance objects for each of the classes to be used as well as
some other variables to receive the data and display it to the
console. The Working-Storage Section will not be reviewed in more
detail as it is pretty self-explanatory.
Now we can start on our four segments!
Connection (OleDbConnection)
The first item that has to be addressed
is connecting to a database. Each database provider will have their
own connection string and you will need to review the providers
documentation in order to create a connection string in the proper
format. The information necessary to connect to the Pervasive V8
database was located in the Pervasive SQL V8 SDK, Programmers
Guide, ADO OLE/DB Programming, Connecting to a database using the OLE
DB Provider. The connection was made an opened with the following
lines of code: 
The first line of code creates a new
connection object (CONNECTIONOBJ) with the parameter specified in the
USING phrase. This is where you need to be sure you have the proper
connection string and syntax for the database you are connecting to.
This is where it all starts. If you can't connect, you can't proceed.
The second line of code invokes, or calls, the OPEN method of the
connection class and establishes connectivity with the database.
Retrieve the Data (OleDbCommand)
Now we have to decide what we are going
to retrieve. For our example we want to display the first and last
name of the person, their email address and their date of birth. We
first researched the database to obtain the proper names for the
columns. The logic used to retrieve the data can best be described as
1. Set the criteria, 2. Create the command, and 3. Execute the
command. The coding for doing this looks like the following
The first line of code establishes what we are going to select from
the database. We are using a variable called MY-STRING to hold the
selection because it made the code easier to read and follow. Another
important point to consider is you can use the code as a template to
create a dynamic selection method that can be called by other
modules. By permitting your users to select options on another form
you can pass in a selection string and then have it execute and return
the data. The second line of
code creates a new command object, OLECOMMANDOBJ, that contains the
command to execute. You have to create a new command object prior to
execution, you can not merely execute the command. The third line of
code calls the ExecuteReader method, which uses the command object and
actually creates a data set that contains your data.
Read/Display the data (OleDbDataReader)
Outstanding! You've got data! Now what?
Now we have to access the data in the DataReader object and display it
on the screen. To accomplish this we will use the GetString
method of the DataReader object (this is why it's a good idea to
review the Namespaces and classes to see what it available to you
before you begin coding). The following code will loop through
accessing the datareader object and returning any data:
The first line of code checks to see if we have any data to process.
It will set the variable MY-BOOLEAN to a binary "1" (true) if
there is data to process, or binary "0" (false) if there is no data to
process. The PERFORM loop will use MY-BOOLEAN to determine when to
stop processing. The first four lines of code within the PERFORM loop
read the data and place it into WORKING-STORAGE variables. The
DATAREADEROBJ returns one column at a time identified by the USING
phrase and has an offset of zero. The date of birth is a DateTime data
type within Pervasive and thus has to be converted to a string before
it can be displayed. This is done by first retrieving it in it's
native format into a DateTime variable. This variable then invokes its
ToString method to convert the data to a displayable format. While
there may be other ways in which to display the data, I find it more
acceptable to first retrieve the data to a comparable format and then
convert the data via that data types methods. Finally we add one to a
counter, display the data to the screen and check to see if we have
any more data to retrieve. If
we have no other data to retrieve we close the connection, prompt for
input from the user and exit the application. These steps can be
reviewed in the attached code sample.
Wrap-up
Accessing databases within .NET other
than SQL Server is very similar to the manner you use to access SQL
Server. I realize that sounds funny but it's true. In accessing SQL
Server via the SQLCLIENT namespace and other databases via the OLEDB
namespace you perform the same steps:
Establish the
connection: Use the SqlConnection or OleDbConnection objects Create the
command: Use the SqlCommand or OleDbCommand objects Read the data:
Use the SqlDataReader or OleDbDataReader objects
The process is the same, the
classes/methods are similar and the result is the same: Easy access to
data. Hats off to Microsoft for providing a stream-lined data access
methodology for not only their database but for others as well.
Review the zip file that goes along with
this article. Remember though, you will need to have Pervasive SQL
Version 8 SDK installed and a data engine. Feel free to use this code
as a template to accessing other databases via OleDB!
Happy Coding! |
