对存储过程一直不是很了解。这两天系统看了下,自己尝试写了个分页存储过程。
获取目录树。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();