gridview分页显示_pageable实现分页

编程小6 (4) 2024-08-07 11:23

Hi，大家好，我是编程小6，很荣幸遇见你，我把这些年在开发过程中遇到的问题或想法写出来，今天说一说
gridview分页显示_pageable实现分页,希望能够帮助你!!!。

想法是这样的：使用bindingNavigator+存储过程实现DataGridView的分页功能，其中包含简单的查询。

存储过程如下：

--创建分页查询存储过程(含输出参数，输入参数（含搜索功能）)
use HotelDB
if exists(select * from sys.procedures where name='pro_getGuestRecord')
drop procedure pro_getGuestRecord
go

create procedure pro_getGuestRecord
   @pageSize int,
   @pageIndex int,
   @beginTime datetime,
   @endTime datetime,
   @ResideID int,
   @RecordCount int output
as
begin
   declare @sqlStr0 nvarchar(max);
   set @sqlStr0 = 'SELECT @RecordCount=count(a.GuestID)
FROM GuestRecord AS a INNER JOIN
       Room AS b ON a.RoomID = b.RoomID INNER JOIN
       ResideState AS e ON a.ResideID = e.ResideID INNER JOIN
       RoomType AS d ON b.RoomTypeID = d.TypeID INNER JOIN
       RoomState AS c ON b.RoomStateID = c.RoomStateID'
