Change ASP.Net GridView Cell Background Color based on Cell value while using Dynamic Pivot query using C# and VB.Net

Hey smile,

Please refer below sample

HTML

<asp:GridView ID="GridView1" runat="server" OnDataBound="GridView1_DataBound">
</asp:GridView>

Namespaces

C#

using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Drawing;

VB.Net

Imports System.Drawing
Imports System.Data.SqlClient
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable sTable = GetPivotedDataTable();
        GridView1.Visible = true;
        GridView1.DataSource = sTable;
        GridView1.DataBind();
    }
}

private DataTable GetPivotedDataTable()
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
    string myqry = @"DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
            DECLARE @ColumnName AS NVARCHAR(MAX)
            SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(Date) FROM (SELECT DISTINCT [Date] FROM Pvt) AS [Date]
            SET @DynamicPivotQuery = ';WITH CTE AS(SELECT [Date],Gender,Participation FROM Pvt)
            SELECT ROW_NUMBER() OVER(ORDER BY(Gender))Row,Gender,'+@ColumnName+' FROM CTE
            PIVOT (MAX(Participation) FOR [Date] IN('+@ColumnName+')) p
            ORDER BY Gender DESC'
            EXEC(@DynamicPivotQuery)";

    SqlCommand cmd = new SqlCommand(myqry, con);
    SqlDataAdapter adapt = new SqlDataAdapter();
    DataTable sTable = new DataTable();
    adapt.SelectCommand = cmd;
    adapt.Fill(sTable);
    con.Close();
    return sTable;
}

protected void GridView1_DataBound(object sender, EventArgs e)
{
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        for (int j = 0; j < GridView1.Rows[i].Cells.Count; j++)
        {
            if (GridView1.Rows[i].Cells[j].Text.ToString().ToLower().Equals("male"))
            {
                GridView1.Rows[i].Cells[j].BackColor = Color.Red;
            }
            else if (GridView1.Rows[i].Cells[j].Text.ToString().ToLower().Equals("female"))
            {
                GridView1.Rows[i].Cells[j].BackColor = Color.Yellow;
            }                
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim sTable As DataTable = GetPivotedDataTable()
        GridView1.Visible = True
        GridView1.DataSource = sTable
        GridView1.DataBind()
    End If
End Sub

Private Function GetPivotedDataTable() As DataTable
    Dim con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
    Dim myqry As String = "DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)"
    myqry += "DECLARE @ColumnName AS NVARCHAR(MAX)"
    myqry += " SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(Date) FROM (SELECT DISTINCT [Date] FROM Pvt) AS [Date]"
    myqry += " SET @DynamicPivotQuery = ';WITH CTE AS(SELECT [Date],Gender,Participation FROM Pvt)"
    myqry += " SELECT ROW_NUMBER() OVER(ORDER BY(Gender))Row,Gender,'+@ColumnName+' FROM CTE"
    myqry += " PIVOT (MAX(Participation) FOR [Date] IN('+@ColumnName+')) p"
    myqry += " ORDER BY Gender DESC'"
    myqry += " EXEC(@DynamicPivotQuery)"
    Dim cmd As SqlCommand = New SqlCommand(myqry, con)
    Dim adapt As SqlDataAdapter = New SqlDataAdapter()
    Dim sTable As DataTable = New DataTable()
    adapt.SelectCommand = cmd
    adapt.Fill(sTable)
    con.Close()
    Return sTable
End Function

Protected Sub GridView1_DataBound(ByVal sender As Object, ByVal e As EventArgs)
    For i As Integer = 0 To GridView1.Rows.Count - 1
        For j As Integer = 0 To GridView1.Rows(i).Cells.Count - 1
            If GridView1.Rows(i).Cells(j).Text.ToString().ToLower().Equals("male") Then
                GridView1.Rows(i).Cells(j).BackColor = Color.Red
            ElseIf GridView1.Rows(i).Cells(j).Text.ToString().ToLower().Equals("female") Then
                GridView1.Rows(i).Cells(j).BackColor = Color.Yellow
            End If
        Next
    Next
End Sub

Screenshot