/// <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)); }
/// <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 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 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)); } }
/// <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)); }
/// <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> /// 从数据库中按表的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)); }
/// <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)); }
/// <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)); }