alt
Advertisement

Online Training
Career Series
Exforsys
Exforsys arrow Tutorials arrow ODP.NET arrow ODP.NET - Retrieving Typed Data
Site Search
Sponsored Links



ODP.NET - Retrieving Typed Data
Article Index
ODP.NET - Retrieving Typed Data
Retrieving Multiple Rows
Page 2 of 2

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: 

  1. Imports Oracle.DataAccess.Client 
  2. Public Class Form4 
  3.  
  4.      Private Sub btnGetEmployees_Click(ByVal sender As
  5.      System.Object, ByVal e As System.EventArgs) Handles 
  6.      btnGetEmployees.Click 
  7.           'create connection to db 
  8.           Dim cn As New OracleConnection("Data Source=xe; _ 
  9.                     User Id=scott;Password=tiger") 
  10.           Try 
  11.                Dim SQL As String
  12.                'build the SELECT statement 
  13.                SQL = String.Format("SELECT empno, ename, job, 
  14.                mgr, hiredate, sal, comm, deptno FROM emp") 
  15.                'create the dataadapter object 
  16.                Dim adp As New OracleDataAdapter(SQL, cn) 
  17.                'create the offline datatable 
  18.                Dim dt As New DataTable 
  19.                'fill the data table with rows 
  20.                adp.Fill(dt) 
  21.                'clear up the resources and work offline 
  22.                adp.Dispose() 
  23.                'check if it has any rows 
  24.                If dt.Rows.Count > 0 Then 
  25.                   'simply bind datatable to grid 
  26.                   Me.DataGridView1.DataSource = dt 
  27.                Else 
  28.                   'display message if no rows found 
  29.                   MessageBox.Show("Not found") 
  30.                   Me.DataGridView1.Rows.Clear() 
  31.                End If 
  32.                Catch ex As Exception 
  33.                'display if any error occurs 
  34.                MessageBox.Show("Error: " & ex.Message) 
  35.                'close the connection if it is still open 
  36.                If cn.State = ConnectionState.Open Then 
  37.                   cn.Close() 
  38.                End If 
  39.           End Try 
  40.      End Sub 
  41. 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: 

  1. If dt.Rows.Count > 0 Then 
  2.    'simply bind datatable to grid 
  3.    Me.DataGridView1.DataSource = dt 
  4. Else 
  5.    'display message if no rows found 
  6.    MessageBox.Show("Not found") 
  7.    Me.DataGridView1.Rows.Clear() 
  8. 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.

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

Tags

See All Tags

Trackback(0)
Comments (0)add comment

Write comment

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