C# 存储过程分页

来源：互联网发布：淘宝旺旺如何设置编辑：程序博客网时间：2024/04/30 15:28

create procedure proc_GetBy_Rownumber  --SQL 2005 及以上 @pageIndex int, @pageSize int --userID,userName,userAge 表字段as    begin set nocount on;     select * from (select userID,userName,userAge,Row_number() over(order by userid asc) as IDNum from dbo.ProcUserInfo)     as Info where IDNum>@pageSize*@pageIndex and IDNum<@pageSize*(@pageIndex+1)   set nocount off; end

private static int pageCount;

public int pageIndex        {            get            {                if (ViewState["pageIndex"] == null)                {                    ViewState["pageIndex"] = 0;                }                return Convert.ToInt32(ViewState["pageIndex"]);            }            set            {                ViewState["pageIndex"] = value;            }        }        public void Bind()        {            int total = 110002;            int pagesize = 100;            pageCount = total / pagesize == 0 ? total / pagesize : (total / pagesize) + 1;            SqlCommand cmd = new SqlCommand("proc_GetBy_Rownumber", conn);            cmd.CommandType = CommandType.StoredProcedure;            SqlParameter[] param = new SqlParameter[2];            param[0] = new SqlParameter("@pageIndex", SqlDbType.Int);            param[0].Value = pageIndex;            param[1] = new SqlParameter("@pageSize", SqlDbType.Int);            param[1].Value = pagesize;            cmd.Parameters.AddRange(param);            DataTable dt = new DataTable();            SqlDataAdapter da = new SqlDataAdapter(cmd);            da.Fill(dt);            if (pageIndex==0)            {                this.Button5.Enabled = false;                this.Button6.Enabled = false;                this.Button7.Enabled = true;                this.Button8.Enabled = true;            }            else if(pageIndex!=0&& pageIndex != pageCount - 1)            {                this.Button5.Enabled = true;                this.Button6.Enabled = true;                this.Button7.Enabled = true;                this.Button8.Enabled = true;            }            else            {                this.Button5.Enabled = true;                this.Button6.Enabled = true;                this.Button7.Enabled = false;                this.Button8.Enabled = false;            }            this.GridView1.DataSource = dt;            this.GridView1.DataBind();            this.Label1.Text = "总共：" + total.ToString() + "条/每页100条/共" + pageCount + "页/当前第" + (pageIndex + 1) + "页";        }

  /// <summary>        /// 首页        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        protected void Button5_Click(object sender, EventArgs e)        {            if (pageIndex != 0)            {                pageIndex = 0;            }            Bind();        }        /// <summary>        /// 上一页        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        protected void Button6_Click(object sender, EventArgs e)        {            if (pageIndex != 0)            {                pageIndex--;
            }            Bind();        }        /// <summary>        /// 下一页        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        protected void Button7_Click(object sender, EventArgs e)        {            if (pageIndex != pageCount)            {                pageIndex++;
            }            Bind();        }        /// <summary>        /// 末页        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        protected void Button8_Click(object sender, EventArgs e)        {            if (pageIndex != pageCount)            {                pageIndex = pageCount - 1;
            }            Bind();        }