Retrieving Data Using OracleDataReader

'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:

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

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:

Using "Using" for Simplicity

The above program can be made simple by using the Using statement together with ODP.NET classes as follows:

Using cn As New OracleConnection("Data Source=xe;
UserId=scott;Password=tiger")
Try
cn.Open()
Dim SQL As String
SQL = String.Format("SELECT ename, sal,
job FROM emp WHERE empno={0}", Me.txtEmpno.Text)
Using cmd As New OracleCommand(SQL, cn)
Using rdr As OracleDataReader = cmd.ExecuteReader
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
End Using
End Using
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message)
If cn.State = ConnectionState.Open Then
cn.Close()
End If
End Try
End Using

The Using keyword is new in Visual Basic 2005, which internally generates try and finally blocks around the object being allocated and calls Dispose() for you saving you the hassle of manually creating it.

The objects created using the Using keyword are automatically erased (and respective resources would be automatically cleared) from the memory once it is out of using scope. Even though it is very flexible to use the Using statement, for the sake of clarity, we will go without using it in the examples of this book.

Retrieving Multiple Rows on to the Grid

In the previous section, we tried to retrieve only one row using OracleDataReader. In this section, we will try to retrieve more than one row (or a result set) and populate a DataGridView on a WinForm.

The following code lists out the details of all employees available in the emp table:

Imports Oracle.DataAccess.Client

Public Class Form2
Private Sub btnGetEmployees_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnGetEmployees.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe;
UserId=scott;Password=tiger")
Try
Dim SQL As String
'build the SELECT statement
SQL = String.Format("SELECT empno, ename, job,
mgr, hiredate, sal, comm, deptno FROM emp")
'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
With Me.DataGridView1
'remove existing rows from grid
.Rows.Clear()
'get the number of columns
Dim ColumnCount As Integer = rdr.FieldCount
'add columns to the grid
For i As Integer = 0 To ColumnCount - 1
.Columns.Add(rdr.GetName(i), rdr.GetName(i))
Next
.AutoSizeColumnsMode =
DataGridViewAutoSizeColumnsMode.ColumnHeader
'loop through every row
While rdr.Read
'get all row values into an array
Dim objCells(ColumnCount - 1) As Object
rdr.GetValues(objCells)
'add array as a row to grid
.Rows.Add(objCells)
End While
End With
Else
'display message if no rows found
MessageBox.Show("Not found")
Me.DataGridView1.Rows.Clear()
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

Except the highlighted section, the rest of the code is already explained as part of the previous section. You can observe that the SELECT statement now tries to retrieve all rows from emp as follows:

SQL = String.Format("SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp")

Once the OracleDataReader is ready with rows, we need to start with clearing the rows already displayed in the DataGridView with the help of the following code:

With Me.DataGridView1
'remove existing rows from grid
.Rows.Clear()

Once the rows are cleared, the first issue is the header of the grid. The moment we add columns to the grid, the header row gets automatically populated (with the column names). Before adding columns to the header, we should know the number of columns being added (just for the loop iterations) with the FieldCount property of DataGridView. The following is the code fragment that finds the number of columns and adds the columns to DataGridView:

Dim ColumnCount As Integer = rdr.FieldCount
For i As Integer = 0 To ColumnCount - 1
.Columns.Add(rdr.GetName(i), rdr.GetName(i))
Next

All the columns get auto-sized based on the column header with the following statement:

.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader

Once the columns are added, we need to read every successive row from the OracleDataReader and add it to the DataGridview. To add all column values at a time, we make use of the GetValues() method of OracleDataReader to push all the values in to an array and finally add the array itself as a row to the DataGridView. The following code fragment accomplishes this.

While rdr.Read
'get all row values into an array
Dim objCells(ColumnCount - 1) As Object
rdr.GetValues(objCells)
'add array as a row to grid
.Rows.Add(objCells)
End While

The output for this code would look similar to the following figure:

Pulling Information Using Table Name

In all of the previous examples, the SELECT statement was used to retrieve a set of rows. The SELECT statement is a good choice if you would like to retrieve only specific columns or to include some complex combinations using sub-queries, joins etc. You can also retrieve a complete table (without using a SELECT statement) by setting the CommandType of OracleCommand to TableDirect. The following code demonstrates the use of TableDirect:

Imports Oracle.DataAccess.Client

Public Class Form2
Private Sub btnGetEmployees_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnGetEmployees.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
UserId=scott;Password=tiger")
Try
Dim SQL As String
'build the SELECT statement

Dim cmd As New OracleCommand("emp", cn)
cmd.CommandType = CommandType.TableDirect
cmd.Connection.Open()
...
End Sub
End Class

The default CommandType is Text, which accepts any SQL statement. When we change it to TableDirect, it accepts only a table name. Another command type available is StoredProcedure. It is mainly used when you want to execute stored procedures using an OracleCommand object. (Working with PL/SQL stored procedures is covered in Chapter 5.)

Retrieving Typed Data

While retrieving values from OracleDataReader, we can extract information available in individual columns (of a particular row) either by using column ordinal (position) values or column names.

Retrieving Typed Data Using Ordinals

ODP.NET provides data-specific enumerations through the namespace oracle. DataAccess.types. This is specially useful if you are trying to retrieve very specific data from the OracleDataReader.

For example, you can modify the code given previously to work with specific data types as following:

Me.txtEname.Text = rdr.GetOracleString(1)
Me.txtSal.Text = rdr.GetFloat(5)
Me.txtJob.Text = rdr.GetOracleString(2)

Here we provide ordinal values (column numbers starting from 0) to retrieve the data in a specific column. Apart from above data types, you also have the full support of every native data type existing in ODP.NET!

Retrieving Typed Data Using Column Names

The strategy of working with column ordinals will not be an issue as long as we know with what columns we are dealing with. But, sometimes, it is very dangerous to play with it. If the underlying table structure gets modified, our application becomes out of synch with the column ordinals. At the same time, using column ordinals can make your code very difficult to follow. It is always suggested not to go for column ordinals (unless we use it for looping purposes).

However, the typed methods only accept column ordinals as parameters.
Fortunately, we can use the GetOrdinal() method to find the ordinal corresponding to a particular column name as demonstrated in the following:

Me.txtEname.Text =
rdr.GetOracleString(rdr.GetOrdinal("ename"))
Me.txtSal.Text = rdr.GetFloat(rdr.GetOrdinal("sal"))
Me.txtJob.Text =
rdr.GetOracleString(rdr.GetOrdinal("job"))

Page 1 | Page 2 | page 3 | page 4 | page 5 | page 6 | page 7 | page 8