GridView 使用存储过程分页

最新推荐文章于 2024-09-15 23:28:35 发布

wjv20110308

最新推荐文章于 2024-09-15 23:28:35 发布

阅读量365

点赞数

文章标签：存储 button dataset object blog sql

本文链接：https://blog.csdn.net/wjv20110308/article/details/7009515

版权

存储过程

use blog

go

create procedure blogcontent_page

@pageSize int,   --一页多少条

@pageIndex int,  --第几页

@pageCount int output  --一共多少页，输出参数

as

begin

declare @totalRecords int --定义变量记录条数

select @totalRecords = count(id) from blog_content--取出总记录条数

set @pageCount=ceiling(@totalRecords / (@pageSize+0.0));--取出总页数

with temp as (select row_number() over (order by id) as inde, * from blog_content)  --给这个表再添加一列序号

select * from temp where inde between (@pageIndex - 1)* @pageSize+1 and @pageIndex * @pageSize  --按页数查询

return @totalRecords

end

后台代码

private void FullGridView(int index)

    {

        string sql = ConfigurationManager.ConnectionStrings["sqlblog"].ConnectionString;

        using (SqlConnection conn = new SqlConnection(sql))

        {

            using (SqlCommand comm = conn.CreateCommand())

            {

                comm.CommandText = "blogcontent_page";

                comm.CommandType = System.Data.CommandType.StoredProcedure;

                comm.Parameters.AddWithValue("@pageSize", 3);

                comm.Parameters.Add("@pageCount", SqlDbType.Int).Direction = ParameterDirection.Output;

                comm.Parameters.AddWithValue("@pageIndex", index);

                SqlDataAdapter da = new SqlDataAdapter(comm);

                DataSet ds = new DataSet();

                da.Fill(ds);

                this.GridView1.DataSource = ds.Tables[0];

                this.GridView1.DataBind();

                this.HiddenField1.Value = index.ToString();//当前页数

                this.HiddenField2.Value = comm.Parameters["@pageCount"].Value.ToString();//总共的页数

                this.Label4.Text = index.ToString()+"/"+HiddenField2.Value;

            }

        }

    }

protected void Button1_Click(object sender, EventArgs e)

    {

        FullGridView (1);

    }

    //最后一页

    protected void Button4_Click(object sender, EventArgs e)

    {

        FullGridView (Convert.ToInt32(this.HiddenField2.Value));

    }

    //上一页

    protected void Button2_Click(object sender, EventArgs e)

    {

        int index =Convert.ToInt32 (this.HiddenField1.Value);

        if (index>1)

        {

            FullGridView (index - 1);

        }

    }

    //下一页

    protected void Button3_Click(object sender, EventArgs e)

    {

        int index = Convert.ToInt32(this.HiddenField1.Value);

        if (index < Convert.ToInt32(this.HiddenField2.Value))

        {

            FullGridView (index + 1);

        }

    }

    //跳到多少页

    protected void Button5_Click(object sender, EventArgs e)

    {

        int countpage=Convert.ToInt32(this.HiddenField2.Value);

        int index = Convert.ToInt32(this.TextBox1.Text);

        if (index>0&&index<=countpage)

        {

            FullGridView (index);

        }

    }