Working with DataTableReader

Imports Oracle.DataAccess.Client
Public Class Form6

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; _
UserId=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 ds As New DataSet
'fill the data set with a data table named emp
adp.Fill(ds, "emp")
'clear up the resources and work offline
adp.Dispose()
'check if it has any rows
If ds.Tables("emp").Rows.Count > 0 Then
'simply bind datatable to grid
Me.DataGridView1.DataSource = ds.Tables("emp")
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

If you can observe the highlighted code in the above script, we are creating a new DataSet object, populating it with a DataTable named "emp" (which contains all the rows) and finally assigning the same DataTable to the grid. The output for the above code would look similar to the figure in the section Retrieving Multiple Rows into a Data Table Using OracleDataAdapter.

Populating a Dataset with Multiple Data Tables

Now, let us add more than one data table into a dataset. The following code retrieves . a list of department details into a data table named Departments and another list of employee details into a data table named Employees:

Imports Oracle.DataAccess.Client
Public Class Form7
Private Sub btnData_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnData.Click
'create connection to db
Dim cn As New OracleConnection("Data Source=xe; _
UserId=scott;Password=tiger")
Try
Dim ds As New DataSet
Dim adp As OracleDataAdapter
adp = New OracleDataAdapter("SELECT deptno,
dname, loc FROM Dept", cn)
adp.Fill(ds, "Departments")
adp.Dispose()
adp = New OracleDataAdapter("SELECT empno, ename,
job, mgr, hiredate, sal, comm, deptno FROM
Emp", cn)
adp.Fill(ds, "Employees")
adp.Dispose()
Me.DataGridView1.DataSource = ds
Me.DataGridView1.DataMember = "Departments"
Me.DataGridView2.DataSource =
ds.Tables("Employees")
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

From the above highlighted code, you can easily observe that we are retrieving two different result sets (identified by Departments and Employees) into the same dataset. The following code fragment creates the Departments data table:

adp = New OracleDataAdapter("SELECT deptno, dname,
loc FROM Dept", cn)
adp.Fill(ds, "Departments")
adp.Dispose()

The following code fragment creates the Employees data table:

adp = New OracleDataAdapter("SELECT empno, ename, job,
mgr, hiredate, sal, comm, deptno FROM Emp", cn)
adp.Fill(ds, "Employees")
adp.Dispose()

Those two result sets are automatically created as two data tables within the same dataset. Once the dataset is populated, we can present them with two different grids (two different methods) as follows:

Me.DataGridView1.DataSource = ds
Me.DataGridView1.DataMember = "Departments"
Me.DataGridView2.DataSource = ds.Tables("Employees")

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

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