Working with Data Tables and Data Sets

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:

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.

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

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:

Dim cn As New OracleConnection("Data Source=xe; _
UserId=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)

Retrieving a Single Row of Information Using OracleDataAdapter

In the previous example, we worked with a set of rows in the DataTable object. Now, we shall work with a particular row using the DataTable object. The following code accepts an employee number from the user and gives you the details of that employee:

Imports Oracle.DataAccess.Client

Public Class Form3

Private Sub btnGetEmployee_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnGetEmployee.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
UserId=scott;Password=tiger")
Try
Dim SQL As String
'build the SELECT statement
SQL = String.Format("SELECT ename, sal, job FROM
emp WHERE empno={0}", Me.txtEmpno.Text)
'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
'extract the details
Me.txtEname.Text = dt.Rows(0)("ename")
Me.txtSal.Text = dt.Rows(0)("sal")
Me.txtJob.Text = dt.Rows(0)("job")
Else
'display message if no rows found
MessageBox.Show("Not found")
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 DataTable object is filled using OracleDataAdapter, we can directly retrieve a particular row using the row index. Once the row is fetched, we extract column values by providing column names for the rows as follows:

Me.txtEname.Text = dt.Rows(0)("ename")
Me.txtSal.Text = dt.Rows(0)("sal")
Me.txtJob.Text = dt.Rows(0)("job")

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

Page 1 | Page 2 | page 3 | page 4 | page 5 | page 6 | page 7 | page 8