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