/// <summary>
/// 分页查询语句结果
/// </summary>
/// <returns></returns>
public SqlQuery Paged()
{
SqlQuery sq = new SqlQuery();
if (pager.order.ToLower() == "asc")
{
sq = SqlIn.And(pager.sort).NotIn(
SqlNotIn.And("1")
.IsEqualTo("1")
.OrderAsc(pager.sort))
.And("1")
.IsEqualTo("1")
.OrderAsc(pager.sort);
}
else
{
sq = SqlIn.And(pager.sort).NotIn(
SqlNotIn.And("1")
.IsEqualTo("1")
.OrderDesc(pager.sort))
.And("1")
.IsEqualTo("1")
.OrderDesc(pager.sort);
}
paging();
return(sq);
}
/// <summary>
/// 添加查询条件，如果存在括号时，则自动判断加上括号
/// </summary>
/// <param name="sqlquery">SqlQuery查询对象</param>
/// <param name="item">当前SqlQuery条件实体</param>
/// <param name="isIn">是否是In查询，是的话第一次加And</param>
/// <returns></returns>
public static Constraint AddConstraint(SqlQuery sqlquery, SqlqueryCondition item, bool isIn = false)
{
//判断是Where查询还是And查询
if (ConstraintType.Where.Equals(item.SQConstraintType) ||
ConstraintType.And.Equals(item.SQConstraintType) || (isIn && !item.IsOr))
{
//判断是否需要加括号
if (item.IsParentheses && item.IsBrackets)
{
return(sqlquery.AndExpression(item.SQColumnName));
}
else
{
return(sqlquery.And(item.SQColumnName));
}
}
//使用Or查询
else
{
//判断是否需要加括号
if (item.IsParentheses && item.IsBrackets)
{
return(sqlquery.OrExpression(item.SQColumnName));
}
else
{
return(sqlquery.Or(item.SQColumnName));
}
}
}
/// <summary>
/// SqlQuery 大于（＞）拼接查询
/// </summary>
/// <param name="qList">QueryModel泛型集合</param>
/// <param name="sq">查询语句</param>
/// <param name="ColumnsName">列名</param>
/// <param name="QueryFiledName">查询传至字段名称</param>
public static void IsGreaterThan(List <QueryModel> qList, SqlQuery sq, string ColumnsName, string QueryFiledName)
{
ControllerHelper c = new ControllerHelper();
if (!string.IsNullOrWhiteSpace(c.GetQueryStr(qList, QueryFiledName)))
{
sq.And(ColumnsName).IsGreaterThan(c.GetQueryStr(qList, QueryFiledName));
}
}
/// <summary>
/// Takes the properties of an object and turns them into SubSonic.Query.Constraint
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public static List <Constraint> ToConstraintList(this object value)
{
var hashedSet = value.ToDictionary();
SqlQuery query = new SqlQuery();
foreach (string key in hashedSet.Keys)
{
if (query.Constraints.Count == 0)
{
query.Where(key).IsEqualTo(hashedSet[key]);
}
else
{
query.And(key).IsEqualTo(hashedSet[key]);
}
}
return(query.Constraints);
}
/// <summary>
/// SqlQuery 小于（＜）拼接查询
/// </summary>
/// <param name="qList">QueryModel泛型集合</param>
/// <param name="sq">查询语句</param>
/// <param name="ColumnsName">列名</param>
/// <param name="QueryFiledName">查询传至字段名称</param>
public static void IsLessThan(List <QueryModel> qList, SqlQuery sq, string ColumnsName, string QueryFiledName)
{
ControllerHelper c = new ControllerHelper();
if (!string.IsNullOrWhiteSpace(c.GetQueryStr(qList, QueryFiledName)))
{
//if (Utils.IsDateTime(c.GetQueryStr(qList, QueryFiledName)))
//{
// DateTime dt = DateTime.Parse(c.GetQueryStr(qList, QueryFiledName));
// sq.And(ColumnsName).IsLessThan(dt.AddDays(1));
//}
//else
//{
// sq.And(ColumnsName).IsLessThan(c.GetQueryStr(qList, QueryFiledName));
//}
sq.And(ColumnsName).IsLessThan(c.GetQueryStr(qList, QueryFiledName));
}
}
public OSqlSelect And(string field)
{
_sqlQuery.And(field);
return(this);
}
public OSqlDeleteEdge And(string field)
{
_sqlQuery.And(field);
return(this);
}
public OSqlUpdate And(string field)
{
_sqlQuery.And(field);
return(this);
}
public OSqlDeleteDocument And(string field)
{
_sqlQuery.And(field);
return(this);
}
/// <summary>
/// 添加查询条件，如果存在括号时，则自动判断加上括号
/// </summary>
/// <param name="sqlquery">SqlQuery查询对象</param>
/// <param name="item">当前SqlQuery条件实体</param>
/// <param name="type">查询类型，0=正常查询，1=in查询，2=not in查询</param>
/// <param name="isFirst">是否是in查询的第一个值，第一个值要加括号</param>
/// <returns></returns>
public static Constraint AddConstraint(SqlQuery sqlquery, SqlqueryCondition item, int type = 0, bool isFirst = false)
{
//是否是in查询
if (type > 0)
{
//是否是第一个值
if (isFirst)
{
//判断是Where查询还是And查询
if (ConstraintType.Where.Equals(item.SQConstraintType) ||
ConstraintType.And.Equals(item.SQConstraintType))
{
return(sqlquery.AndExpression(item.SQColumnName));
}
else
{
return(sqlquery.OrExpression(item.SQColumnName));
}
}
else
{
//in查询中，每个值之间都是Or关系
if (type == 1)
{
return(sqlquery.Or(item.SQColumnName));
}
//not in查询中，每个值之间都是And关系
else
{
return(sqlquery.And(item.SQColumnName));
}
}
}
else
{
//判断是Where查询还是And查询
if (ConstraintType.Where.Equals(item.SQConstraintType) ||
ConstraintType.And.Equals(item.SQConstraintType))
{
//判断是否需要加括号
if (item.IsParentheses)
{
return(sqlquery.AndExpression(item.SQColumnName));
}
else
{
return(sqlquery.And(item.SQColumnName));
}
}
//使用Or查询
else
{
//判断是否需要加括号
if (item.IsParentheses)
{
return(sqlquery.OrExpression(item.SQColumnName));
}
else
{
return(sqlquery.Or(item.SQColumnName));
}
}
}
}
/// <summary>
/// 添加查询条件，如果存在括号时，则自动判断加上括号
/// </summary>
/// <param name="sqlquery">SqlQuery查询对象</param>
/// <param name="item">当前SqlQuery条件实体</param>
/// <param name="type">查询类型，0=正常查询，1=in查询，2=not in查询</param>
/// <param name="isFirst">是否是in查询的第一个值，第一个值要加括号</param>
/// <returns></returns>
public static Constraint AddConstraint(SqlQuery sqlquery, SqlqueryCondition item, int type = 0, bool isFirst = false)
{
//是否是in查询
if (type > 0)
{
//是否是第一个值
if (isFirst)
{
//判断是Where查询还是And查询
if (ConstraintType.Where.Equals(item.SQConstraintType) ||
ConstraintType.And.Equals(item.SQConstraintType))
{
return sqlquery.AndExpression(item.SQColumnName);
}
else
{
return sqlquery.OrExpression(item.SQColumnName);
}
}
else
{
//in查询中，每个值之间都是Or关系
if (type == 1)
return sqlquery.Or(item.SQColumnName);
//not in查询中，每个值之间都是And关系
else
return sqlquery.And(item.SQColumnName);
}
}
else
{
//判断是Where查询还是And查询
if (ConstraintType.Where.Equals(item.SQConstraintType) ||
ConstraintType.And.Equals(item.SQConstraintType))
{
//判断是否需要加括号
if (item.IsParentheses)
{
return sqlquery.AndExpression(item.SQColumnName);
}
else
return sqlquery.And(item.SQColumnName);
}
//使用Or查询
else
{
//判断是否需要加括号
if (item.IsParentheses)
{
return sqlquery.OrExpression(item.SQColumnName);
}
else
return sqlquery.Or(item.SQColumnName);
}
}
}
public IOrientDBQueryable And(string field)
{
_sqlQuery.And(field);
return(this);
}