Best way to sort a gridview by age groups

Question

I am in the process of creating an asp.net web app in C# using Visual Studio. I have a registration page whereby children can register, their details are then sent to a 'children' table in my database, and upon clicking a 'view registered children' button, a new page opens which displays the first name, DOB and username(pk) of every child registered. As things stand, the DOB are stored and read as '04/02/2006'. I now need to find a way to get an age from the DOB and to be able to view the children by age groups (6-10, 11-13, 14-16).

I was wondering what the simplest way to achieve this would be? Radio buttons for each age group, that when selected, show only the children from that age group in the gridview. Or maybe when the page is loaded and all the children are shown on the gridview, they're already sorted into age groups?

Could someone advise me on the easiest way to achieve this, bearing in mind that I am new to C# & asp.net! I've attached screenshots of how my gridview currently looks. Thanks in advance.

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

namespace Coursework
{
public partial class Testy1 : System.Web.UI.Page
{
    //create a datasource
    SqlDataSource source = new SqlDataSource();

    protected void Page_Load(object sender, EventArgs e)
    {
        //always set some defaults for the sqldatasource
        source.ID = "source1";
        SqlConnection connectionString = new SqlConnection(ConfigurationManager.ConnectionStrings["newregDBConnectionString"].ConnectionString);
        source.SelectCommand = "SELECT firstname, dob, DATEDIFF(hour, dob, GETDATE()) / 8766 AS age FROM table ORDER BY age";

        if (!IsPostBack)
        {
            //bind the grid
            GridView1.DataSource = source;
            GridView1.DataBind();
        }
    }

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        //the new database query, now with where clause
        source.SelectCommand = "SELECT firstname, dob, DATEDIFF(hour, dob, GETDATE()) / 8766 AS age FROM table WHERE (DATEDIFF(hour, dob, GETDATE()) / 8766 BETWEEN @start AND @end) ORDER BY age";

        //get the end age from the dropdown and cast as int
        int end = Convert.ToInt32(DropDownList1.SelectedValue);

        //get the start int for the filter
        int start = end - 5;

        //if the filter is resetted, make sure the query returns all ages
        if (end == 0)
        {
            start = 0;
            end = 99;
        }

        //replace the parameters in the query
        source.SelectParameters.Add("start", start.ToString());
        source.SelectParameters.Add("end", end.ToString());

        //rebind the grid
        GridView1.DataSource = source;
        GridView1.DataBind();
    }
}

}

Answer 1

You can start with this snippet. The first thing you need to do is edit your query of getting data from the database. Add the following: DATEDIFF(hour, dob, GETDATE()) / 8766 as AGE, this will give you the age of the people in your database. You can then use it to filter by age.

Then add a DropDownList to the page with an OnSelectedIndexChanged event and AutoPostback set to true.

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
    <asp:ListItem Text="Filter age" Value="0"></asp:ListItem>
    <asp:ListItem Text="0 - 5" Value="5"></asp:ListItem>
    <asp:ListItem Text="6 - 10" Value="10"></asp:ListItem>
    <asp:ListItem Text="10 - 15" Value="15"></asp:ListItem>
</asp:DropDownList>

<asp:GridView ID="GridView1" runat="server">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <%# Eval("firstname") %>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <%# Convert.ToDateTime(Eval("dob")).ToString("d MMMM yyyy") %>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <%# Eval("age") %>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

And then in code behind

using System;
......

namespace Project1
{
    public partial class WebForm1: System.Web.UI.Page
    {
        //create a datasource
        SqlDataSource source = new SqlDataSource();

        protected void Page_Load(object sender, EventArgs e)
        {
            //always set some defaults for the sqldatasource
            source.ID = "source1";
            source.ConnectionString = "connectionString";
            source.SelectCommand = "SELECT firstname, dob, DATEDIFF(hour, dob, GETDATE()) / 8766 AS age FROM table ORDER BY age";

            if (!IsPostBack)
            {
                //bind the grid
                GridView1.DataSource = source;
                GridView1.DataBind();
            }
        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //the new database query, now with where clause
            source.SelectCommand = "SELECT firstname, dob, DATEDIFF(hour, dob, GETDATE()) / 8766 AS age FROM table WHERE (DATEDIFF(hour, dob, GETDATE()) / 8766 BETWEEN @start AND @end) ORDER BY age";

            //get the end age from the dropdown and cast as int
            int end = Convert.ToInt32(DropDownList1.SelectedValue);

            //get the start int for the filter
            int start = end - 5;

            //if the filter is resetted, make sure the query returns all ages
            if (end == 0)
            {
                start = 0;
                end = 99;
            }

            //replace the parameters in the query
            source.SelectParameters.Add("start", start.ToString());
            source.SelectParameters.Add("end", end.ToString());

            //rebind the grid
            GridView1.DataSource = source;
            GridView1.DataBind();
        }
    }
}