ODP.NET - Retrieving Data Using OracleDataReader

OracleDataReader is simply a read-only and forward-only result set. It works only if the database connection is open and it makes sure that the connection is open while you are retrieving data. As the data that it retrieves is read-only, it is a bit faster than any other method to retrieve data from Oracle.

ODP.NET - Retrieving Data Using OracleDataReader

OracleDataReader is simply a read-only and forward-only result set. It works only if the database connection is open and it makes sure that the connection is open while you are retrieving data. As the data that it retrieves is read-only, it is a bit faster than any other method to retrieve data from Oracle.

You need to work with OracleCommand together with OracleConnection to get access to OracleDataReader. There is an ExecuteReader method in the OracleCommand class, which gives you the OracleDataReader.

Retrieving a Single Row of Information

Let us start by retrieving a single row from Oracle database using ODP.NET and populate the data into few textboxes on a WinForm.

To connect to and work with Oracle database, we need to start with OracleConnection. Once a connection to the database is established, we need to issue a SELECT statement to retrieve some information from the database. A query (or any SQL command) can be executed with the help of an OracleCommand object. Once the SELECT statement gets executed, we can use OracleDataReader to retrieve the information.

The following code accepts an employee number from the user and gives you the details of that employee:

Imports Oracle.DataAccess.Client
 
