locked
Multiple filter on repeater control filter using checkbox list using stored procedure RRS feed

  • Question

  • User1152553138 posted
    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="TestFilter.aspx.cs" Inherits="TestFilter" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
    
        <asp:Label ID="LblValue" runat="server" Text="Label"></asp:Label>
    
        <asp:CheckBoxList ID="ChkProductGender" runat="server" AutoPostBack="true" 
            onselectedindexchanged="ChkProductGender_SelectedIndexChanged"   >  
        <asp:ListItem Text="Men" Value="Men"></asp:ListItem>
        <asp:ListItem Text="Women" Value="Women"></asp:ListItem>
        <asp:ListItem Text="Kids" Value="Kids"></asp:ListItem>
        <asp:ListItem Text="Children" Value="Children"></asp:ListItem>
        </asp:CheckBoxList>
    
       <asp:Repeater ID="R1" runat="server" >                          
       <ItemTemplate>							
    	<asp:Label ID="LblProduct" runat="server"  Text='<%# Eval("Product") %>' ></asp:Label>
        								
        <asp:Label ID="Label1" runat="server"  Text='<%# Eval("Gender") %>' ></asp:Label>	 
        <br />
        </ItemTemplate>
        </asp:Repeater>
    
         <asp:Repeater ID="rptPager" runat="server">    
           <ItemTemplate>   
    
           <asp:LinkButton ID="lnkPage" runat="server" class="active" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
                CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
                OnClick="Page_Changed" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>   
     </ItemTemplate>
        </asp:Repeater>
    
       
    
    
    
        </form>
    </body>
    </html>
    
    using System;
    using System.Data;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    using System.Data.SqlClient;
    using System.Collections.Specialized;
    using System.Text;
    using System.IO;
    using System.Collections.Generic;
    
    public partial class TestFilter : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.GetProduct(1);
            }
        }
    
    
    
    
        protected void Button1_Click(object sender, EventArgs e)
        {
    
        }
    
        private int PageSize = 2;
    
        private void GetProduct(int pageIndex)
        {
            string constring = ConfigurationManager.ConnectionStrings["Con"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constring))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT [ID],[Product],[Gender] FROM [dbo].[TestTable]", con))
                {
                    //cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
                    //cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    //cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
                    //cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                    con.Open();
                    IDataReader idr = cmd.ExecuteReader();
                    R1.DataSource = idr;
                    R1.DataBind();
                    //idr.Close();
                    //con.Close();
                    //int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
                    //this.PopulatePager(recordCount, pageIndex);
                }
            }
        }
    
    
        
    
        private void PopulatePager(int recordCount, int currentPage)
        {
            double dblPageCount = (double)((decimal)recordCount / (decimal)PageSize);
            int pageCount = (int)Math.Ceiling(dblPageCount);
    
            List<ListItem> pages = new List<ListItem>();
    
    
    
            if (pageCount > 0)
            {
                pages.Add(new ListItem("<", "1", currentPage > 1));
                if (currentPage != 1)
                {
                    pages.Add(new ListItem("Pre", (currentPage - 1).ToString()));
                }
                if (pageCount < 4)
                {
                    for (int i = 1; i <= pageCount; i++)
                    {
                        pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                }
                else if (currentPage < 4)
                {
                    for (int i = 1; i <= 4; i++)
                    {
                        pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                    pages.Add(new ListItem("...", (currentPage).ToString(), false));
                }
                else if (currentPage > pageCount - 4)
                {
                    pages.Add(new ListItem("...", (currentPage).ToString(), false));
                    for (int i = currentPage - 1; i <= pageCount; i++)
                    {
                        pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                }
                else
                {
                    pages.Add(new ListItem("...", (currentPage).ToString(), false));
                    for (int i = currentPage - 2; i <= currentPage + 2; i++)
                    {
                        pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
                    }
                    pages.Add(new ListItem("...", (currentPage).ToString(), false));
                }
                if (currentPage != pageCount)
                {
                    pages.Add(new ListItem("Next", (currentPage + 1).ToString()));
                }
                pages.Add(new ListItem(">", pageCount.ToString(), currentPage < pageCount));
            }
            rptPager.DataSource = pages;
            rptPager.DataBind();
    
            //rptPager1.DataSource = pages;
            //rptPager1.DataBind();
        }
    
        protected void Page_Changed(object sender, EventArgs e)
        {
            int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
            this.GetProduct(pageIndex);
        }
    
        protected void ChkProductGender_SelectedIndexChanged(object sender, EventArgs e)
        {
            string conString = ConfigurationManager.ConnectionStrings["Con"].ConnectionString;
            string query = "SELECT [ID],[Product],[Gender] FROM [dbo].[TestTable]";
           
    
            string condition = string.Empty;
            foreach (ListItem item in ChkProductGender.Items)
            {
                condition += item.Selected ? string.Format("'{0}',", item.Value) : "";
            }
            if (!string.IsNullOrEmpty(condition))
            {
                condition = string.Format(" where Gender in ({0})", condition.Substring(0, condition.Length - 1));
            }
            SqlCommand cmd = new SqlCommand(query + condition);
            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
    
                    sda.SelectCommand = cmd;
                    using (DataSet ds = new DataSet())
                    {
                        sda.Fill(ds);
                        R1.DataSource = ds;
                        R1.DataBind();
                    }
                }
            }
        }
    }

    Everything is working fine ... I need to filter using stored procedure ... How do i pass checkbox list selected value into stored procedure ....

    Help me

    Friday, March 22, 2019 12:02 PM

