ASP.Net 2.0 GridView Compute Column Sum using C#

To compute the GridView Sum in asp.net let’s take a simple example of SQL Database table. For GridView sum in ASP.net you can use the Northwind SQL database table to test the source code given in this tutorial. Here we will use products SQL table of Northwind database. In this tutorial we will compute the sum of units in stock of each product in the specified category.

Now you are ready to connect this SQL table with ASP.net web page to display the records in SQL table. But still you need a control to display the records in tabular format so that you could show the sum of column exactly below the Units in Stock column of GridView control.

HTML code with Auto Formatted GridView in ASP.Net Web Page

[code:html]<asp:GridView
    ID="GridView1"
    runat="server"
    AutoGenerateColumns="False"
    CellPadding="2"
    ShowFooter="true">

<Columns>

<asp:TemplateField HeaderText="Product Name" HeaderStyle-Width="200px">
<ItemTemplate>
    <%# DataBinder.Eval(Container.DataItem, "ProductName") %>
</ItemTemplate>

<FooterTemplate>
     <asp:Label ID="Label1" runat="server" Text="Total Units"></asp:Label>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Units In Stock">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "UnitsInStock") %>
</ItemTemplate>

<FooterTemplate>
<asp:Label ID="Label2" runat="server"></asp:Label>
</FooterTemplate>
</asp:TemplateField>

</Columns>

<HeaderStyle HorizontalAlign="Left" />
<FooterStyle BackColor="#cccccc" Font-Bold="true" />

</asp:GridView>[/code]

In the above HTML code of GridView control of ASP.Net 2.0 you can notice that we have created two columns of GridView using ItemTemplate of TemplateField to display the Product Name and Units in stock Columns of Products Table. In both TemplateField columns we also added FooterTemplate sections having Label controls. Under product name column Text property of Label control has been set to "Total Unit". For the Label control placed inside the FooterTemplate of second TemplateField displaying the Units In Stock column we will set its Text property dynamically after computing the sum of associated column.

After setting the GridView control, let's come to the main part i.e. C# code. You need to write all the required code to connect the SQL Database to retrieve the records from the database table. If you don't know the Database connectivity in ASP.Net 2.0 then learn it from the tutorial: ASP.Net Connect to SQL Database

C# Sample code to Compute the Sum of Column in GridView control of ASP.Net 

[code:c#]SqlCommand mySqlCommand = new SqlCommand("select productid, productname, unitsinstock from products where categoryid = @categoryid", mySQLconnection);

SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlCommand);

mySqlCommand.Parameters.Add("@categoryid", SqlDbType.Int).Value = 1;

DataSet myDataSet = new DataSet();

mySqlAdapter.Fill(myDataSet);

GridView1.DataSource = myDataSet;

GridView1.DataBind();

((Label)GridView1.FooterRow.Cells[1].FindControl("Label2")).Text = myDataSet.Tables[0].Compute("sum(unitsinstock)", "").ToString();[/code]

In the above C# code Compute function is used that returns the passed expression for all the rows of a table in a dataset. According to above code, it will return the sum of units in stock for each product in the specified category.

Compute function of DataTable in ASP.Net

Compute function takes 2 parameters:

1. String Expression:
   Expression as a agregate function of sql.
2. String Filter:
   Filter criteria to filter the retrived rows.

E.g.:

[code:c#]((Label)GridView1.FooterRow.Cells[1].FindControl("Label2")).Text = myDataSet.Tables[0].Compute("sum(unitsinstock)", "categoryid=1").ToString();[/code]

Above C# example code line shows that you can also specify the string Filter as a second parameter of Compute function to evaluate the result according to to specifiedstring expression as aggregate function as a first parameter.