Displaying Master-Detail relationship using ListView, Nested Repeater and JQuery

Posted on November 4, 2011 by simpledotnetsolutions

The following example displays master-detail data similar to MS-Access. It also shows the sum total of Price Column at the end of each Products section. Attached screenshots shows various views, when the application is run.

View1        View2      View3

I used the Categories & products table from sample Northwind DB. A given category can have multiple products which is the basis of this master-detail.The 2 tables are joined by CategoryID. I used ListView for the Outer Category table & the light-weight Repeater as the nested one which shows the product details for a given Category.JQuery helps in hide/Show of each detail section. Rest is all self-explainatory. Here’s the complete code.

//aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ListViewGrouping2.aspx.cs" Inherits="ListViewGrouping2" %>
<%@ Import Namespace="System.Data" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.5.js" type="text/javascript"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $('.expandcolapse').click(function () {
            var image = $(this)[0];
            if ($(image).attr("src") == "./images/plus.png") {
                $(image).attr("src", "./images/minus.png");
                $(this).closest('table').next('table').find('tbody').find('tr').removeClass('hideDetails');
            }
            else {
                $(image).attr("src", "./images/plus.png");
                $(this).closest('table').next('table').find('tbody').find('tr').addClass('hideDetails');
            }
        });
    });
</script>

<style type="text/css">
.hideDetails
{
    display:none;
}
.RowOdd
{
    color:Green;
}
.RowEven
{
    color:Olive;
}
.RowHeader
{
    color:Red;
}
.RepeaterBkdg
{
    color:Orange;
}
</style>
</head>

<body>
<form id="form1" runat="server">
<div>
<asp:ListView runat="server" ID="lv1" onitemdatabound="lv1_ItemDataBound">
<LayoutTemplate>
    <b><u><span class="RowHeader">Categories</span></u></b>
    <table >
        <tr class="RowHeader">
        <td style="width:40px;"></td>
        <td style="width:40px;">ID</td>
        <td style="width:150px;">Category Name</td>
        <td style="width:450px;">Description</td>
        </tr>
    </table>
    <tr id="itemPlaceholder" runat="server"></tr>
</LayoutTemplate>
        
<ItemTemplate>
<table>
<tr class='<%# Convert.ToBoolean(Container.DataItemIndex % 2) ? "RowOdd" : "RowEven" %>' >
    <td style="width:40px;"><IMG id="<%#Eval("CategoryID")%>img1" class="expandcolapse" src="./images/plus.png"></td>
    <td style="width:40px;"><%#Eval("CategoryID", "{0:d}")%></td>
    <td style="width:150px;" ><%# Eval("CategoryName")%></td>
    <td style="width:450px;"><%# Eval("Description")%></td>
</tr>
</table>

<asp:Repeater ID="rptProducts" OnItemDataBound="rptProducts_ItemDataBound" OnItemCreated="rptProducts_ItemCreated" 
runat="server" DataSource='<%# ((DataRowView)Container.DataItem).Row.GetChildRows("myrelation") %>'>
    <HeaderTemplate>        
    <table class="RepeaterBkdg" style="padding-left:50px;">
    <tr class="hideDetails"><td colspan="5"><b><u>Related Products</u></b></td></tr> 
    <tr class="hideDetails" >
        <td>&nbsp;</td>
        <td><b>ID</b></td>
        <td><b>Name</b></td>
        <td><b>Qty</b></td>
        <td><b>Price</b></td>
    </tr>
    </HeaderTemplate>
    <ItemTemplate>
    <tr class="hideDetails"  >
    <td>&nbsp;</td>
    <td><%# DataBinder.Eval(Container.DataItem, "[\"ProductID\"]")%></td>
    <td><%# DataBinder.Eval(Container.DataItem, "[\"ProductName\"]")%></td>
    <td><%# DataBinder.Eval(Container.DataItem, "[\"QuantityPerUnit\"]")%></td>
    <td><%# DataBinder.Eval(Container.DataItem, "[\"UnitPrice\"]")%></td>
    </tr>
    </ItemTemplate>
                 
    <FooterTemplate>
    <tr class="hideDetails" ><td colspan="5"><hr /></td></tr>
    <tr class="hideDetails" >
    <td colspan="5">
        <asp:Label ID="Label1" style="float:right;padding-right:2px;" runat="server" Text="<%#TotalPrice%>"></asp:Label>
    </td></tr>
    </table> 
</FooterTemplate>
</asp:Repeater>

</ItemTemplate>
</asp:ListView>

</div>
</form>
</body>
</html>

//codebehind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

public partial class ListViewGrouping2 : System.Web.UI.Page
{
    private string _DBConnection1 = ConfigurationManager.AppSettings["conStr3"];
    private float _TPrice = 0.0f;
    public string TotalPrice
    {
        get
        {
            return "<b>Total Price:</b>&nbsp;$" + String.Format("{0:0.##}", _TPrice.ToString());
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            lv1.DataSource = GetData2().Tables["Categories"];
            lv1.DataBind();
        }
    }

    private DataSet GetData2()
    {
        SqlConnection cnn = new SqlConnection(_DBConnection1);
        SqlDataAdapter cmd1 = new SqlDataAdapter("select CategoryID,CategoryName,Description from Categories WHERE CategoryID is NOT NULL order by CategoryID", cnn);

        DataSet ds = new DataSet();
        cmd1.Fill(ds, "Categories");
        SqlDataAdapter cmd2 = new SqlDataAdapter("select ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock from Products", cnn);
        cmd2.Fill(ds, "Products");

        //Create the relation bewtween the Category and Products tables.
        ds.Relations.Add("myrelation",
        ds.Tables["Categories"].Columns["CategoryID"],
        ds.Tables["Products"].Columns["CategoryID"]);

        cnn.Close();
        return ds;
    }

    protected void lv1_ItemDataBound(object sender, ListViewItemEventArgs e)
    {
        _TPrice = 0.0f;
    }


    protected void rptProducts_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            _TPrice += float.Parse(((System.Data.DataRow)(e.Item.DataItem)).ItemArray[5].ToString());  
        } 
    }

    protected void rptProducts_ItemCreated(object sender, RepeaterItemEventArgs e)
    {
        
    }
}

Thanks for reading.

This entry was posted in General ASP.Net C#. Bookmark the permalink.

Leave a comment