Master-Detail DataGrids in ASP.Net...

Date: January 17, 2003

In this article you will learn how to link two datagrids in a master-detail relationship. There is more than one way to accomplish this task. In this article we will implement row filtering to find the detail records corresponding to a master record selected in the master grid. The tables we will use are the Orders and Order Details tables from the Northwind database. We include an aspx file to setup our two datagrids, and a code-behind file to implement the database selection, grid binding, and row filtering.

First the aspx file. It is fairly straight forward, although we are setting a number of properties of the master grid and using templates to control the appearance of the grid. The key points to notice in the master grid are that we have implemented the OnSelectedIndexChanged method to raise the SelectedIndexChanged event. This event, not surprisingly, is raised when an item in the datagrid is selected. We have named our event handler "dtgMaster_Select". This event will be seen in the code-behind file. We have also implemented a button column with text of "Show Details" so that we can select a particular row of the datagrid. We have also set the DataKeyField property to "OrderID". The is the key common to both tables and will be accessible to the details datagrid upon selection of a row in the master datagrid. The details datagrid contains only cosmetic property settings and templates.

<%@ Page Language="vb" src="MasterDetailGrids.aspx.vb" Inherits="MasterDetail" %>
<html>
<head>
<title>Master-Detail DataGrids</title>
</HEAD>
<body>
<h2>Master-Detail DataGrids</h2>
<form runat="server" ID="Form1">
<h4>Orders Table</h4>
<asp:datagrid id="dtgMaster" runat="server"
              width="80%" CellSpacing="2"
              GridLines="Both" CellPadding="4"
              BackColor="White" ForeColor="Black"
              OnPageIndexChanged="dtgMaster_Page"
              AllowPaging="true" PageSize="5"
              OnSelectedIndexChanged="dtgMaster_Select"
              DataKeyField="OrderID">
  <HeaderStyle font-bold="True" forecolor="White" backcolor="#4A3C8C"></HeaderStyle>
  <PagerStyle horizontalalign="Center" backcolor="#C6C3C6" _
    mode="NumericPages"></PagerStyle>
  <SelectedItemStyle forecolor="White" backcolor="#9471DE"></SelectedItemStyle>
  <ItemStyle backcolor="#DEDFDE"></ItemStyle>
  <Columns>
    <asp:ButtonColumn Text="Show details" CommandName="Select">
      <ItemStyle font-size="Smaller" font-bold="True"></ItemStyle>
    </asp:ButtonColumn>
  </Columns>
</asp:datagrid>
<p>
<h4>Order Details Table</h4>
<asp:datagrid id="dtgDetails" runat="server"
              width="80%" CellSpacing="1"
              GridLines="Both" CellPadding="3"
              BackColor="White" ForeColor="Black">
  <HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle>
  <ItemStyle backcolor="#DEDFDE"></ItemStyle>
</asp:datagrid>
</p>
</form>
</body>
</html>

Now for the code-behind file where all the work gets done. In the page load event we set the master datagrid's SelectedIndex property to 0 (first row in the grid) and then call the binding sub-routines for the two grids. Next we see the "dtgMster_Select" event handler we mentioned above when discussing the aspx file master datagrid layout. You might have expected to see several lines of code here, but actually all it does is call the bind routine for the details datagrid. Next is the sub-routine to handle paging in the master grid. After that comes the master datagrid's databinding routine. Here we connect to the database, execute our SQL Select statement, fill a dataset and do a databind on the master grid. Nothing unusual there.

Last is the BindDetails() routine where the real work is done to get the details datagrid in sync with the master datagrid. Again we connect to the database. Notice the SQL statement however. It includes a WHERE clause setting OrderID to a particular value. In the line above the select statement appears:
Dim strFilter As String = CStr(dtgMaster.DataKeys(dtgMaster.SelectedIndex)).Replace("'", "''")
Remember that, in the aspx file, we set the master datagrid's DataKeyField property to "OrderID"? This is where it gets used. The line above is saying "get the DataKeyField (OrderID) for the master grid row that was selected and store it in the variable strFilter". The Replace method is used just in case our key field was string rather than numeric. So we now have a WHERE clause that instructs the sql to fetch rows from the Order Details table where the OrderID matches the OrderID in the row selected from the master datagrid. We then get the data and we are done!

Imports System
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient

Public Class MasterDetail : Inherits Page

  Protected WithEvents dtgMaster As DataGrid
  Protected WithEvents dtgDetails As DataGrid

  Private Sub Page_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    If Not Page.IsPostBack Then
      dtgMaster.SelectedIndex = 0
      BindMaster()
      BindDetails()
    End If
  End Sub

  Sub dtgMaster_Select(Sender As Object, E As EventArgs)
    BindDetails()
  End Sub

  Sub dtgMaster_Page(Sender As Object, E As DataGridPageChangedEventArgs)
    If dtgMaster.SelectedIndex <> -1 Then
      'undo the selection
      dtgMaster.SelectedIndex = -1
      BindDetails()
    End If

    dtgMaster.CurrentPageIndex = e.NewPageIndex
    BindMaster()
  End Sub

  Sub BindMaster()
    Dim strConnection As String = "server=localhost;database=northwind;uid=sa;pwd=admin;"
    Dim strQuery As String = "Select Top 10 OrderID, OrderDate, CustomerID From Orders"
    Dim objConn As New SqlConnection(strConnection)
    Dim dataAdapter As New SqlDataAdapter(strQuery, objConn)
    Dim ds As New DataSet()

    dataAdapter.Fill(ds)
    dtgMaster.DataSource = ds
    dtgMaster.DataBind()
  End Sub

  Sub BindDetails()
    ' get the filter value from the master Grid's DataKeys collection
    If dtgMaster.SelectedIndex <> -1 Then
      Dim strConnection As String = _
        "server=localhost;database=northwind;uid=sa;pwd=admin;"
      Dim strFilter As String = _
        CStr(dtgMaster.DataKeys(dtgMaster.SelectedIndex)).Replace("'", "''")
      Dim strQuery As String = "select OrderID, ProductID, Quantity, UnitPrice " & _
                              "From [Order Details] Where OrderID = '" & strFilter & "'"

      Dim objConn As New SqlConnection(strConnection)
      Dim dataAdapter As New SqlCommand(strQuery, objConn)

      objConn.Open()
      dtgDetails.DataSource = dataAdapter.ExecuteReader(CommandBehavior.CloseConnection)
    End If
    dtgDetails.DataBind()
  End Sub

End Class

Conclusion: In this artice you have seen how to relate a details datagrid to a master datagrid and return rows in the details grid based upon user selection of a row in the master grid.

You may download the code here.