C#分页存储过程及调用

create proc Proc_Page
(
@name varchar(20),
@pageIndex int,
@pageSize int,
@pageCount int out
)
as
begin
set nocount on --开启不计受影响行数

select top(@pageSize) * from
(select ROW_NUMBER() over (order by Id) as 'RowIndex', * from UserInfo where Name like '%'+@name+'%') t
where RowIndex > ((@pageIndex-1) * @pageSize)

declare @total int = 0

select @total = count(*) from UserInfo

set @pageCount = CEILING(@total * 1.0 / @pageSize)
end

declare @s int
exec Proc_Page '',2,3,@s out
print @s

调用

public DataTable Proc(int pageIndex, int pageSize, out int count, string name = "")
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter() { ParameterName = "@name", SqlDbType = SqlDbType.VarChar, SqlValue = name },
new SqlParameter() { ParameterName = "@pageIndex", SqlDbType = SqlDbType.Int, SqlValue = pageIndex },
new SqlParameter() { ParameterName = "@pageSize", SqlDbType = SqlDbType.Int, SqlValue = pageSize },
new SqlParameter() { ParameterName = "@pageCount", SqlDbType = SqlDbType.Int,Direction=ParameterDirection.Output }
};
DataTable dt = DBHelper.Do_Proc("proc_Add", paras);
count = Convert.ToInt32(paras[1].Value);
return dt;
}