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