Programmatically creating filters on GridView

rss2
rss2 used Ask the Experts™
on
Hi Experts,

I have an aspx page whereby I call a stored procedure (SQL) and bind the results to a gridview. I am doing all the paging and sorting in the code behind. Now I would like to do filtering in the code behind.

Could you please tell me how to:
1. Create a filter drop down list in the code behind underneath the first row of the dataset (i.e. the header column names) returned by the stored procedure (which is just a select * on a table)..

2. Re-bind the gridview after the filter drop-down list has been changed and a value selected.

3. Also and example  of the above, but uing a textbox and just filtering the gridview on that column's textbox value.. please.

Thank you very much. I'm really stuck.

rss2

ASPX:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Collateral_Management_Web._Default" %>

<!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>Collateral Management Web - Securities Update Form</title>
    <link href="Collateral_Management_Sharepoint.css" rel="Stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div class="menu">
        <asp:GridView ID="GridView1" runat="server"
         AllowSorting="True"
         OnSorting="gvSamples_OnSorting"
         Font-Names="Arial"
         GridLines="None"
         CellPadding="0" ForeColor="#333333" Width="4000px" CssClass="menu">
         <RowStyle BackColor="#F7F6F3" ForeColor="#333333" Width="0.5" />
        <FooterStyle BackColor="red" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="red" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle Width="300" BackColor="red" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
     <AlternatingRowStyle BackColor="White" Font-Names="Arial" ForeColor="#284775" />
        </asp:GridView>
        <a href="" id="FirstPageLink" runat="server">First</a>
        <a href="" id="PrevPageLink" runat="server">Prev</a>
        <asp:Label id="lblGoToPage" Text="Go to page:" runat="server"></asp:Label>
        <asp:TextBox ID="tbGoToPage" runat="server" Width="40"></asp:TextBox>
        <asp:Button ID="btnGo" runat="server" Text="GO" onclick="btnGo_Click" />
        <a href="" id="NextPageLink" runat="server">Next</a>
        <a href="" id="LastPageLink" runat="server">Last</a>
        <asp:Label ID="CurrentPage" runat="server"></asp:Label>
        <asp:Button ID="btnExportGrid" runat="server" Text="Export to Excel" OnClick="BtnExportGrid_Click" />
        <asp:Label ID="lblExportTimeMessage" runat="server" Text="" Font-Size="X-Small"></asp:Label>
    </div>
    </form>
</body>
</html>

Open in new window


C#:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
using System.Data;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

namespace Collateral_Management_Web
{
    public partial class _Default : System.Web.UI.Page
    {
        private int pageCount = 0;
        private int pageIndex = 0;
        private int pageSize = 30;
        private int pageTotalRows = 0;

        string SortField = "";
        /*
         * Maintain the direction of the sort with the ViewState, which is
         * very handy for variables specific to this page. Using ViewState is
         * analogous to having 'hidden' form fields to store data. 
         * This takes 3 values, asc, desc and flip to invert the current value
         */
        string SortDirection
        {
            get
            {
                if (ViewState["SortDirection"] == null)
                {
                    ViewState["SortDirection"] = "ASC";
                }
                return ViewState["SortDirection"].ToString();
            }
            set
            {
                string s = SortDirection;

                if (value == "flip")
                {
                    s = s == "ASC" ? "DESC" : "ASC";
                }
                else
                {
                    s = value;
                }

                ViewState["SortDirection"] = s;
            }
        }


        protected void Page_Load(object sender, EventArgs e)
        {
            if (Request["page"] == null)
            {
                pageIndex = 1;
            }
            else
            {
                pageIndex = Int16.Parse(Request["page"]);
            }

            if (Session["pageCount"] == null)
            {
                // Get the total number of pages in the table
                using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["GBMReportsConnString"].ConnectionString))
                {
                    object returnvalue;
                    cn.Open();
                    SqlCommand cm = cn.CreateCommand();
                    cm.CommandText = "usp_hsbc_liquidity_collateral_mngmt_eligible_assets_web_getTotalpages";
                    cm.CommandType = CommandType.StoredProcedure;
                    SqlParameter totalrows = cm.Parameters.Add("@totalRows", SqlDbType.Int);
                    totalrows.Direction = ParameterDirection.Output;
                    returnvalue = cm.ExecuteScalar();
                    pageCount = Convert.ToInt32(totalrows.Value.ToString());//Convert.ToInt16(cm.ExecuteScalar());
                    pageTotalRows = pageCount;
                    pageCount = pageCount / pageSize;

                    Session["pageCount"] = pageCount;
                    Session["pageTotalRows"] = pageTotalRows;

                }


                }
                else{
                    pageCount = (int)Session["pageCount"];

                    GetCurrentPageData();
                }


            try
            {
                if (Page.IsPostBack == false)
                {
                    GridViewDataLoad();
                    UpdatePagination();
                }
            }
            catch (Exception ex)
            {
                string ErrorMessage = "An error occurred when loading this page.";
                ErrorMessage += Environment.NewLine;
                ErrorMessage += Environment.NewLine;
                ErrorMessage += ex.Message;
            }

            // display the error message somewhere

