Retrieve dynamic ASP.Net GridView cell value through Column Name using C# and VB.Net

Hey smile,

Please refer below sample.

HTML

<div>
    <asp:GridView ID="gvDetails" runat="server">
        <Columns>
            <asp:TemplateField>
                <HeaderTemplate>
                    <asp:CheckBox ID="checkAll" runat="server" />
                </HeaderTemplate>
                <ItemTemplate>
                    <asp:CheckBox ID="chkSelect" runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <asp:Button Text="Send" runat="server" OnClick="btnSMS_Click" />
</div>

Namespaces

C#

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

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = GetData();
        gvDetails.DataSource = dt;
        gvDetails.DataBind();
    }
}

private DataTable GetData()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string query = @"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 Gender,'+@ColumnName+' FROM CTE
                                            PIVOT (MAX(Participation) FOR [Date] IN('+@ColumnName+')) p
                                            ORDER BY Gender DESC'
                EXEC(@DynamicPivotQuery)";

    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    return dt;
                }
            }
        }
    }
}

protected void btnSMS_Click(object sender, EventArgs e)
{
    string dynamicVal = string.Empty;
    DataTable dt = GetData();
    foreach (GridViewRow row in gvDetails.Rows)
    {
        CheckBox chk = (row.Cells[0].FindControl("chkSelect") as CheckBox);
        if (chk.Checked)
        {
            DataRow[] dr = dt.Select("Gender='" + row.Cells[1].Text + "'");
            for (int i = 0; i < dr.Length; i++)
            {
                for (int k = 0; k < dt.Columns.Count - 1; k++)
                {
                    string columnName = dt.Columns[k + 1].ColumnName;
                    string value = dr[i].ItemArray[k].ToString() == "" ? "Null" : dr[i].ItemArray[k].ToString();
                    if (k >= 1)
                    {
                        dynamicVal += value + " on " + Convert.ToDateTime(columnName).ToString("MMM d yyyy") + ",";
                    }
                    else
                    {
                        dynamicVal += value + ",";
                    }
                }
                dynamicVal += "</br>";
            }
        }
    }
    Response.Write(dynamicVal);
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As DataTable = GetData()
        gvDetails.DataSource = dt
        gvDetails.DataBind()
    End If
End Sub

Private Function GetData() As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim query As String = "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 Gender,'+@ColumnName+' FROM CTE PIVOT (MAX(Participation) FOR [Date] IN('+@ColumnName+')) p ORDER BY Gender DESC' EXEC(@DynamicPivotQuery)"
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Using
End Function

Protected Sub btnSMS_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim dynamicVal As String = String.Empty
    Dim dt As DataTable = GetData()

    For Each row As GridViewRow In gvDetails.Rows
        Dim chk As CheckBox = (TryCast(row.Cells(0).FindControl("chkSelect"), CheckBox))

        If chk.Checked Then
            Dim dr As DataRow() = dt.[Select]("Gender='" & row.Cells(1).Text & "'")

            For i As Integer = 0 To dr.Length - 1

                For k As Integer = 0 To dt.Columns.Count - 1 - 1
                    Dim columnName As String = dt.Columns(k + 1).ColumnName
                    Dim value As String = If(dr(i).ItemArray(k).ToString() = "", "Null", dr(i).ItemArray(k).ToString())

                    If k >= 1 Then
                        dynamicVal += value & " on " & Convert.ToDateTime(columnName).ToString("MMM d yyyy") & ","
                    Else
                        dynamicVal += value & ","
                    End If
                Next

                dynamicVal += "</br>"
            Next
        End If
    Next

    Response.Write(dynamicVal)
End Sub

Screenshot