如何在选定的下拉列表值上分页gridview [英] How to paging gridview on selected dropdown list value
本文介绍了如何在选定的下拉列表值上分页gridview的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在我的webform中，我使用pageload中的数据集从sqlserver绑定gridview，另一个控件是值为5,10,100,500的下拉列表...那么如何在选定的下拉列表值上分页gridview？
/ *页面来源* /
In my webform i am binding gridview with complete records from sqlserver using dataset in pageload, and another control is dropdown list with values 5,10,100,500... So How to paging gridview on selected dropdown list value?
/*Page source*/
<asp:DropDownList ID="ddlStatus" runat="server" class="col-md-2 drop">
<asp:ListItem>All Status</asp:ListItem>
<asp:ListItem>Active</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
<asp:ListItem>AutoHold</asp:ListItem>
<asp:ListItem>Free</asp:ListItem>
<asp:ListItem>Suspend</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlColumns" runat="server" AutoPostBack="true"
class="col-md-2 drop" onselectedindexchanged="ddlColumns_SelectedIndexChanged">
<asp:ListItem>All</asp:ListItem>
<asp:ListItem Value="UserId">Account Number</asp:ListItem>
<asp:ListItem Value="Name">Customer Name</asp:ListItem>
<asp:ListItem Value="MobNo">Mobile Number</asp:ListItem>
<asp:ListItem Value="UserName">User Name</asp:ListItem>
<asp:ListItem Value="PackagePeriod">Package Period</asp:ListItem>
<asp:ListItem Value="PackageName">Package Name</asp:ListItem>
<asp:ListItem Value="EmpName">Attended User</asp:ListItem>
<asp:ListItem Value="AreaName">Customers By Area</asp:ListItem>
<asp:ListItem Value="BalAreaName">Balance By Area</asp:ListItem>
<asp:ListItem Value="Balance">Amount Of</asp:ListItem>
<asp:ListItem Value="LastPaidDate">Due From</asp:ListItem>
<asp:ListItem Value="BoxTypeName">Box Type</asp:ListItem>
<asp:ListItem Value="LastUpdateTime">Last Account Updated Time</asp:ListItem>
<asp:ListItem Value="RecordsBySerialNumber">Records By Serial Number</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlColumValue" runat="server" Visible="false" class="col-md-2 "
AutoPostBack="True"></asp:DropDownList>
<asp:TextBox ID="txtColumValue" runat="server" class="col-md-2" Visible="false"></asp:TextBox>
<asp:GridView ID="gridCustomer" runat="server" AutoGenerateColumns="False" CssClass="grid-customer"
onrowcommand="gridCustomer_RowCommand" AllowSorting="True"
onrowdatabound="gridCustomer_RowDataBound" >
<Columns>
<asp:TemplateField HeaderText="#">
<ItemTemplate>
<%#Container.DataItemIndex + 1%>
</ItemTemplate>
<ItemStyle />
</asp:TemplateField>
<asp:BoundField DataField="UserId" HeaderText="Account-No" />
<asp:TemplateField HeaderText="Name">
<ItemTemplate>
<asp:LinkButton ID="linkName" runat="server" Text='<%#Eval("Name")%>' CommandArgument='<%#Eval("UserId")%>' CommandName="VIEW"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="UserName" HeaderText="User-Name" />
<asp:TemplateField HeaderText="Status">
<ItemTemplate>
<asp:Label ID="lblStatus" runat="server" Text='<%#Eval("Status")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="PackagePeriod" HeaderText="Package-Period" />
<asp:BoundField DataField="PackageName" HeaderText="Package-Name" />
<asp:BoundField DataField="ActivationDate" HeaderText="Activation-Date" />
<asp:BoundField DataField="OldExpiryDate" HeaderText="Account-Expiry" />
<asp:BoundField DataField="Balance" HeaderText="Balance" />
<asp:BoundField DataField="PyingAmount" HeaderText="Last-Paid-Amount" />
<asp:BoundField DataField="LastPaidDate" HeaderText="Last-Paid-Date" />
<asp:BoundField DataField="LastUpdateTime" HeaderText="Last-Acc-Updated-Time" />
<asp:BoundField DataField="AreaName" HeaderText="Area" />
<asp:BoundField DataField="MobNo" HeaderText="Mobile" />
<asp:BoundField DataField="EmpName" HeaderText="Attended-User" />
<asp:BoundField DataField="Address" HeaderText="Address" />
<asp:BoundField DataField="CreatedDate" HeaderText="Created-Date" />
<asp:BoundField DataField="BoxTypeName" HeaderText="Box Type Name" />
</Columns>
</asp:GridView>
<asp:DropDownList ID="ddlPaging" runat="server" AutoPostBack="True" class="rec-per-page"
onselectedindexchanged="PageSize_Changed">
<asp:ListItem>5</asp:ListItem>
<asp:ListItem Selected="True">10</asp:ListItem>
<asp:ListItem>25</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
<asp:ListItem>100</asp:ListItem>
<asp:ListItem>250</asp:ListItem>
<asp:ListItem>500</asp:ListItem>
<asp:ListItem>1000</asp:ListItem>
</asp:DropDownList>
My store procedure query
$b $b
My store procedure query
ALTER PROCEDURE [dbo].[sp_Get_CustInfoSerach]
@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
,@bal float OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cmd AS NVARCHAR(max)
CREATE TABLE #result
(
rownum INT,
userid INT,
NAME VARCHAR(100),
username VARCHAR(100),
status VARCHAR(15),
packageperiod VARCHAR(15),
packagename VARCHAR(100),
activationdate DATETIME,
oldexpirydate varchar(100),
balance float,
pyingamount NUMERIC(22, 4),
lastpaiddate DATETIME,
lastupdatetime DATETIME,
areaname VARCHAR(100),
mobno VARCHAR(20),
empname VARCHAR(100),
address VARCHAR(5000),
createddate DATETIME,
boxTypeName varchar(100)
)
SET @cmd ='
SELECT ROW_NUMBER() OVER (ORDER BY C_Register.UserId desc )AS RowNumber,
C_Register.UserId, C_Register.Name, C_Register.UserName,
C_Register.Status, Packages.PackagePeriod, Packages.PackageName,
C_Register.ActivationDate,Replace(CONVERT(VARCHAR(20), Receive_Payment.OldExpiryDate, 106),'' '',''-'') AS OldExpiryDate,
Receive_Payment.Balance, Receive_Payment.PyingAmount,
Receive_Payment.LastPaidDate, C_Register.LastUpdateTime,
Area.AreaName, C_Register.MobNo, Employee.EmpName,
C_Register.Address,C_Register.CreatedDate,BoxType.BoxTypeName
FROM C_Register INNER JOIN Receive_Payment ON C_Register.UserId = Receive_Payment.UserId
INNER JOIN Area ON C_Register.AreaId = Area.AreaId
INNER JOIN Employee ON Receive_Payment.EmpId = Employee.EmpId
INNER JOIN Packages ON Receive_Payment.PackageId = Packages.PackageId
INNER JOIN BoxType ON C_Register.BoxTypeId = BoxType.BoxTypeId
where C_Register.AccountExpiry= Receive_Payment.OldExpiryDate'
Insert into #result
EXEC(@cmd)
SELECT @RecordCount = COUNT(*)
FROM #result
SELECT * FROM #result
WHERE rownum BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
select @bal = sum(balance) FROM #result
--DROP TABLE #result
END
Code behind side
Code behind side
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack == true)
{
gridCustAllInfoBind(1);
}
}
protected void Page_Changed(object sender, EventArgs e)
{
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
//this.gridCustInfoBind(pageIndex);
btnSearchCust_Click(sender, e);
}
private void PopulatePager(int recordCount, int currentPage)
{
double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPaging.SelectedValue));
int pageCount = (int)Math.Ceiling(dblPageCount);
List<ListItem> pages = new List<ListItem>();
if (pageCount > 0)
{
pages.Add(new ListItem("First", "1", currentPage > 1));
for (int i = 1; i <= pageCount; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
}
rptPager.DataSource = pages;
rptPager.DataBind();
}
protected void PageSize_Changed(object sender, EventArgs e)
{
//this.gridCustInfoBind(1);
btnSearchCust_Click(sender, e);
}
public void gridCustAllInfoBind(int pageIndex)
{
try
{
//open the db connection if it is closed...
if (connection.State == ConnectionState.Closed)
connection.Open();
command = new SqlCommand();
command.CommandText = "sp_Get_CustInfoSerach";
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
command.Parameters.AddWithValue("@PageIndex", pageIndex);
command.Parameters.AddWithValue("@PageSize", int.Parse(ddlPaging.SelectedValue));
command.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
command.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
//SqlParameter outParam = command.Parameters.Add("@bal", SqlDbType.Float);
//outParam.Direction = ParameterDirection.Output;
command.Parameters.Add("@bal", SqlDbType.Int, 4);
command.Parameters["@bal"].Direction = ParameterDirection.Output;
SqlDataAdapter daAcc = new SqlDataAdapter(command);
DataSet dsAcc = new DataSet();
daAcc.Fill(dsAcc);
//IDataReader idr = command.ExecuteReader();
if (dsAcc.Tables[0].Rows.Count == 0)
{
dsAcc.Tables[0].Rows.Add(dsAcc.Tables[0].NewRow());
gridCustomer.DataSource = dsAcc;
gridCustomer.DataBind();
int columncount = gridCustomer.Rows[0].Cells.Count;
gridCustomer.Rows[0].Cells.Clear();
gridCustomer.Rows[0].Cells.Add(new TableCell());
gridCustomer.Rows[0].Cells[0].ColumnSpan = columncount;
gridCustomer.Rows[0].Cells[0].Text = "No Records Found";
}
else
{
//gridCustomer.DataSource = idr;
gridCustomer.DataSource = dsAcc;
gridCustomer.DataBind();
}
//idr.Close();
connection.Close();
if (dsAcc.Tables[0].Rows.Count == 0)
{
txtPendings.Text = "Total Pendings";
txtPendings.Visible = true;
}
else
{
int recordCount = Convert.ToInt32(command.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
txtPendings.Text = "Pendings = " + (command.Parameters["@bal"].Value).ToString();
txtPendings.Visible = true;
}
}
catch (Exception ex)
{
lblMessageCustSerach.Text = ex.Message;
lblMessageCustSerach.Visible = true;
}
finally //Close db Connection if it is open....
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
protected void btnSearchCust_Click(object sender, EventArgs e)
{
if (ddlStatus.SelectedIndex == 0 && ddlColumns.SelectedIndex == 0)
{
gridCustAllInfoBind(1);
}
else if (ddlStatus.SelectedIndex != 0 && ddlColumns.SelectedIndex == 0)
{
gridCustwithStatBinds(1);
}
else if (ddlStatus.SelectedIndex == 0 && ddlColumns.SelectedIndex != 0 && ddlColumns.SelectedIndex != 1 && ddlColumns.SelectedIndex != 10 && ddlColumns.SelectedIndex != 11 && ddlColumns.SelectedIndex != 13 && ddlColumns.SelectedIndex != 14 && ddlColumns.SelectedIndex != 9)
{
gridWithoutStatusBind(1);
}
else if (ddlStatus.SelectedIndex != 0 && ddlColumns.SelectedIndex != 1 && ddlColumns.SelectedIndex != 0 && ddlColumns.SelectedIndex != 10 && ddlColumns.SelectedIndex != 11 && ddlColumns.SelectedIndex != 13 && ddlColumns.SelectedIndex != 14 && ddlColumns.SelectedIndex != 9)
{
gridCustInfoBinds(1);
}
else if (ddlStatus.SelectedIndex == 0 && ddlColumns.SelectedIndex == 1)
{
gridCustwithUidBinds(1);
}
else if (ddlStatus.SelectedIndex != 0 && ddlColumns.SelectedIndex == 1)
{
gridCustBinds(1);
}
else if (ddlStatus.SelectedIndex == 0 && ddlColumns.SelectedIndex == 10)
{
gridCustwithBalBinds(1);
}
else if (ddlStatus.SelectedIndex != 0 && ddlColumns.SelectedIndex == 10)
{
gridCustwithBalStatBinds(1);
}
else if (ddlStatus.SelectedIndex == 0 && ddlColumns.SelectedIndex == 11)
{
gridCustwithDueBinds(1);
}
else if (ddlStatus.SelectedIndex != 0 && ddlColumns.SelectedIndex == 11)
{
gridCustwithDueStatBinds(1);
}
else if (ddlStatus.SelectedIndex == 0 && ddlColumns.SelectedIndex == 13)
{
gridCustAscLastAccUpBinds(1);
}
else if (ddlStatus.SelectedIndex != 0 && ddlColumns.SelectedIndex == 13)
{
gridCustStatAscLastAccUpBinds(1);
}
else if (ddlStatus.SelectedIndex == 0 && ddlColumns.SelectedIndex == 14)
{
gridAscCustBinds(1);
}
else if (ddlStatus.SelectedIndex != 0 && ddlColumns.SelectedIndex == 14)
{
gridAscCustStatBinds(1);
}
else if (ddlStatus.SelectedIndex == 0 && ddlColumns.SelectedIndex == 9)
{
gridCustAreaBinds(1);
}
else if (ddlStatus.SelectedIndex != 0 && ddlColumns.SelectedIndex == 9)
{
gridCustAreaStatBinds(1);
}
gridCustomer.Columns[18].Visible = false;
}
protected void ddlPaging_SelectedIndexChanged(object sender, EventArgs e)
{
btnSearchCust_Click(sender, e);
}
public void selectedColChange()
{
try
{
if (ddlColumns.SelectedIndex == 0)
{
ddlColumValue.Items.Clear();
txtColumValue.Text = "";
ddlColumValue.Visible = false;
txtColumValue.Visible = false;
}
else if (ddlColumns.SelectedIndex == 1)
{
ddlColumValue.Items.Clear();
txtColumValue.Text = "";
ddlColumValue.Visible = false;
txtColumValue.Visible = true;
}
else if (ddlColumns.SelectedIndex == 2)
{
ddlColumValue.Items.Clear();
txtColumValue.Text = "";
ddlColumValue.Visible = false;
txtColumValue.Visible = true;
}
else if (ddlColumns.SelectedIndex == 3)
{
ddlColumValue.Items.Clear();
txtColumValue.Text = "";
ddlColumValue.Visible = false;
txtColumValue.Visible = true;
}
else if (ddlColumns.SelectedIndex == 4)
{
ddlColumValue.Items.Clear();
txtColumValue.Text = "";
ddlColumValue.Visible = false;
txtColumValue.Visible = true;
}
else if (ddlColumns.SelectedIndex == 5)
{
ddlColumValue.Items.Clear();
ddlColumValue.Visible = true;
txtColumValue.Visible = false;
ddlColumValue.Items.Add(new ListItem("--Select--", "0"));
ddlColumValue.Items.Add(new ListItem("Monthly", "Monthly"));
ddlColumValue.Items.Add(new ListItem("Quarterly", "Quarterly"));
ddlColumValue.Items.Add(new ListItem("Half Yearly", "Half Yearly"));
ddlColumValue.Items.Add(new ListItem("Yearly", "Yearly"));
//ddlColumValue.Controls.Add(ddl);
}
else if (ddlColumns.SelectedIndex == 6)
{
ddlColumValue.Items.Clear();
ddlColumValue.Visible = true;
txtColumValue.Visible = false;
if (connection.State == ConnectionState.Closed)
connection.Open();
command = new SqlCommand();
command.CommandText = "sp_Get_PackNameNonCondition";
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
ddlColumValue.DataSource = command.ExecuteReader();
ddlColumValue.DataValueField = "PackageName";
ddlColumValue.DataTextField = "packName";
ddlColumValue.DataBind();
//ddlColumValue.Items.Insert(0, new ListItem("--Select Package Name--", "0"));
if (connection.State == ConnectionState.Open)
connection.Close();
}
else if (ddlColumns.SelectedIndex == 7)
{
ddlColumValue.Items.Clear();
ddlColumValue.Visible = true;
txtColumValue.Visible = false;
if (connection.State == ConnectionState.Closed)
connection.Open();
command = new SqlCommand();
command.CommandText = "sp_Get_Emp";
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
ddlColumValue.DataSource = command.ExecuteReader();
ddlColumValue.DataValueField = "EmpName";
ddlColumValue.DataTextField = "EmpName";
ddlColumValue.DataBind();
//ddlColumValue.Items.Insert(0, new ListItem("--Select Name--", "0"));
if (connection.State == ConnectionState.Open)
connection.Close();
}
else if (ddlColumns.SelectedIndex == 8)
{
ddlColumValue.Items.Clear();
ddlColumValue.Visible = true;
txtColumValue.Visible = false;
if (connection.State == ConnectionState.Closed)
connection.Open();
command = new SqlCommand();
command.CommandText = "Get_Area";
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
ddlColumValue.DataSource = command.ExecuteReader();
ddlColumValue.DataValueField = "AreaName";
ddlColumValue.DataTextField = "AreaName";
ddlColumValue.DataBind();
//ddlColumValue.Items.Insert(0, new ListItem("--Select Area Name--", "0"));
if (connection.State == ConnectionState.Open)
connection.Close();
}
else if (ddlColumns.SelectedIndex == 9)
{
ddlColumValue.Items.Clear();
ddlColumValue.Visible = true;
txtColumValue.Visible = false;
if (connection.State == ConnectionState.Closed)
connection.Open();
command = new SqlCommand();
command.CommandText = "Get_Area";
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
ddlColumValue.DataSource = command.ExecuteReader();
ddlColumValue.DataValueField = "AreaName";
ddlColumValue.DataTextField = "AreaName";
ddlColumValue.DataBind();
//ddlColumValue.Items.Insert(0, new ListItem("--Select Area Name--", "0"));
if (connection.State == ConnectionState.Open)
connection.Close();
}
else if (ddlColumns.SelectedIndex == 10)
{
ddlColumValue.Items.Clear();
ddlColumValue.Visible = false;
txtColumValue.Visible = true;
}
else if (ddlColumns.SelectedIndex == 11)
{
ddlColumValue.Items.Clear();
ddlColumValue.Visible = true;
txtColumValue.Visible = false;
ddlColumValue.Items.Add(new ListItem("More Than 2 Month", "(GetDate() - 60)"));
ddlColumValue.Items.Add(new ListItem("More Than 3 Month", "(GetDate() - 90)"));
ddlColumValue.Items.Add(new ListItem("More Than 4 Month", "(GetDate() - 120)"));
ddlColumValue.Items.Add(new ListItem("More Than 5 Month", "(GetDate() - 150)"));
ddlColumValue.Items.Add(new ListItem("More Than 6 Month", "(GetDate() - 180)"));
ddlColumValue.Items.Add(new ListItem("More Than 9 Month", "(GetDate() - 270)"));
ddlColumValue.Items.Add(new ListItem("More Than 1 Year", "(GetDate() - 360)"));
}
else if (ddlColumns.SelectedIndex == 12)
{
ddlColumValue.Items.Clear();
ddlColumValue.Visible = true;
txtColumValue.Visible = false;
if (connection.State == ConnectionState.Closed)
connection.Open();
command = new SqlCommand();
command.CommandText = "sp_Get_BoxType";
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
ddlColumValue.DataSource = command.ExecuteReader();
ddlColumValue.DataValueField = "BoxTypeName";
ddlColumValue.DataTextField = "BoxTypeName";
ddlColumValue.DataBind();
ddlColumValue.Items.Insert(0, new ListItem("--Select Box Name--", "0"));
if (connection.State == ConnectionState.Open)
connection.Close();
}
}
catch (Exception e)
{
lblMessageCustSerach.Text = e.Message;
lblMessageCustSerach.Visible = true;
}
}
protected void ddlColumns_SelectedIndexChanged(object sender, EventArgs e)
{
selectedColChange();
}推荐答案
Would suggest you to check these articles which may help you
http://www.aspsnippets.com/Articles/ASPNet-GridView-Custom-Paging-with-PageSize-Change-Dropdown.aspx[^]
http://www.c-sharpcorner.com/UploadFile/satyapriyanayak/paging-with-dropdownlist-in-Asp-Net/[^]
Would suggest you to check these articles which may help you
http://www.aspsnippets.com/Articles/ASPNet-GridView-Custom-Paging-with-PageSize-Change-Dropdown.aspx[^]
http://www.c-sharpcorner.com/UploadFile/satyapriyanayak/paging-with-dropdownlist-in-Asp-Net/[^]
这篇关于如何在选定的下拉列表值上分页gridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