            // Log the error
        }

        private void GetCurrentPageData()
        {
            if (pageIndex > pageCount || pageIndex < 1) return;

            SqlDataSource sds = new SqlDataSource();
            sds.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["GBMReportsConnString"].ConnectionString;
            sds.SelectCommand = "usp_hsbc_liquidity_collateral_mngmt_eligible_assets_web";
            sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
            sds.SelectParameters.Add("pageIndex", pageIndex.ToString());
            sds.SelectParameters.Add("pageSize", pageSize.ToString());
            
            GridView1.DataSource = sds;
            GridView1.DataBind();

            UpdatePagination();


        }

        private void UpdatePagination()
        {
            FirstPageLink.HRef = "Default.aspx?page=1";
            PrevPageLink.HRef = "Default.aspx?page=" + (pageIndex - 1);
            NextPageLink.HRef = "Default.aspx?page=" + (pageIndex + 1);
            LastPageLink.HRef = "Default.aspx?page=" + pageCount;

            // On first page
            if (pageIndex <= 1)
            {
                FirstPageLink.HRef = String.Empty;
                PrevPageLink.HRef = String.Empty;
            }

            // On last page
            else if (pageIndex >= pageCount)
            {
                NextPageLink.HRef = String.Empty;
                LastPageLink.HRef = String.Empty;
            }

            CurrentPage.Text = "Page " + pageIndex.ToString() + " of " + pageCount.ToString(); 
        }
        private void GridViewDataLoad()
        {
            string spstr = "usp_hsbc_liquidity_collateral_mngmt_eligible_assets_web " + pageIndex.ToString() + ", " + pageSize.ToString();
            string connectionString = ConfigurationManager.ConnectionStrings["GBMReportsConnString"].ConnectionString;
            System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);

            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(spstr, connection);
            System.Data.DataSet ds = new System.Data.DataSet();

            da.Fill(ds);
            Session["ds"] = ds;

            GridView1.DataSource = ds;
            GridView1.DataBind();



        }


        protected void btnGo_Click(object sender, EventArgs e)
        {
            if (tbGoToPage.Text != "")
            {
                int goToPage = Convert.ToInt32(tbGoToPage.Text);

                if (goToPage <= pageCount)
                {
                    pageIndex = goToPage;
                    GridViewDataLoad();
                    UpdatePagination();
                }
            }
        }

        protected void BtnExportGrid_Click(object sender, EventArgs args)
        {
            lblExportTimeMessage.Text = "Export to excel could take up to 10 minutes..";
            pageIndex = 1;
            //pageSize = (int)Session["pageTotalRows"];
            GridViewDataLoad();
            //  pass the grid that for exporting ...
            GridViewExportUtil.Export("Collateral_Management_Securities.xls", GridView1);

            if (Request["page"] == null)
            {
                pageIndex = 1;
            }
            else
            {
                pageIndex = Int16.Parse(Request["page"]);
            }

            pageSize = 30;
            lblExportTimeMessage.Text = "";

        }


        private String GridViewSortExpression
        {
            get { return ViewState["SortExp"] as String ?? ""; }
            set { ViewState["SortExp"] = value; }
        }

        private String GridViewSortDirection
        {
            get { return ViewState["SortDir"] as String ?? ""; }
            set { ViewState["SortDir"] = value; }
        }

        

        protected DataView SortTable(DataTable dt)
        {
            DataView dv = new DataView();
            if (dt != null)
            {
                dv = new DataView(dt);
                if (GridViewSortExpression != "")
                {
                    dv.Sort = GridViewSortExpression + " " +
                    GridViewSortDirection;
                }
            }
            return dv;
        }

        protected void gvSamples_OnSorting(object sender, GridViewSortEventArgs e)
        {
            SortField = e.SortExpression;
            /*
             * The GridViewSortEventArgs argument has a 'SortDirection' element but
             * its not maintained by the control. I think its probably only used when
             * you are using a SQL or object datasource, but I'm not sure. In any
             * even we are responsible for remembering what the sort direction is. 
             * Here we just flip it from ASC to DESC or vice-versa each time the 
             * column is sorted.
             */
            SortDirection = "flip";
        }
        protected override void OnPreRender(EventArgs e)
        {
            base.OnPreRender(e);
            /*
             * In a real application we'd be doing a SQL call with with the 
             * SortField and SortDirection being used in the SELECT statement. 
             * Here we get some sample data and use the very cool DataView 
             * class to perform a sort on the DataTable containing the data.
             */

            //pageIndex = 1;
            //pageSize = (int)Session["pageTotalRows"];

            //string spstr = "usp_hsbc_liquidity_collateral_mngmt_eligible_assets_web " + pageIndex.ToString() + ", " + pageSize.ToString();
            //string connectionString = ConfigurationManager.ConnectionStrings["GBMReportsConnString"].ConnectionString;
            //System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);

            //System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(spstr, connection);
            //System.Data.DataSet ds = new System.Data.DataSet();

            //da.Fill(ds);

            //GridViewDataLoad();


            DataSet ds = (DataSet)Session["ds"];
            DataTable dt = ds.Tables[0];
            DataView dv = new DataView(dt);
            if (SortField != "") dv.Sort = SortField + " " + SortDirection;
            /*
             * Load up datagrid.
             */
            GridView1.DataSource = dv;
            GridView1.DataBind();
            /*
             * Add an up/down arrow to the sort column
             */
            //AddSortImage();
        }



    }
}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Hi,

No, neither one of these really helped very much.

I think I need to access the GridView1_RowCreated function, and when the e.Row.RowType is DataRow, I need to create a textbox for each column.

Alternatively, I would be happy with just one textbox on the page whereby I could search the entire contents of GridView1. Either solution I would be happy with.

As you can see, I am creating the gridview in the code behind.. so I suspect as the gridview is being created, just after the header, I should insert a row, create a textbox for each column, and I'm done. And when the user enters something into the textbox filter, just an <enter> will filter the gridview's contents and rebind the gridview.

Please please help! I am absolutely stuck. I can't seem to find the columns of the e.Row.RowType = DataRow.. e.Row.Cells[0] = 0. :( I've been working on this for ages and really could use your guidance.

Thank you,
rss2

Author

Commented:
I was forced to accept this is a solution because I need to open another question. NOT the most accurate way to accept answers is it, experts-exchange.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial