分页存储过程

对存储过程一直不是很了解。这两天系统看了下，自己尝试写了个分页存储过程。

获取目录树。Column_tree中包含字段tree_id,tree_name,add_time等

Create Procedure [dbo].[GetTree]
(
    @PageNumber Int,
    @PagePerPage int,
    @HowMany int output
)
as

--定义表变量
declare @tree table
(
    rowNumber int,
    tree_id int,
    tree_name varchar(50)
)

--插入数据
insert into @tree
select ROW_NUMBER() OVER (order by add_time),tree_id,tree_name
from Column_tree

--返回总数
select @HowMany=count(tree_id) from @tree

select tree_id,tree_name from @tree
where rowNumber>(@PageNumber-1)*@PagePerPage
 and rowNumber<=@PageNumber*@PagePerPage

在sql server中使用此存储过程

Declare @count int
exec GetTree
@PageNumber=2,
    @PagePerPage=10,
    @HowMany=@count output
select @count

--或者使用
exec GetTree  2,10,@count output
select @count
go

在ado.net中使用

            string conString = @"server=.;Integrated Security=true;database=db;";          

            SqlConnection conn = new SqlConnection(conString);
            conn.Open();
            SqlCommand cmd = new SqlCommand();

            cmd.Parameters.Add(new SqlParameter("@PageNumber", 1));
            cmd.Parameters.Add(new SqlParameter("@PagePerPage", 10));
            cmd.Parameters.Add(new SqlParameter("@HowMany",SqlDbType.Int));
            cmd.Parameters["@HowMany"].Direction=ParameterDirection.Output;
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetTree";
            SqlDataReader dr = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(dr);
            dr.Close();
            foreach (DataRow row in dt.Rows)
            {
                Response.Write(row["tree_id"].ToString() + row["tree_name"].ToString() + "<br />");
            }
            int count = int.Parse(cmd.Parameters["@HowMany"].Value.ToString());   //获取输出值，有了这个值，就可以计算分页了。
            Response.Write(count);
            conn.Close();