Home

ODP.NET - Populating a Dataset with Multiple Data Tables

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

ODP.NET - 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:

Ads

Sample Code

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; _
User Id=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: