Software Programs and Projects

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

<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>


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.



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


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();


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.:


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


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 specified string expression as aggregate function as a first parameter.


tag:-ASP.Net 2.0 GridView Compute Column Sum using C#

This tutorial will show you how to display data using the .NET GridView Control, stored procedures, ASP.NET 2.0 and C#.NET

Querying an SQL database with stored procedures using C# .NET is easy to do.

First, you will need to import the System.Data.SqlClient namespace.

The System.Data.SqlClient namespace contains the SqlCommand and SqlConnection classes that we need in order to connect to our database and to send an SQL command to it.

using System.Data.SqlClient;

We'll put our code in the Page_Load() event.

When the Page_Load() event fires, a new SqlCommand object is instantiated with our connection string and our stored procedure name. We add the parameters needed for the stored procedure by using our SqlCommand object's Parameters.Add() method.

Afterwards, we will attempt to connect using the Open() method of our cmd.Connection object. Once it is connected we will attempt to execute the stored procedure we specified earlier (in this example CustOrderHist stored procedure in the Northwind db).

If all goes well, we will have the results of our SQL query assigned to the gvwExample's DataSource property. Now all we have to do is call the DataBind() method of our gvwExample to bind the data to the control. The data is now ready to be displayed.


protected void Page_Load(object sender, EventArgs e)
{
try
{
SqlCommand cmd = new SqlCommand("CustOrderHist", new SqlConnection("Server=localhost;Database=Northwind;Trusted_Connection=True;"));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("CustomerID", txtCustID.Text);

cmd.Connection.Open();

gvwExample.DataSource = cmd.ExecuteReader();

gvwExample.DataBind();

cmd.Connection.Close();
cmd.Connection.Dispose();
}
catch (Exception ex)
{
lblStatus.Text = ex.Message;
}
}


We have to add a few tags on the front end of the .aspx page to place where we want the GridView control to display its bound data. We also specify what part of the data from the data set we would like to display. The front end .aspx page looks something like this:



<table width="600" border="0" align="center" cellpadding="5" cellspacing="1" bgcolor="#cccccc">
<tr>
<td width="100" align="right" bgcolor="#eeeeee" class="header1"> Customer Data Using Stored Procedures:</td>
<td align="center" bgcolor="#FFFFFF">
<asp:GridView ID="gvwExample" runat="server" AutoGenerateColumns="False" CssClass="basix" >
<columns>
<asp:BoundField DataField="ProductName" HeaderText="Product Name" />
<asp:BoundField DataField="Total" HeaderText="Total" />
</columns>
</asp:GridView> &nbsp;
<br />
Customer ID:
<asp:TextBox ID="txtCustID" runat="server" Width="42px">ALFKI</asp:TextBox>
Order ID:<asp:TextBox ID="txtOrderID" runat="server" Width="43px">10256</asp:TextBox><br />
<asp:Button ID="btnSubmit" runat="server" Text="Go" />
<br />
<asp:label ID="lblStatus" runat="server"></asp:label></td>
</tr>
</table>


The flow for the code behind page is as follows.


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
try
{
SqlCommand cmd = new SqlCommand("CustOrderHist", new SqlConnection("Server=localhost;Database=Northwind;Trusted_Connection=True;"));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("CustomerID", txtCustID.Text);

cmd.Connection.Open();

gvwExample.DataSource = cmd.ExecuteReader();

gvwExample.DataBind();

cmd.Connection.Close();
cmd.Connection.Dispose();
}
catch (Exception ex)
{
lblStatus.Text = ex.Message;
}
}
}



tag:- Using Stored Procs with ASP.NET 2.0 GridView and C#


Related Articles

Find Checkbox control in the Gridview and Check box is checked or not

Get Cell Contents in GridView using C#.Net

How To Find HeaderRow in GridView Using C#.Net in Asp.net