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


and the GridView shoud display data as shown below


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;

            //    }

            //}

        }

    }

}