/// <summary> /// 分页查询集合 /// </summary> public List <BS_ORDER> GetListPage(ref PagerModel pager, int?status, string remark, DateTime?startTime, DateTime?endTime) { using (var session = DBHelper.GetSession()) { SqlString sql = new SqlString(@" select t.*, u.real_name as OrderUserRealName from bs_order t left join sys_user u on t.order_userid=u.id where 1=1"); if (status != null) { sql.AppendSql(" and t.status=@status", status); } if (!string.IsNullOrWhiteSpace(remark)) { sql.AppendSql(" and t.remark like concat('%',@roomNo,'%')", remark); } if (startTime != null) { sql.AppendSql(" and t.order_time>=STR_TO_DATE(@startTime, '%Y-%m-%d %H:%i:%s') ", startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")); } if (endTime != null) { sql.AppendSql(" and t.order_time<=STR_TO_DATE(@endTime, '%Y-%m-%d %H:%i:%s') ", endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")); } string orderby = " order by t.order_time desc, t.id asc "; pager = session.FindPageBySql <BS_ORDER>(sql.SQL, orderby, pager.PageSize, pager.CurrentPage, sql.Params); return(pager.Result as List <BS_ORDER>); } }
/// <summary> /// 获取集合 /// </summary> public List <TWO_ORDER> GetList(string name, DateTime startTime, DateTime endTime, int?status) { SqlString sql = new SqlString(@" select * from two_order t where 1=1 and DEL_FLAG=@delFlag", "0"); sql.AppendSql(@" and ( ORDER_TIME>=STR_TO_DATE(@startTime, '%Y-%m-%d %H:%i:%s') and ORDER_TIME<=STR_TO_DATE(@endTime, '%Y-%m-%d %H:%i:%s') )", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss")); if (!string.IsNullOrWhiteSpace(name)) { sql.AppendSql(" and PRISONER_NAME like @name", "%" + name + "%"); } if (status != null) { sql.AppendSql(" and T_STATUS = @status", status); } sql.AppendSql(" and 1=1"); //测试没有参数 sql.AppendSql(" order by ORDER_TIME desc, ID asc"); //测试排序 List <TWO_ORDER> result = DBHelper.FindListBySql <TWO_ORDER>(sql.SQL, sql.Params); return(result); }
/// <summary> /// 获取集合 /// </summary> public List <utils_test> GetList3(string name, DateTime startTime, DateTime endTime) { SqlString sql = new SqlString(@" select * from utils_test t where 1=1 "); sql.AppendSql(" and name like @name", "%" + name + "%"); sql.AppendSql(" and (add_time between @startTime and @endTime)", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss")); List <utils_test> result = DBHelper.FindListBySql <utils_test>(sql.SQL, sql.Params); return(result); }
/// <summary> /// 查询集合 /// </summary> public async Task <List <BS_ORDER> > GetListAsync(int?status, string remark, DateTime?startTime, DateTime?endTime) { using (var session = await DBHelper.GetSessionAsync()) { SqlString sql = new SqlString(@" select t.*, u.real_name as OrderUserRealName from bs_order t left join sys_user u on t.order_userid=u.id where 1=1"); if (status != null) { sql.AppendSql(" and t.status=@status", status); } if (!string.IsNullOrWhiteSpace(remark)) { sql.AppendSql(" and t.remark like concat('%',@roomNo,'%')", remark); } if (startTime != null) { sql.AppendSql(" and t.order_time>=STR_TO_DATE(@startTime, '%Y-%m-%d %H:%i:%s') ", startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")); } if (endTime != null) { sql.AppendSql(" and t.order_time<=STR_TO_DATE(@endTime, '%Y-%m-%d %H:%i:%s') ", endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")); } sql.AppendSql(" order by t.order_time desc, t.id asc "); List <BS_ORDER> list = await session.FindListBySqlAsync <BS_ORDER>(sql.SQL, sql.Params); return(list); } }
/// <summary> /// 分页获取集合 /// </summary> public List <TWO_ORDER> GetListPage(ref PagerModel pager, string name, DateTime startTime, DateTime endTime, int?status) { SqlString sql = new SqlString(string.Format(@" select * from two_order t where 1=1 and DEL_FLAG='{0}'", "0")); sql.AppendSql(@" and ( ORDER_TIME>=STR_TO_DATE(@startTime, '%Y-%m-%d %H:%i:%s') and ORDER_TIME<=STR_TO_DATE(@endTime, '%Y-%m-%d %H:%i:%s') )", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss")); if (!string.IsNullOrWhiteSpace(name)) { sql.AppendSql(" and PRISONER_NAME like @name", "%" + name + "%"); } if (status != null) { sql.AppendSql(" and T_STATUS = @status", status); } if (status != null) { sql.AppendFormat(" and T_STATUS = '{0}'", status); //测试追加非参数化SQL } sql.AppendSql(" and 1=1"); //测试没有参数 string orderby = "order by ORDER_TIME desc, ID asc"; //测试排序 pager = DBHelper.FindPageBySql <TWO_ORDER>(sql.SQL, orderby, pager.rows, pager.page, sql.Params); return(pager.result as List <TWO_ORDER>); }