Most commercial applications use data in some way or the other. Many times, related data from two or more tables has to be used in these applications. We can define relations between two tables easily by using a DataSet.
Let's create a Master / Detail relation application using the Northwind Database that comes with the SQL Server. We will use the Customers and Orders tables for this example. The Customers table contains information about the customers and the Order table contains information about orders. Orders are placed by customers and hence each order must be related to a customer.
In our example, we will show the customers in one DataGrid and orders placed by a selected customer in the second DataGrid.
Steps to create a Master/Detail Application
1. Start a new Windows application.
2. Place 2 DataGrids and a command button on the form.
3. Configure 2 DataAdapters for the Customers table and Orders table respectively with the help of the DataAdapter Configuration wizard. If you want to try out something different, use the Server Explorer instead.
- If the Server explorer is not open, open it from View menu.
- Expand the servers.
- Expand appropriate SQL server instance.
- Expand Northwind Database.
- Select Customers table and drag it on the form.
- Select Orders table and drag it on the form.
This will create the two required DataAdapters.
5. Generate a DataSet named DS11 for both the adapters:
- Select first adapter and click on Generate DataSet.
- Call the DataSet DS11.
- Select Customers table from the wizard.
- Select second Adapter and click on Generate DataSet.
- Select the same DataSet DS11.
- Select the Orders table.
6. Write following code in the click event of the Load Button:
[code="xml"]
Private Sub BtnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Fill Data Sets
Me.SqlDataAdapter1.Fill(DS11, "Customers")
Me.SqlDataAdapter2.Fill(DS11, "Orders")
Me.SqlDataAdapter3.Fill(DS11, "Order Details")
DS11.Relations.Add("CustOrders",_
DS11.Tables("Customers").Columns("CustomerID"),_
DS11.Tables("Orders").Columns("CustomerID"))
DataGrid1.DataSource = DS11
DataGrid2.DataSource = DS11
DataGrid1.SetDataBinding(DS11, "Customers")
DataGrid2.SetDataBinding(DS11,"Customers.CustOrders")
End Sub |
[/code]
The application is now ready to run. Click the Load button. Click any Customer from the first grid and you will notice the second grid displaying records only for the selected Customer.
