Part 3 - How to bind a computed column to GridView

Suggested Videos:
Part 1 - How to retrieve data from different databases in asp.net
Part 2 - Display database table metadata in asp.net web application

In this  we will discuss, binding a computed column to a GridView control. Here is the question faced by one of our Youtube channel subscribers in a Dot Net written test.

Populate a grid view with the data selected from table ‘Employees’ having columns
1) SerialNumber
2) EmpCode
3) Name
4) DateOfJoining

If an employee has completed his 5 years tenure of working he will be eligible for Gratuity and for this we need to have a column in gridview ‘Eligible for Gratuity’ with the value ‘Yes’ or ‘No’. If value is ‘Yes’ highlight the column with a different color.

In short, this is how the database table is
bind a computed column to GridView

and the GridView shoud display data as shown below
bind a calculated column to GridView

Step 1: Create the Employees table and populate it with test data using the script below.
Create Table Employees
(
     SerialNumber int primary key identity,
     EmpCode nvarchar(50),
     Name nvarchar(50),
     DateOfJoining date
)
Go

Insert into Employees values ('E01', 'John', '11/20/2001')
Insert into Employees values ('E02', 'Mark', '02/10/2014')
Insert into Employees values ('E03', 'Mary', '08/18/2013')
Insert into Employees values ('E04', 'Stacy', '10/22/2002')
Insert into Employees values ('E05', 'Ben', '12/15/2003')
Go

Step 2: Create a new empty asp.net web application. Add a web form. Drag and drop a GridView control on the web form.

Step 3: Generate event handler method for RowDataBound event of the GridView control.

Step 4: Copy and paste the following code in the code-behind file
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            SqlConnection con = new SqlConnection(CS);
            SqlCommand cmd = new SqlCommand("Select * from Employees", con);
            con.Open();
            SqlDataReader rdr = cmd.ExecuteReader();

            DataTable dt = new DataTable();
            dt.Columns.Add("Serial Number");
            dt.Columns.Add("Employee Code");
            dt.Columns.Add("Name");
            dt.Columns.Add("Date Of Joining");
            dt.Columns.Add("Eligible for Gratuity");

            while (rdr.Read())
            {
                DataRow dr = dt.NewRow();
                dr["Serial Number"] = rdr["SerialNumber"];
                dr["Employee Code"] = rdr["EmpCode"];
                dr["Name"] = rdr["Name"];
                dr["Date Of Joining"] = ((DateTime)rdr["DateOfJoining"]).ToShortDateString();
                dr["Eligible for Gratuity"] = IsEligibleForGratuity((DateTime)rdr["DateOfJoining"]);
                dt.Rows.Add(dr);
            }
            con.Close();

            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

        private string IsEligibleForGratuity(DateTime dateOfJoining)
        {
            double differenceInDays = (DateTime.Now - dateOfJoining).TotalDays;

            if (differenceInDays >= (365 * 5))
                return "Yes";
            else
                return "No";
        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)
            {
                DataRowView drv = (DataRowView)e.Row.DataItem;
                int columnIndex = drv.DataView.Table.Columns["Eligible for Gratuity"].Ordinal;
                if (drv[columnIndex].ToString() == "Yes")
                {
                    e.Row.Cells[columnIndex].BackColor = System.Drawing.Color.Yellow;
                }
            }

            // This code will also work, but the page will crash or will not work as
            // expected when new columns are added or existing columns deleted
            //if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)
            //{
            //    if (e.Row.Cells[4].Text == "Yes")
            //    {
            //        e.Row.Cells[4].BackColor = System.Drawing.Color.Yellow;
            //    }
            //}
        }
    }
}

0 Comments