alt
Advertisement

Online Training
Career Series
Exforsys
Exforsys arrow Tutorials arrow ODP.NET arrow ODP.NET - Presenting Master-Detail Information Using a Dataset
Site Search
Sponsored Links



ODP.NET - Presenting Master-Detail Information Using a Dataset

ODP.NET - Presenting Master-Detail Information Using a Dataset

As mentioned before, a DataSet object can have its own relations between data tables existing in it. We can add these relations dynamically at the client side (within an application), to represent master-detail (or hierarchical) information. The following code gives the list of employees (in the bottom grid) based on the department you choose in the top grid:

  1. Imports Oracle.DataAccess.Client
  2. Public Class Form8
  3.  
  4. Private Sub btnData_Click(ByVal sender As
  5. System.Object, ByVal e As System.EventArgs) Handles
  6. btnData.Click
  7. 'create connection to db
  8. Dim cn As New OracleConnection("Data Source=xe; _
  9. User Id=scott;Password=tiger")
  10. Try
  11. Dim ds As New DataSet
  12. Dim adp As OracleDataAdapter
  13.  
  14. adp = New OracleDataAdapter("SELECT deptno,
  15. dname, loc FROM Dept", cn)
  16. adp.Fill(ds, "Departments")
  17. adp.Dispose()
  18.  
  19. adp = New OracleDataAdapter("SELECT empno, ename,
  20. job, mgr, hiredate, sal, comm, deptno FROM
  21. Emp", cn)
  22. adp.Fill(ds, "Employees")
  23. adp.Dispose()
  24.  
  25. ds.Relations.Add(New DataRelation("FK_Emp_Dept",
  26. ds.Tables("Departments").Columns("Deptno"),
  27. ds.Tables("Employees").Columns("Deptno")))
  28. Dim bsMaster As New BindingSource(ds, _
  29. "Departments")
  30. Dim bsChild As New BindingSource(bsMaster, _
  31. "FK_Emp_Dept")
  32. Me.DataGridView1.DataSource = bsMaster
  33. Me.DataGridView2.DataSource = bsChild
  34.  
  35. Catch ex As Exception
  36. 'display if any error occurs
  37. MessageBox.Show("Error: " & ex.Message)
  38. 'close the connection if it is still open
  39. If cn.State = ConnectionState.Open Then
  40. cn.Close()
  41. End If
  42. End Try
  43. End Sub
  44. End Class
 

Once the DataSet is filled with data tables (Departments and Employees), we can add an in-memory relation using the following statement:

ds.Relations.Add(New DataRelation("FK_Emp_Dept",
ds.Tables("Departments").Columns("Deptno"),
ds.Tables("Employees").Columns("Deptno")))

The above statement simply adds a new relation (named FK_Emp_Dept) between two DataTable objects (Departments and Employees) based on the column Deptno (available in both DataTable objects).

To present the information in a master-detail fashion, we can make use of the BindingSource object as follows:

Dim bsMaster As New BindingSource(ds, "Departments")
Dim bsChild As New BindingSource(bsMaster, "FK_Emp_Dept")

In the above code fragment, we used two BindingSource objects corresponding to master and child data tables respectively. The child BindingSource object is created based on the master BindingSource object together with the specification of DataRelation. Once the BindingSource objects are ready, we can assign them as data sources to the DataGridView controls as following:

Me.DataGridView1.DataSource = bsMaster
Me.DataGridView2.DataSource = bsChild

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

You can observe that this screen displays only the employees working in department number 20 as that is selected in the top grid.


Trackback(0)
Comments (0)add comment

Write comment

busy
 
< Prev   Next >
Sponsored Links
© 2008 Exforsys.com
Joomla! is Free Software released under the GNU/GPL License.
Page copy protected against web site content infringement by Copyscape