分页查询
用到的表结构、数据、实体类
方便测试Demo
Student
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`StudentId` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`SchoolId` int(11) NOT NULL,
PRIMARY KEY (`StudentId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `student` VALUES (1, 'Zzr', 1);
INSERT INTO `student` VALUES (2, 'Ls', 2);
INSERT INTO `student` VALUES (3, 'Wem', 3);
INSERT INTO `student` VALUES (4, 'Hpf', 4);
INSERT INTO `student` VALUES (5, 'Zzr', 5);
INSERT INTO `student` VALUES (6, 'hpf', 2);
INSERT INTO `student` VALUES (7, 'hpf', 2);
INSERT INTO `student` VALUES (9, 'hpf', 2);
INSERT INTO `student` VALUES (11, 'qwe', 0);
INSERT INTO `student` VALUES (12, 'qwe', 0);
public class Student
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int StudentId { get; set; }
public string Name { get; set; }
public int SchoolId { get; set; }
[Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一 SchoolId是Student类里面的
public School School { get; set; } //不能赋值
}
School
CREATE TABLE `school` (
`SchoolId` int(11) NOT NULL AUTO_INCREMENT,
`SchoolName` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`SchoolId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `school` VALUES (1, '一中');
INSERT INTO `school` VALUES (2, '二中');
INSERT INTO `school` VALUES (3, '三中');
INSERT INTO `school` VALUES (4, '四中');
INSERT INTO `school` VALUES (5, '五中');
public class School
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int SchoolId { get; set; }
public string SchoolName { get; set; }
}
1.单表分页查询
int pageNumber = 1;//第几页（可随意修改查看结果）
int pageSize = 2;//每页多少数据
int totalNumber = 0;//总数据
List<Student> list = db.Queryable<Student>().ToPageList(pageNumber, pageSize, ref totalNumber);
//SELECT `StudentId`,`Name`,`SchoolId` FROM `Student` LIMIT 0,2
2.异步单表分页查询
int pageNumber = 1;//第几页
int pageSize = 2;//每页多少数据
RefAsync<int> totalNumber = 0;//总数据
List<Student> list = await db.Queryable<Student>().ToPageListAsync(pageNumber, pageSize, totalNumber);
//SELECT `StudentId`,`Name`,`SchoolId` FROM `Student` LIMIT 0,2
3.多表分页查询
int pageNumber = 1;//第几页
int pageSize = 2;//每页多少数据
int totalNumber = 0;//总数据
var cs = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left, st.SchoolId == sc.SchoolId))
.Select((st, sc) => new { Id = st.SchoolId, StudentName = st.Name, SchoolName = sc.SchoolName })
.ToPageList(pageNumber, pageSize,ref totalNumber);
//SELECT `st`.`SchoolId` AS `Id` , `st`.`Name` AS `StudentName` , `sc`.`SchoolName` AS `SchoolName` FROM `Student` st Left JOIN `School` sc ON ( `st`.`SchoolId` = `sc`.`SchoolId` ) LIMIT 0,2
4.异步多表分页查询
int pageNumber = 1;//第几页
int pageSize = 2;//每页多少数据
RefAsync<int> totalNumber = 0;//总数据
var cs =await db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left, st.SchoolId == sc.SchoolId))
.Select((st, sc) => new { Id = st.SchoolId, StudentName = st.Name, SchoolName = sc.SchoolName })
.ToPageListAsync(pageNumber, pageSize, totalNumber);
//SELECT `st`.`SchoolId` AS `Id` , `st`.`Name` AS `StudentName` , `sc`.`SchoolName` AS `SchoolName` FROM `Student` st Left JOIN `School` sc ON ( st`.`SchoolId` = `sc`.`SchoolId` ) LIMIT 0,2
5.原始分页Sql语句
SELECT * FROM
(SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER()
OVER(ORDER BY GetDate())AS RowIndex FROM [STudent]) T
WHERE RowIndex BETWEEN 1 AND 20
6.SqlSever2012分页
5.0.3.2以上的版本支持
把 ToPageList 换成 ToOffsetPage
