Dapper实现 Linq 和 存储过程 + JS 实现分页
后台方法Linq
using (SqlConnection conn = new SqlConnection(str))
{
string sql = $"select * from Goods where 1 = 1";
if (!string.IsNullOrWhiteSpace(name))
{
sql += $"and Name like '%{name}%'";
}
var list = conn.Query<ModelInfo>(sql);
Pages pag = new Pages();
pag.ModelInfos = list.OrderBy(x => x.ID)
.Skip((index - 1) * size)
.Take(size).ToList();
var count = list.Count();
pag.Page = count / size + (count % size == 0 ? 0 : 1);
return pag;
}
public class Pages
{
public List<类名> 类别名{ get; set; }
public int Page { get; set; }
}
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
后台方法存储过程
[HttpGet]
public PageDate GetGoods2(int index, int size)
{
var p = new DynamicParameters();
p.Add("@index", index);
p.Add("@size", size);
p.Add("@totalcount", dbType: DbType.Int32, direction: ParameterDirection.Output); //总数据数
p.Add("@pagecount", dbType:DbType.Int32,direction:ParameterDirection.Output); //总页数
List<Goods> list = new List<Goods>();
using (SqlConnection conn=new SqlConnection(connstr))
{
list = conn.Query<Goods>("sp_Show",p,commandType:CommandType.StoredProcedure).ToList();
}
PageDate page = new PageDate();
page.List = list;
page.PageCount = p.Get<int>("@pagecount");
return page;
}
create proc sp_Show
(
@index int,
@size int,
@totalcount int out, --总数据数
@pagecount int out --总页数
)
as
begin
--如果当前页数小于一
if(@index<1)
begin
set @index=1
end
--计算总数据数
select @totalcount=count(*) from Goods
--计算总页数
set @pagecount=CEILING(@totalcount*1.0/@size)
--分页查询
select * from
(select *,ROW_NUMBER() over (order by GId) rn from Goods) tb1 where rn between (@index-1)*@size+1 and @index*@size
end
declare @x int,@y int
exec sp_Show 1,2,@x out,@y out
select @x,@y
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
前台代码
<div>
<input id="Button1" onclick="first()" type="button" value="首页" />
<input id="Button1" onclick="prev()" type="button" value="上一页" />
<input id="Button1" onclick="next()" type="button" value="下一页" />
<input id="Button1" onclick="last()" type="button" value="尾页" />
</div>
<script>
var index1 = "";
var pagecount = "";
function first() {
index1 = 1;
log(index1);
}
function prev() {
index1--;
if (index1 == 0) {
index1 = 1;
}
log(index1);
}
function next() {
index1++;
if (index1 > pagecount) {
index1 = pagecount;
}
log(index1);
}
function last() {
log(pagecount);
}
</script>