How To: Do Calculations in GridView

• Download source code - 2.7 KB

Introduction

I've seen many fresh developers in various forums struggling at implementing a simple calculation in GridView. Usually, they wanted to calculate the total amount while typing the value into a TextBox control and display the grand total value at the footer. As you may already know, this can be easily done using a server-side approach.

Let's take a quick review on how to implement a server-side approach calculation. Consider that we have this GridView markup below.

Server-Side Approach

Suppose we have this GridView markup:

<asp:GridView ID="GridView1" runat="server" 
AutoGenerateColumns="false" ShowFooter="true">  
  <Columns>  
     <asp:BoundField DataField="ItemDescription" HeaderText="Item"/>  
     <asp:TemplateField HeaderText="Amount">  
       <ItemTemplate>  
         <asp:TextBox ID="TextBox1" runat="server"   
                      AutoPostBack="True"  
                      ontextchanged="TextBox1_TextChanged">    
         </asp:TextBox>  
       </ItemTemplate>  
     </asp:TemplateField>  
  </Columns>  
</asp:GridView>  

The markup above is pretty much simple. A GridView that contains a BoundField column for displaying the item, and a TemplateField for the amount column. Now, let's populate the grid with data from the database using the ADO.NET way. Here's the code block below:

private string GetConnectionString(){  
        //Where MYDBConnection is the connetion string that was set up from the web config file  
        return System.Configuration.ConfigurationManager.ConnectionStrings
        ["MyDBConnection"].ConnectionString;  
}
  
// Method for Binding the GridView Control  
private void BindGridView(){  
    using (SqlConnection connection = new SqlConnection(GetConnectionString())) {  
            string sql = "SELECT * FROM YourTable";  
            using (SqlCommand cmd = new SqlCommand(sql, connection)) {  
                    connection.Open();  
                    using(var adapter = new SqlDataAdapter(cmd)){  
                          adapter.Fill(dt)  
                          if (dt.Rows.Count > 0){  
                               GridView1.DataSource = dt;  
                               GridView1.DataBind();  
                          }  
                    }  
           }  
    }  
}
  
//Bind GridView on initial postabck  
protected void Page_Load(object sender, EventArgs e){  
        if (!Page.IsPostBack)  
            BindGridView();   
}  

And here's the code block for calculating the total:

//Calculate the Totals in the TextBox rows  
protected void TextBox1_TextChanged(object sender, EventArgs e){  
        double total = 0;  
        foreach (GridViewRow gvr in GridView1.Rows)  
        {  
            TextBox tb = (TextBox)gvr.Cells[1].FindControl("TextBox1");  
            double sum;  
            if(double.TryParse(tb.Text.Trim(),out sum))  
            {  
                total += sum;  
            }  
        }  
        //Display  the Totals in the Footer row  
        GridView1.FooterRow.Cells[1].Text = total.ToString();  
}  

That is simple! Running the code will now provide you a grid with enabled total amount calculation. Now there are certain cases where you may be required to implement a client-side calculation for whatever reasons. In this article, we will take a look at how we will implement it. Keep in mind that the server-side approach isn't really ideal to do such operation as the TextChanged event will trigger a server postback everytime to change/type a value in the TextBox.

The Client-Side Approach with JavaScript

To get started, let's setup the form. For simplicity, let's just setup the form like this:

<asp:gridview ID="GridView1"  runat="server"  
ShowFooter="true" AutoGenerateColumns="false">  
    <Columns>  
        <asp:BoundField DataField="RowNumber" HeaderText="Row Number" />  
        <asp:BoundField DataField="Description" HeaderText="Item Description" />  
        <asp:TemplateField HeaderText="Item Price">  
            <ItemTemplate>  
                <asp:Label ID="LBLPrice" runat="server" 
                Text='<%# Eval("Price","{0:C}") %>'></asp:Label>  
            </ItemTemplate>  
            <FooterTemplate>  
                <b>Total Qty:</b>  
            </FooterTemplate>  
        </asp:TemplateField>  
        <asp:TemplateField HeaderText="Quantity">  
            <ItemTemplate>  
                <asp:TextBox ID="TXTQty" runat="server" 
                onkeyup="CalculateTotals();"></asp:TextBox>  
            </ItemTemplate>  
            <FooterTemplate>  
                <asp:Label ID="LBLQtyTotal" runat="server" 
                Font-Bold="true" ForeColor="Blue" Text="0" ></asp:Label>       
                <b>Total Amount:</b>  
            </FooterTemplate>  
        </asp:TemplateField>  
        <asp:TemplateField HeaderText="Sub-Total">  
            <ItemTemplate>  
                <asp:Label ID="LBLSubTotal" runat="server" 
                ForeColor="Green" Text="0.00"></asp:Label>  
            </ItemTemplate>  
            <FooterTemplate>  
                <asp:Label ID="LBLTotal" runat="server" 
                ForeColor="Green" Font-Bold="true" Text="0.00"></asp:Label>  
            </FooterTemplate>  
        </asp:TemplateField>  
    </Columns>  
</asp:gridview> 

As you can see, there's really nothing fancy about the markup above. It just contains a standard GridView with BoundFields and TemplateFields on it. Just for the purpose of the demo, I will just use a dummy data for populating the GridView. Here's the code block:

