rss2
asked on
Programmatically creating filters on GridView
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:
C#:
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>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();
}
}
}
Check the links
http://www.telerik.com/help/wpf/gridview-setting-filters-programmatically.html
http://msdn.microsoft.com/en-us/library/aa479353.aspx
Hope this will help you
http://www.telerik.com/help/wpf/gridview-setting-filters-programmatically.html
http://msdn.microsoft.com/en-us/library/aa479353.aspx
Hope this will help you
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
http://www.telerik.com/community/forums/wpf/gridview/programmatically-set-filters.aspx