ODP.NET - Populating a Dataset with a Single Data Table
A dataset is simply a group of data tables. These data tables can be identified with their own unique names within a dataset. You can also add relations between data tables available in a dataset.
The following code gives you the details of all employees available in the emp table by populating a dataset with only a single data table using OracleDataAdapter:
Sample Code
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; _
User Id=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
Copyright exforsys.com
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.