/// <summary>
/// 进行分页查询
/// </summary>
/// <typeparam name="TModel"></typeparam>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="sql"></param>
/// <param name="filterLinked">条件链表</param>
/// <param name="sort">排序规则</param>
/// <returns></returns>
public PageResult <TModel> Page <TModel>(int page, int rows, string sql, FilterLinked filterLinked = null, Sort sort = null)
{
if (sort == null)
{
throw new ArgumentNullException("sort");
}
PageResult <TModel> result = new PageResult <TModel>();
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
//List<string> conditions = new List<string>();
//List<string> sorts = new List<string>();
//this.BuildSql(conditions, dbParameters, sqlParams);
//this.BuildSql(sorts, sqlParams);
//if (sqlParams.Count<DynamicSqlParam>(x => (x.Type == DynamicSqlParamType.Sort)) <= 0)
//{
// throw new ArgumentException("分页时至少指定一个排序规则");
//}
sql = BuildSql(sql, filterLinked, sort, dbParameters);
result.Page = page;
result.RecordCount = Count(sql, dbParameters.ToArray());
result.PageCount = (int)Math.Ceiling((double)(((double)result.RecordCount) / ((double)rows)));
string str = Regex.Replace(sql.TrimStart(), "^select?", "SELECT top 100 percent ROW_NUMBER() OVER(ORDER BY " + string.Join(",", sort.ToString()) + ") _index,", RegexOptions.IgnoreCase);
object[] objArray = new object[] { "select * from (", str, ") pagerTmp where pagerTmp._index between ", (((page - 1) * rows) + 1).ToString(), " and ", page *rows };
str = string.Concat(objArray);
result.Data = this.GetList <TModel>(str, dbParameters.ToArray());
return(result);
}
public IList <TEntity> GetList(string sql, FilterLinked filterLinked = null, Sort sort = null)
{
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
sql = this.BuildSql(sql, filterLinked, sort, dbParameters);
return(this.GetList(sql, dbParameters.ToArray()));
}
//private string BuildSql(string sql, List<IDbDataParameter> dbParameters, params DynamicSqlParam[] sqlParams)
//{
// List<string> conditions = new List<string>();
// List<string> sorts = new List<string>();
// this.BuildSql(conditions, dbParameters, sqlParams);
// this.BuildSql(sorts, sqlParams);
// return this.BuildSql(sql, conditions, sorts);
//}
//private string BuildSql(string sql, List<string> conditions, List<string> orderbys)
//{
// sql = sql.ToLower();
// if (conditions.Count > 0)
// {
// if (sql.IndexOf("{@dynamicCondition}", StringComparison.InvariantCultureIgnoreCase) > 0)
// {
// sql = Regex.Replace(sql, "{@dynamicCondition}", " and " + string.Join(" and ", conditions), RegexOptions.IgnoreCase);
// }
// else
// {
// if (!sql.EndsWith("where "))
// {
// if (sql.EndsWith("where"))
// {
// sql = sql + " ";
// }
// else
// {
// sql = sql + " where ";
// }
// }
// sql = sql + string.Join(" and ", conditions);
// }
// }
// else if (sql.IndexOf("{@dynamicCondition}", StringComparison.InvariantCultureIgnoreCase) > 0)
// {
// sql = Regex.Replace(sql, "{@dynamicCondition}", string.Empty, RegexOptions.IgnoreCase);
// }
// if (orderbys.Count > 0)
// {
// sql = sql + " order by " + string.Join(",", orderbys);
// }
// return sql;
//}
///// <summary>
///// 将给定的DynamicSqlParam参数中的排序参数转换成条件字符串列表
///// </summary>
///// <param name="sorts">用于返回转换结果集</param>
///// <param name="sqlParams">要转换的动态sql参数</param>
//private void BuildSql(List<string> sorts, params DynamicSqlParam[] sqlParams)
//{
// foreach (DynamicSqlSorter sorter in from x in sqlParams
// where x.Type == DynamicSqlParamType.Sort
// select x)
// {
// sorts.Add(sorter.ToSql());
// }
//}
/// <summary>
/// 将提供的不带条件子句的sql语句拼接为完整的sql语句
/// </summary>
/// <param name="sql">原始sql</param>
/// <param name="filterLinked">条件链表</param>
/// <param name="sort">排序对象</param>
/// <returns></returns>
string BuildSql(string sql, FilterLinked filterLinked, Sort sort, List <IDbDataParameter> dbParameters)
{
if (dbParameters == null)
{
throw new ArgumentNullException("dbParameters");
}
StringBuilder sqlBuilder = new StringBuilder(sql);
Dictionary <string, object> parameters = new Dictionary <string, object>();
if (filterLinked != null)
{
string condition = filterLinked.ToString(parameters);
foreach (var parameterName in parameters.Keys)
{
dbParameters.Add(CreateParameter(parameterName, parameters[parameterName]));
}
sqlBuilder.Append(" where ").Append(condition);
}
if (sort != null)
{
string order = sort.ToString();
sqlBuilder.Append(" order by ").Append(order);
}
return(sqlBuilder.ToString());
}
public IList <TModel> GetList <TModel>(string sql, FilterLinked filterLinked = null, Sort sort = null)
{
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
sql = this.BuildSql(sql, filterLinked, sort, dbParameters);
DataSet ds = this._helper.ExecuteQuery(sql, dbParameters);
return(Mapper.MapList <TModel>(ds));
}
/// <summary>
/// 将Ext.Net的参数中的过滤参数转换成条件链表
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
public static FilterLinked ConvertToFilterLinked(StoreReadDataEventArgs e, Func <FilterCondition, object> converter = null)
{
FilterLinked filterLinked = null;
if (string.IsNullOrEmpty(e.Parameters["filter"]))
{
return(filterLinked);
}
FilterConditions filters = new FilterConditions(e.Parameters["filter"]);
foreach (var item in filters.Conditions)
{
SqlFilter filter = null;
string field = item.Field;
if (converter != null)
{
//执行开发者指定的转换，返回null将进行默认的转换
var result = converter(item);
if (result != null)
{
if (result is SqlFilter)
{
filter = result as SqlFilter;
}
else if (result is string)
{
field = Convert.ToString(result);
}
else
{
throw new ArgumentException("ConvertToFilterLinked方法中的converter参数中的返回值只能是string型或FilterLinked型，为string型表示只自定义转换字段名，为SqlFilter型时表示完全自定义");
}
}
else
{
filter = ConvertToFilter(item, field);
}
}
if (filter == null)
{
filter = ConvertToFilter(item, field);
}
if (filterLinked == null)
{
filterLinked = new FilterLinked(filter);
}
else
{
filterLinked.AddFilter(LogicOperation.And, filter);
}
}
return(filterLinked);
}
public List <RolePrivilegeModel> GetRoles(int page, int pageSize, out int recordCount, FilterLinked filterLinked = null, Sort sort = null)
{
Dictionary <string, object> parameters = new Dictionary <string, object>();
string condition = string.Empty, order = string.Empty;
if (filterLinked != null)
{
condition = filterLinked.ToString(parameters);
}
if (sort != null)
{
order = sort.ToString();
}
//string dynamicConditions = this.ParseDynamicConditions(e, parameters, fieldMap);
var list2 = this._privilegeBase.GetRoles(page, pageSize, condition, order, out recordCount, parameters.Values.ToArray()).ToList();
list2.ForEach(delegate(RolePrivilegeModel role)
{
if (role.Name == this._privilegeBase.SystemRoleName)
{
role.Privileges = "全部权限";
}
});
return(list2);
}
public IList <UserRoleDetailInfo> GetUsers(int page, int pageSize, out int recordCount, FilterLinked filterLinked = null, Sort sort = null)
{
Dictionary <string, object> parameters = new Dictionary <string, object>();
string condition = null, order = null;
if (filterLinked != null)
{
condition = filterLinked.ToString(parameters);
}
if (sort != null)
{
order = sort.ToString();
}
return(_privilegeBase.GetUsers(page, pageSize, out recordCount, condition, order, parameters.Values.ToArray()));
}
public PageResult <TEntity> Page(int page, int rows, string sql, FilterLinked filterLinked = null, Sort sort = null)
{
return(Page <TEntity>(page, rows, sql, filterLinked, sort));
}
//public Dictionary<TKey, TModel> GetDictionaryBy<TModel, TKey>(string sql, Func<TModel, TKey> keySelector, params DynamicSqlParam[] sqlParams) where TModel : class, new()
//{
// List<IDbDataParameter> dbParameters = new List<IDbDataParameter>();
// sql = this.BuildSql(sql, dbParameters, sqlParams);
// DataSet ds = this._helper.ExecuteQuery(sql);
// return this.EntityMapper.Map<TModel, TKey>(ds, keySelector);
//}
public IDictionary <TKey, TModel> GetDictionaryBy <TModel, TKey>(string sql, Func <TModel, TKey> keySelector, FilterLinked sqlFilter = null, Sort sort = null)
{
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
sql = this.BuildSql(sql, sqlFilter, sort, dbParameters);
DataSet ds = this._helper.ExecuteQuery(sql);
return(Mapper.Map <TKey, TModel>(ds, x => keySelector(x)));
}
public IDictionary <TKey, TEntity> GetDictionaryBy <TKey>(string sql, Func <TEntity, TKey> keySelector, FilterLinked filterLinked = null, Sort sort = null)
{
List <IDbDataParameter> dbParameters = new List <IDbDataParameter>();
sql = this.BuildSql(sql, filterLinked, sort, dbParameters);
return(this.GetDictionaryBy <TKey>(sql, keySelector, dbParameters.ToArray()));
}
/// <summary>
/// 将Ext.Net的参数中的过滤参数转换成条件链表
/// </summary>
/// <param name="e"></param>
/// <returns></returns>
public static FilterLinked ConvertToFilterLinked(StoreReadDataEventArgs e, Func<FilterCondition, object> converter = null)
{
FilterLinked filterLinked = null;
if (string.IsNullOrEmpty(e.Parameters["filter"]))
{
return filterLinked;
}
FilterConditions filters = new FilterConditions(e.Parameters["filter"]);
foreach (var item in filters.Conditions)
{
SqlFilter filter = null;
string field = item.Field;
if (converter != null)
{
//执行开发者指定的转换，返回null将进行默认的转换
var result = converter(item);
if (result != null)
{
if (result is SqlFilter)
{
filter = result as SqlFilter;
}
else if (result is string)
{
field = Convert.ToString(result);
}
else
{
throw new ArgumentException("ConvertToFilterLinked方法中的converter参数中的返回值只能是string型或FilterLinked型，为string型表示只自定义转换字段名，为SqlFilter型时表示完全自定义");
}
}
else
{
filter = ConvertToFilter(item, field);
}
}
if (filter == null)
{
filter = ConvertToFilter(item, field);
}
if (filterLinked == null)
{
filterLinked = new FilterLinked(filter);
}
else
{
filterLinked.AddFilter(LogicOperation.And, filter);
}
}
return filterLinked;
}
public List<RolePrivilegeModel> GetRoles(int page, int pageSize, out int recordCount, FilterLinked filterLinked = null, Sort sort = null)
{
Dictionary<string, object> parameters = new Dictionary<string, object>();
string condition = string.Empty, order = string.Empty;
if (filterLinked != null)
{
condition = filterLinked.ToString(parameters);
}
if (sort != null)
{
order = sort.ToString();
}
//string dynamicConditions = this.ParseDynamicConditions(e, parameters, fieldMap);
var list2 = this._privilegeBase.GetRoles(page, pageSize, condition, order, out recordCount, parameters.Values.ToArray()).ToList();
list2.ForEach(delegate(RolePrivilegeModel role)
{
if (role.Name == this._privilegeBase.SystemRoleName)
{
role.Privileges = "全部权限";
}
});
return list2;
}
public IList<UserRoleDetailInfo> GetUsers(int page, int pageSize, out int recordCount, FilterLinked filterLinked = null, Sort sort = null)
{
Dictionary<string, object> parameters = new Dictionary<string, object>();
string condition = null, order = null;
if (filterLinked != null)
{
condition = filterLinked.ToString(parameters);
}
if (sort != null)
{
order = sort.ToString();
}
return _privilegeBase.GetUsers(page, pageSize, out recordCount, condition, order, parameters.Values.ToArray());
}