Using OLEDBDataReader and OLEDBCommand

Data Reader is almost same as Recordset in ADOs. You do not get the real benefits of ADO.NET trough Data Reader. However it is a simple way of getting the required rows from data source.

' import required namespaces

Imports system
Imports System.data
Imports system.data.oledb


Module DataReader1
    
    Public Sub Main()
        
       dim  constr as string = "provider=Microsoft.jet.oledb.4.0;Data source=c:\srikanth\phones.mdb"

       dim con as New OLEDBConnection(constr)

       Dim cmd As New OLEDBCommand("select * from cities", con)

       Dim dr As OLEDBDataReader
        
     
        con.Open()
           
        ' execute query and put data into data reader
        dr = cmd.ExecuteReader()
        
        
        Do While dr.Read()
            console.Writeline(dr.getstring(0) & ":" & dr.getstring(1) & ":" & dr.getstring(2))
        Loop
        
        ' close data reader first
        dr.Close()
        
        ' close connection
        con.Close()
        
    End Sub
    
End Module

OLEDBDataReader class providers a forward-only access to the data source. It is more like forward-only Recordset in OLEDBs.

An object of OLEDBDataReader is returned by using ExecuteReader method of OLEDBCommand object.

In the above example first we connected to the database. Remember the database is irrelevant. Any other database will do. I am using MSAccess to simplify the process.

PHONES.MDB database has a table called CITIES, which contains information about all the cites. It has the following structure.

Field	DataType	Meaning
citycode	Text(5)	A unique code to identify city
cityname	Text(50)	Name of the city
stdcode	Text(5)	STD code of the city

The program created an object of OLEDBCommand class. OLEDBCommand class is used to execute a command. The following code is used to open connection and create command object.

        
       dim  constr as string = "provider=Microsoft.jet.oledb.4.0;Data source=c:\srikanth\phones.mdb"

       dim con as New OLEDBConnection(constr)

       Dim cmd As New OLEDBCommand("select * from cities", con)

       Dim dr As OLEDBDataReader
        
     
           con.Open()
 

ExecuteReader method of OLEDBCommand class is used to execute the query and return and object of OLEDBDataReader.

         
    ' execute query and put data into data reader
    dr = cmd.ExecuteReader()

 
        Do While dr.Read()
            console.Writeline(dr.getstring(0) & ":" & dr.getstring(1) & ":" & dr.getstring(2))
        Loop

        ' close data reader first
        dr.Close()
        
        ' close connection
        con.Close()

Properties of OLEDBDataReader class

Property	Meaning
FieldCount	The number of fields in the record. This is a read-only property.
IsClosed	Returns true if data reader is closed otherwise false. Read-only property.
Item	Returns the value of the column as object. It takes the column position as the parameter. Read-only property.

The following sample program is used to display the data in the data reader. It first checks whether the data reader is opened and then reads all rows and sends the data to console.

' import required namespaces

Imports system
Imports System.data
Imports system.data.oledb


Module DataReader1
    
    Public Sub Main()
        
       dim  constr as string = "provider=Microsoft.jet.oledb.4.0;Data source=c:\srikanth\phones.mdb"

       dim con  as New OLEDBConnection(constr)

       Dim cmd As New OLEDBCommand("select * from cities", con)

       Dim dr As OLEDBDataReader
        
        
        
        con.Open()
       
        
        ' execute query and put data into data reader
        dr = cmd.ExecuteReader()
        
 
        If dr.IsClosed Then
            console.WriteLine("Sorry. Data reader is closed")
            Exit Sub
        End If
        
        Dim i As Integer
        
        Do While dr.read()

            For i = 0 To dr.FieldCount - 1
                console.Writeline(dr.item(i).ToString())
            Next
            ' print one extra line
            console.WriteLine(" ")
            
        Loop
        
        ' close data reader first
        dr.Close()
        
        ' close connection
        con.Close()
       
        
    End Sub
    
End Module

 

While loop is repeated as long as Read method returns true. Read method is used to read one row at a time. For loop is set to repeat for each column in the record using FieldCount property. It displays the value of each column. As Item() property returns the value of the specified column as an Object, we converted that to a string using ToString() method.

The following are the methods of OLEDBDataReader class.

This class is used to execute an SQL command on the data source. It needs a connection to the data source and the command to be executed.

Constructors of OLEDBCommand

The following are the various constructors of this class.

New()

Creates a new instance of the class. Takes no parameters.

New(CommandText as String)

Creates a new instance and sets the command to be executed to the given command.

New(CommandText as String, Connection as OLEDBConnection)

The following are the important properties of OLEDBCommand class.

