SubTotals in GridView

To display Sub Totals in a GridView is a situation that arise frequently.

Here's a sample on how to do it using GridView.

First we need a table TBL_Patients. Here's the structure



Now lets populate some sample values to it.



The ASPX

<asp:GridView ID="gvwPatientBill" runat="server"
                    AutoGenerateColumns="false"
                    DataKeyNames="BillID"
                    GridLines="Both"
                    OnRowDataBound="gvwPatientBill_RowDataBound">
        <Columns>
            <asp:TemplateField HeaderText="Bill ID">
                <ItemTemplate>
                    <asp:Label ID="lblBillID" runat="server"
                            Text='<%# Eval("BillID") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Patient ID">
                <ItemTemplate>
                    <asp:Label ID="lblPatientID" runat="server"
                            Text='<%# Eval("PatientID") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Patient Name">
                <ItemTemplate>
                    <asp:Label ID="lblPatientName" runat="server"
                            Text='<%# Eval("PatientName") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Patient Ward">
                <ItemTemplate>
                    <asp:Label ID="lblPatientWard" runat="server"
                            Text='<%# Eval("PatientWard") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Bill Amount">
                <ItemTemplate>
                    <asp:Label ID="lblBillAmount" runat="server"
                            Text='<%# Eval("BillAmount") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <HeaderStyle BackColor="Black" ForeColor="White" />
    </asp:GridView>

The ASPX.CS

int iPatientIDCount = 0;
int iRowsCount = 0;
int iAddBills = 0;
string sPatientID = "";

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGridView();
    }
}

private void BindGridView()
{
    DataTable dtPatientBills = new DataTable();
    string strSelectCommand = "SELECT * FROM TBL_PatientBills ORDER BY PatientID";
    using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
    {
        using (SqlDataAdapter adapPatientBills = new SqlDataAdapter(strSelectCommand, sqlConn))
        {
            adapPatientBills.Fill(dtPatientBills);
        }
    }
    iRowsCount = dtPatientBills.Rows.Count - 1;
    gvwPatientBill.DataSource = dtPatientBills;
    gvwPatientBill.DataBind();
}

protected void gvwPatientBill_RowDataBound(object sender, GridViewRowEventArgs e)
{

    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        string sName = "";
        if (e.Row.RowIndex == 0)
        {
            sPatientID = ((Label)e.Row.FindControl("lblPatientID")).Text;
            iAddBills = Convert.ToInt32(((Label)e.Row.FindControl("lblBillAmount")).Text);
        }
        else
        {
            sName = ((Label)gvwPatientBill.Rows[e.Row.RowIndex - 1].FindControl("lblPatientName")).Text;
            iPatientIDCount = iPatientIDCount + 1;
            if (sPatientID == ((Label)e.Row.FindControl("lblPatientID")).Text)
            {
                iAddBills += Convert.ToInt32(((Label)e.Row.FindControl("lblBillAmount")).Text);
            }
            else
            {
                sPatientID = ((Label)e.Row.FindControl("lblPatientID")).Text;
                Table tblTemp = (Table)this.gvwPatientBill.Controls[0];
                int intIndex = tblTemp.Rows.GetRowIndex(e.Row);
                GridViewRow gvrSubTotal = CreateGridViewRow(
                        intIndex,
                        "#8FD8D8",
                        gvwPatientBill.Columns.Count,
                        "Total for " + sName + " in " + iPatientIDCount + " Entries: " + iAddBills,
                        20);
                tblTemp.Controls.AddAt(intIndex, gvrSubTotal);
                iPatientIDCount = 0;
                iAddBills = Convert.ToInt32(((Label)e.Row.FindControl("lblBillAmount")).Text);
            }
            if (iRowsCount == e.Row.RowIndex)
            {
                sName = ((Label)e.Row.FindControl("lblPatientName")).Text;
                Table tblTemp = (Table)this.gvwPatientBill.Controls[0];
                int intIndex = tblTemp.Rows.GetRowIndex(e.Row) + 1;

                GridViewRow gvrLast = CreateGridViewRow(
                        intIndex,
                        "#8FD8D8",
                        gvwPatientBill.Columns.Count,
                        "Total for " + sName + " in " + iPatientIDCount + " Entries: " + iAddBills,
                        20);
                tblTemp.Controls.AddAt(intIndex, gvrLast);
            }
        }
    }
}

private GridViewRow CreateGridViewRow(int iCurrentIndex, string sTableBackColor,int iTableColumnSpan, string sTableText, int iTableHeight)
{
    GridViewRow gvrTemp = new GridViewRow(iCurrentIndex, iCurrentIndex, DataControlRowType.Separator, DataControlRowState.Normal);
    TableCell cellTemp = new TableCell();
    cellTemp.BackColor = System.Drawing.ColorTranslator.FromHtml("#8FD8D8");
    cellTemp.Font.Bold = true;
    cellTemp.ColumnSpan = iTableColumnSpan;
    cellTemp.HorizontalAlign = HorizontalAlign.Left;
    cellTemp.Text = sTableText;
    cellTemp.Height = Unit.Pixel(iTableHeight);
    gvrTemp.Cells.Add(cellTemp);
    return gvrTemp;
}

And the final result looks like this.


NB: Its a no-no if you wanna implement Paging and Sorting here. For that, we have to use the wonderful third party
GridView controls like Teleriks RadGrid and all
Read more...