set @sqlStr0 +=' WHERE (a.ResideDate BETWEEN ''' + CAST(@beginTime as nvarchar)+ ''' AND '''+ CAST(@endTime as nvarchar)+ ''')';
if(@ResideID <> 0)
begin
       set @sqlStr0 += ' AND (e.ResideID = '+CAST(@ResideID AS nvarchar)+')';
end

declare @sqlStr1 varchar(max);
set @sqlStr1 = 'select * from(
       select row_number() over(order by a.GuestID asc) as num,
       a.GuestID, a.GuestName, a.ResideDate, a.Reposit, a.LeaveDate, a.TotalMoney, b.RoomID, d.TypeName,
       c.RoomStateName, b.RoomStateID
       FROM GuestRecord AS a INNER JOIN Room AS b ON a.RoomID = b.RoomID
       INNER JOIN ResideState AS e ON a.ResideID = e.ResideID
       INNER JOIN RoomType AS d ON b.RoomTypeID = d.TypeID
       INNER JOIN RoomState AS c ON b.RoomStateID = c.RoomStateID'
       set @sqlStr1 +=' WHERE (a.ResideDate BETWEEN ''' + CAST(@beginTime as varchar)+ ''' AND '''+ CAST(@endTime as nvarchar)+ ''')';
       if(@ResideID <> 0)
       begin
           set @sqlStr1 += ' AND (e.ResideID = '+CAST(@ResideID AS nvarchar)+')';
       end
set @sqlStr1 +=') as GuestRecordInfo';
set @sqlStr1 +=' where num between '+CAST(@pageSize as nvarchar)+'*('+CAST(@pageIndex as nvarchar)+'-1)+1 and '+CAST(@pageSize as nvarchar)+'*'+CAST(@pageIndex as nvarchar)+'';
execute sp_executesql @sqlStr0,N'@RecordCount int output',@RecordCount output
--execute sp_executesql @sqlStr1
exec(@sqlStr1);
end

winform界面设计如下：

主要的CS代码如下：

//每页显示记录数
private int pageSize = 2;
//当前页码
private int pageIndex = 1;
//总页数
private int pageCount = 0;
//总记录数
private int RecordCount = 0;

//默认显示当天的全部入住状态的数据。
//开始时间
private DateTime beginTime = DateTime.Now.Date;
//截止时间
private DateTime endTime = DateTime.Now.Date.AddDays(1);
//入住状态ID
private int ResideID = 0;

private void Form_Load(object sender, EventArgs e)
{

Bind();

//绑定DataGridView数据。
setDGVData();
//设置分页显示数据
setPageDate();
//设置下拉选择可选内容
setComboBoxDataSource();
}

//绑定查询处ComboBox数据源
private void Bind()
{

this.cbbResideState.DataSource = ResideStateManager.GetResideStateList();
this.cbbResideState.ValueMember = "ResideID";
this.cbbResideState.DisplayMember = "ResideName";
}

//查询
private void btnQuery_Click(object sender, EventArgs e)
{

pageIndex = 1;
beginTime = Convert.ToDateTime(dtpStartTime.Text.Trim());
endTime = Convert.ToDateTime(dtpEndTime.Text.Trim()).AddDays(1);
ResideID = Convert.ToInt32(cbbResideState.SelectedValue);

//绑定DataGridView数据。
setDGVData();
//设置分页显示数据
setPageDate();
//设置下拉选择可选内容
setComboBoxDataSource();
}

//绑定DataGridView数据。
private void setDGVData()
{

DataTable GuestInfo = GuestRecordManager.GetGuestInfoByPage(beginTime, endTime, ResideID, pageSize, pageIndex, ref RecordCount);
dgvRecordInfo.DataSource = GuestInfo;
//设置“入住时间”、“离开时间”的显示格式
dgvRecordInfo.Columns[2].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss";
dgvRecordInfo.Columns[4].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss";
}

//设置分页显示数据
private void setPageDate()
{

//计算总页数
if (RecordCount % pageSize == 0)
{

pageCount = RecordCount / pageSize;
}
else
{

pageCount = RecordCount / pageSize + 1;
}

//设置显示的当前页码
this.bindingNavigatorPositionItem.Text = pageIndex.ToString();
//设置显示的总页数
this.bindingNavigatorCountItem.Text = "/ {" + pageCount.ToString() + "}";
//设置显示的总记录数
this.toolStripLabel_totalRecord.Text = "共" + RecordCount.ToString() + "条记录";

//设置“首页”、“前一页”、“下一页”、“尾页”按钮的可用性
if (RecordCount == 0)
{

this.bindingNavigatorMoveFirstItem.Enabled = false;
this.bindingNavigatorMovePreviousItem.Enabled = false;
this.bindingNavigatorMoveNextItem.Enabled = false;
this.bindingNavigatorMoveLastItem.Enabled = false;
}
else
{

setPageButtonEnable();
}
}

//设置“首页”、“前一页”、“下一页”、“尾页”按钮的可用性
private void setPageButtonEnable()
{

if (pageIndex <= 1 && pageIndex == pageCount)//只有1页数据时
{

this.bindingNavigatorMoveFirstItem.Enabled = false;
this.bindingNavigatorMovePreviousItem.Enabled = false;
this.bindingNavigatorMoveNextItem.Enabled = false;
this.bindingNavigatorMoveLastItem.Enabled = false;
}
else if (pageIndex <= 1)
{

this.bindingNavigatorMoveFirstItem.Enabled = false;
this.bindingNavigatorMovePreviousItem.Enabled = false;
this.bindingNavigatorMoveNextItem.Enabled = true;
this.bindingNavigatorMoveLastItem.Enabled = true;
}
else if (pageIndex >= pageCount)
{

this.bindingNavigatorMoveFirstItem.Enabled = true;
this.bindingNavigatorMovePreviousItem.Enabled = true;
this.bindingNavigatorMoveNextItem.Enabled = false;
this.bindingNavigatorMoveLastItem.Enabled = false;
}
else
{

this.bindingNavigatorMoveFirstItem.Enabled = true;
this.bindingNavigatorMovePreviousItem.Enabled = true;
this.bindingNavigatorMoveNextItem.Enabled = true;
this.bindingNavigatorMoveLastItem.Enabled = true;
}
}

//设置toolStripComboBox1下拉选择可选内容
private void setComboBoxDataSource()
{

List<ClassIntString> comboBoxDataList = new List<ClassIntString>();
ClassIntString data;
for (int i = 1; i <= pageCount; i++)
{

data = new ClassIntString();
data.id = i;
data.name = "第" + i + "页";
comboBoxDataList.Add(data);
}
this.toolStripComboBox1.ComboBox.DataSource = comboBoxDataList;
this.toolStripComboBox1.ComboBox.ValueMember = "id";
this.toolStripComboBox1.ComboBox.DisplayMember = "name";
this.toolStripComboBox1.ComboBox.SelectedValue = 1;
}

//自定义类ClassIntString：

//public class ClassIntString
//{

// public int id { get; set; }
// public string name { get; set; }
//}

//首页
private void bindingNavigatorMoveFirstItem_Click(object sender, EventArgs e)
{

this.toolStripComboBox1.SelectedIndex = 0;
}
//上一页
private void bindingNavigatorMovePreviousItem_Click(object sender, EventArgs e)
{

this.toolStripComboBox1.SelectedIndex = pageIndex - 2;
}
//下一页
private void bindingNavigatorMoveNextItem_Click(object sender, EventArgs e)
{

this.toolStripComboBox1.SelectedIndex = pageIndex;
}
//尾页
private void bindingNavigatorMoveLastItem_Click(object sender, EventArgs e)
{

this.toolStripComboBox1.SelectedIndex = pageCount - 1;
}
//下拉切换到指定分页
private void toolStripComboBox1_SelectedIndexChanged(object sender, EventArgs e)
{

pageIndex = this.toolStripComboBox1.SelectedIndex + 1;
//绑定DataGridView数据。
setDGVData();
//设置分页显示数据
setPageDate();
this.toolStripComboBox1.ComboBox.SelectedValue = pageIndex;
}