sql的分页和调用

-- 分页存储过程
ALTER proc [dbo].[p_pageShow](
@pageSize int, --每页大小
@currentPage int out, --当前页
@houseName nvarchar(50), --房产名称
@totalCount int out, --总记录数
@totalPage int out --总页数
)
as
begin
--纠正当前页<1的情况
if @currentPage < 1
set @currentPage = 1
--查出总记录数
select @totalCount = count(*) from House where HouseName like '%' + @houseName + '%'
--查出总页数
if @totalCount % @pageSize = 0
set @totalPage = @totalCount / @pageSize
else
set @totalPage = @totalCount / @pageSize + 1
--纠正当前页 > 总页数的情况
if @currentPage > @totalPage
set @currentPage = @totalPage
--分页查询
select * from
(select h.HouseId, h.HouseName, h.HouseType, h.Leixing, h.Floor, h.TotalFloor, h.Rent, h.Addr, a.AreaName,
ROW_NUMBER() over (order by houseId) rn from house h left join area a on h.AreaId = a.AreaId
where h.HouseName like '%' + @houseName + '%' ) t1
where rn between (@currentPage - 1) * @pageSize + 1 and @currentPage * @pageSize
end
调用
private string connStr = "Data Source=.;Initial Catalog=zufang;Integrated Security=True";
/// <summary>
/// 分页查询的方法
/// </summary>
/// <param name="pageSize"></param>
/// <param name="currentPage"></param>
/// <param name="houseName"></param>
public HouseShowModel Show(int pageSize, int currentPage, string houseName)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open(); //打开数据库连接
var cmd = conn.CreateCommand(); //建立command对象
cmd.CommandText = "p_pageShow"; //存储过程名称
cmd.CommandType = CommandType.StoredProcedure; //指定command对象是存储过程
//给command对象加参数
var p_size = new SqlParameter("pageSize", pageSize);
var p_current = new SqlParameter("currentPage", currentPage) { Direction = ParameterDirection.InputOutput };
var p_name = new SqlParameter("houseName", houseName);
var p_count = new SqlParameter("totalCount", 0) { Direction = ParameterDirection.Output };
var p_page = new SqlParameter("totalPage", 0) { Direction = ParameterDirection.Output };
cmd.Parameters.AddRange(new[] { p_size, p_current, p_name, p_count, p_page });
var dr = cmd.ExecuteReader(); //cmd对象执行为dataReader对象
List<House> houses = new List<House>(); //新建一个列表的实体类
while (dr.Read())
{
House h = new House(); //新建一个实体类, 每一个字段、每一行赋值
h.HouseId = Convert.ToInt32(dr["HouseId"]);
h.HouseName = dr["HouseName"].ToString();
h.HouseType = dr["HouseType"].ToString();
h.Leixing = dr["Leixing"].ToString();
h.Floor = Convert.ToInt32(dr["Floor"]);
h.TotalFloor = Convert.ToInt32(dr["TotalFloor"]);
h.Rent = Convert.ToInt32(dr["Rent"]);
h.Addr = dr["Addr"].ToString();
h.AreaName = dr["AreaName"].ToString();
houses.Add(h); //把赋值好的实体类加到列表实体类中去
}
dr.Close(); //关闭数据读取
var m = new HouseShowModel(); //新建一个返回的实体类
m.Houses = houses; //列表赋值
m.TotalCount = Convert.ToInt32(p_count.Value); //总记录数赋值
m.TotalPage = Convert.ToInt32(p_page.Value); //总页数赋值
m.CurrentPage = Convert.ToInt32(p_current.Value); //当前页赋值
return m;
}
}