Home

ODP.NET - Filling a DataTable Using OracleDataReader

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

ODP.NET - Filling a DataTable Using OracleDataReader

So far, we have been filling data tables using OracleDataAdapter. ADO.NET 2.0 gives us the flexibility to fill a data table using OracleDataReader as well. The following code gives you the details of all employees available in the emp table by filling a data table using an OracleDataReader:

Ads

Sample Code

Dim cn As New OracleConnection("Data Source=xe; _
User Id=scott;Password=tiger")
Try
Dim SQL As String
Dim dt As New DataTable
'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
'simply bind datatable to grid
dt.Load(rdr, LoadOption.OverwriteChanges)
Me.DataGridView1.DataSource = dt
Else
'display message if no rows found
MessageBox.Show("Not found")
Me.DataGridView1.Rows.Clear()
End If
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

Once the OracleConnection and OracleDataReader are created, we need to create and fill a DataTable object using OracleDataReader itself. The following is the statement that creates a DataTable object:

Dim dt As New DataTable

Ads

To fill the above DataTable object with respect to OracleDataReader, we can directly use the Load method of DataTable, which accepts a DataReader object and the type of LoadOption. The following statement loads the content of an OracleDataReader into a DataTable object with a LoadOption as OverwriteChanges (overwrites all the modifications that are available as part of the DataTable object):

dt.Load(rdr, LoadOption.OverwriteChanges)

This tutorial is part of a ODP.NET tutorial series. Read it from the beginning and learn yourself.

ODP.NET