Before I had blogged some post which enables us to show Grand Total and Group Total in GridView. In this same series, I am blogging about Running Total and Grand Total on Grid View in this post.
I am taking Northwind database for getting the data and show it on the Grid View. So to run the source code, please make sure you have Northwind database setup in your SQL Server.
The examples show Order details on the Grid View as in the picture below.
I am taking below requirements to be implemented in this post.
In this method we are going to implement by showing the Grid View with a footer row which shows the sum of amount column. The Running Total title shows before the Amount column starts.
The GridView Page
Second Example (RunningTotal1.aspx):
In this we will take the same implementation and show the Running Total title by merging all empty cells which are on the left.
The GridView script will be same except an addition of RowDataBound event. So the GridView script would be -
Third Example (FooterNGrandTotal.aspx):
In this implementation we will show the Grid View with two footer rows. The first footer shows the Running Total and the second one shows the Grand Total.
The GridView script
Fourth Example (FooterNGrandTotal1.aspx)
This example shows the Running Total and Grand Total in a single footer row where the both the totals should show in separate lines. So output will be same as 3rdmethod but instead of having two footer row it will be single footer row.
The ASPX GridView script will be same as 3rd method. But the C# code has two methods difference from 3rd method.
download the source code in C# here and in VB here.
I am taking Northwind database for getting the data and show it on the Grid View. So to run the source code, please make sure you have Northwind database setup in your SQL Server.
The examples show Order details on the Grid View as in the picture below.
- The Grid View should show records as normal with pagination (as shown above image).
- The Grid View must have a footer row to show the sum values for amount column for the rows on the particular page.
- The footer row should have a title Running Total before the running total value starts.
- The Grid View also can have one more footer row after the Running Total footer row.
- The second footer row shows the Grand Total for the amount columns shown for running total.
- The Grand Total row should show the title Grand Total before the grand total starts.
- Show the Grid View with a footer row which shows the sum of amount column. The Running Total title shows before the Amount column starts.
- Take the same example and merge the cells which are empty before total starts. So the Running Total heading will be in a single cell.
- Show the Grid View with two footer rows. The first footer shows the Running Total and the second one shows the Grand Total. The empty cells must be merged as done in second method.
- Show the Running Total and Grand Total in a single footer row where the both the totals should show in separate lines. So output will be same as 3rdmethod but instead of having two footer row it will be single footer row.
In this method we are going to implement by showing the Grid View with a footer row which shows the sum of amount column. The Running Total title shows before the Amount column starts.
The GridView Page
<asp:GridView ID="grdViewOrders" runat="server"
AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"
DataKeyNames="ProductID" Width="100%"
CellPadding="4" ForeColor="#333333" ShowFooter="True"
onrowdatabound="grdViewOrders_RowDataBound"
onpageindexchanging="grdViewOrders_PageIndexChanging">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="Order ID" />
<asp:BoundField DataField="OrderDate" HeaderText="Order Date" DataFormatString="{0:dd-MMMM-yyyy}" />
<asp:BoundField DataField="ProductName" HeaderText="Product Name" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit" />
<asp:BoundField DataField="UnitPrice" HeaderText="Unit Price" DataFormatString="{0:#0.00}" >
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:BoundField DataField="Quantity" HeaderText="Quantity" >
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:TemplateField HeaderText="Discount">
<ItemTemplate>
<%# Convert.ToDouble(Eval("Discount")).ToString("0.00") %>
</ItemTemplate>
<FooterTemplate>
Running Total :
</FooterTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterStyle HorizontalAlign="Right" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Amount">
<ItemTemplate>
<%# Convert.ToDouble(Eval("Amount")).ToString("0.00")%>
</ItemTemplate>
<FooterTemplate>
<asp:Label runat="server" ID="lblFooterAmount" Text=""></asp:Label>
</FooterTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterStyle HorizontalAlign="Right" />
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
</asp:GridView>The OrderView entity classpublic class OrderView
{
public int OrderID { get; set; }
public DateTime OrderDate { get; set; }
public string ProductName { get; set; }
public string QuantityPerUnit { get; set; }
public int ProductID { get; set; }
public double UnitPrice { get; set; }
public int Quantity { get; set; }
public double Discount { get; set; }
public double Amount { get; set; }
}The C# Codepublic partial class RunningTotal : System.Web.UI.Page
{
// To calculate Running Total
private double dblFooterAmount = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid();
}
}
/// <summary>
/// Method which binds the data to the Grid
/// </summary>
private void BindGrid()
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand(
"SELECT Orders.OrderID, Orders.OrderDate, Products.ProductName, Products.QuantityPerUnit, " +
"OrderDetails.ProductID, OrderDetails.UnitPrice, OrderDetails.Quantity, OrderDetails.Discount " +
"FROM Orders JOIN [Order Details] OrderDetails ON Orders.OrderID = OrderDetails.OrderID " +
"JOIN Products ON OrderDetails.ProductID = Products.ProductID " +
"ORDER BY Orders.OrderID, OrderDetails.ProductID", connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
IList<OrderView> orderViewList = new List<OrderView>();
while (dr.Read())
{
OrderView orderView = new OrderView();
orderView.OrderID = Convert.ToInt32(dr["OrderID"].ToString());
orderView.OrderDate = Convert.ToDateTime(dr["OrderDate"].ToString());
orderView.ProductName = dr["ProductName"].ToString();
orderView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
orderView.ProductID = Convert.ToInt32(dr["ProductID"].ToString());
orderView.UnitPrice = Convert.ToDouble(dr["UnitPrice"].ToString());
orderView.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
orderView.Discount = Convert.ToDouble(dr["Discount"].ToString());
orderView.Amount = Convert.ToDouble(dr["UnitPrice"].ToString()) * Convert.ToDouble(dr["Quantity"].ToString()) - Convert.ToDouble(dr["Discount"].ToString());
orderViewList.Add(orderView);
}
grdViewOrders.DataSource = orderViewList;
grdViewOrders.DataBind();
}
}
protected void grdViewOrders_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
// This code runs when the data rows getting bound.
// So we can calculatethe total and keep to the temp variable declared in the class
// Get the Row Amount
OrderView orderView = (OrderView)e.Row.DataItem;
// Each Row Amount values are added to the footer Amount variable
dblFooterAmount = dblFooterAmount + orderView.Amount;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
// This code will run when footer row getting bound.
Label lblFooterAmount = (Label)e.Row.FindControl("lblFooterAmount");
lblFooterAmount.Text = dblFooterAmount.ToString("C");
}
}
protected void grdViewOrders_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdViewOrders.PageIndex = e.NewPageIndex;
BindGrid();
}
}The output of the code will be
Second Example (RunningTotal1.aspx):
In this we will take the same implementation and show the Running Total title by merging all empty cells which are on the left.
The GridView script will be same except an addition of RowDataBound event. So the GridView script would be -
<asp:GridView ID="grdViewOrders" runat="server" AllowPaging="True" AutoGenerateColumns="False" TabIndex="1" DataKeyNames="ProductID" Width="100%" CellPadding="4" ForeColor="#333333" ShowFooter="True" onrowdatabound="grdViewOrders_RowDataBound" onpageindexchanging="grdViewOrders_PageIndexChanging"> <Columns> <!-- Removed all the columns as all are same --> </Columns> </asp:GridView>The C# code for RowDataBound event would be
protected void grdViewOrders_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Footer)
{
// First cell is used for specifying the Total text
int intNoOfMergeCol = e.Row.Cells.Count - 1; /*except last column */
for (int intCellCol = 1; intCellCol < intNoOfMergeCol; intCellCol++)
e.Row.Cells.RemoveAt(1);
e.Row.Cells[0].ColumnSpan = intNoOfMergeCol;
e.Row.Cells[0].Text = "Running Total : ";
e.Row.Cells[0].HorizontalAlign = HorizontalAlign.Right;
}
}The output of this code would be -
Third Example (FooterNGrandTotal.aspx):
In this implementation we will show the Grid View with two footer rows. The first footer shows the Running Total and the second one shows the Grand Total.
The GridView script
<asp:GridView ID="grdViewOrders" runat="server"
AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"
DataKeyNames="ProductID" Width="100%"
CellPadding="4" ForeColor="#333333" ShowFooter="True"
onrowdatabound="grdViewOrders_RowDataBound"
onpageindexchanging="grdViewOrders_PageIndexChanging"
onrowcreated="grdViewOrders_RowCreated">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="Order ID" />
<asp:BoundField DataField="OrderDate" HeaderText="Order Date" DataFormatString="{0:dd-MMMM-yyyy}" />
<asp:BoundField DataField="ProductName" HeaderText="Product Name" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit" />
<asp:BoundField DataField="UnitPrice" HeaderText="Unit Price" DataFormatString="{0:#0.00}" >
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:BoundField DataField="Quantity" HeaderText="Quantity" >
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:TemplateField HeaderText="Discount">
<ItemTemplate>
<%# Convert.ToDouble(Eval("Discount")).ToString("0.00") %>
</ItemTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterStyle HorizontalAlign="Right" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Amount">
<ItemTemplate>
<%# (Convert.ToDouble(Eval("UnitPrice")) * Convert.ToDouble(Eval("Quantity")) - Convert.ToDouble(Eval("Discount"))).ToString("0.00") %>
</ItemTemplate>
<FooterTemplate>
<asp:Label runat="server" ID="lblFooterAmount" Text=""></asp:Label>
</FooterTemplate>
<ItemStyle HorizontalAlign="Right" />
<FooterStyle HorizontalAlign="Right" />
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
</asp:GridView>The C# Code behindpublic partial class FooterNGrandTotal : System.Web.UI.Page
{
// To calculate Running Total
private double dblFooterAmount = 0;
private double dblGrandTotal = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGrid();
}
}
/// <summary>
/// Method which binds the data to the Grid
/// </summary>
private void BindGrid()
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand(
"SELECT Orders.OrderID, Orders.OrderDate, Products.ProductName, Products.QuantityPerUnit, " +
"OrderDetails.ProductID, OrderDetails.UnitPrice, OrderDetails.Quantity, OrderDetails.Discount " +
"FROM Orders JOIN [Order Details] OrderDetails ON Orders.OrderID = OrderDetails.OrderID " +
"JOIN Products ON OrderDetails.ProductID = Products.ProductID " +
"ORDER BY Orders.OrderID, OrderDetails.ProductID", connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
IList<OrderView> orderViewList = new List<OrderView>();
while (dr.Read())
{
OrderView orderView = new OrderView();
orderView.OrderID = Convert.ToInt32(dr["OrderID"].ToString());
orderView.OrderDate = Convert.ToDateTime(dr["OrderDate"].ToString());
orderView.ProductName = dr["ProductName"].ToString();
orderView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
orderView.ProductID = Convert.ToInt32(dr["ProductID"].ToString());
orderView.UnitPrice = Convert.ToDouble(dr["UnitPrice"].ToString());
orderView.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
orderView.Discount = Convert.ToDouble(dr["Discount"].ToString());
orderView.Amount = Convert.ToDouble(dr["UnitPrice"].ToString()) * Convert.ToDouble(dr["Quantity"].ToString()) - Convert.ToDouble(dr["Discount"].ToString());
dblGrandTotal = dblGrandTotal + orderView.Amount;
orderViewList.Add(orderView);
}
grdViewOrders.DataSource = orderViewList;
grdViewOrders.DataBind();
}
}
protected void grdViewOrders_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
// This code runs when the data rows getting bound.
// So we can calculatethe total and keep to the temp variable declared in the class
// Get the Row Amount
OrderView orderView = (OrderView)e.Row.DataItem;
// Each Row Amount values are added to the footer Amount variable
dblFooterAmount = dblFooterAmount + orderView.Amount;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
// This code also works, but as we already assigned the footer amount in RowCreated I am commenting this code...
//// This code will run when footer row getting bound.
//Label lblFooterAmount = (Label)e.Row.FindControl("lblFooterAmount");
//lblFooterAmount.Text = dblFooterAmount.ToString("C");
}
}
protected void grdViewOrders_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdViewOrders.PageIndex = e.NewPageIndex;
BindGrid();
}
protected void grdViewOrders_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Footer)
{
int intNoOfMergeCol = e.Row.Cells.Count - 1; /*except last column */
GridViewRow footerRow = new GridViewRow(0, 0, DataControlRowType.Footer, DataControlRowState.Insert);
//Adding Footer Total Text Column
TableCell cell = new TableCell();
cell.Text = "Running Total : ";
cell.HorizontalAlign = HorizontalAlign.Right;
cell.ColumnSpan = intNoOfMergeCol;
footerRow.Cells.Add(cell);
//Adding Footer Total Amount Column
cell = new TableCell();
Label lbl = new Label();
lbl.ID = "lblFooterAmount";
lbl.Text = dblFooterAmount.ToString("C");
cell.Controls.Add(lbl);
cell.HorizontalAlign = HorizontalAlign.Right;
footerRow.Cells.Add(cell);
grdViewOrders.Controls[0].Controls.Add(footerRow);
// First cell is used for specifying the Total text
for (int intCellCol = 1; intCellCol < intNoOfMergeCol; intCellCol++)
e.Row.Cells.RemoveAt(1);
e.Row.Cells[0].ColumnSpan = intNoOfMergeCol;
e.Row.Cells[0].Text = "Grand Total : ";
e.Row.Cells[0].HorizontalAlign = HorizontalAlign.Right;
e.Row.Cells[1].Text = dblGrandTotal.ToString("C");
}
}
}The output of the source code would be
Fourth Example (FooterNGrandTotal1.aspx)
This example shows the Running Total and Grand Total in a single footer row where the both the totals should show in separate lines. So output will be same as 3rdmethod but instead of having two footer row it will be single footer row.
The ASPX GridView script will be same as 3rd method. But the C# code has two methods difference from 3rd method.
protected void grdViewOrders_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
// This code runs when the data rows getting bound.
// So we can calculatethe total and keep to the temp variable declared in the class
// Get the Row Amount
OrderView orderView = (OrderView)e.Row.DataItem;
// Each Row Amount values are added to the footer Amount variable
dblFooterAmount = dblFooterAmount + orderView.Amount;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
// This code will run when footer row getting bound.
Label lblFooterAmount = (Label)e.Row.FindControl("lblFooterAmount");
lblFooterAmount.Text = dblFooterAmount.ToString("C") + "<b r />" + dblGrandTotal.ToString("C");
}
}
protected void grdViewOrders_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Footer)
{
// First cell is used for specifying the Total text
int intNoOfMergeCol = e.Row.Cells.Count - 1; /*except last column */
for (int intCellCol = 1; intCellCol < intNoOfMergeCol; intCellCol++)
e.Row.Cells.RemoveAt(1);
e.Row.Cells[0].ColumnSpan = intNoOfMergeCol;
e.Row.Cells[0].Text = "Running Total : <b r /> Grand Total : ";
e.Row.Cells[0].HorizontalAlign = HorizontalAlign.Right;
}
}The output of this code would be -
download the source code in C# here and in VB here.
where can i find database for this task can u plz send the database to uday.ece18@gmail.com