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.

ASP.Net 2.0 Gridview Sorting Using C#

ASP.Net 2.0 provides the sorting feature also in GridView Control. You can sort the records displayed using Gridview control in ascending or descending orderretrieved from sql database. You can use C# code to bind the SQL data with GridView control and follow the following simple steps to make your ASP.Net GridView control with sorting enabled. 

First of all drag the GridView control from Data controls menu. It will add the GridView control HTML source code as given above. Now click on GridView control to load the control properties at right side panel.

[code:html]
<asp:GridView id="GridView1" runat="server"></asp:GridView>
[/code]

To allow the sorting in GridView control select True from the dropdown list of AllowSorting property of Gridview as shown in above image.

This will add AllowSorting="True" in HTML source code of GridView Control.

Next step is to bind the Sorting event for GridView Control.

To bind the Sorting event of GridView control, double click on the Sorting event in the properties viewer of GridView. This will add the Sorting event in the HTML source code and C# code for Gridview.

HTML Source code for GridView Sorting

[code:html]<asp:GridView ID="GridView1"
              runat="server"
              CellPadding="2"
              AllowSorting="True" 
              OnSorting="GridView1_Sorting" 
              AutoGenerateColumns="False"
              Width="500px">
    <Columns>

        <asp:BoundField DataField="ProductID" HeaderText="ProductID" SortExpression="ProductName">
            <HeaderStyle HorizontalAlign="Left" />
        </asp:BoundField>

        <asp:BoundField DataField="ProductName" HeaderText="Product Name" SortExpression="ProductName">
            <HeaderStyle HorizontalAlign="Left" Width="200px" />
        </asp:BoundField>

        <asp:BoundField DataField="UnitsInStock" HeaderText="Units In Stock" SortExpression="UnitsInStock">
            <HeaderStyle HorizontalAlign="Left" />
            <ItemStyle HorizontalAlign="Center" />
        </asp:BoundField>

        <asp:BoundField DataField="UnitPrice" HeaderText="Price Per Unit" SortExpression="UnitPrice">
            <HeaderStyle HorizontalAlign="Left" />
            <ItemStyle HorizontalAlign="Center" />
        </asp:BoundField>

    </Columns>
</asp:GridView>
[/code]

C# Code for GridView Sorting

[code:c#]    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ViewState["sortOrder"] = "";
            bindGridView("","");
        }
    }

    public void bindGridView(string sortExp,string sortDir)
    {
        // string variable to store the connection string
        // defined in ConnectionStrings section of web.config file.
        string connStr = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

        // object created for SqlConnection Class.
        SqlConnection mySQLconnection = new SqlConnection(connStr);

        // if condition that can be used to check the sql connection
        // whether it is already open or not.
        if (mySQLconnection.State == ConnectionState.Closed)
        {
            mySQLconnection.Open();
        }

        SqlCommand mySqlCommand = new SqlCommand("select top 10 ProductID, ProductName, UnitsInStock, UnitPrice from products", mySQLconnection);
        SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlCommand);
        DataSet myDataSet = new DataSet();
        mySqlAdapter.Fill(myDataSet);

        DataView myDataView = new DataView();
        myDataView = myDataSet.Tables[0].DefaultView;

        if (sortExp!=string.Empty)
        {
            myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir);
        }

        GridView1.DataSource = myDataView;
        GridView1.DataBind();

        // if condition that can be used to check the sql connection
        // if it is open then close it.
        if (mySQLconnection.State == ConnectionState.Open)
        {
            mySQLconnection.Close();
        }

    }

    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
     
        bindGridView(e.SortExpression, sortOrder);
       
    }

    public string sortOrder
    {
        get
        {
            if (ViewState["sortOrder"].ToString() == "desc")
            {
                ViewState["sortOrder"] = "asc";
            }
            else
            {
                ViewState["sortOrder"] = "desc";
            }

            return ViewState["sortOrder"].ToString();
        }
        set
        {
            ViewState["sortOrder"] = value;
        }
    }
[/code]

ASP.Net 2.0 Gridview Paging using C#

In ASP.Net 2.0 GridView is most often used to display the data retrieved from the database in Tabular form with features of Gridview like data paging, sorting andauto formats.

You can use C# code to bind the SQL data with GridView control and follow the following simple steps to make your ASP.Net GridView control with paging enabled.

First of all drag the GridView control from Data controls menu. It will add the GridView control HTML source code as given above. Now click on GridView control to load the control properties at right side panel.

[code:html]
<asp:GridView id="GridView1" runat="server"></asp:GridView>
[/code]

To enable the paging in GridView control select True from the dropdown list of AllowPaging property of GridView control as shown in the above image.

It will add AllowPaging="True" in HTML source code of Gridview.

Next step is to bind the data with Gridview control and handle the GridView paging event.

To bind the PageIndexChanging of GridView control, double click on thePageIndexChanging event in the properties of Gridview. It will add the event in HTML source code as well as C# code.

HTML source code for GridView Control

[code:html]
<asp:GridView ID="GridView1"
              runat="server"
              CellPadding="2"
              AllowPaging="True" 
              PageSize="10"
              OnPageIndexChanging="GridView1_PageIndexChanging" 
              AutoGenerateColumns="False"
              Width="500px">
    <Columns>

        <asp:BoundField DataField="ProductID" HeaderText="ProductID">
            <HeaderStyle HorizontalAlign="Left" />
        </asp:BoundField>

        <asp:BoundField DataField="ProductName" HeaderText="Product Name">
            <HeaderStyle HorizontalAlign="Left" Width="200px" />
        </asp:BoundField>

        <asp:BoundField DataField="UnitsInStock" HeaderText="Units In Stock">
            <HeaderStyle HorizontalAlign="Left" />
            <ItemStyle HorizontalAlign="Center" />
        </asp:BoundField>

        <asp:BoundField DataField="UnitPrice" HeaderText="Price Per Unit">
            <HeaderStyle HorizontalAlign="Left" />
            <ItemStyle HorizontalAlign="Center" />
        </asp:BoundField>

    </Columns>

</asp:GridView>
[/code]

You can set the PageSize value as per your requirement. 

C# Code Sample

[code:c#]
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            bindGridView();
    }

    public void bindGridView()
    {
        // string variable to store the connection string
         // defined in ConnectionStrings section of web.config file.
        string connStr = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

        // object created for SqlConnection Class.
        SqlConnection mySQLconnection = new SqlConnection(connStr);

        // if condition that can be used to check the sql connection
        // whether it is already open or not.
        if (mySQLconnection.State == ConnectionState.Closed)
        {
            mySQLconnection.Open();
        }

        SqlCommand mySqlCommand = new SqlCommand("select ProductID, ProductName, UnitsInStock, UnitPrice from products", mySQLconnection);
        SqlDataAdapter mySqlAdapter = new SqlDataAdapter(mySqlCommand);
        DataSet myDataSet = new DataSet();
        mySqlAdapter.Fill(myDataSet);

        GridView1.DataSource = myDataSet;
        GridView1.DataBind();


        // if condition that can be used to check the sql connection
        // if it is open then close it.
        if (mySQLconnection.State == ConnectionState.Open)
        {
            mySQLconnection.Close();
        }
    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        bindGridView();
    }
[/code]

After data binding with GridView control you will get the results as shown in the live sample of Gridview control on page load.