The following are the methods of OLEDBCommand class.

Method	Meaning
ExecuteReader()	Executes the query given in CommandText property and returns a data reader object with the data that is retrieved.
ExecuteNonQuery	Executes a non query and returns the number of rows affected by the command.
ExecuteScalar()	Executes the query given in CommandText and returns the first column of first row. This is very useful with queries that return a single aggregate value.
Prepare	Prepares or compiles the given command. Subsequent executions of the command will be faster.

The following command is used to change the email address of the person P.Srikanth to the new email address. It updates PHONES table that has the following structure.

Field	DataType	Meaning
citycode	Text(5)	City code to which person belongs
person	Text(50)	Name of the person
phoneno	Text(50)	One or more phone numbers of the person. Multiple numbers are separated by comma.
email	Text(20)	Email address of the person

The following sample program demonstrates how we can use OLEDBCommand object to execute a non-query.

' import required namespaces

Imports system
Imports system.data.OLEDB


Module OLEDBCOMExample1
    
    Public Sub Main()
        
        Dim con As New OLEDBConnection("provider=Microsoft.jet.oledb.4.0;data source=c:\srikanth\phones.mdb")

        Dim cmd As New OLEDBCommand("",con)   ' create an object that uses con connection
        
        
        con.Open()
        
        
        cmd.CommandText = "update phones set email = 'srikanthpragada@rediffmail.com' where person = 'P.Srikanth' "
        
        ' execute the command given in CommandText property
        cmd.ExecuteNonQuery()   
        
        console.WriteLine("Updation Successful")

        
        con.Close()
        
    End Sub
    
End Module

Invoking Oracle stored procedure using OLEDBCommand

The following sample program will show you how to execute a stored procedure of Oracle. It uses Parameters collection of OLEDBCommand object to send parameters to Oracle stored procedure. It also uses OLE DB Provider of Oracle .

Before we look at the code to invoke the procedure, let us look at the procedure itself. It is a very simple procedure to change the salary of the employee. It takes two parameters - employee number and salary.

create or replace procedure  ChangeSalary(p_empno number, p_sal number)
is
begin

     update  emp
     set  sal = p_sal
     where empno = p_empno;

     commit;
end;

Now let us see how we can call this procedure from OLEDB.NET.

' import required namespaces

Imports system
Imports system.data
Imports system.data.oledb


Module CallProcedure
    
    Public Sub Main()
        
        Dim con As New OLEDBConnection("Provider=MSDAORA.1;Password=tiger;User ID=scott")
        Dim cmd As New OLEDBCommand()
        
        con.Open()
        
        cmd.Connection = con
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "changesalary"
        
        ' create paramters 
        
        Dim p1 As New OLEDBParameter("p_empno", OLEDBType.Numeric)
        Dim p2 As New OLEDBParameter("p_salary", OLEDBType.Numeric)
        
        ' add parameters to paramters collection
        
        cmd.Parameters.Add(p1)
        cmd.Parameters.Add(p2)
        
        
        'set values to paramters
        
        p1.Value = "7369"
        p2.Value = "6000"
        
        ' execute the procedure 
        
        cmd.ExecuteNonQuery()
        
        con.Close()
        
    End Sub
    
End Module

We first connected to Oracle database using OLE DB provider for Oracle. Then we set the required properties of OLEDBCommand object.

        cmd.connection = con
        cmd.CommandType = Commandtype.StoredProcedure
        cmd.CommandText = "changesalary"

CommandText is set to the name of the stored procedure to be executed and CommandType is set to CommandType.StoredProcedure to indicate that the command is not an SQL command but instead a stored procedure.

As the procedure expects two parameters, we created two parameters and add them to Parameters collection of OLEDBCommand. The type of both the parameters is set to OLEDBType.Numeric.

        ' create paramters 
        
        Dim p1 As New OLEDBParameter("p_empno", OLEDBDBType.Numeric)
        Dim p2 As New OLEDBParameter("p_salary", OLEDBDBType.Numeric)
        
        ' add parameters to parameters collection
        
        cmd.Parameters.Add(p1)
        cmd.Parameters.Add(p2)

Then finally we called the procedure by invoking ExecuteNonQuery method. At this point the stored procedure is called and the updation is done.

NoteSQLCommand is similar to OLEDBCommand. The only difference is; it uses SQL Server .NET Data Provider. Whatever is accomplished using OLEDBCommand that can be accomplished using SQLCommand also.

Note Please see online documentation of OLEDBParamter to get complete list of its properties and methods.

In the next article, we will discuss about DataSet. DataSet is a new addition and provides much of the power of OLEDB.NET.

P.Srikanth.