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