public List <TruckModel> GetTruckList(int pageIndex, int pageSize, string number, DateTime dateTime, out int total) { total = 0; QueryPageModel queryPageModel = new QueryPageModel(); queryPageModel.OrderBy = "TruckID"; queryPageModel.PageIndex = pageIndex; queryPageModel.PageSize = pageSize; queryPageModel.QuerySql = @"(select * from [Truck]) u where 1=1 {0} )"; string sqlWhere = ""; TruckModel model = new TruckModel(); if (!string.IsNullOrEmpty(number)) { sqlWhere += " and Number like @Number"; model.Number = number + "%"; } if (dateTime > new DateTime(1950, 1, 1)) { sqlWhere += " and CheckInTime < @CheckInTime"; model.CheckInTime = dateTime; } queryPageModel.QuerySql = string.Format(queryPageModel.QuerySql, sqlWhere); var result = PageDataHelper.QueryWithPage <TruckModel>(queryPageModel, model); total = PageDataHelper.QueryTotalCount(queryPageModel, model); return(result.ToList()); }
public void TestMethodQuery6() { QueryPageModel queryPageModel = new QueryPageModel(); queryPageModel.OrderBy = "UserID"; queryPageModel.PageIndex = 0; queryPageModel.PageSize = 5; queryPageModel.QuerySql = @"(select s.*,r.RoleName from [User] s left join [Role] r on s.FK_RoleID = r.RoleID ) u where u.UserName like '张%' )"; UserModel model = new UserModel(); var result = PageDataHelper.QueryWithPage <UserModel>(queryPageModel, model); var total = PageDataHelper.QueryTotalCount <UserModel>(queryPageModel, model); Assert.AreNotEqual(0, result); }
public void TestMethodQuery5() { QueryPageModel queryPageModel = new QueryPageModel(); queryPageModel.OrderBy = "UserID"; queryPageModel.PageIndex = 0; queryPageModel.PageSize = 5; queryPageModel.QuerySql = @"(select * from [User]) u where UserName like @UserName )"; UserModel model = new UserModel(); model.UserName = "******"; var result = PageDataHelper.QueryWithPage <UserModel>(queryPageModel, model); var total = PageDataHelper.QueryTotalCount(queryPageModel, model); Assert.AreNotEqual(0, result); }
public void TestMethodQuery4() { QueryPageModel queryPageModel = new QueryPageModel(); queryPageModel.OrderBy = "UserID"; queryPageModel.PageIndex = 1; queryPageModel.PageSize = 5; queryPageModel.QuerySql = @"(select *, (select RoleName from [Role] where u.FK_RoleID = [Role].RoleID) as RoleName from [User] as u) uu where uu.RoleName = @RoleName)"; UserModel model = new UserModel(); model.RoleName = "系统管理员"; var result = PageDataHelper.QueryWithPage <UserModel>(queryPageModel, model); Assert.AreNotEqual(0, result); }
/// <summary> /// /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="userModel"></param> /// <param name="total"></param> /// <returns></returns> public List <UserModel> GetNewUserPageList(int pageIndex, int pageSize, UserModel userModel, out int total) { total = 0; //创建一个分页查询的model QueryPageModel model = new QueryPageModel(); model.PageIndex = pageIndex; model.PageSize = pageSize; //排序字段的名字 model.OrderBy = "UserID"; //定义查询的sql语句 //model.QuerySql = @"(select * from [User]) u where 1=1 {0})"; //model.QuerySql = @"(select *, // (select RoleName from [Role] where u.FK_RoleID = [Role].RoleID) as RoleName from [User] as u) uu where 1=1 {0})"; model.QuerySql = @"(select s.*,r.RoleName from [User] s left join [Role] r on s.FK_RoleID = r.RoleID ) u where 1=1 {0})"; string sqlWhere = ""; if (!string.IsNullOrEmpty(userModel.Account)) { sqlWhere += " and u.Account=@Account "; } if (!string.IsNullOrEmpty(userModel.UserName)) { sqlWhere += " and u.UserName like @UserName "; } if (!string.IsNullOrEmpty(userModel.RoleName) && userModel.RoleName != "请选择") { sqlWhere += " and u.RoleName=@RoleName"; } //拼接查询语句与where条件。 model.QuerySql = string.Format(model.QuerySql, sqlWhere); //调用分页查询的帮助类,查询数据。 List <UserModel> users = PageDataHelper.QueryWithPage <UserModel>(model, userModel).ToList(); //查询当前的sql 总条数。 total = PageDataHelper.QueryTotalCount <UserModel>(model, userModel); return(users); }