Friday, May 15, 2009

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

Related Posts :



1 comments on "SubTotals in GridView"

Add your comment. Please don't spam!
Subscribe in a Reader
Fábio on January 27, 2011 at 8:40 AM said...

Your post helped me a lot. Thanks for sharing your knowledge.

att

Fábio Andrade (Brazil)

Post a Comment