分页存储过程简单实例

USE [test1]

GO

/****** Object:  StoredProcedure [dbo].[test300]    Script Date: 12/16/2010 18:08:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER proc [dbo].[test300]

(

@pagecurrent int,

@pagesize int

)

as

declare @count int

declare @pagecount int

select @count=COUNT(*) from personal

 

if(@count%@pagesize)=0

set @pagecount=@count/@pagesize

else

set @pagecount=@count/@pagesize+1

 

if @pagecurrent>@pagecount

set @pagecurrent=@pagecount

 

if @pagecurrent<1

set @pagecurrent=1

 

if @pagecurrent=1

begin

select top (@pagesize)id,name,xuexiao,wangzhan from personal order by id

end

else

begin

select top (@pagesize) id,name,xuexiao,wangzhan from personal where id>(select MAX(id) from (select top ((@pagecurrent-1)*@pagesize) id from personal order by id) as t) order by id

end

 

 

 

 

using System;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

 

public partial class _Default : System.Web.UI.Page 

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            show(10, 3);

        }

    }

 

    protected void show(int pagesize, int pagecurrent)

    {

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ToString());

        conn.Open();

        SqlCommand cmd = new SqlCommand("test300", conn);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@pagesize", SqlDbType.Int).Value = pagesize;

        cmd.Parameters.Add("@pagecurrent", SqlDbType.Int).Value = pagecurrent;

        SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        DataTable dt = new DataTable();

        dt.Load(dr);

        GridView1.DataSource = dt;

        GridView1.DataBind();

    }

}