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);
}
/// <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);
}
