手动分页源关键代码

使用返回参数的方式
create procedure sp_GetPagingBooks
(
@pageSize int, --每页显示多少条?
@pageIndex int --当前第几页(从开始)
)as
------------- 起始数和结束数------------------
declare @start int;
declare @end int;
set @start = (@pageIndex - 1) * @pageSize + 1;
set @end = @pageIndex * @pageSize;

------------- 计算分页数据-------------
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice,id)
AS PriceRank,id,UnitPrice,ISBN,Title,Author,PublishDate FROM books) AS R WHERE PriceRank BETWEEN @start AND @end;

------------- 计算总页数---------------
declare @Count int ---- 总记录条数
declare @pageCount int ---- 总页数
select @Count =COUNT(id) from Books;
select @pageCount =@Count / @pageSize
if (@Count % @pageSize!=0)
set @pageCount = @pageCount +1;
return @pageCount
Go

C#方法
private List<Book> GetBooksBySP(int pageSize, int pageIndex, ref int pageCount)
{
    List<Book> list = null;
    Book b = null;
    string strConn = "server=.;database=bookshop;uid=sa;pwd=sa";
    SqlConnection conn = new SqlConnection(strConn);
    int start = (pageIndex - 1) * pageSize + 1;
    int end = pageIndex * pageSize;
    string sql = @"sp_GetPagingBooks";
    SqlCommand cmd = new SqlCommand(sql, conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
    cmd.Parameters.Add("@pageIndex", SqlDbType.Int).Value = pageIndex;
    cmd.Parameters.Add("@return", SqlDbType.Int);
    cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;

    SqlDataReader dr = null;
    try
    {
        conn.Open();
        dr = cmd.ExecuteReader();
        if (!dr.HasRows) return list;

        list = new List<Book>();
        while (dr.Read())
        {
            b = new Book();
            b.Id = Convert.ToInt32(dr["id"]);
            b.ISBN = dr["isbn"].ToString();
            b.PublishDate = Convert.ToDateTime(dr["PublishDate"]);
            b.UnitPrice = Convert.ToDecimal(dr["UnitPrice"]);
            b.Author = dr["Author"].ToString();
            b.Title = dr["Title"].ToString();

            list.Add(b);
        }
        dr.Close();

        pageCount = Convert.ToInt32(cmd.Parameters["@return"].Value); //返回参数
   }
    catch (Exception)
    {}

    return list;
}

使用输出参数的方式
ALTER procedure sp_GetPagingBooks
(
@pageSize int, --每页显示多少条?
@pageIndex int --当前第几页(从1开始)
@pageCount int output --返回参数,一共有多少数据
)as
------------- 计算数据的总条数---------------
select @pageCount=COUNT(id) from Books;

------------- 起始数和结束数 ------------------
declare @start int;
declare @end int;
set @start = (@pageIndex - 1) * @pageSize + 1;
set @end = @pageIndex * @pageSize;
Go

------------- 计算分页数据 -------------
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice,id)
AS PriceRank,id,UnitPrice,ISBN,Title,Author,PublishDate FROM books)
AS R WHERE PriceRank BETWEEN @start AND @end;

C#代码中,只要把第3个参数的参数名和方向改一下就可以了:
    cmd.Parameters.Add("@pageCount", SqlDbType.Int);
    cmd.Parameters["@pageCount"].Direction = ParameterDirection.Output;

使用SQL语句的方式:
private List<Book> GetBooksByPaging(int pageSize, int currentPage, ref int pageCount)
{
    List<Book> list = null;
    Book b = null;
    string strConn = "server=.;database=bookshop;uid=sa;pwd=sa";
    SqlConnection conn = new SqlConnection(strConn);
    int start, end;
    start = (currentPage - 1) * pageSize + 1;
    end = currentPage * pageSize;
    string sql = @" SELECT * FROM
                    (SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice,id)
                    AS PriceRank,id,ISBN,Title,UnitPrice,Author,PublishDate FROM books) R
                    WHERE PriceRank BETWEEN " + start + " AND " + end;
    string sql2 = "select count(id) from books";
    SqlCommand cmd = new SqlCommand(sql, conn);
    SqlDataReader dr = null;
    try
    {
        conn.Open();
        dr = cmd.ExecuteReader();
        if (!dr.HasRows) return list;

        list = new List<Book>();
        while (dr.Read())
        {
            b = new Book();
            b.Id = Convert.ToInt32(dr["id"]);
            b.ISBN = dr["isbn"].ToString();
            b.PublishDate = Convert.ToDateTime(dr["PublishDate"]);
            b.UnitPrice = Convert.ToDecimal(dr["UnitPrice"]);
            b.Author = dr["Author"].ToString();
            b.Title = dr["Title"].ToString();

            list.Add(b);
        }
        dr.Close();

        cmd = new SqlCommand(sql2, conn);
        int count = Convert.ToInt32(cmd.ExecuteScalar());
        conn.Close();

        pageCount = count / pageSize;
        pageCount = count % pageSize == 0 ? pageCount : pageCount + 1;
    }
    catch (Exception)
    {
    }

    return list;
}
--------------------编程问答-------------------- 我贴个Oracle版的。http://blog.csdn.net/chinacsharper/article/details/9095387 --------------------编程问答--------------------

、
学习了 mark --------------------编程问答-------------------- mark

补充：.NET技术 ,  ASP.NET