All replies

  • User-2054057000 posted

    You are running a string query -

    SELECT [ID],[Product],[Gender] FROM [dbo].[TestTable]

    You need to replace it with a stored procedure. 

    The necessary code to be added in the C# function would be:

    SqlCommand cmd = new SqlCommand();

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.CommandText = "sp_GetProductByCustomPaging";

    cmd.Parameters.AddWithValue("@Gender", GenderValue);

    Check this tutorial which binds GridView by executing a stored procedure. You just need to create a stored procedure and then inside the ChkProductGender_SelectedIndexChanged method you execute the stored procedure.

    Friday, March 22, 2019 6:43 PM
  • User1152553138 posted

    You are running a string query -

    SELECT [ID],[Product],[Gender] FROM [dbo].[TestTable]

    You need to replace it with a stored procedure. 

    The necessary code to be added in the C# function would be:

    SqlCommand cmd = new SqlCommand();

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.CommandText = "sp_GetProductByCustomPaging";

    cmd.Parameters.AddWithValue("@Gender", GenderValue);

    Check this tutorial which binds GridView by executing a stored procedure. You just need to create a stored procedure and then inside the ChkProductGender_SelectedIndexChanged method you execute the stored procedure.

    Thank for the reply ....

    cmd.Parameters.AddWithValue("@Gender", GenderValue); 

    I do i pass checkbox list as parameter in the above query
     
    string condition = string.Empty; foreach (ListItem item in ChkProductGender.Items) { condition += item.Selected ? string.Format("'{0}',", item.Value) : ""; } if (!string.IsNullOrEmpty(condition)) { condition = string.Format(" where Gender in ({0})", condition.Substring(0, condition.Length - 1)); }

    Saturday, March 23, 2019 2:42 AM
  • User-2054057000 posted

    You will have to first create a stored procedure in your SQL Server like this:

    CREATE PROCEDURE [dbo].[sp_GetProduct]  
        @Gender VARCHAR(50)
    AS           
    BEGIN
    exec('SELECT [ID],[Product],[Gender] FROM [dbo].[TestTable] WHERE Gender in ('+ @Gender +')')
    END

    Note you have to pass the @Gender Parameter values in comma separated manner. 

    Then you execute this sp from your C# function.

    Saturday, March 23, 2019 9:28 AM
  • User-893317190 posted

    Hi Ashraf007,

    Since you are the number of your parameter is not static, it is not suitable to execute your sql using stored procedure, because the count of parameter in store procedure is static.

    So I suggest you could  write your code as follows  to dynamically change where clause.

      private static string constr = ConfigurationManager.ConnectionStrings["EntityDb"].ConnectionString;
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void CheckBoxList1_SelectedIndexChanged(object sender, EventArgs e)
            {
                if (CheckBoxList1.SelectedIndex == -1)// if null is selected, return empty table
                {
                    DataTable table = new DataTable();
                  
                    GridView1.DataSource = table;
                    GridView1.DataBind();
                    return;
                }
    
                using (SqlDataAdapter adapter = new SqlDataAdapter("",constr ))
                    {
                    
                    string commandText = "select * from customer where name in (";
                    bool isFirst = true;
                    int count = 1;
                
                    foreach (ListItem item in CheckBoxList1.Items)
                    {
                        if (item.Selected)
                        {
                            if (isFirst)// if is the first parameter
                            {
                                commandText += "@parameter" + count; // name the parameter, the parameter name will be parameter1, parameter2 ...
                                adapter.SelectCommand.Parameters.AddWithValue("@parameter" + count, item.Value);
                               
                                isFirst = false; // then all the other parameter is not first
                            }
                            else
                            {
                                commandText += ",@parameter" + count;  // other parameter should add ,
                                adapter.SelectCommand.Parameters.AddWithValue("@parameter" + count, item.Value);
                              
                            }
    
                            count++; // plus one
                        }
                    }
                    commandText += ")"; // add )
    
                    adapter.SelectCommand.CommandText = commandText;
                   
    
                    DataTable table = new DataTable();
                        adapter.Fill(table);
                    GridView1.DataSource = table;
                    GridView1.DataBind();
                    }
                
            }

    Aspx.

    <form id="form1" runat="server">
            <asp:CheckBoxList ID="CheckBoxList1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="CheckBoxList1_SelectedIndexChanged">
                <asp:ListItem Text="A" Value="A"></asp:ListItem>
        <asp:ListItem Text="B" Value="B"></asp:ListItem>
        <asp:ListItem Text="C" Value="C"></asp:ListItem>
        <asp:ListItem Text="D" Value="D"></asp:ListItem>
    
            </asp:CheckBoxList>
    
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        </form>

    The result.

    Best regards,

    Ackerly Xu

    Monday, March 25, 2019 3:24 AM