Retrieving Multiple Rows

Home

Reviews

ODP.NET

Table of Contents

ODP.NET - Retrieving Typed Data

Retrieving Multiple Rows

Page 2 of 2

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

ODP.NET - Retrieving Typed Data

Retrieving Multiple Rows into a DataTable Using OracleDataAdapter

Now that we understand about OracleDataAdapter, let us try to use it to retrieve all the employees available in the emp table:

Ads

Sample Code

Imports Oracle.DataAccess.Client
Public Class Form4
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 the dataadapter object
Dim adp As New OracleDataAdapter(SQL, cn)
'create the offline datatable
Dim dt As New DataTable
'fill the data table with rows
adp.Fill(dt)
'clear up the resources and work offline
adp.Dispose()
'check if it has any rows
If dt.Rows.Count > 0 Then
'simply bind datatable to grid
Me.DataGridView1.DataSource = dt
Else
'display message if no rows found
MessageBox.Show("Not found")
Me.DataGridView1.Rows.Clear()
End If
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

Once the OracleConnection is established, we need to start with the OracleDataAdapter object as follows:

SQL = String.Format("SELECT empno, ename, job,
mgr, hiredate, sal, comm, deptno FROM emp")
Dim adp As New OracleDataAdapter(SQL, cn)

You can understand from the above that OracleDataAdapter can be used directly with a SELECT statement. You can also specify an OracleCommand object in place of a SELECT statement if necessary.

To place data offline, we need to either work with DataSet or DataTable objects. In this scenario, we will deal with a DataTable object, and it is created as follows:

Dim dt As New DataTable

Once the DataTable object is created, we need to fill up all the rows using the OracleDataAdapter object as follows:

adp.Fill(dt)

Once all the rows are available in the DataTable object (which will always be in memory), we can close (dispose) the OracleDataAdapter using the following statement:

adp.Dispose()

The DataTable object contains a collection of DataRow objects corresponding to each row populated into it. We can retrieve the number of rows available in the DataTable object using the DataTable.Rows.Count property as follows:

Sample Code

If dt.Rows.Count > 0 Then
'simply bind datatable to grid
Me.DataGridView1.DataSource = dt
Else
'display message if no rows found
MessageBox.Show("Not found")
Me.DataGridView1.Rows.Clear()
End If

In the above code fragment, we are assigning the DataTable object as DataSource to DataGridView. This would automatically populate entire DataGridView with all the column names (as part of the header) and all rows.

Ads

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