Public Class Form1
 
 
 Private Sub btnGetEmployee_Click(ByVal sender As
   System.Object, ByVal e As System.EventArgs) Handles
   btnGetEmployee.Click
   'create connection to db
   Dim cn As New OracleConnection("Data Source=xe; _
                       User Id=scott;Password=tiger")
   Try
   Dim SQL As String
   'build the SELECT statement
   SQL = String.Format("SELECT ename, sal, job FROM
       emp WHERE empno={0}", Me.txtEmpno.Text)
   'create command object to work with SELECT
   Dim cmd As New OracleCommand(SQL, cn)
   'open the connection
   cmd.Connection.Open()
   'get the DataReader object from command object
   Dim rdr As OracleDataReader = _
   cmd.ExecuteReader(CommandBehavior.CloseConnection)
   'check if it has any rows
   If rdr.HasRows Then
      'read the first row
      rdr.Read()
      'extract the details
      Me.txtEname.Text = rdr("ename")
      Me.txtSal.Text = rdr("sal")
      Me.txtJob.Text = rdr("job")
   Else
      'display message if no rows found
      MessageBox.Show("Not found")
   End If
   'clear up the resources
   rdr.Close()
 Catch ex As Exception
    'display if any error occurs
    MessageBox.Show("Error: " & ex.Message)
    'close the connection if it is still open
    If cn.State = ConnectionState.Open Then
       cn.Close()
    End If
  End Try
 End Sub
End Class 
   
Imports%20Oracle.DataAccess.Client%0A%20%0APublic%20Class%20Form1%0A%20%0A%0D%0A%20Private%20Sub%20btnGetEmployee_Click%28ByVal%20sender%20As%0D%0A%20%20%20System.Object%2C%20ByVal%20e%20As%20System.EventArgs%29%20Handles%0D%0A%20%20%20btnGetEmployee.Click%0D%0A%20%20%20%27create%20connection%20to%20db%0D%0A%20%20%20Dim%20cn%20As%20New%20OracleConnection%28%22Data%20Source%3Dxe%3B%20_%0D%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20User%20Id%3Dscott%3BPassword%3Dtiger%22%29%0D%0A%20%20%20Try%0D%0A%26nbsp%3B%20%26nbsp%3BDim%20SQL%20As%20String%0D%0A%26nbsp%3B%20%26nbsp%3B%27build%20the%20SELECT%20statement%0D%0A%26nbsp%3B%20%26nbsp%3BSQL%20%3D%20String.Format%28%22SELECT%20ename%2C%20sal%2C%20job%20FROM%0D%0A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bemp%20WHERE%20empno%3D%7B0%7D%22%2C%20Me.txtEmpno.Text%29%0D%0A%26nbsp%3B%20%26nbsp%3B%27create%20command%20object%20to%20work%20with%20SELECT%0D%0A%26nbsp%3B%20%26nbsp%3BDim%20cmd%20As%20New%20OracleCommand%28SQL%2C%20cn%29%0D%0A%26nbsp%3B%20%26nbsp%3B%27open%20the%20connection%0D%0A%26nbsp%3B%20%26nbsp%3Bcmd.Connection.Open%28%29%0D%0A%26nbsp%3B%20%26nbsp%3B%27get%20the%20DataReader%20object%20from%20command%20object%0D%0A%26nbsp%3B%20%26nbsp%3BDim%20rdr%20As%20OracleDataReader%20%3D%20_%0D%0A%26nbsp%3B%20%26nbsp%3Bcmd.ExecuteReader%28CommandBehavior.CloseConnection%29%0D%0A%26nbsp%3B%20%26nbsp%3B%27check%20if%20it%20has%20any%20rows%0D%0A%26nbsp%3B%20%26nbsp%3BIf%20rdr.HasRows%20Then%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20%27read%20the%20first%20row%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20rdr.Read%28%29%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20%27extract%20the%20details%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20Me.txtEname.Text%20%3D%20rdr%28%22ename%22%29%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20Me.txtSal.Text%20%3D%20rdr%28%22sal%22%29%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20Me.txtJob.Text%20%3D%20rdr%28%22job%22%29%0D%0A%26nbsp%3B%20%26nbsp%3BElse%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20%27display%20message%20if%20no%20rows%20found%0D%0A%26nbsp%3B%20%26nbsp%3B%20%20%20MessageBox.Show%28%22Not%20found%22%29%0D%0A%26nbsp%3B%20%26nbsp%3BEnd%20If%0D%0A%26nbsp%3B%20%26nbsp%3B%27clear%20up%20the%20resources%0D%0A%26nbsp%3B%20%26nbsp%3Brdr.Close%28%29%0D%0A%20Catch%20ex%20As%20Exception%0D%0A%20%20%20%20%27display%20if%20any%20error%20occurs%0D%0A%20%20%20%20MessageBox.Show%28%22Error%3A%20%22%20%26amp%3B%20ex.Message%29%0D%0A%20%20%20%20%27close%20the%20connection%20if%20it%20is%20still%20open%0D%0A%20%20%20%20If%20cn.State%20%3D%20ConnectionState.Open%20Then%0D%0A%20%20%20%20%20%20%20cn.Close%28%29%0D%0A%20%20%20%20End%20If%0D%0A%20%20End%20Try%0D%0A%20End%20Sub%0D%0AEnd%20Class

As explained earlier, the above program creates an OracleConnection object as follows:

Dim cn As New OracleConnection("Data Source=xe; _
User Id=scott;Password=tiger")

Next, we need to create an OracleCommand object by providing a SELECT query and the connection object (through which it can connect to the database):

Dim SQL As String
SQL = String.Format("SELECT ename, sal, job FROM
emp WHERE empno={0}", Me.txtEmpno.Text)
Dim cmd As New OracleCommand(SQL, cn)

Once the OracleCommand object is created, it is time to open the connection and execute the SELECT query. The following does this:

cmd.Connection.Open()
Dim rdr As OracleDataReader = _
cmd.ExecuteReader(CommandBehavior.CloseConnection)

You must observe that the query gets executed using the ExecuteReader method of OracleCommand object, which in turn returns an OracleDataReader object. In the above statement, the ExecuteReader method is specified with CommandBehavior. CloseConnection, which simply closes the database connection once the OracleDataReader and OracleCommand are disposed.

We can use the HasRows property of OracleDataReader to test whether the reader retrieved any rows or not. If any rows are retrieved, we can read each successive row using the Read method of OracleDataReader. The Read method returns a Boolean value to indicate whether it has successfully read a row or not. Once the Read succeeds, we can retrieve each value in the row with the column name as follows:

If rdr.HasRows Then 
   'read the first row 
   rdr.Read() 
   'extract the details 
   Me.txtEname.Text = rdr("ename") 
   Me.txtSal.Text = rdr("sal") 
   Me.txtJob.Text = rdr("job") 
Else 
   'display message if no rows found 
   MessageBox.Show("Not found") 
End If 
   
If%20rdr.HasRows%20Then%20%0A%20%20%20%27read%20the%20first%20row%20%0A%20%20%20rdr.Read%28%29%20%0A%20%20%20%27extract%20the%20details%20%0A%20%20%20Me.txtEname.Text%20%3D%20rdr%28%22ename%22%29%20%0A%20%20%20Me.txtSal.Text%20%3D%20rdr%28%22sal%22%29%20%0A%20%20%20Me.txtJob.Text%20%3D%20rdr%28%22job%22%29%20%0AElse%20%0A%20%20%20%27display%20message%20if%20no%20rows%20found%20%0A%20%20%20MessageBox.Show%28%22Not%20found%22%29%20%0AEnd%20If
 

Finally, we close the OracleDataReader object using the Close method as follows:

rdr.Close()

If it could read successfully, the output for this code would look similar to the following figure:

Prev - Next >>