/// <summary> /// 自定义查询实体列表 /// </summary> /// <param name="sqlCustom">指定SQL查询语句,适合多表关联,不包含Where</param> /// <param name="queryBuliders">查询条件组</param> /// <param name="sort">排序</param> /// <returns></returns> public virtual List <T> GetList <T>(string sqlCustom, List <QueryBuilder> queryBuliders, SortCondition sort = null) { try { var sql = new StringBuilder(); sql.AppendFormat(" {0} where 1=1", sqlCustom); var p = new DynamicParameters(); foreach (var queryBulider in queryBuliders.Where(queryBulider => queryBulider != null && queryBulider.WhereItems.Count > 0)) { SqlOperators.BuildParameters(queryBulider, ref p); string whereSql = SqlOperators.BuildWhere(queryBulider.WhereItems, queryBulider.IsAnd); //重构后代码 sql.AppendFormat(queryBulider.IsAnd ? " {0} {1}" : " {0} ( {1} )", queryBulider.IsGroupAnd ? "And" : "Or", whereSql == string.Empty ? "1=1" : whereSql); } if (sort != null) { sql.AppendFormat(" Order by {0} {1} ", sort.Field, sort.ListSortDirection == ListSortDirection.Ascending ? "Asc" : "Desc"); } Console.WriteLine(sql.ToString()); System.Diagnostics.Debug.WriteLine(sql.ToString()); return(_mydb.Query <T>(sql.ToString(), p).ToList()); }catch (Exception) { throw; } finally { //_mydb.Dispose(); } // return _table.All().ToList(); }
/// <summary> /// 自定义获取分页实体列表 /// </summary> /// <typeparam name="T">自定义实体</typeparam> /// <param name="withTables">自定义 with table as () 的查询语句</param> /// <param name="sqlTable">自定义查询的 Table SQL 语句,不包含 Where条件</param> /// <param name="queryBuliders">查询条件</param> /// <param name="sort">排序</param> /// <param name="intPageSize">分页大小</param> /// <param name="intCurrentIndex">当前页</param> /// <param name="total">总数</param> /// <returns></returns> public List <T> QueryPage <T>(string withTables, string sqlTable, List <QueryBuilder> queryBuliders, SortCondition sort, int intPageSize, int intCurrentIndex, out int total) where T : class { StringBuilder sql = new StringBuilder(); sql.AppendFormat(" {0} where 1=1", sqlTable); var p = new DynamicParameters(); foreach (var queryBulider in queryBuliders.Where(queryBulider => queryBulider != null && queryBulider.WhereItems.Count > 0)) { SqlOperators.BuildParameters(queryBulider, ref p); string whereSql = SqlOperators.BuildWhere(queryBulider.WhereItems, queryBulider.IsAnd); //重构后代码 sql.AppendFormat(queryBulider.IsAnd ? " {0} {1}" : " {0} ( {1} )", queryBulider.IsGroupAnd ? "And" : "Or", whereSql == string.Empty ? "1=1" : whereSql); } var pageSql = withTables + _mydb.GetPageSelString(sql.ToString(), sort, "*", intPageSize, intCurrentIndex); var countSql = string.Format("SELECT COUNT(*) DataCount FROM ({0}) T ", sql); Console.WriteLine(withTables + sql.ToString()); System.Diagnostics.Debug.WriteLine(withTables + sql.ToString()); total = _mydb.ExecuteScalar <int>(withTables + countSql, p); return(_mydb.Query <T>(pageSql, p).ToList()); //return db.Query<T>(sqlstr) }