public JsonResult Sort_changed(SortTypeEnum?ddlSort) { int count; var list = _rep.QueryByPage(0, int.MaxValue, out count, null, Sort).Select(Map).ToList(); var str = string.Empty; switch (ddlSort) { case SortTypeEnum.Default: str = GetHtmlStr(list); _sortType = ddlSort.Value; break; case SortTypeEnum.Line: str = GetHtmlStr(list, SortTypeEnum.Line); _sortType = ddlSort.Value; break; case SortTypeEnum.Date: str = GetHtmlStr(list, SortTypeEnum.Date); _sortType = ddlSort.Value; break; case null: break; default: throw new ArgumentOutOfRangeException("ddlSort", ddlSort, null); } return(Json(str.Replace("border=\"1\"", ""))); }
private IQueryable <Talent> ApplySorting(IQueryable <Talent> talents, SortTypeEnum sort) { switch (sort) { case SortTypeEnum.priceAsc: talents = talents.OrderBy(m => m.Price); break; case SortTypeEnum.priceDesc: talents = talents.OrderByDescending(m => m.Price); break; case SortTypeEnum.alphabetAz: talents = talents.OrderBy(m => m.FullName); break; case SortTypeEnum.alphabetZa: talents = talents.OrderByDescending(m => m.FullName); break; //this case will be uncommented //when responseTime field will be filled after giving response to request //case SortTypeEnum.responseTime: // result = result.OrderBy(m => m.FirstName).ThenBy(m => m.LastName); // break; default: //def talents = talents.OrderBy(m => m.ID); break; } return(talents); }
public ISortStrategy CreateSort(SortAlgorithmEnum sortAlgorithm, SortTypeEnum sortType, IStepCounter stepCounter) { ISortType neededSortType = this.sortTypeFactory.CreateSortType(sortType); switch (sortAlgorithm) { case SortAlgorithmEnum.InsertionSort: { return(new InsertionSort(neededSortType, stepCounter)); } case SortAlgorithmEnum.MergeSort: { return(new MergeSort(neededSortType, stepCounter)); } case SortAlgorithmEnum.QuickSort: { return(new QuickSort(neededSortType, stepCounter)); } case SortAlgorithmEnum.SelectionSort: { return(new SelectionSort(neededSortType, stepCounter)); } default: return(null); } }
/// <summary> /// 数据库sql order by参数的封装类 /// </summary> /// <param name="expression">表达式,例u=>u.Id</param> /// <param name="orderByType">SortType.ASC/SortType.DESC</param> public DbOrderBy(Expression <Func <TEntity, object> > expression, SortTypeEnum orderByType = SortTypeEnum.ASC) { PropertyInfo property = GetProperty(expression) as PropertyInfo; var real = property.GetCustomAttribute(typeof(ColumnAttribute), false); if (real != null && real is ColumnAttribute) { PropertyName = ((ColumnAttribute)real).Name; } else { PropertyName = property.Name; } OrderByType = orderByType.ToString(); }
public void Sort(string sequence, SortAlgorithmEnum sortAlgorithm, SortTypeEnum sortType) { this.errorMessage = string.Empty; try { this.sortResult = this.sortHandler.Handle(sequence, sortAlgorithm, sortType, new StepCounter()); } catch (ValidationException) { errorMessage = "Not validating sequence"; } this.OnPropertyChanged(string.Empty); }
public ISortResult Handle(string sequence, SortAlgorithmEnum sortAlgorithm, SortTypeEnum sortType, IStepCounter stepCounter) { if (sequence == null) { throw new ArgumentNullException(nameof(sequence)); } if (stepCounter == null) { throw new ArgumentNullException(nameof(sequence)); } IEnumerable <decimal> numbersSequence = this.stringToDecimalCollectionParser.ParseStringToCollection(sequence); ISortResult sortResult = this.sortStrategyFactory.CreateSort(sortAlgorithm, sortType, stepCounter).Sort(numbersSequence); return(sortResult); }
protected static void OrderBy <T>(ref IQueryable <T> records, string sortBy, SortTypeEnum sortType) { var methodName = sortType == SortTypeEnum.Asc ? "OrderBy" : "OrderByDescending"; var parameter = Expression.Parameter(records.ElementType, "p"); var memberAccess = Expression.Property(parameter, sortBy); var orderByLambda = Expression.Lambda(memberAccess, parameter); var result = Expression.Call( typeof(Queryable), methodName, new[] { records.ElementType, memberAccess.Type }, records.Expression, Expression.Quote(orderByLambda)); records = records.Provider.CreateQuery <T>(result); }
public HttpResponseMessage GetOverviews( string category = "", string levelMin = "", string levelMax = "", string searchString = "", int availOnly = 1, int dailyRewardsOnly = 0, int pageNum = 0, int numPerpage = 10, int userId = 0, SortByEnum sortBy = SortByEnum.DateModified, SortTypeEnum sortType = SortTypeEnum.Descending ) { try { QuizzOverviewQueryParam qSetting = new QuizzOverviewQueryParam { Category = category, GradeLevelMin = levelMin, GradeLevelMax = levelMax, SearchString = searchString, PageNum = pageNum, NumPerPage = numPerpage, SortBy = sortBy, SortType = sortType, UserId = userId, AvailOnly = availOnly, DailyRewardsOnly = dailyRewardsOnly, }; var result = _quizzOverviewSvc.GetQuizzOverviewModels(qSetting); if (result == null) { return(Request.CreateResponse(HttpStatusCode.BadRequest)); } return(Request.CreateResponse(HttpStatusCode.OK, result)); } catch (ServiceException ex) { return(Request.CreateResponse(ex.HttpStatusCode, ex.Message)); } }
public IQueryable <Talent> Search(int categoryID, SortTypeEnum sort, int?count = null) { IQueryable <Talent> talents = GetWithRelatedDataForSearchAsIQueryable(); if (categoryID > 0) { talents = talents.Where(m => m.TalentCategories.Select(c => c.CategoryId).Contains(categoryID)); } talents = ApplySorting(talents, sort); //switch (sort) //{ // case SortTypeEnum.priceAsc: // talents = talents.OrderBy(m => m.Price); // break; // case SortTypeEnum.priceDesc: // talents = talents.OrderByDescending(m => m.Price); // break; // case SortTypeEnum.alphabetAz: // talents = talents.OrderBy(m => m.FullName); // break; // case SortTypeEnum.alphabetZa: // talents = talents.OrderByDescending(m => m.FullName); // break; // //this case will be uncommented // //when responseTime field will be filled after giving response to request // //case SortTypeEnum.responseTime: // // result = result.OrderBy(m => m.FirstName).ThenBy(m => m.LastName); // // break; // default: //def // talents = talents.OrderBy(m => m.ID); // break; //} if (count.HasValue && count > 0) { talents = talents.Take(count.Value); } return(talents); }
public ISortType CreateSortType(SortTypeEnum sortType) { switch (sortType) { case SortTypeEnum.Ascending: { return(new AscendingSortType()); } case SortTypeEnum.Descending: { return(new DescendingSortType()); } default: { return(null); } } }
public async Task <IQueryable <StarWars> > QueryAsync( string query, int page, int limit, string orderBy, SortTypeEnum ascending) { // Use Request.Query or Model Binding as declared inside Action // Method 1. Use BaseControllerr // (var isNeedPaging, var pagingUriParam) = await this.TryGetPagingUriParamsAsync(); // Method 2. Use ControllerBaseExtensions var isNeedPaging = this.TryGetPagingUriParams(this.Request, out PagingUriParam pagingUriParam); if (pagingUriParam.ValidationErrors != null && pagingUriParam.ValidationErrors.Count > 0) { this._logger.LogError(pagingUriParam.ValidationErrors.Aggregate(string.Empty, (x, y) => x + ";" + y)); this.Response.StatusCode = StatusCodes.Status400BadRequest; return(null); } IQueryable <StarWars> pagedTableData = null; var queryedData = this._tableDatas.AsQueryable().Where(x => x.Name.Contains(query, StringComparison.InvariantCultureIgnoreCase)); if (isNeedPaging) { pagedTableData = queryedData.Paging(pagingUriParam.OrderBy, pagingUriParam.SortType, pagingUriParam.Page.Value, pagingUriParam.Limit.Value); } else { pagedTableData = this._tableDatas.AsQueryable().OrderBy(x => x.Name); } // Set X-Total-Count this.Response.Headers.Add(CustomHttpHeaderFactory.TotalCount, queryedData.Count().ToString()); return(await Task.FromResult(pagedTableData)); }
public IQueryable <Talent> GetNew(int?categoryID, int?count = null, SortTypeEnum sort = SortTypeEnum.def) { IQueryable <Talent> talents = GetWithRelatedDataForSearchAsIQueryable(); talents = talents.Where(m => m.DateCreated >= DateTime.Now.AddDays(-360)); //must be set to -7 if (categoryID.HasValue && categoryID > 0) { talents = talents.Where(m => m.TalentCategories .Select(c => c.CategoryId) .Contains(categoryID.Value)); } talents = ApplySorting(talents, sort); if (count.HasValue && count > 0) { talents = talents.Take(count.Value); } return(talents); }
public IQueryable <Talent> GetFeatured(int?categoryID, int?count = null, SortTypeEnum sort = SortTypeEnum.def) { IQueryable <Talent> talents = GetWithRelatedDataForSearchAsIQueryable(); talents = talents.Where(m => m.IsFeatured); if (categoryID.HasValue && categoryID > 0) { talents = talents.Where(m => m.TalentCategories .Select(c => c.CategoryId) .Contains(categoryID.Value)); } talents = ApplySorting(talents, sort); if (count.HasValue && count > 0) { talents = talents.Take(count.Value); } return(talents); }
/// <summary> /// 从数据库中按表的ID列值分页大小进行获取实体对象集合(返回值不需要判断是否为null),默认返回按表中的标识字段降序排序的集合。 /// </summary> /// <param name="pageSize">分页大小,即分页显示多少条记录</param> /// <param name="pageIndex">当前页码</param> /// <param name="condition">指定的条件,要求带SQL语句Where关键字的条件,如果不带Where关键字则条件无效,如果条件字符串中含有SQL参数标记(@),且必须写成如下格式:(@p0,@p1...) /// <example>e.g.:[UserName]=@p0 AND [Password] = @p1</example></param> /// <param name="orderByColumnName">排序字段名称,不要求带ORDER BY关键字,只要指定排序字段名称即可</param> /// <param name="sortType">SQL语句排序类型</param> /// <param name="parameterValues">SQL参数对应值的集合,如果条件字符串中含有参数标记,则必须设置该数组的值</param> /// <param name="columnNames">该实体对象中对应的数据库表的列名</param> /// <returns>当前分页的所有取实体对象集合</returns> public virtual PageData <T> SelectWithPageSizeByIdentity(int pageSize, int pageIndex, string condition, string orderByColumnName, SortTypeEnum sortType, object[] parameterValues, params string[] columnNames) { return(this.SelectWithPageSizeByRowId(pageSize, pageIndex, condition, orderByColumnName, sortType, parameterValues, columnNames)); }
public BaseResponse <List <CommonValueViewModel> > GetListCommonValueByGroup(string valueGroup, int lang = 1, SortTypeEnum sortType = SortTypeEnum.Ascending) { try { var result = _commonContext.CommonValues .AsNoTracking() .Where(e => valueGroup.ToUpper().Equals(e.ValueGroup.ToUpper())) .Select(k => _mapper.Map <CommonValues, CommonValueViewModel>(k)) .OrderBy("OrderBy " + sortType.ToString()) .ToList(); result = LoadResouceValue(result, lang); return(BaseResponse <List <CommonValueViewModel> > .Success(result)); } catch (Exception ex) { return(BaseResponse <List <CommonValueViewModel> > .InternalServerError(new List <CommonValueViewModel>(), message : ex.Message, fullMsg : ex.StackTrace)); } }
public RQLib.RQQueryForm.RQquery.SortType Cast(SortTypeEnum sortType) { return((RQLib.RQQueryForm.RQquery.SortType)sortType); }
public ModelParameters(SortTypeEnum sortType) { this.SortType = sortType; }
/// <summary> /// 从数据库中按表的ID列值分页大小进行获取实体对象集合(返回值不需要判断是否为null),默认返回按表中的标识字段降序排序的集合。 /// </summary> /// <param name="pageSize">分页大小,即分页显示多少条记录</param> /// <param name="pageIndex">当前页码</param> /// <param name="condition">指定的条件,要求带SQL语句Where关键字的条件,如果不带Where关键字则条件无效,如果条件字符串中含有SQL参数标记(@),且必须写成如下格式:(@p0,@p1...) /// <example>e.g.:[UserName]=@p0 AND [Password] = @p1</example></param> /// <param name="orderByColumnName">排序字段名称,不要求带ORDER BY关键字,只要指定排序字段名称即可</param> /// <param name="sortType">SQL语句排序类型</param> /// <param name="parameterValues">SQL参数对应值的集合,如果条件字符串中含有参数标记,则必须设置该数组的值</param> /// <param name="columnNames">该实体对象中对应的数据库表的列名</param> /// <returns>当前分页的所有取实体对象集合</returns> public virtual PageData <T> SelectWithPageSizeByIdentity(int pageSize, int pageIndex, string condition, string orderByColumnName, SortTypeEnum sortType, object[] parameterValues, params string[] columnNames) { if (!this.ContainWhere(condition)) { throw new ArgumentException("指定的条件,要求带SQL语句Where关键字的条件", "condition"); } if (pageSize < 1) { pageSize = 10; } if (pageIndex < 0) { pageIndex = 0; } //临时表大小 int tempTableSize = pageIndex * pageSize; //设置SQL语句的MAX函数或MIN函数返回空值时的替换值 int isNullValue = 0; //设置第五个格式化参数为去掉Where字符串 string param5 = string.IsNullOrEmpty(condition) ? string.Empty : string.Format("AND {0}", condition.Trim().Substring(5)); string orderBy = string.IsNullOrEmpty(orderByColumnName) ? string.Format("[{0}] {1}", this._primaryKey, sortType.ToString()) : string.Format("[{0}] {1}", orderByColumnName, sortType.ToString()); //获取筛选列 string columns = this.GetColumns(columnNames); columns = string.Format("TOP {0} {1}", pageSize, columns); //分页获取数据库记录集的SQL语句 StringBuilder sqlFormat = new StringBuilder(); if (sortType == SortTypeEnum.ASC) { sqlFormat.Append("SELECT {0} "); sqlFormat.Append("FROM [{1}] "); sqlFormat.Append("WHERE ([{2}] > "); sqlFormat.Append("(SELECT ISNULL(MAX([{2}]),{7}) "); sqlFormat.Append("FROM (SELECT TOP {3} [{2}] "); sqlFormat.Append("FROM [{1}] {4} "); sqlFormat.Append("ORDER BY {6}) AS TempTable) {5} ) "); sqlFormat.Append("ORDER BY {6}"); } else { sqlFormat.Append("SELECT {0} "); sqlFormat.Append("FROM [{1}] "); sqlFormat.Append("WHERE ([{2}] < "); sqlFormat.Append("(SELECT ISNULL(MIN([{2}]),{7}) "); sqlFormat.Append("FROM (SELECT TOP {3} [{2}] "); sqlFormat.Append("FROM [{1}] {4} "); sqlFormat.Append("ORDER BY {6}) AS TempTable) {5} ) "); sqlFormat.Append("ORDER BY {6}"); //设置为最大整数值 isNullValue = int.MaxValue; } string sqlCmd = string.Format(sqlFormat.ToString(), columns, this._tableName, this._primaryKey, tempTableSize, condition, param5, orderBy, isNullValue); return(this.GetPageData(condition, parameterValues, columnNames, sqlCmd, CommandType.Text)); }
public List <CommonValueViewModel> GetListCommonValueByGroupAsync(string valueGroup, SortTypeEnum sortType = SortTypeEnum.Descending) { return(GetListCommonValueByGroup(valueGroup, sortType)); }
/// <summary> /// 从数据库中获取满足指定条件的前N项实体对象集合(返回值不需判断是否为null)。 /// </summary> /// <param name="topN">表中的前N条记录</param> /// <param name="condition">指定的条件,要求带SQL语句Where关键字的条件,如果条件字符串中含有SQL参数标记(@),且必须写成如下格式:(@p0,@p1...) /// <param name="orderByColumnName">排序字段名称,不要求带ORDER BY关键字,只要指定排序字段名称即可</param> /// <param name="sortType">SQL语句排序类型</param> /// <example>e.g.:[UserName]=@p0 AND [Password] = @p1</example></param> /// <param name="parameterValues">SQL参数对应值的集合,如果条件字符串中含有参数标记,则必须设置该数组的值</param> /// <param name="columnNames">该实体对象中对应的数据库表的列名</param> /// <returns>实体对象集合</returns> public virtual List <T> SelectTopN(int topN, string condition, string orderByColumnName, SortTypeEnum sortType, object[] parameterValues, params string[] columnNames) { //如果topN的值不合法. if (topN < 1) { return(this.SelectWithCondition(condition, columnNames)); } if (!this.ContainWhere(condition)) { throw new ArgumentException("指定的条件,要求带SQL语句Where关键字的条件", "condition"); } //获取筛选列 string columns = this.GetColumns(columnNames); columns = string.Format("TOP {0} {1}", topN, columns); string sqlCmd = string.Format("SELECT {0} FROM [{1}] {2} ", columns, this._tableName, condition); if (!string.IsNullOrEmpty(orderByColumnName)) { sqlCmd = string.Format("{0} ORDER BY [{1}] {2}", sqlCmd, orderByColumnName, sortType.ToString()); } return(this.GetEntities(sqlCmd, parameterValues, columnNames)); }
/// <summary> /// 利用数据库表的RowId属性对数据进行分页查询的方法,该方法只能支持SqlServer2005以上版的数据库(返回值不需判断是否为null)。 /// </summary> /// <param name="pageSize">分页大小,即分页显示多少条记录</param> /// <param name="pageIndex">当前页码</param> /// <param name="condition">指定的条件,要求带SQL语句Where关键字的条件,如果不带Where关键字则条件无效,如果条件字符串中含有SQL参数标记(@),且必须写成如下格式:(@p0,@p1...) /// <example>e.g.:[UserName]=@p0 AND [Password] = @p1</example></param> /// <param name="orderByColumnName">排序字段名称,不要求带ORDER BY关键字,只要指定排序字段名称即可</param> /// <param name="sortType">SQL语句排序类型</param> /// <param name="parameterValues">SQL参数对应值的集合,如果条件字符串中含有参数标记,则必须设置该数组的值</param> /// <param name="columnNames">该实体对象中对应的数据库表的列名</param> /// <returns>当前分页的所有取实体对象集合</returns> public virtual PageData <T> SelectWithPageSizeByRowId(int pageSize, int pageIndex, string condition, string orderByColumnName, SortTypeEnum sortType, object[] parameterValues, params string[] columnNames) { if (string.IsNullOrEmpty(orderByColumnName)) { throw new ArgumentException("排序字段名称,不要求带ORDER BY关键字,只要指定排序字段名称即可", "orderByColumnName"); } if (!this.ContainWhere(condition)) { throw new ArgumentException("指定的条件,要求带SQL语句Where关键字的条件", "condition"); } //设置默认分页参数 if (pageSize < 1) { pageSize = 10; } if (pageIndex < 0) { pageIndex = 0; } //设置分页起点与终点 int startRowId = (pageIndex - 1) * pageSize + 1; int endRowId = pageSize * pageIndex; endRowId = endRowId == 0 ? pageSize : endRowId; //获取筛选列 string columns = this.GetColumns(columnNames); //分页获取数据库记录集的SQL语句 StringBuilder sqlFormat = new StringBuilder(); sqlFormat.Append("SELECT * "); sqlFormat.Append("FROM (SELECT {0}, "); sqlFormat.Append("ROW_NUMBER() OVER (ORDER BY {1} {2}) AS 'RowId' "); sqlFormat.Append("FROM [{3}] {4}) as temptable "); sqlFormat.Append("WHERE RowId BETWEEN {5} AND {6} "); string sqlCmd = string.Format(sqlFormat.ToString(), columns, orderByColumnName, sortType.ToString(), this._tableName, condition, startRowId, endRowId); return(GetPageData(condition, parameterValues, columnNames, sqlCmd)); }
public OrderByOperand(SortTypeEnum sortType, params string[] columnNames) { this._sortType = sortType; this._columnNames = columnNames; }
public List <CommonValueViewModel> GetListCommonValueByGroup(string valueGroup, SortTypeEnum sortType = SortTypeEnum.Ascending) { try { return(_context.CommonValues .Where(e => valueGroup.Equals(e.ValueGroup)) .Select(k => _mapper.Map <CommonValues, CommonValueViewModel>(k)) .OrderBy("OrderBy " + sortType.ToString()) .ToList()); } catch (Exception ex) { return(new List <CommonValueViewModel>()); } }
public IQueryable <T> GetDataPart(Expression <Func <T, bool> > where, Expression <Func <T, dynamic> > sort, SortTypeEnum sortType, int skipCount, int takeCount) { throw new NotImplementedException(); }
/// <summary> /// 从数据库中获取所有的实体对象集合(返回值不需判断是否为null)。 /// </summary> /// <param name="orderByColumnName">排序字段名称,不要求带ORDER BY关键字,只要指定排序字段名称即可</param> /// <param name="sortType">SQL语句排序类型</param> /// <param name="columnNames">该实体对象中对应的数据库表的列名</param> /// <returns>实体对象集合</returns> public virtual List <T> Select(string orderByColumnName, SortTypeEnum sortType, params string[] columnNames) { return(this.SelectWithCondition(string.Empty, orderByColumnName, sortType, null, columnNames)); }
/// <summary> /// Paging extension method /// </summary> /// <typeparam name="T">Generice type</typeparam> /// <param name="data">IQueryable collection</param> /// <param name="orderBy">The column name to sort</param> /// <param name="sortType">Ascending or Descending</param> /// <param name="page">Page</param> /// <param name="limit">Limit(Page size)</param> /// <returns>Paged IQueryable collection</returns> public static IQueryable <T> Paging <T>(this IQueryable <T> data, string orderBy, SortTypeEnum sortType, int page, int limit) { IQueryable <T> pagingData = null; // Convert orderBy column name from Lowercase-camel to Uppercase-camel orderBy = orderBy.ToFirstCharUpper(); // Sort and take paged data switch (sortType) { case SortTypeEnum.Descending: pagingData = data.OrderBy($"{orderBy} DESC").Skip(limit * (page - 1)).Take(limit); break; case SortTypeEnum.Ascending: default: pagingData = data.OrderBy(orderBy).Skip(limit * (page - 1)).Take(limit); break; } return(pagingData); }
/// <summary> /// 从数据库中按分页大小进行获取实体对象集合(返回值不需判断是否为null),默认返回按表中的字段降序排序的集合, /// notinColumnName(指定的筛选列名称),该参数为必须指定,且为当前表中合法的列名称。如果未指定列名称,该方法将返回null。 /// </summary> /// <param name="pageSize">分页大小,即分页显示多少条记录</param> /// <param name="pageIndex">当前页码</param> /// <param name="condition">指定的条件,要求带SQL语句Where关键字的条件,如果不带Where关键字则条件无效,如果条件字符串中含有SQL参数标记(@),且必须写成如下格式:(@p0,@p1...) /// <example>e.g.:[UserName]=@p0 AND [Password] = @p1</example></param> /// <param name="notinColumnName">指定的筛选列名称,该参数为必须指定,且为当前表中合法的列名称。如果未指定列名称,该方法将返回null</param> /// <param name="orderByColumnName">排序字段名称,不要求带ORDER BY关键字,只要指定排序字段名称即可</param> /// <param name="sortType">SQL语句排序类型</param> /// <param name="parameterValues">SQL参数对应值的集合,如果条件字符串中含有参数标记,则必须设置该数组的值</param> /// <param name="columnNames">该实体对象中对应的数据库表的列名</param> /// <returns>当前分页的所有取实体对象集合</returns> public virtual PageData <T> SelectWithPageSizeByNotIn(int pageSize, int pageIndex, string condition, string notinColumnName, string orderByColumnName, SortTypeEnum sortType, object[] parameterValues, params string[] columnNames) { if (string.IsNullOrEmpty(notinColumnName)) { throw new ArgumentException("请指定的筛选列名称,该参数为必须指定,且为当前表中合法的列名称", "notinColumnName"); } if (!this.ContainWhere(condition)) { throw new ArgumentException("指定的条件,要求带SQL语句Where关键字的条件", "condition"); } //设置默认分页参数 if (pageSize < 1) { pageSize = 10; } if (pageIndex < 0) { pageIndex = 0; } //临时表大小 int tempTableSize = pageIndex * pageSize; //获取筛选列 string columns = this.GetColumns(columnNames); columns = string.Format("TOP {0} {1}", pageSize, columns); //设置第五个格式化参数为去掉Where字符串 string param5 = string.IsNullOrEmpty(condition) ? string.Empty : string.Format("AND {0}", condition.Trim().Substring(5)); string orderBy = string.IsNullOrEmpty(orderByColumnName) ? string.Format("[{0}] {1}", notinColumnName, sortType.ToString()) : string.Format("[{0}] {1}", orderByColumnName, sortType.ToString()); string notInCaluse = tempTableSize > 0 ? string.Format("WHERE [{5}] NOT IN (SELECT TOP {0} [{1}] FROM [{2}] {3} ORDER BY {4})", tempTableSize, notinColumnName, this._tableName, condition, orderBy, notinColumnName) : string.Empty; //分页获取数据库记录集的SQL语句 StringBuilder sqlFormat = new StringBuilder(); sqlFormat.Append("SELECT {0} "); sqlFormat.Append("FROM [{1}] "); sqlFormat.Append("{2} "); sqlFormat.Append("{3} ORDER BY {4}"); string sqlCmd = string.Format(sqlFormat.ToString(), columns, this._tableName, notInCaluse, condition, orderBy); return(GetPageData(condition, parameterValues, columnNames, sqlCmd)); }
public static OrderByOperand OrderBy(SortTypeEnum sortType, params string[] columnNames) { return new OrderByOperand(sortType, columnNames); }
/// <summary> /// 利用存储过程对表中数据进行分页查询的方法(返回值不需判断是否为null)。 /// </summary> /// <param name="pageSize">分页大小,即分页显示多少条记录</param> /// <param name="pageIndex">当前页码</param> /// <param name="condition">指定的条件,要求不带SQL语句Where关键字的条件,空值为无条件</param> /// <param name="orderby">排序字段名称,不要求带ORDER BY关键字,只要指定排序字段名称即可</param> /// <param name="sortType">SQL语句排序类型</param> /// <param name="parameterValues">SQL参数对应值的集合,如果条件字符串中含有参数标记,则必须设置该数组的值</param> /// <param name="columnNames">该实体对象中对应的数据库表的列名</param> /// <returns>当前分页的所有取实体对象集合</returns> public virtual PageData <T> SelectWithPageSizeByStoredProcedure(int pageSize, int pageIndex, string condition, string orderby, SortTypeEnum sortType, params string[] columnNames) { if (this.ContainWhere(condition)) { throw new ArgumentException("指定的条件,要求不带SQL语句Where关键字的条件", "condition"); } OleDbParameter[] parameters = { new OleDbParameter("@tblName", OleDbType.VarChar, 255), new OleDbParameter("@fldName", OleDbType.VarChar, 255), new OleDbParameter("@OrderfldName", OleDbType.VarChar, 255), new OleDbParameter("@StatfldName", OleDbType.VarChar, 255), new OleDbParameter("@PageSize", OleDbType.Integer), new OleDbParameter("@PageIndex", OleDbType.Integer), new OleDbParameter("@IsReCount", OleDbType.Integer), new OleDbParameter("@OrderType", OleDbType.Integer), new OleDbParameter("@strWhere", OleDbType.VarChar, 1000) }; parameters[0].Value = this._tableName; parameters[1].Value = "*"; parameters[2].Value = orderby; parameters[3].Value = ""; parameters[4].Value = pageSize; parameters[5].Value = pageIndex; parameters[6].Value = 0; parameters[7].Value = (int)sortType; parameters[8].Value = condition; PageData <T> pageData = new PageData <T>(); pageData.RecordSet = this.GetEntities("sp_SelectWithPageSize", parameters, CommandType.StoredProcedure, columnNames); pageData.Count = this.Count(condition); return(pageData); //存储过程代码 //CREATE PROCEDURE [dbo].[sp_SelectWithPageSize] // @tblName varchar(255), -- 表名 // @fldName varchar(255), -- 字段名 // @PageSize int = 10, -- 页尺寸 // @PageIndex int = 1, -- 页码 // @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回 // @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 // @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where) //AS //declare @strSQL varchar(6000) -- 主语句 //declare @strTmp varchar(100) -- 临时变量 //declare @strOrder varchar(400) -- 排序类型 //if @OrderType != 0 //begin // set @strTmp = '<(select min' // set @strOrder = ' order by [' + @fldName +'] desc' //end //else //begin // set @strTmp = '>(select max' // set @strOrder = ' order by [' + @fldName +'] asc' //end //set @strSQL = 'select top ' + str(@PageSize) + ' * from [' // + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' // + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' // + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' // + @strOrder //if @strWhere != '' // set @strSQL = 'select top ' + str(@PageSize) + ' * from [' // + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' // + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' // + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' // + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder //if @PageIndex = 1 //begin // set @strTmp ='' // if @strWhere != '' // set @strTmp = ' where ' + @strWhere // set @strSQL = 'select top ' + str(@PageSize) + ' * from [' // + @tblName + ']' + @strTmp + ' ' + @strOrder //end //if @IsReCount != 0 // set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere //exec (@strSQL) }
/// <summary> /// 从数据库中获取满足指定条件的实体对象集合(返回值不需判断是否为null)。 /// </summary> /// <param name="condition">指定的条件,要求带SQL语句Where关键字的条件,如果条件字符串中含有SQL参数标记(@),且必须写成如下格式:(@p0,@p1...) /// <param name="orderByColumnName">排序字段名称,不要求带ORDER BY关键字,只要指定排序字段名称即可</param> /// <param name="sortType">SQL语句排序类型</param> /// <example>e.g.:[UserName]=@p0 AND [Password] = @p1</example></param> /// <param name="parameterValues">SQL参数对应值的集合,如果条件字符串中含有参数标记,则必须设置该数组的值</param> /// <param name="columnNames">该实体对象中对应的数据库表的列名</param> /// <returns>实体对象集合</returns> public virtual List <T> SelectWithCondition(string condition, string orderByColumnName, SortTypeEnum sortType, object[] parameterValues, params string[] columnNames) { if (!this.ContainWhere(condition)) { throw new ArgumentException("指定的条件,要求带SQL语句Where关键字的条件", "condition"); } //获取筛选列 string columns = this.GetColumns(columnNames); string sqlCmd = string.Format("SELECT {0} FROM {1} {2} {3}", columns, this._tableName, condition, string.IsNullOrEmpty(orderByColumnName) ? string.Empty : string.Format("ORDER BY {0} {1}", orderByColumnName, sortType.ToString())); return(this.GetEntities(sqlCmd, parameterValues, columnNames)); }
/// <summary> /// Verileri kolonlarını seçerek getirir Skip ve Limit dahil eder. /// SELECT TOP TAKECOUNT * FROM T WHERE PREDICATE ORDER BY SORT SORTTYPE /// </summary> ///// <param name="where">Veri şartı</param> /// <param name="sort">Sıralama şartı</param> /// <param name="sortType">Sıralama tipi</param> /// <param name="skipCount">Getirilen verilerde atlanacak veri sayısı</param> /// <param name="takeCount">Getirilen verilerde alınacak veri sayısı</param> /// <returns></returns> public IQueryable <T> GetDataPart(Expression <Func <T, bool> > where1, Expression <Func <T, dynamic> > sort, SortTypeEnum sortType, int skipCount, int takeCount) { if (sortType == SortTypeEnum.DESC) { return(_dbSet .AsNoTracking() .Where(where1) .Skip(skipCount) .Take(takeCount)); } return(_dbSet .AsNoTracking() .OrderBy(sort) .Where(where1) .Skip(skipCount) .Take(takeCount)); }
/// <summary> /// 利用数据库表的RowId属性对数据进行分页查询的方法(返回值不需判断是否为null)。 /// </summary> /// <param name="pageSize">分页大小,即分页显示多少条记录</param> /// <param name="pageIndex">当前页码</param> /// <param name="condition">指定的条件,要求带SQL语句Where关键字的条件,如果不带Where关键字则条件无效,如果条件字符串中含有SQL参数标记(@),且必须写成如下格式:(@p0,@p1...) /// <example>e.g.:[UserName]=@p0 AND [Password] = @p1</example></param> /// <param name="orderByColumnName">排序字段名称,不要求带ORDER BY关键字,只要指定排序字段名称即可</param> /// <param name="sortType">SQL语句排序类型</param> /// <param name="parameterValues">SQL参数对应值的集合,如果条件字符串中含有参数标记,则必须设置该数组的值</param> /// <param name="columnNames">该实体对象中对应的数据库表的列名</param> /// <returns>当前分页的所有取实体对象集合</returns> public virtual PageData <T> SelectWithPageSizeByRowId(int pageSize, int pageIndex, string condition, string orderByColumnName, SortTypeEnum sortType, object[] parameterValues, params string[] columnNames) { if (string.IsNullOrEmpty(orderByColumnName)) { throw new ArgumentException("排序字段名称,不要求带ORDER BY关键字,只要指定排序字段名称即可", "orderByColumnName"); } if (!this.ContainWhere(condition)) { throw new ArgumentException("指定的条件,要求带SQL语句Where关键字的条件", "condition"); } //设置默认分页参数 if (pageSize < 1) { pageSize = 10; } if (pageIndex < 0) { pageIndex = 0; } //设置分页起点与终点 int startRowId = pageIndex * pageSize; //获取筛选列 string columns = this.GetColumns(columnNames); //分页获取数据库记录集的SQL语句 StringBuilder sqlFormat = new StringBuilder(); sqlFormat.Append("SELECT {0} "); sqlFormat.Append("FROM {1} "); sqlFormat.Append("{2} ORDER BY {3} {4} LIMIT {5},{6} "); string sqlCmd = string.Format(sqlFormat.ToString(), columns, this._tableName, condition, orderByColumnName, sortType.ToString(), startRowId, pageSize); return(this.GetPageData(condition, parameterValues, columnNames, sqlCmd)); }