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