alt
Online Training
Career Series
Exforsys
Exforsys arrow Tutorials arrow ODP.NET arrow ODP.NET - Retrieving Multiple Rows on to the Grid
Site Search


ODP.NET - Retrieving Multiple Rows on to the Grid
Article Index
ODP.NET - Retrieving Multiple Rows on to the Grid
Pulling Information Using Table Name
Page 1 of 2

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.

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

  1. Imports Oracle.DataAccess.Client 
  2. Public Class Form2 
  3.    Private Sub btnGetEmployees_Click(ByVal sender As 
  4.       System.Object, ByVal e As System.EventArgs) Handles 
  5.       btnGetEmployees.Click 
  6.       'create connection to db 
  7.       Dim cn As New OracleConnection("Data Source=xe; 
  8.                User Id=scott;Password=tiger") 
  9.       Try 
  10.          Dim SQL As String 
  11.          'build the SELECT statement 
  12.          SQL = String.Format("SELECT empno, ename, job, 
  13.             mgr, hiredate, sal, comm, deptno FROM emp") 
  14.          'create command object to work with SELECT 
  15.          Dim cmd As New OracleCommand(SQL, cn) 
  16.          'open the connection 
  17.          cmd.Connection.Open() 
  18.          'get the DataReader object from command object 
  19.          Dim rdr As OracleDataReader = _ 
  20.          cmd.ExecuteReader(CommandBehavior.CloseConnection) 
  21.          'check if it has any rows 
  22.          If rdr.HasRows Then 
  23.             With Me.DataGridView1 
  24.                'remove existing rows from grid 
  25.                .Rows.Clear() 
  26.                'get the number of columns 
  27.                Dim ColumnCount As Integer = rdr.FieldCount 
  28.                'add columns to the grid 
  29.                For i As Integer = 0 To ColumnCount - 1 
  30.                   .Columns.Add(rdr.GetName(i), rdr.GetName(i)) 
  31.                Next 
  32.                .AutoSizeColumnsMode = 
  33.                DataGridViewAutoSizeColumnsMode.ColumnHeader 
  34.                'loop through every row 
  35.                While rdr.Read 
  36.                   'get all row values into an array 
  37.                   Dim objCells(ColumnCount - 1) As Object 
  38.                   rdr.GetValues(objCells) 
  39.                   'add array as a row to grid 
  40.                   .Rows.Add(objCells) 
  41.                End While 
  42.             End With 
  43.          Else 
  44.             'display message if no rows found 
  45.             MessageBox.Show("Not found") 
  46.             Me.DataGridView1.Rows.Clear() 
  47.          End If 
  48.          'clear up the resources 
  49.          rdr.Close() 
  50.       Catch ex As Exception 
  51.          'display if any error occurs 
  52.          MessageBox.Show("Error: " & ex.Message) 
  53.          'close the connection if it is still open 
  54.          If cn.State = ConnectionState.Open Then 
  55.             cn.Close() 
  56.          End If 
  57.       End Try 
  58.    End Sub 
  59. 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. 

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

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


Prev - Next >>
 
< Prev   Next >
[ Back ]
Exforsys Offers
© 2008 Exforsys.com
Joomla! is Free Software released under the GNU/GPL License.
 Page copy protected against web site content infringement by Copyscape