/// <summary> /// 查询是否存在符合条件的记录 /// </summary> /// <param name="scopeList">范围查找的集合</param> /// <returns></returns> public bool ExistsRecord(ScopeList scopeList) { if (scopeList.HasInner) { return(_cdal.ExistsRecord <T>(scopeList)); } ParamList list = null; SelectCondition sc = GetSelectContant(list, scopeList, CurEntityInfo.DBInfo.CurrentDbAdapter.FormatParam(CurEntityInfo.PrimaryProperty[0].ParamName)); string sql = CurEntityInfo.DBInfo.CurrentDbAdapter.GetTopSelectSql(sc, 1); bool exists = false; Dictionary <string, bool> cacheTables = null; if (scopeList.UseCache) { cacheTables = _oper.DBInfo.QueryCache.CreateMap(CurEntityInfo.TableName); } using (IDataReader reader = _oper.Query(sql, list, cacheTables)) { if (reader.Read()) { exists = true; } } return(exists); }
/// <summary> /// 生成SQL语句 /// </summary> /// <param name="list">参数列表</param> /// <param name="oper">连接对象</param> /// <param name="objCondition">条件对象</param> /// <param name="objPage">分页记录类</param> /// <returns></returns> public static string CreatePageSql(ParamList list, DataBaseOperate oper, SelectCondition objCondition, PageContent objPage, bool useCache) { //if (objCondition.Condition == null || objCondition.Condition == "")//初始化查询条件 //{ // objCondition.Condition = "1=1"; //} if (objPage.CurrentPage < 0 || objPage.PageSize <= 0)//初始化页数 { return(""); } if (objPage.IsFillTotalRecords) { objPage.TotalRecords = GetTotalRecord(list, oper, objCondition, objPage, (useCache?objCondition.CacheTables:null));//获取总记录数 long totalPage = (long)Math.Ceiling((double)objPage.TotalRecords / (double)objPage.PageSize); objPage.TotalPage = totalPage; if (objPage.CurrentPage >= objPage.TotalPage - 1) { objPage.CurrentPage = objPage.TotalPage - 1; //objCondition.CurrentPage = objPage.CurrentPage; } } return(CreateCutPageSql(objCondition, objPage)); }
public PTuple GetTuplebyId(ref PRelation rel, string tupId) { var relid = rel.id; PTuple reVal = null; { var relation = RelationService.Instance().getAllRelation().Where(r => r.id.Equals(relid)).First(); tupId = tupId.Replace("{", ""); tupId = tupId.Replace("}", ""); if (!(relation is null)) { var pri = relation.schema.Attributes.Where(a => a.primaryKey).First(); var atr = String.Format("{0}.{1}", relation.relationName, pri.AttributeName); if (!(pri is null)) { try { reVal = relation.tupes.Where(t => SelectCondition.EQUAL(t.valueSet[atr].First(), tupId.Trim(), pri.Type.TypeName)).First(); } catch //ko tim thay (insert khi id is Empty) { if (String.IsNullOrEmpty(tupId)) { reVal = new PTuple(relation); } } } rel = relation; } } return(reVal); }
///// <summary> ///// 查询范围 ///// </summary> ///// <param name="star">开始条数</param> ///// <param name="totalRecord">显示条数</param> ///// <returns></returns> //public KeyWordLimitItem Limit(uint star, uint totalRecord) //{ // KeyWordLimitItem item = new KeyWordLimitItem(star, totalRecord, this); // return item; //} internal override void Tran(KeyWordInfomation info) { info.IsWhere = true; string ret = ""; SelectCondition con = info.Condition as SelectCondition; for (int i = 0; i < paramhandles.Count; i++) { BQLParamHandle prm = paramhandles[i]; string prmStr=prm.DisplayValue(info); ret +=prmStr ; if (i < paramhandles.Count - 1) { ret += ","; } if (con != null) { info.Condition.PrimaryKey.Add(prmStr); } } info.Condition.GroupBy.Append(ret); info.IsWhere = false; //return " group by " + ret; }
/// <summary> /// 获取top的查询字符串 /// </summary> /// <param name="sql">查询字符串</param> /// <param name="top">top值</param> /// <returns></returns> public string GetTopSelectSql(SelectCondition sql, int top) { StringBuilder sbSql = new StringBuilder(500); sbSql.Append("select top "); sbSql.Append(top.ToString()); sbSql.Append(" " + sql.SqlParams.ToString() + " from "); sbSql.Append(sql.Tables.ToString()); if (sql.Condition.Length > 0) { sbSql.Append(" where " + sql.Condition.ToString()); } if (sql.Orders.Length > 0) { sbSql.Append(" order by "); sbSql.Append(sql.Orders.ToString()); } if (sql.Having.Length > 0) { sbSql.Append(" having "); sbSql.Append(sql.Having.ToString()); } return(sbSql.ToString()); }
private void GenerateCurrencyType() { var generator = new CurrencyTypeGenerator(_pathToSolution); var names = generator.GetCurrencyTypesLines(); foreach (string name in names) { string fullName = name.Substring(0, name.LastIndexOf('(')); string shortName = name.Substring(name.LastIndexOf('(') + 1, 3); fullName = GeneratorHelper.RemoveDigits(fullName); var currencyType = new CurrencyType { Name = fullName, ShortName = shortName }; var condition = new SelectCondition(); var rule = new Rule(ClassNameHelper.GetFieldName <CurrencyType>(p => p.ShortName), Rule.Operations.Like, shortName); condition.Where.Rules.Add(rule); if (!Dao.IsExist <CurrencyType>(condition)) { Dao.SaveOrUpdate(currencyType); } } }
/// <summary> /// 执行sql语句,分页返回DataSet /// </summary> /// <param name="BQL">sql语句</param> public DataSet QueryDataSet <E>(BQLQuery BQL, bool useCache) { AbsCondition con = ToCondition(BQL, null, true, typeof(E)); DataSet ds = null; Dictionary <string, bool> cacheTables = null; if (useCache) { cacheTables = con.CacheTables; } con.Oper = _oper; if (con.DbParamList != null) { ds = _oper.QueryDataSet(con.GetSql(useCache), con.DbParamList, cacheTables); } else { SelectCondition sCon = con as SelectCondition; DataTable dt = con.DBinfo.CurrentDbAdapter.QueryDataTable(sCon.GetSelect(), sCon.PageContent, _oper, null); dt.TableName = "newTable"; ds = new DataSet(); ds.Tables.Add(dt); } return(ds); }
/// <summary> /// 执行sql语句,分页返回DataSet /// </summary> /// <param name="BQL">sql语句</param> /// <param name="objPage">分页对象</param> public DataSet QueryDataSet(BQLQuery bql, Type tableType, PageContent objPage, bool useCache) { AbsCondition con = ToCondition(bql, null, true, tableType); Dictionary <string, bool> cacheTables = null; if (useCache) { cacheTables = con.CacheTables; } DataSet ds = null; using (BatchAction ba = _oper.StarBatchAction()) { if (con.DbParamList != null) { con.PageContent = objPage; con.Oper = _oper; string sql = con.GetSql(useCache); ds = _oper.QueryDataSet(sql, con.DbParamList, cacheTables); } else { SelectCondition sCon = con as SelectCondition; DataTable dt = con.DBinfo.CurrentDbAdapter.QueryDataTable(sCon.GetSelect(), objPage, _oper, null); dt.TableName = "newTable"; ds = new DataSet(); ds.Tables.Add(dt); } } return(ds); }
/// <summary> /// 获取top的查询字符串 /// </summary> /// <param name="sql">查询字符串</param> /// <param name="top">top值</param> /// <returns></returns> public string GetTopSelectSql(SelectCondition sql, int top) { StringBuilder sbSql = new StringBuilder(sql.GetSelect()); //sbSql.Append(sql); sbSql.Append(" limit " + top + " offset 0"); return(sbSql.ToString()); }
/// <summary> /// 获取全部查询的条件 /// </summary> /// <param name="list">参数列表</param> /// <param name="scopeList">范围查找的集合</param> /// <returns></returns> protected string GetSelectPageContant(ParamList list, ScopeList scopeList) { SelectCondition condition = new SelectCondition(CurEntityInfo.DBInfo); condition.Oper = this._oper; condition.Tables.Append(CurEntityInfo.DBInfo.CurrentDbAdapter.FormatTableName(CurEntityInfo.TableName)); condition.SqlParams.Append(GetSelectParams(scopeList)); if (scopeList.UseCache) { condition.CacheTables = CurEntityInfo.DBInfo.QueryCache.CreateMap(CurEntityInfo.TableName); } condition.Condition.Append("1=1"); foreach (EntityPropertyInfo ep in CurEntityInfo.PrimaryProperty) { condition.PrimaryKey.Add(CurEntityInfo.DBInfo.CurrentDbAdapter.FormatParam(ep.ParamName)); } string conditionWhere = ""; SortList sortList = scopeList.OrderBy; if (scopeList != null) { condition.Condition.Append(DataAccessCommon.FillCondition(CurEntityInfo, list, scopeList)); } if (conditionWhere.Length > 0) { condition.Condition.Append(conditionWhere); } //排序方式 if (sortList != null && sortList.Count > 0) { string orderBy = GetSortCondition(sortList); if (orderBy != "") { if (condition.Orders.Length > 0) { condition.Orders.Append("," + orderBy); } else { condition.Orders.Append(orderBy); } } } condition.PageContent = scopeList.PageContent; //throw new Exception(""); condition.DbParamList = list; //if (scopeList.UseCache) //{ // cachetables[CurEntityInfo.DBInfo.CurrentDbAdapter.FormatTableName(CurEntityInfo.TableName)]=true; //} return(condition.GetSql(true)); }
public ICriteria OrderBy(ICriteria criteria, SelectCondition condition) { foreach (var orderBy in condition.OrdersBy) { var order = new Order(orderBy.Column, orderBy.Asc); criteria.AddOrder(order); } return(criteria); }
/// <summary> /// 获取top的查询字符串 /// </summary> /// <param name="sql">查询字符串</param> /// <param name="top">top值</param> /// <returns></returns> public string GetTopSelectSql(SelectCondition sql, int top) { PageContent objPage = new PageContent(); objPage.IsFillTotalRecords = false; objPage.StarIndex = 0; objPage.PageSize = top; return(CutPageSqlCreater.GetCutPageSql(sql.GetSelect(), objPage)); }
/// <summary> /// 生成分页SQL语句 /// </summary> /// <param name="part">SQL条件</param> /// <returns></returns> private static string GetCutPageSql(SelectCondition objCondition, PageContent objPage) { string orderBy = null; if (objCondition.Orders.Length > 0)//如果有排序就用本身排序 { orderBy = objCondition.Orders.ToString(); } else//如果没有就用主键排序 { StringBuilder sbOrder = new StringBuilder(); foreach (string pkName in objCondition.PrimaryKey) { sbOrder.Append(pkName + ","); } if (sbOrder.Length > 0) { sbOrder.Remove(sbOrder.Length - 1, 1); orderBy = sbOrder.ToString(); } } long starIndex = objPage.GetStarIndex() + 1; string rowNumberName = "\"__cur_rowNumber" + objPage.PagerIndex + "\""; long endIndex = objPage.PageSize * (objPage.CurrentPage + 1); StringBuilder sql = new StringBuilder(5000); sql.Append("select * from ("); sql.Append("select ROW_NUMBER() over(order by " + orderBy + ") as " + rowNumberName + ","); if (!objCondition.HasGroup) { sql.Append(objCondition.SqlParams.ToString() + " from " + objCondition.Tables.ToString()); if (objCondition.Condition.Length > 0) { sql.Append(" where " + objCondition.Condition.ToString()); } if (objCondition.GroupBy.Length > 0) { sql.Append(" group by " + objCondition.GroupBy.ToString()); } if (objCondition.Having.Length > 0) { sql.Append(" having "); sql.Append(objCondition.Having.ToString()); } } else { sql.Append("\"_tmpInnerTable\".*"); sql.Append(" from ("); Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.CutPageSqlCreater.GetGroupPart(objCondition, sql); sql.Append(") \"_tmpInnerTable\""); } sql.Append(") tmp where " + rowNumberName + " >=" + starIndex + " and " + rowNumberName + " <=" + endIndex); return(sql.ToString()); }
/// <summary> /// 获取top的查询字符串 /// </summary> /// <param name="sql">查询字符串</param> /// <param name="top">top值</param> /// <returns></returns> public string GetTopSelectSql(SelectCondition sql, int top) { StringBuilder sbSql = new StringBuilder(500); sbSql.Append(sql.GetSelect()); sbSql.Append(" fetch first "); sbSql.Append(top.ToString()); sbSql.Append(" rows only"); return(sbSql.ToString()); }
public PagedResult <T> SelectRange <T>(SelectCondition condition) where T : IEntityBase { return(TryExecute(() => { var creteria = Session.CreateCriteria(typeof(T)); creteria = _conditionResolver.Where(creteria, condition); creteria = _conditionResolver.OrderBy(creteria, condition); return _conditionResolver.ToPagedResult <T>(creteria, condition); })); }
public virtual T Select <T>(SelectCondition condition) where T : class, IEntityBase { return(TryExecute(() => { var creteria = Session.CreateCriteria(typeof(T)); creteria = _conditionResolver.Where(creteria, condition); creteria = _conditionResolver.OrderBy(creteria, condition); return creteria.UniqueResult <T>(); })); }
internal override string DisplayValue(KeyWordInfomation info) { SelectCondition con = info.Condition as SelectCondition; bool isGroup = false; if (con != null) { isGroup = con.HasGroup; } if (info.Condition.PrimaryKey.Count <= 0 && !CommonMethods.IsNull(_table) && !isGroup) { foreach (string pks in _table.GetPrimaryParam()) { StringBuilder pkStr = new StringBuilder(); if (!string.IsNullOrEmpty(_aliasName)) { pkStr.Append(info.DBInfo.CurrentDbAdapter.FormatTableName(_aliasName) + "."); } else { pkStr.Append(_table.DisplayValue(info) + "."); } pkStr.Append(pks); info.Condition.PrimaryKey.Add(pkStr.ToString()); } } string result = null; if (!CommonMethods.IsNull(_table)) { result = _table.DisplayValue(info); } else if (_query != null) { KeyWordInfomation qinfo = info.Clone() as KeyWordInfomation; qinfo.Condition = new SelectCondition(info.DBInfo); qinfo.ParamList = info.ParamList; KeyWordConver objCover = new KeyWordConver(); string sql = objCover.ToConver(_query, qinfo).GetSql(false); foreach (KeyValuePair <string, bool> kvp in qinfo.ContainTables) { info.ContainTables[kvp.Key] = true; } result = "(" + sql + ")"; } if (!string.IsNullOrEmpty(_aliasName)) { result += " " + info.DBInfo.CurrentDbAdapter.FormatTableName(_aliasName); } return(result); }
public bool IsExist <T>(SelectCondition condition) { return(TryExecute(() => { var creteria = Session.CreateCriteria(typeof(T)); creteria = _conditionResolver.Where(creteria, condition); creteria = creteria.SetProjection(Projections.RowCountInt64()); creteria.ClearOrders(); var itemscount = (long)creteria.UniqueResult(); return itemscount > 0; })); }
public static object GetJsonData <T>(PagedResult <T> items, SelectCondition selectCondition) where T : IEntityBase { var jsonData = new { total = items.ItemsCount / selectCondition.PageSize + 1, page = selectCondition.PageIndex, records = items.Result.Count, rows = items.Result.ToArray() }; return(jsonData); }
private Expression CreateExpressionFromCondition(ParameterExpression parameterExpression, SelectCondition selectCondition) { var _expressionBuilder = _compareConditionStrategy.GetExpressionBuilder(selectCondition.Condition); Expression _member = string.IsNullOrEmpty(selectCondition.PropertyName) ? (Expression)parameterExpression : Expression.Property(parameterExpression, selectCondition.PropertyName); var _constant = Expression.Constant(Convert.ChangeType(selectCondition.Value, _member.Type)); ConvertProperty(selectCondition, ref _member); return(_expressionBuilder.Build(_member, _constant)); }
/// <summary> /// 获取全部查询的条件 /// </summary> /// <param name="list">参数列表</param> /// <param name="scopeList">范围查找的集合</param> /// <param name="param">输出字段</param> /// <returns></returns> protected SelectCondition GetSelectContant(ParamList list, ScopeList scopeList, string param) { string conditionWhere = ""; string orderBy = ""; SelectCondition condition = new SelectCondition(CurEntityInfo.DBInfo); if (condition.SqlParams.Length > 0) { condition.SqlParams.Append(","); } condition.SqlParams.Append(param); condition.Tables.Append(CurEntityInfo.DBInfo.CurrentDbAdapter.FormatTableName(CurEntityInfo.TableName)); condition.Condition.Append("1=1"); if (scopeList.UseCache) { condition.CacheTables = CurEntityInfo.DBInfo.QueryCache.CreateMap(CurEntityInfo.TableName); } if (scopeList != null) { condition.Condition.Append(DataAccessCommon.FillCondition(CurEntityInfo, list, scopeList)); } if (conditionWhere.Length > 0) { condition.Condition.Append(conditionWhere); } SortList sortList = scopeList.OrderBy; if (sortList != null && sortList.Count > 0) { if (orderBy != "") { orderBy = orderBy + "," + GetSortCondition(sortList); } else { orderBy = GetSortCondition(sortList); } } if (orderBy != "") { condition.Orders.Append(orderBy); } condition.DbParamList = list; return(condition); }
public PagedResult <TE> ToPagedResult <TE>(ICriteria criteria, SelectCondition condition) where TE : IEntityBase { ICriteria countCriteria = CriteriaTransformer.Clone(criteria).SetProjection(Projections.RowCountInt64()); countCriteria.ClearOrders(); var itemscount = (long)countCriteria.UniqueResult(); var items = criteria.SetMaxResults(condition.PageSize) .SetFirstResult((condition.PageIndex - 1) * condition.PageSize) .List <TE>(); var result = new PagedResult <TE>(items, itemscount); return(result); }
public static SelectCondition ToSelectCondition(GridSettings gridSettings) { var result = new SelectCondition(gridSettings.PageSize, gridSettings.PageIndex); if (!string.IsNullOrEmpty(gridSettings.SortColumn)) { result.OrdersBy.Add(new OrderBy(gridSettings.SortColumn, gridSettings.SortOrder)); } if (gridSettings.Where != null) { } return(result); }
public void IsSatisfiedBy_WithTags_ResultExpected( string[] specificationTags, SelectCondition tagsCondition, string[] buildTags, bool expectedResult) { var build = new BuildReadModel { Tags = buildTags.ToList() }; var specification = new WithTagsSpecification(specificationTags, tagsCondition); var result = specification.IsSatisfiedBy(build); Assert.Equal(expectedResult, result); }
private CurrencyType GetCurrencyType(string shortName) { shortName = shortName.Trim(); var condition = new SelectCondition(); var rule = new Rule(ClassNameHelper.GetFieldName <CurrencyType>(p => p.ShortName), Rule.Operations.Like, shortName); condition.Where.Rules.Add(rule); var result = Dao.SelectRange <CurrencyType>(condition); if (result.ItemsCount > 0) { return(result.Result.First()); } return(null); }
private void IsSatisfiedBy_WithSuffixes_ResultExpected( string[] specificationSuffixes, SelectCondition condition, string[] buildSuffixes, bool expectedResult) { var build = new BuildReadModel { Suffixes = buildSuffixes.ToList() }; var specification = new WithSuffixesSpecification(specificationSuffixes, condition); var result = specification.IsSatisfiedBy(build); Assert.Equal(expectedResult, result); }
/// <summary> /// 生成SQL语句 /// </summary> /// <param name="list">参数列表</param> /// <param name="oper">连接对象</param> /// <param name="objCondition">条件对象</param> /// <param name="objPage">分页记录类</param> /// <returns></returns> public static string CreatePageSql(ParamList list, DataBaseOperate oper, SelectCondition objCondition, PageContent objPage, bool useCache) { if (objPage.CurrentPage < 0 || objPage.PageSize <= 0)//初始化页数 { return(""); } //string sql = "select " + objCondition.SqlParams + " from " + DbAdapterLoader.CurrentDbAdapter.FormatTableName(objCondition.Tables) + " where " + objCondition.Condition + " order by " + objCondition.Orders; //StringBuilder sql = new StringBuilder(5000); //sql.Append("select "); //sql.Append(objCondition.SqlParams.ToString()); //sql.Append(" from "); //sql.Append(DbAdapterLoader.CurrentDbAdapter.FormatTableName(objCondition.Tables.ToString())); //if (objCondition.Condition.Length > 0) //{ // sql.Append(" where "); // sql.Append(objCondition.Condition.ToString()); //} //if (objCondition.GroupBy.Length > 0) //{ // sql.Append(" group by "); // sql.Append(objCondition.GroupBy.ToString()); //} //if (objCondition.Orders.Length>0) //{ // sql.Append(" order by "); // sql.Append(objCondition.Orders.ToString()); //} //if (objCondition.Having.Length > 0) //{ // sql.Append(" having "); // sql.Append(objCondition.Having.ToString()); //} string sql = objCondition.GetSelect(); if (objPage.IsFillTotalRecords) { objPage.TotalRecords = GetTotalRecord(list, oper, objCondition.GetSelect(false), objPage.MaxSelectRecords, (useCache?objCondition.CacheTables:null));//获取总记录数 //long totalPage = (long)Math.Ceiling((double)objPage.TotalRecords / (double)objPage.PageSize); //objPage.TotalPage = totalPage; if (objPage.CurrentPage >= objPage.TotalPage - 1) { objPage.CurrentPage = objPage.TotalPage - 1; } } return(GetCutPageSql(sql, objPage)); }
public async Task <List <T> > FindAsync(string propertName, SelectCondition condition, string givenValue) { string operation = string.Empty; switch (condition) { case SelectCondition.Equal: operation = QueryComparisons.Equal; break; case SelectCondition.GreaterThan: operation = QueryComparisons.GreaterThan; break; case SelectCondition.GreaterThanOrEqual: operation = QueryComparisons.GreaterThanOrEqual; break; case SelectCondition.LessThan: operation = QueryComparisons.LessThan; break; case SelectCondition.LessThanOrEqual: operation = QueryComparisons.LessThanOrEqual; break; case SelectCondition.NotEqual: operation = QueryComparisons.NotEqual; break; } var filter = TableQuery.GenerateFilterCondition(propertName, operation, givenValue); var result = new List <T>(); var query = new TableQuery <T>().Where(filter); TableContinuationToken tableContinuationToken = null; do { var queryResponse = await _table.ExecuteQuerySegmentedAsync(query, tableContinuationToken); tableContinuationToken = queryResponse.ContinuationToken; result.AddRange(queryResponse.Results); } while (tableContinuationToken != null); return(result); }
private void ConvertProperty(SelectCondition selectCondition, ref Expression member) { var _convert = _valueConverterStrategy.GetExpressionBuilder(selectCondition.Converter); if (_convert is NullConverter) { return; } member = member switch { MemberExpression _memberExpression => _convert.Convert(_memberExpression), ParameterExpression _parameterExpression => _convert.Convert(_parameterExpression), _ => throw new ArgumentException("Unexpected type of member", nameof(member)) }; } }
/// <summary> /// 执行sql语句,分页返回List /// </summary> /// <typeparam name="E">实体类型</typeparam> /// <param name="BQL">BQL</param> /// <param name="objPage">分页数据</param> /// <param name="outPutTables">输出表</param> /// <returns></returns> public List <E> QueryPageList <E>(BQLQuery BQL, PageContent objPage, IEnumerable <BQLEntityTableHandle> outPutTables, bool useCache) where E : EntityBase, new() { AbsCondition con = ToCondition(BQL, outPutTables, false, typeof(E)); con.PageContent = objPage; List <E> retlist = null; IDataReader reader = null; try { Dictionary <string, bool> cacheTables = null; if (useCache) { cacheTables = con.CacheTables; } if (con.DbParamList != null) { con.PageContent = objPage; con.Oper = _oper; string sql = con.GetSql(useCache); reader = _oper.Query(sql, con.DbParamList, cacheTables); } else { SelectCondition sCon = con as SelectCondition; reader = con.DBinfo.CurrentDbAdapter.Query(sCon.GetSelect(), objPage, _oper); } retlist = LoadFromReader <E>(con.AliasManager, reader); } finally { if (reader != null) { reader.Close(); } } return(retlist); }
/// <summary> /// Returns data set depending on specified properties. /// </summary> /// <param name="ids">ID(s) of the items to load. If null, all items are loaded</param> /// <param name="topN">Only take topN items</param> /// <param name="pageIndex">Index of the page to get</param> /// <param name="offset">Offset of the items</param> /// <param name="maxRecords">Maximum number of the records to get</param> /// <param name="forceReload">Force reloading of the data</param> /// <param name="totalRecords">Returns total number of records</param> private DataSet GetResultSet(string ids, int topN, int pageIndex, bool forceReload, int offset, int maxRecords, ref int totalRecords) { DataSet ds = null; // Init columns string columns = null; if (DisplayNameFormat == USER_DISPLAY_FORMAT) { // Ensure columns which are needed for USER_DISPLAY_FORMAT columns = "UserName;FullName;"; } else if (DisplayNameFormat != null) { columns = DataHelper.GetNotEmpty(TextHelper.GetMacros(DisplayNameFormat, true), Object.DisplayNameColumn).Replace(";", ", "); } else { columns = Object.DisplayNameColumn; } // Add the default format name column to the query if (DefaultDisplayNameFormat != null) { string defaultColumn = DataHelper.GetNotEmpty(TextHelper.GetMacros(DefaultDisplayNameFormat, true), Object.DisplayNameColumn).Replace(";", ", "); columns = SqlHelperClass.MergeColumns(columns, defaultColumn); } // Add return column name columns = SqlHelperClass.MergeColumns(columns, ReturnColumnName); // Add additional columns columns = SqlHelperClass.MergeColumns(columns, AdditionalColumns); // Add enabled column name columns = SqlHelperClass.MergeColumns(columns, EnabledColumnName); // Add priority column name if (PrioritizeItems) { columns = SqlHelperClass.MergeColumns(columns, "isnull(" + objectType.TypeInfo.PriorityColumn + "," + (int)ObjectPriorityEnum.Low + ") as " + objectType.TypeInfo.PriorityColumn); } // Ensure SiteID column (for global object prefixes/suffixes) if (AddGlobalObjectNamePrefix || AddGlobalObjectSuffix) { if ((objectType != null) && (objectType.SiteIDColumn != TypeInfo.COLUMN_NAME_UNKNOWN)) { columns = SqlHelperClass.MergeColumns(columns, objectType.SiteIDColumn); } } // Return result set for single selectors string itemsWhere = null; // Prepare the parameters QueryDataParameters parameters = new QueryDataParameters(); using (var condition = new SelectCondition(parameters)) { switch (SelectionMode) { case SelectionModeEnum.Multiple: case SelectionModeEnum.MultipleButton: case SelectionModeEnum.MultipleTextBox: if (ids != null) { // Get where condition for selected items string[] items = ids.Split(ValuesSeparator.ToCharArray(), StringSplitOptions.RemoveEmptyEntries); bool isString = !Object.IDColumn.EqualsCSafe(ReturnColumnName, true); if (isString) { // Names condition.Prepare(ReturnColumnName, items, false); } else { // IDs condition.PrepareIDs(ReturnColumnName, items); } // Do not return anything if the selection is empty if (condition.IsEmpty) { return null; } itemsWhere = condition.WhereCondition; } break; default: // Build where condition if (!String.IsNullOrEmpty(ids)) { itemsWhere = ReturnColumnName + " = '" + SqlHelperClass.GetSafeQueryString(ids, false) + "'"; } break; } // Modify WHERE condition string where = SqlHelperClass.AddWhereCondition(this.WhereCondition, itemsWhere); // Apply value restrictions if (ApplyValueRestrictions) { where = SqlHelperClass.AddWhereCondition(where, (ListingWhereCondition != String.Empty) ? ListingWhereCondition : WhereCondition); } // Apply priority restrictions if (MinimumPriority != null) { if (objectType.TypeInfo.PriorityColumn == TypeInfo.COLUMN_NAME_UNKNOWN) { throw new GeneralCMSException("Priority column not set in TYPEINFO"); } where = SqlHelperClass.AddWhereCondition(where, objectType.TypeInfo.PriorityColumn + " >= " + MinimumPriority); } // Apply site restrictions string siteWhere = GetSiteWhereCondition(); if (!string.IsNullOrEmpty(siteWhere)) { where = SqlHelperClass.AddWhereCondition(where, siteWhere); } // Order by string orderBy = String.Empty; if (PrioritizeItems) { orderBy = "isnull(" + objectType.TypeInfo.PriorityColumn + "," + (int)ObjectPriorityEnum.Low + ") DESC,"; } if (String.IsNullOrEmpty(OrderBy)) { orderBy += Object.DisplayNameColumn; } else { orderBy += OrderBy; } GeneralizedInfo obj = ListingObject ?? Object; // Get the result set ds = obj.GetData(parameters, where, orderBy, topN, columns, false, offset, maxRecords, ref totalRecords); } return ds; }
/// <summary> /// Returns data set depending on specified properties. /// </summary> private DataSet GetResultSet(string id, int topN, int pageIndex, bool forceReload, int offset, int maxRecords, ref int totalRecords) { DataSet ds = null; // Init columns string columns = null; if (DisplayNameFormat == USER_DISPLAY_FORMAT) { // Ensure columns which are needed for USER_DISPLAY_FORMAT columns = "UserName;FullName;"; } else if (DisplayNameFormat != null) { columns = DataHelper.GetNotEmpty(TextHelper.GetMacros(DisplayNameFormat), Object.DisplayNameColumn).Replace(";", ", "); } else { columns = Object.DisplayNameColumn; } // Add the default format name column to the query if (DefaultDisplayNameFormat != null) { string defaultColumn = DataHelper.GetNotEmpty(TextHelper.GetMacros(DefaultDisplayNameFormat), Object.DisplayNameColumn).Replace(";", ", "); columns = SqlHelperClass.MergeColumns(columns, defaultColumn); } // Add return column name columns = SqlHelperClass.MergeColumns(columns, ReturnColumnName); // Add additional columns columns = SqlHelperClass.MergeColumns(columns, AdditionalColumns); // Add enabled column name columns = SqlHelperClass.MergeColumns(columns, EnabledColumnName); // Ensure SiteID column (for global object prefixes/suffixes) if (this.AddGlobalObjectNamePrefix || this.AddGlobalObjectSuffix) { if ((objectType != null) && (objectType.SiteIDColumn != TypeInfo.COLUMN_NAME_UNKNOWN)) { columns = SqlHelperClass.MergeColumns(columns, objectType.SiteIDColumn); } } // Return result set for single selectors string itemsWhere = null; // Prepare the parameters QueryDataParameters parameters = new QueryDataParameters(); using (var condition = new SelectCondition(parameters)) { if (SelectionMode != SelectionModeEnum.Multiple) { // Build where condition if (!String.IsNullOrEmpty(id)) { itemsWhere = ReturnColumnName + " = '" + SqlHelperClass.GetSafeQueryString(id, false) + "'"; } } // Return result set for multiple selection else { // Get where condition for selected items string[] items = hiddenField.Value.Split(ValuesSeparator.ToCharArray(), StringSplitOptions.RemoveEmptyEntries); bool isString = !Object.IDColumn.Equals(ReturnColumnName, StringComparison.InvariantCultureIgnoreCase); if (isString) { // Names condition.Prepare(ReturnColumnName, items, false); } else { // IDs condition.PrepareIDs(ReturnColumnName, items); } // Do not return anything if the selection is empty if (condition.IsEmpty) { return null; } itemsWhere = condition.WhereCondition; } // Modify WHERE condition string where = itemsWhere; // Apply value restrictions if (ApplyValueRestrictions) { where = SqlHelperClass.AddWhereCondition(where, (ListingWhereCondition != String.Empty) ? ListingWhereCondition : WhereCondition); } // Order by string orderBy = OrderBy; if (String.IsNullOrEmpty(orderBy)) { orderBy = Object.DisplayNameColumn; } GeneralizedInfo obj = (ListingObject == null) ? Object : ListingObject; // Get the result set ds = obj.GetData(parameters, where, orderBy, topN, columns, false, offset, maxRecords, ref totalRecords); } return ds; }