Implementing Cumulative total and Select All functionality with Events in ASP.NET GridView

• Download EventsOfGridView_V2.zip
• Download source code 

Introduction 

There are many scenarios in which the data can be bound in a Gridview, in which various operation other than provided by default by Gridview needs to be implemented. In this article we will try to bind such operations with some more features.

Background

This article describes various events of the Gridview and in addition some more functionalities which are needed in most scenarios but not provided by ASP.NET Gridview control by default. This article is written for beginner's i.e. We will be using a lot of looping and basic language constructs and most of the code can be optimized.

Using the code 

We will be implementing the following additional functionalities with a data bound gridview.

1. Getting the sum of any column in footer of gridview. 
2. Possibility to check few or all records in gridview for bulk operations.
3. Inserting new record in database and bound in gridview. 
4. Validations using Regular Expression Validators. 

Lets start by looking at the Database schema which we will be using in our demo implementation. We have a simple Database with a single table named tmp_table. It consists of 4 columns

In the table shown above:

1. ID which is an identity field
2. uname which is nvarchar(50) datatype
3. totalMarks which is of float datatype and the last one is
4. selectedItem with bit datatype.

First we will create a function named BindGridView() in which the details fetched in the Datatable will be bound to the Gridview.

protected void BindGridView()
{
    DataTable dt = null;
    using (conn = new SqlConnection(ConfigurationManager.ConnectionStrings["tempdbConn"].ConnectionString))
    {
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select row_number() OVER (ORDER BY id) " + 
                "AS sno,id,uname,totalMarks,selectedItem from tmp_table";
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                dt = new DataTable();
                da.Fill(dt);
            }
        }
    }
    GridView1.DataSource = dt;
    GridView1.DataBind();
}

Now in RowDataBound event of Gridview, we will calculate the grand total of marks column and display them in the Footer of the respective column.

if (e.Row.RowType == DataControlRowType.DataRow)
{
    lgTots += Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "totalMarks"));
}
if (e.Row.RowType == DataControlRowType.Footer)
{
    Label lgTotal = (Label)e.Row.FindControl("lblGrandTotal");
    lgTotal.Text = lgTots.ToString();
}

Now to look into the other functionality i.e. to Select few or All CheckBox. We will do that by having a Checkbox for Select All in Header and selecting/unselecting it will result in selecting/unselecting all the records respectively.

CheckBox chkA = GridView1.HeaderRow.FindControl("chekSelectALL") as CheckBox;
foreach (GridViewRow gv in GridView1.Rows)
{
    CheckBox chkS = gv.FindControl("chekSelect") as CheckBox;
    if (chkA.Checked)
    {
        chkS.Checked = true;
    }
    else
    {
        chkS.Checked = false;
    }
}     

Now the additional functionalities which we originally planned to implement are done. For the sake of completeness, we will also implement the usual functionalities and events like PageIndexChanging, RowEditing, RowCancelingEdit, RowDeleting, RowUpdating for the Gridview.

Now when we run the application, we can see that all the usual functionalities of this Gridview are working along with the added functionalities that we have implemented.

In the next version we will see how to insert a new record into database, for this implementation lets have a TemplateField Texbox for inserting Name and Marks simultaneously in Footer. OnClick event of LinkButton lnkInsert, we will write the following code:

protected void lnkInsert_Click(object sender, EventArgs e)
    {
        string nme = string.Empty;
        TextBox tb = GridView1.FooterRow.FindControl("txtName") as TextBox;

        if (tb != null)
        {
            nme = tb.Text.Trim();
        }

        string mrks = string.Empty;
        tb = null;
        tb = GridView1.FooterRow.FindControl("txtTotalMarks") as TextBox;

        if (tb != null)
        {
            mrks = tb.Text.Trim();
        }

        bool chkSele = false;
        CheckBox chk = GridView1.FooterRow.FindControl("chekSelect") as CheckBox;

        if (chk != null)
        {
            if (chk.Checked == true)
            {
                chkSele = true;
            }
            else
            {
                chkSele = false;
            }
        }
        using (conn = new SqlConnection(ConfigurationManager.ConnectionStrings["tempdbConn"].ConnectionString))
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "Insert into tmp_table(uname,totalMarks,selectedItem) values(@nme,@mrks,@selectItem)";
                cmd.Parameters.AddWithValue("@nme", nme);
                cmd.Parameters.AddWithValue("@mrks", mrks);
                cmd.Parameters.AddWithValue("@selectItem", chkSele);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }   
        BindGridView();
    }

Implemented the validations

Points of Interest

In this article we tried to implement some commonly needed functionalities with a Gridview control in addition to the default functionalities provided by ASP.NET. The code can be improved in many ways but since it is written for the beginner programmers, I tried to keep the logic simple. I will implement more such functionalities and update this article.

History

• 01 November 2012: First version. 
• 02 November 2012: Second version
  • Implements the functionality to insert new record. 
  • Implemented validations using Regular Expression Validators.