Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Part 3 - How to bind a computed column to GridView

Suggested Videos:
Part 1 - How to retrieve data from different databases in
Part 2 - Display database table metadata in 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
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

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

Step 2: Create a new empty 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);
            SqlDataReader rdr = cmd.ExecuteReader();

            DataTable dt = new DataTable();
            dt.Columns.Add("Serial Number");
            dt.Columns.Add("Employee Code");
            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"]);

            GridView1.DataSource = dt;

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

            if (differenceInDays >= (365 * 5))
                return "Yes";
                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;
            //    }

dot net written test questions answers

1 comment:

  1. what is the pupose of :: double colon symbol


If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.