ODP.NET - Filling a DataTable Using OracleDataReader

Category: ODP.NET
Comments (0)

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:

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

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)

Sponsored Links

ODP.NET Tutorials

ODP.NET - Filling a DataTable Using OracleDataReader

ODP.NET - Filling a DataTable Using OracleDataReader

Comments

Post Your Comment:

Sponsored Links