CODEWITHSUNDEEP
asp.net
StrattonL
StrattonL

Reputation: 726

ASP.NET group data by month

I wish to group some data by month (stored in an SQL Database), with collapsible DIVs, no doubt with jQuery. I don't particularly want to use to GridView, as I'd like to stay away from tables for simplicity when writing the jQuery.

What is the best method to do this in ASP.NET, which control would I use?

Please see the following example, for an idea of what I am trying to describe:

Grouping Example

Upvotes: 4

Views: 2429

Answers (1)

Denys Wessels
Denys Wessels

Reputation: 17039

I've created a project which displays data by month using nested repeaters, it's uploaded on Google Docs (just click File->Save to download the .zip).

Here's a brief overview of how it works:

Assuming you have a simple Orders table in the db with the following columns

  1. OrderId
  2. Product
  3. Quantity
  4. DateOfSale

Process

  1. Execute a stored procedure to get the months for a given year (I used EntityFramework functions, you could change this to ADO.NET,LINQ etc)
  2. Bind the returned months to a label in the master repeater(These will be your headings)
  3. Handle the OnItemDataBound event of the master repeater, this event runs every time an item is bound to the repeater.
  4. Inside OnItemDataBound execute a stored procedure to get all the records for the specific month and year and simply bind the returned data to the child repeater
  5. Add little jQuery to show and hide divs and you're set.

Code

Stored procedures:

CREATE PROCEDURE dbo.GetMonthsByYear
@Year INT 
AS
BEGIN
    SELECT DISTINCT DATENAME(Month,DateOfSale) AS [Month] FROM Orders
    WHERE Year(DateOfSale) = @Year
END

CREATE PROCEDURE dbo.GetOrdersByMonth
@Month NVARCHAR(15),
@Year INT 
AS
BEGIN
    SELECT * FROM Orders
    WHERE (Year(DateOfSale) = @Year) AND DATENAME(MONTH,DateOfSale) = @Month
END

ASPX:

<head runat="server">
    <script src="Scripts/jquery-1.7.1.js" type="text/javascript"></script>
    <script type="text/javascript">
        var showHide;
        $(document).ready(function () {
            showHide = function (control) {
                var parent = $(control).next();
                var display = parent.css('display');
                if (display == "none") { parent.fadeIn('slow'); }
                else { parent.fadeOut('slow'); }

            };
        });
    </script>
    <style type="text/css">
        .detail
        {
            height:300px;
            display:none;
            width: 100%;
            border: 1px solid black;
        }

        .header
        {
            vertical-align: top;
            padding: 3px;
            height: 30px;
            background: black;
            color: White;
            font-weight: bold;
        }
    </style>
    <title>Nested Repeater</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Select year:&nbsp;<asp:TextBox ID="txtYear" runat="server" /><br />
        <asp:Button ID="btnSearch" runat="server" Text="Search" OnClick="Search" /><br />
        <asp:Repeater ID="masterRepeater" runat="server" OnItemDataBound="ItemDataBound">
            <ItemTemplate>
                <div id='<%# Container.DataItem  %>' class="header" onclick="showHide(this);">
                    <asp:Label ID="lblMonth" runat="server" Text='<%# Container.DataItem %>' />
                </div>
                <div class="detail">
                    <asp:Repeater ID="detailRepeater" runat="server">
                        <HeaderTemplate>
                            <span style="text-decoration: underline">Product</span><br />
                        </HeaderTemplate>
                        <ItemTemplate>
                            <asp:Label ID="lblName" runat="server" Text='<%# Bind("Product") %>' />
                            <asp:Label ID="lblQuantity" runat="server" Text='<%# Bind("Quantity") %>' />
                            <asp:Label ID="lblDateOfSale" runat="server" Text='<%# Bind("DateOfSale") %>' /><br />
                        </ItemTemplate>
                    </asp:Repeater>
                </div>
            </ItemTemplate>
        </asp:Repeater>
    </div>
    </form>
</body>

Code behind:

protected void Search(object sender, EventArgs e)
{
    int year = 0;
    if (Int32.TryParse(txtYear.Text, out year))
    {
        orderEntities orders = new orderEntities();
        List<string> months = orders.GetMonthByYear(year).ToList();
        masterRepeater.DataSource = months;
        masterRepeater.DataBind();
    }
}

protected void ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
    {
        int year = 0;
        if (Int32.TryParse(txtYear.Text, out year))
        {
            Label lblMonth = e.Item.FindControl("lblMonth") as Label;
            if (lblMonth != null)
            {
                string month = lblMonth.Text;
                Repeater detailRepeater = e.Item.FindControl("detailRepeater") as Repeater;
                if (detailRepeater != null)
                {
                    orderEntities orders = new orderEntities();
                    var ordersByMonth = orders.GetOrdersByMonth(month, year).ToList();
                    detailRepeater.DataSource = ordersByMonth;
                    detailRepeater.DataBind();
                }
            }
        }
    }
}

Result:

enter image description here

Upvotes: 2

Related Questions