Filter ASP.Net GridView with dynamic Pivot query using C# and VB.Net

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on Mar 28, 2019 11:44 PM
Forum: Data Controls
Answer: 1
Views: 8024
Sample Code: Download

I write pivot dynamic query to get record

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(FeesDetail) FROM (SELECT DISTINCT FeesDetail FROM tblFeesDetail where Class=@CId and Section=@SId) AS FeesHead
SET @DynamicPivotQuery = ';WITH CTE AS(SELECT dis.AdmissionNo,ReferenceNo,dis.Year,dis.Class,dis.Section,SName,FName,ParentID,FeesDetail,Amount,Discount,ISNULL(DisAmount,0) - ISNULL(Discount,0) as [ActualFees] FROM tblFeesDetail as fd inner join tblDiscount as dis on fd.Class=dis.Class and fd.Section=dis.Section inner join tblStdReg as sr on dis.AdmissionNo=sr.AdmissionNo and Active_Status = ''Active'' )
    SELECT AdmissionNo,ReferenceNo,Year,Class,Section,SName,FName,ParentID,'+@ColumnName+',Discount,ActualFees  FROM CTE
    PIVOT (MAX(Amount)
    FOR FeesDetail IN('+@ColumnName+')) p
    where Class=(''Five'') and Section=(''A'')                        '
EXEC(@DynamicPivotQuery);

this query is working fine and showing output

Now I want to get class and section wise data of student. I used following code but nothing is shown how to fix it

    protected void ddlSection_SelectedIndexChanged(object sender, EventArgs e)
    {
       this.BindGridSec();

    }
    protected void BindGridSec()
    {
        con = new SqlDbConnect();
        con.SqlQuery(@"DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
                        DECLARE @ColumnName AS NVARCHAR(MAX)
                        SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(FeesDetail) FROM (SELECT DISTINCT FeesDetail FROM tblFeesDetail where Class=@CId and Section=@SId) AS FeesHead
                        SET @DynamicPivotQuery = ';WITH CTE AS(SELECT dis.AdmissionNo,ReferenceNo,dis.Year,dis.Class,dis.Section,SName,FName,ParentID,FeesDetail,Amount,Discount,ISNULL(DisAmount,0) - ISNULL(Discount,0) as [ActualFees] FROM tblFeesDetail as fd inner join tblDiscount as dis on fd.Class=dis.Class and fd.Section=dis.Section inner join tblStdReg as sr on dis.AdmissionNo=sr.AdmissionNo and Active_Status = ''Active'' )
                        SELECT AdmissionNo,ReferenceNo,Year,Class,Section,SName,FName,ParentID,'+@ColumnName+',Discount,ActualFees  FROM CTE
                        PIVOT (MAX(Amount)
                        FOR FeesDetail IN('+@ColumnName+')) p
                        where Class=(''+@CId+'') and Section=(''+@SId+'')                        '
                        EXEC(@DynamicPivotQuery);");
        
        con.Cmd.Parameters.Add(new SqlParameter("@CId", ddlClass.SelectedItem.ToString()));
        con.Cmd.Parameters.Add(new SqlParameter("@SId", ddlSection.SelectedItem.ToString()));
       
        adapt.SelectCommand = con.Cmd;
        adapt.Fill(sTable);
        if (sTable.Rows.Count > 0)
        {
            GridView1.Visible = true;
            GridView1.DataSource = sTable;
            GridView1.DataBind();
            lblSTotal.Text = GridView1.Rows.Count.ToString();
        }

pandeyis...

0 3

Reply
ANSWER
User: pandeyism
Replied: on Mar 29, 2019 01:43 AM
Report

Hi smile,

Refer below sample.

HTML

<asp:DropDownList ID="ddlGender" runat="server" AutoPostBack="true" OnSelectedIndexChanged="Filter">
    <asp:ListItem Text="Select" Value="0"></asp:ListItem>
    <asp:ListItem Text="Male"></asp:ListItem>
    <asp:ListItem Text="Female"></asp:ListItem>
</asp:DropDownList>
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

Namespaces

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

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

protected void Filter(object sender, EventArgs e)
{
    DataTable dt = GetData(ddlGender.SelectedItem.Text);
    GridView1.DataSource = dt;
    GridView1.DataBind();
}

private DataTable GetData(string value)
{
    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
                                            where Gender='''+@gender+''''
                EXEC(@DynamicPivotQuery)";
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@gender", value);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }
    }
}

VB.Net

Protected Sub Filter(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = GetData(ddlGender.SelectedItem.Text)
    GridView1.DataSource = dt
    GridView1.DataBind()
End Sub

Private Function GetData(ByVal value As String) As DataTable
    Dim query As String = "DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)"
    query += "DECLARE @ColumnName AS NVARCHAR(MAX)"
    query += "     SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(Date) FROM (SELECT DISTINCT [Date] FROM Pvt) AS [Date]"
    query += "      SET @DynamicPivotQuery = '; WITH CTE AS"
    query += "                             ("
    query += "                              SELECT [Date],Gender,Participation FROM Pvt"
    query += "                          )"
    query += "                          SELECT Gender,'+@ColumnName+' FROM CTE"
    query += "                         PIVOT (MAX(Participation) FOR [Date] IN('+@ColumnName+')) p"
    query += "    where Gender='''+@gender+''''"
    query += " EXEC(@DynamicPivotQuery)"

    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            cmd.Parameters.AddWithValue("@gender", value)
            Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

Screenshot

Filter ASP.Net GridView with dynamic Pivot query using C# and VB.Net

Company

Explore

Follow us

Contact Us