ODP.NET - Retrieving Multiple Rows on to the Grid

Page 1 of 2

Author: Packt Publishing Published on: 5th Apr 2008 | Last Updated on: 9th Apr 2008

ODP.NET - 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.

Ads

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

Sample Code

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;
User Id=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.

Sample Code