public partial class GridCalculation : System.Web.UI.Page  
{  
        private void BindDummyDataToGrid() {  
  
            DataTable dt = new DataTable();  
            DataRow dr = null;  
  
            dt.Columns.Add(new DataColumn("RowNumber", typeof(int)));  
            dt.Columns.Add(new DataColumn("Description", typeof(string)));  
            dt.Columns.Add(new DataColumn("Price", typeof(string)));  
  
            dr = dt.NewRow();  
            dr["RowNumber"] = 1;  
            dr["Description"] = "Nike";  
            dr["Price"] = "1000";  
            dt.Rows.Add(dr);  
  
            dr = dt.NewRow();  
            dr["RowNumber"] = 2;  
            dr["Description"] = "Converse";  
            dr["Price"] = "800";  
            dt.Rows.Add(dr);  
  
            dr = dt.NewRow();  
            dr["RowNumber"] = 3;  
            dr["Description"] = "Adidas";  
            dr["Price"] = "500";  
            dt.Rows.Add(dr);  
  
            dr = dt.NewRow();  
            dr["RowNumber"] = 4;  
            dr["Description"] = "Reebok";  
            dr["Price"] = "750";  
            dt.Rows.Add(dr);  
            dr = dt.NewRow();  
            dr["RowNumber"] = 5;  
            dr["Description"] = "Vans";  
            dr["Price"] = "1100";  
            dt.Rows.Add(dr);  
            dr = dt.NewRow();  
            dr["RowNumber"] = 6;  
            dr["Description"] = "Fila";  
            dr["Price"] = "200";  
            dt.Rows.Add(dr);  
           //Bind the GridView  
            GridView1.DataSource = dt;  
            GridView1.DataBind();  
        }  

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

Running the page should result in something like this:

Keep in mind that the client-side implementation is way different since you need to deal with the DOM elements to extract the controls, and you'll need to understand JavaScript syntax that is a bit complex compared to C# with server-side implementation. Now let's go into the “meat” of this demo, and that is the implementation of the client-side calculation. The main functionality includes the following:

• Number validation
• Formatting values into readable money format with separators
• Calculation for sub-totals and total amount

Here is the JavaScript code (this should be placed inside the <head> section of your WebForm page):

<script type="text/javascript">  
        
        function CalculateTotals() {  
            var gv = document.getElementById("<%= GridView1.ClientID %>");  
            var tb = gv.getElementsByTagName("input");  
            var lb = gv.getElementsByTagName("span");  
  
            var sub = 0;  
            var total = 0;  
            var indexQ = 1;  
            var indexP = 0;  
            var price = 0;  
  
            for (var i = 0; i < tb.length; i++) {  
                if (tb[i].type == "text") {  
                    ValidateNumber(tb[i]);  
  
                    price = lb[indexP].innerHTML.replace
                    ("$", "").replace(",", "");  
                    sub = parseFloat(price) * parseFloat(tb[i].value);  
                    if (isNaN(sub)) {  
                        lb[i + indexQ].innerHTML = "0.00";  
                        sub = 0;  
                    }  
                    else {  
                        lb[i + indexQ].innerHTML = 
                        FormatToMoney(sub, "$", ",", "."); ;  
                    }  
                     
                    indexQ++;  
                    indexP = indexP + 2;  
  
                    total += parseFloat(sub);  
                }  
            }  
  
            lb[lb.length - 1].innerHTML = 
            FormatToMoney(total, "$", ",", ".");  
        }  
  
        function ValidateNumber(o) {  
            if (o.value.length > 0) {  
                o.value = o.value.replace(/[^\d]+/g, ''); //Allow only whole numbers  
            }  
        } 
 
        function isThousands(position) {  
            if (Math.floor(position / 3) * 3 == position) return true;  
            return false;  
        };  
  
        function FormatToMoney(theNumber, theCurrency, theThousands, theDecimal) {  
            var theDecimalDigits = Math.round((theNumber * 100) - (Math.floor(theNumber) * 100));  
            theDecimalDigits = "" + (theDecimalDigits + "0").substring(0, 2);  
            theNumber = "" + Math.floor(theNumber);  
            var theOutput = theCurrency;  
            for (x = 0; x < theNumber.length; x++) {  
                theOutput += theNumber.substring(x, x + 1);  
                if (isThousands(theNumber.length - x - 1) && (theNumber.length - x - 1 != 0)) {  
                    theOutput += theThousands;  
                };  
            };  
            theOutput += theDecimal + theDecimalDigits;  
            return theOutput;  
        }   
</script> 

Let's try to evaluate each JavaScript function above. The FormatToMoney() is a function that would format numeric values to money by passing the numeric value, the currency, thousands and decimal separators. The isThousand() function evaluates the value and returns Boolean. This function is used within the FormatToMoney() function to determine if the value is on thousand. The ValidateNumber() is a function that validates if the value supplied is a valid number. Finally, the CalculateTotals() is the main function that extracts each element from the GridView, calculates the values and sets the calculated values back to the GridView element that in this case is the sub-total and total amount.

Now, call the JavaScript CalculateTotals() function on “onkeyup” or “onkeypress” event like this:

<ItemTemplate>  
     <asp:TextBox ID="TXTQty" runat="server" onkeyup="CalculateTotals();"></asp:TextBox> 
</ItemTemplate> 

Running the page will provide you with the following output.

On initial load:

After entering values into the TextBox:

That's it! I hope someone finds this post useful.