ODP.NET - Retrieving Data Using OracleDataReader

Category: ODP.NET
Comments (1)

Using keyword in Visual Basic 2005

ODP.NET - Retrieving Data Using OracleDataReader

Page 1 of 2

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

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:

Sample Code

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

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:

Sample Code