C#——数据翻页

来源：互联网发布：网络电视能不能看直播编辑：程序博客网时间：2024/05/16 09:44

一、页面设计：

1、所需控件：

bindingNavigator

2、操作：

网页面中拖一个bindinNavigator

3、控件设计样式：

4、控件中butoon功能：

前四个是在同一页中“第一条，上一条，下一条，最后一条”，后面butoon实现翻页

二、代码部分：

存储过程

-- ================================================-- Template generated from Template Explorer using:-- Create Procedure (New Menu).SQL---- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below.---- This block of comments will not be included in-- the definition of the procedure.-- ================================================SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- ============================================= Create PROCEDURE UP_CommonPagerImprove          @Sql NVARCHAR(3200),                --需要执行的SQL语句          @CurrPageIndex INT = 1,             --当前页索引          @PageSize INT = 10,             --每页记录数量          @OrderCondition NVARCHAR(160) = ''      --排序条件,不带ORDER BY      AS          DECLARE @ExecSQL NVARCHAR(3200)         --最终执行的SQL语句          DECLARE @RecordStartIndex INT           --本次搜索开始时的索引          DECLARE @RecordEndIndex INT         --本次搜索结束时的索引          --计算开始和结束的索引          SET @RecordStartIndex = ((@CurrPageIndex-1) * (@PageSize)) + 1          SET @RecordEndIndex = @RecordStartIndex + @PageSize - 1                --拼接SQL命令,使用TOP命令的方式进行查询          --SUBSTRING(@Sql,7,LEN(@Sql))会跳过语句(SELECT ID FROM TABLE)中的(SELECT )          SET @Sql = 'SELECT TOP @RecordEndIndex TEMPCOL=0,' + SUBSTRING(@Sql,7,LEN(@Sql))           SET @Sql = REPLACE(@Sql,'@RecordEndIndex',CONVERT(varchar(10),@RecordEndIndex))          --如果存在排序语句,将排序语句添加到SQL查询命令中,否则不进行排序          IF @OrderCondition != ''              SET @Sql = @Sql + ' ORDER BY ' + @OrderCondition          --使用WITH对子查询定义一个别名          SET @ExecSQL = 'WITH TEMPTABLE AS (              SELECT ROW_NUMBER() OVER (ORDER BY TEMPCOL) AS RowIndex,UP_CommonPager_TempTbl.*               FROM (@Sql) UP_CommonPager_TempTbl)              SELECT * FROM TEMPTABLE WHERE RowIndex >= @RecordStartIndex'          SET @ExecSQL = REPLACE(@ExecSQL,'@RecordStartIndex',Convert(varchar(10),@RecordStartIndex))          SET @ExecSQL = REPLACE(@ExecSQL,'@Sql',@Sql)          --执行SQL语句          EXEC (@ExecSQL)

封装使用存储过程(此方法将返回一个Table数据集)

 public DataTable SelectDataByProc(string strSQL, int pageIndex, int pageSize, string orderCondition)    {        DataTable table = null;        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ToString()))        {            //指定存储过程的名称              SqlCommand cmd = new SqlCommand("UP_CommonPagerImprove", conn);            //指定cmd类型是Proc,并且给参数赋值              cmd.CommandType = CommandType.StoredProcedure;            cmd.Parameters.Clear();            SqlParameter[] paramters = new SqlParameter[]              {                  new SqlParameter("@Sql",strSQL),                  new SqlParameter("@CurrPageIndex",pageIndex),                  new SqlParameter("@PageSize",pageSize),                  new SqlParameter("@OrderCondition",orderCondition)              };            cmd.Parameters.AddRange(paramters);            try            {                conn.Open();            }            catch (Exception ex)            {                throw ex;            }            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);            table = new DataTable();            dataAdapter.Fill(table);            conn.Close();        }        return table;    }

每一个页面具体实现：

1、控制变量：

pageSize ; //页面大小

currentPage; //当前页面

totalPage; //总的页面数

totalCount; //总的记录数

firstpage; //第一页

lastpage; //最后一页

2、业务代码：

//绑定数据源（不一定非要用DatagridView,我用了)
public void bind()
{
wker_gdview.DataSource = db.SelectDataByProc("select * from WKER_INFO", currentPage, pageSize, "");
wker_gdview.DataBind();
}
//Butoon是否可操作控制
public void limit()
{
if (CurrentPage== Firstpage)
{
toolStripButton_previousPage.Enabled = false;
toolStripButton_nextPage.Enabled = true;
toolStripButton_firstPage.Enabled = false;
toolStripButton_lastPage.Enabled = true;
}
else if (CurrentPage == Lastpage)
{
toolStripButton_nextPage.Enabled = false;
toolStripButton_lastPage.Enabled = false;
toolStripButton_previousPage.Enabled = true;
toolStripButton_firstPage.Enabled = true;
}
else if (CurrentPage == Firstpage && Firstpage ==TotalPage)
{
toolStripButton_nextPage.Enabled = false;
toolStripButton_lastPage.Enabled = false;
toolStripButton_previousPage.Enabled = false;
toolStripButton_firstPage.Enabled = false;
}
else
{
toolStripButton_firstPage.Enabled = true;
toolStripButton_lastPage.Enabled = true;
toolStripButton_previousPage.Enabled = true;
toolStripButton_nextPage.Enabled = true;
}
}
//第一页
private void toolStripButton_firstPage_Click(object sender, EventArgs e)
{
currentPage = 1;
bind();
limit();
}
//上一页
private void toolStripButton_previousPage_Click(object sender, EventArgs e)
{
currentPage--;
if (currentPage == 0)
return;
bind();
limit();
}
//下一页
private void toolStripButton_nextPage_Click(object sender, EventArgs e)
{
currentPage++;
if (currentPage > totalPage)
return;
bind();
limit();
}
//最后一页
private void toolStripButton_lastPage_Click(object sender, EventArgs e)
{
currentPage--;
if (currentPage == 0)
return;
bind();

limit();

}

至此，翻页功能完成！

0 0