protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
{
switch (node.Operator)
{
case SqlColumnConstraintOperator.Equal:
case SqlColumnConstraintOperator.NotEqual:
//在 Oracle 中，空字符串的对比，需要转换为对 Null 值的对比。
var strValue = node.Value as string;
if (strValue != null && strValue.Length == 0)
{
node.Value = null;
}
break;
default:
break;
}
return base.VisitSqlColumnConstraint(node);
}
protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
{
switch (node.Operator)
{
case SqlColumnConstraintOperator.Equal:
case SqlColumnConstraintOperator.NotEqual:
//在 Oracle 中，空字符串的对比，需要转换为对 Null 值的对比。
var strValue = node.Value as string;
if (strValue != null && strValue.Length == 0)
{
node.Value = null;
}
break;
default:
break;
}
return(base.VisitSqlColumnConstraint(node));
}
/// <summary>
/// 转换空值比较
/// </summary>
/// <param name="node"></param>
/// <returns></returns>
protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
{
switch (node.Operator)
{
case SqlColumnConstraintOperator.Equal:
case SqlColumnConstraintOperator.NotEqual:
//在 MySql 中，空字符串的对比，需要转换为对 Null 值的对比。
if (node.Value is string)
{
if (node.Value != null)
{
if (node.Value.ToString().Length == 0)
{
node.Value = "";
}
}
}
break;
//case SqlColumnConstraintOperator.Contains:
// if (node.Value is string && node.Value.ToString().IndexOf('%')>=0)
// {
// node.Value = "locate('" + node.Value + "'," + node.Column.ColumnName + ")!=0";
// }
// break;
//case SqlColumnConstraintOperator.NotContains:
// if (node.Value is string&& node.Value.ToString().IndexOf('%') >= 0)
// {
// node.Value = "locate('" + node.Value + "'," + node.Column.ColumnName + ") = 0";
// }
// break;
default:
break;
}
return(base.VisitSqlColumnConstraint(node));
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
private ISqlSelect ModifyToPagingTreeWithNotIn(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
//如果是第一页，则只需要使用 TOP 语句即可。
if (pagingInfo.PageNumber == 1)
{
return(new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + pagingInfo.PageSize + " "
},
raw.Selection ?? SqlSelectAll.Default
},
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy
});
}
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* 转换分页后：
*
* SELECT TOP 10 *
* FROM ASN
* WHERE ASN.Id > 0 AND ASN.Id NOT IN(
* SELECT TOP 20 Id
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
* )
* ORDER BY ASN.AsnCode ASC
*
**********************************************************************/
//先要找到主表的 PK，分页时需要使用此主键列来生成分页 Sql。
//这里约定 Id 为主键列名。
var finder = new FirstTableFinder();
var pkTable = finder.Find(raw.From);
var pkColumn = new SqlColumn {
Table = pkTable, ColumnName = EntityConvention.IdColumnName
};
//先生成内部的 Select
var excludeSelect = new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + (pagingInfo.PageNumber - 1) * pagingInfo.PageSize + " "
},
pkColumn
},
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy,
};
var res = new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + pagingInfo.PageSize + " "
},
raw.Selection ?? SqlSelectAll.Default
},
From = raw.From,
OrderBy = raw.OrderBy,
};
var newWhere = new SqlColumnConstraint
{
Column = pkColumn,
Operator = SqlColumnConstraintOperator.NotIn,
Value = excludeSelect
};
if (raw.Where != null)
{
res.Where = new SqlBinaryConstraint
{
Left = raw.Where,
Opeartor = SqlBinaryConstraintType.And,
Right = newWhere
};
}
else
{
res.Where = newWhere;
}
return(res);
}
protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
{
var op = node.Operator;
var value = node.Value;
value = this.PrepareConstraintValue(value);
#region 处理一些特殊的值
switch (op)
{
case SqlColumnConstraintOperator.Like:
case SqlColumnConstraintOperator.Contains:
case SqlColumnConstraintOperator.StartsWith:
case SqlColumnConstraintOperator.EndsWith:
//如果是空字符串的模糊对比操作，直接认为是真。
var strValue = value as string;
if (string.IsNullOrEmpty(strValue))
{
_sql.Append("1 = 1");
return node;
}
break;
case SqlColumnConstraintOperator.NotLike:
case SqlColumnConstraintOperator.NotContains:
case SqlColumnConstraintOperator.NotStartsWith:
case SqlColumnConstraintOperator.NotEndsWith:
//如果是空字符串的模糊对比操作，直接认为是假。
var strValue2 = value as string;
if (string.IsNullOrEmpty(strValue2))
{
_sql.Append("1 != 1");
return node;
}
break;
case SqlColumnConstraintOperator.In:
case SqlColumnConstraintOperator.NotIn:
//对于 In、NotIn 操作，如果传入的是空列表时，需要特殊处理：
//In(Empty) 表示 false，NotIn(Empty) 表示 true。
if (value is IEnumerable)
{
bool hasValue = false;
foreach (var item in value as IEnumerable)
{
hasValue = true;
break;
}
if (!hasValue)
{
if (op == SqlColumnConstraintOperator.In)
{
_sql.Append("0 = 1");
}
else
{
_sql.Append("1 = 1");
}
return node;
}
}
break;
default:
break;
}
#endregion
this.AppendColumnUsage(node.Column);
//根据不同的操作符，来生成不同的_sql。
switch (op)
{
case SqlColumnConstraintOperator.Equal:
if (value == null || value == DBNull.Value)
{
_sql.Append(" IS NULL");
}
else
{
_sql.Append(" = ");
_sql.AppendParameter(value);
}
break;
case SqlColumnConstraintOperator.NotEqual:
if (value == null || value == DBNull.Value)
{
_sql.Append(" IS NOT NULL");
}
else
{
_sql.Append(" != ");
_sql.AppendParameter(value);
}
break;
case SqlColumnConstraintOperator.Greater:
_sql.Append(" > ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.GreaterEqual:
_sql.Append(" >= ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.Less:
_sql.Append(" < ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.LessEqual:
_sql.Append(" <= ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.Like:
_sql.Append(" LIKE ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.NotLike:
_sql.Append(" NOT LIKE ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.Contains:
_sql.Append(" LIKE ");
_sql.AppendParameter("%" + value + "%");
break;
case SqlColumnConstraintOperator.NotContains:
_sql.Append(" NOT LIKE ");
_sql.AppendParameter("%" + value + "%");
break;
case SqlColumnConstraintOperator.StartsWith:
_sql.Append(" LIKE ");
_sql.AppendParameter(value + "%");
break;
case SqlColumnConstraintOperator.NotStartsWith:
_sql.Append(" NOT LIKE ");
_sql.AppendParameter(value + "%");
break;
case SqlColumnConstraintOperator.EndsWith:
_sql.Append(" LIKE ");
_sql.AppendParameter("%" + value);
break;
case SqlColumnConstraintOperator.NotEndsWith:
_sql.Append(" NOT LIKE ");
_sql.AppendParameter("%" + value);
break;
case SqlColumnConstraintOperator.In:
case SqlColumnConstraintOperator.NotIn:
var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN";
_sql.Append(" ").Append(opSql).Append(" (");
if (value is IEnumerable)
{
bool first = true;
bool needDelimiter = false;
int i = 0;
foreach (var item in value as IEnumerable)
{
if (++i > this.MaxItemsInInClause) throw new TooManyItemsInInClauseException();
if (first)
{
first = false;
needDelimiter = item is string || item is DateTime || item is Guid;
}
else { _sql.Append(','); }
if (needDelimiter)
{
_sql.Append('\'').Append(item).Append('\'');
}
else
{
_sql.Append(item);
}
//由于集合中的数据可能过多，所以这里不要使用参数化的查询。
//_sql.AppendParameter(item);
}
}
else if (value is SqlNode)
{
_sql.AppendLine();
this.Indent++;
this.Visit(value as SqlNode);
this.Indent--;
_sql.AppendLine();
}
_sql.Append(')');
break;
default:
throw new NotSupportedException();
}
return node;
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
private ISqlSelect ModifyToPagingTree_With_NotIn(SqlSelect raw, PagingInfo pagingInfo)
{
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* 转换分页后：
*
* SELECT TOP 10 *
* FROM ASN
* WHERE ASN.Id > 0 AND ASN.Id NOT IN(
* SELECT TOP 20 Id
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
* )
* ORDER BY ASN.AsnCode ASC
*
**********************************************************************/
//先要找到主表的 PK，分页时需要使用此主键列来生成分页 Sql。
//这里约定 Id 为主键列名。
var finder = new FirstTableFinder();
var pkTable = finder.Find(raw.From);
var pkColumn = new SqlColumn {
Table = pkTable, ColumnName = EntityConvention.IdColumnName
};
//先生成内部的 Select
var excludeSelect = new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + (pagingInfo.PageNumber - 1) * pagingInfo.PageSize + " "
},
pkColumn
},
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy,
};
var res = new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + pagingInfo.PageSize + " "
},
raw.Selection ?? SqlSelectAll.Default
},
From = raw.From,
OrderBy = raw.OrderBy,
};
//where
var newWhere = new SqlColumnConstraint
{
Column = pkColumn,
Operator = SqlColumnConstraintOperator.NotIn,
Value = excludeSelect
};
if (raw.Where != null)
{
res.Where = new SqlBinaryConstraint
{
Left = raw.Where,
Opeartor = SqlBinaryConstraintType.And,
Right = newWhere
};
}
else
{
res.Where = newWhere;
}
return(res);
}
protected virtual SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
{
this.Visit(node.Column);
return node;
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pkColumn">需要指定主键列</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
public virtual SqlSelect ModifyToPagingTree(SqlSelect raw, SqlColumn pkColumn, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
//如果是第一页，则只需要使用 TOP 语句即可。
if (pagingInfo.PageNumber == 1)
{
raw.Top = pagingInfo.PageSize;
return(raw);
}
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* SELECT TOP 10 *
* FROM ASN
* WHERE ASN.Id > 0 AND ASN.Id NOT IN(
* SELECT TOP 20 Id
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
* )
* ORDER BY ASN.AsnCode ASC
*
**********************************************************************/
var excludeSelect = new SqlSelect
{
Top = (pagingInfo.PageNumber - 1) * pagingInfo.PageSize,
Selection = pkColumn,
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy,
};
var res = new SqlSelect
{
Top = pagingInfo.PageSize,
Selection = raw.Selection,
From = raw.From,
OrderBy = raw.OrderBy,
};
var newWhere = new SqlColumnConstraint
{
Column = pkColumn,
Operator = SqlColumnConstraintOperator.NotIn,
Value = excludeSelect
};
if (raw.Where != null)
{
res.Where = new SqlBinaryConstraint
{
Left = raw.Where,
Opeartor = SqlBinaryConstraintType.And,
Right = newWhere
};
}
else
{
res.Where = newWhere;
}
return(res);
}
protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
{
var op = node.Operator;
var value = node.Value;
#region 处理一些特殊的值
switch (op)
{
case SqlColumnConstraintOperator.Like:
case SqlColumnConstraintOperator.Contains:
case SqlColumnConstraintOperator.StartWith:
case SqlColumnConstraintOperator.EndWith:
//如果是空字符串的模糊对比操作，直接认为是真。
var strValue = value as string;
if (string.IsNullOrEmpty(strValue))
{
_sql.Append("1 = 1");
return(node);
}
break;
case SqlColumnConstraintOperator.In:
case SqlColumnConstraintOperator.NotIn:
//对于 In、NotIn 操作，如果传入的是空列表时，需要特殊处理：
//In(Empty) 表示 false，NotIn(Empty) 表示 true。
if (value is IEnumerable)
{
bool hasValue = false;
foreach (var item in value as IEnumerable)
{
hasValue = true;
break;
}
if (!hasValue)
{
if (op == SqlColumnConstraintOperator.In)
{
_sql.Append("0 = 1");
}
else
{
_sql.Append("1 = 1");
}
return(node);
}
}
break;
default:
break;
}
#endregion
this.AppendColumnUsage(node.Column);
//根据不同的操作符，来生成不同的_sql。
switch (op)
{
case SqlColumnConstraintOperator.Equal:
if (value == null || value == DBNull.Value)
{
_sql.Append(" IS NULL");
}
else
{
_sql.Append(" = ");
_sql.AppendParameter(value);
}
break;
case SqlColumnConstraintOperator.NotEqual:
if (value == null || value == DBNull.Value)
{
_sql.Append(" IS NOT NULL");
}
else
{
_sql.Append(" != ");
_sql.AppendParameter(value);
}
break;
case SqlColumnConstraintOperator.Greater:
_sql.Append(" > ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.GreaterEqual:
_sql.Append(" >= ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.Less:
_sql.Append(" < ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.LessEqual:
_sql.Append(" <= ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.Like:
_sql.Append(" LIKE ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.Contains:
_sql.Append(" LIKE ");
_sql.AppendParameter("%" + value + "%");
break;
case SqlColumnConstraintOperator.StartWith:
_sql.Append(" LIKE ");
_sql.AppendParameter(value + "%");
break;
case SqlColumnConstraintOperator.EndWith:
_sql.Append(" LIKE ");
_sql.AppendParameter("%" + value);
break;
case SqlColumnConstraintOperator.In:
case SqlColumnConstraintOperator.NotIn:
var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN";
_sql.Append(" ").Append(opSql).Append(" (");
if (value is IEnumerable)
{
bool first = true;
foreach (var item in value as IEnumerable)
{
if (!first)
{
_sql.Append(',');
}
_sql.AppendParameter(item);
first = false;
}
}
else if (value is SqlNode)
{
_sql.AppendLine();
this.Indent++;
this.Visit(value as SqlNode);
this.Indent--;
_sql.AppendLine();
}
_sql.Append(')');
break;
default:
throw new NotSupportedException();
}
return(node);
}
protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
{
var op = node.Operator;
var value = node.Value;
value = this.PrepareConstraintValue(value);
#region 处理一些特殊的值
switch (op)
{
case SqlColumnConstraintOperator.Like:
case SqlColumnConstraintOperator.Contains:
case SqlColumnConstraintOperator.StartsWith:
case SqlColumnConstraintOperator.EndsWith:
//如果是空字符串的模糊对比操作，直接认为是真。
var strValue = value as string;
if (string.IsNullOrEmpty(strValue))
{
_sql.Append("1 = 1");
return(node);
}
break;
case SqlColumnConstraintOperator.NotLike:
case SqlColumnConstraintOperator.NotContains:
case SqlColumnConstraintOperator.NotStartsWith:
case SqlColumnConstraintOperator.NotEndsWith:
//如果是空字符串的模糊对比操作，直接认为是假。
var strValue2 = value as string;
if (string.IsNullOrEmpty(strValue2))
{
_sql.Append("1 != 1");
return(node);
}
break;
case SqlColumnConstraintOperator.In:
case SqlColumnConstraintOperator.NotIn:
//对于 In、NotIn 操作，如果传入的是空列表时，需要特殊处理：
//In(Empty) 表示 false，NotIn(Empty) 表示 true。
if (value is IEnumerable)
{
bool hasValue = false;
foreach (var item in value as IEnumerable)
{
hasValue = true;
break;
}
if (!hasValue)
{
if (op == SqlColumnConstraintOperator.In)
{
_sql.Append("0 = 1");
}
else
{
_sql.Append("1 = 1");
}
return(node);
}
}
break;
default:
break;
}
#endregion
this.AppendColumnUsage(node.Column);
//根据不同的操作符，来生成不同的_sql。
switch (op)
{
case SqlColumnConstraintOperator.Equal:
if (value == null || value == DBNull.Value)
{
_sql.Append(" IS NULL");
}
else
{
_sql.Append(" = ");
_sql.AppendParameter(value);
}
break;
case SqlColumnConstraintOperator.NotEqual:
if (value == null || value == DBNull.Value)
{
_sql.Append(" IS NOT NULL");
}
else
{
_sql.Append(" != ");
_sql.AppendParameter(value);
}
break;
case SqlColumnConstraintOperator.Greater:
_sql.Append(" > ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.GreaterEqual:
_sql.Append(" >= ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.Less:
_sql.Append(" < ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.LessEqual:
_sql.Append(" <= ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.Like:
_sql.Append(" LIKE ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.NotLike:
_sql.Append(" NOT LIKE ");
_sql.AppendParameter(value);
break;
case SqlColumnConstraintOperator.Contains:
_sql.Append(" LIKE ");
_sql.AppendParameter(WILDCARD_ALL + this.Escape(value) + WILDCARD_ALL);
this.AppendEscapePlause(value);
break;
case SqlColumnConstraintOperator.NotContains:
_sql.Append(" NOT LIKE ");
_sql.AppendParameter(WILDCARD_ALL + this.Escape(value) + WILDCARD_ALL);
this.AppendEscapePlause(value);
break;
case SqlColumnConstraintOperator.StartsWith:
_sql.Append(" LIKE ");
_sql.AppendParameter(this.Escape(value) + WILDCARD_ALL);
this.AppendEscapePlause(value);
break;
case SqlColumnConstraintOperator.NotStartsWith:
_sql.Append(" NOT LIKE ");
_sql.AppendParameter(this.Escape(value) + WILDCARD_ALL);
this.AppendEscapePlause(value);
break;
case SqlColumnConstraintOperator.EndsWith:
_sql.Append(" LIKE ");
_sql.AppendParameter(WILDCARD_ALL + this.Escape(value));
this.AppendEscapePlause(value);
break;
case SqlColumnConstraintOperator.NotEndsWith:
_sql.Append(" NOT LIKE ");
_sql.AppendParameter(WILDCARD_ALL + this.Escape(value));
this.AppendEscapePlause(value);
break;
case SqlColumnConstraintOperator.In:
case SqlColumnConstraintOperator.NotIn:
var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN";
_sql.Append(" ").Append(opSql).Append(" (");
if (value is IEnumerable)
{
bool first = true;
bool needDelimiter = false;
int i = 0;
foreach (var item in value as IEnumerable)
{
if (++i > this.MaxItemsInInClause)
{
throw new TooManyItemsInInClauseException();
}
if (first)
{
first = false;
needDelimiter = item is string || item is DateTime || item is Guid;
}
else
{
_sql.Append(',');
}
if (needDelimiter)
{
_sql.Append('\'').Append(item).Append('\'');
}
else
{
_sql.Append(item);
}
//由于集合中的数据可能过多，所以这里不要使用参数化的查询。
//_sql.AppendParameter(item);
}
}
else if (value is SqlNode)
{
_sql.AppendLine();
this.Indent++;
this.Visit(value as SqlNode);
this.Indent--;
_sql.AppendLine();
}
_sql.Append(')');
break;
default:
throw new NotSupportedException();
}
return(node);
}
/// <summary>
/// 重写VisitSqlColumnConstraint方法
/// 处理In、NotIn操作，如果传入的值为字符串，则查询条件增加N前缀
/// </summary>
/// <param name="node"></param>
/// <returns></returns>
protected override SqlColumnConstraint VisitSqlColumnConstraint(SqlColumnConstraint node)
{
var op = node.Operator;
var value = node.Value;
value = this.DbTypeCoverter.ToDbParameterValue(value);
#region 处理一些特殊的值
switch (op)
{
case SqlColumnConstraintOperator.In:
case SqlColumnConstraintOperator.NotIn:
//对于 In、NotIn 操作，如果传入的是空列表时，需要特殊处理：
//In(Empty) 表示 false，NotIn(Empty) 表示 true。
if (value is IEnumerable)
{
bool hasValue = false;
foreach (var item in value as IEnumerable)
{
hasValue = true;
break;
}
if (!hasValue)
{
if (op == SqlColumnConstraintOperator.In)
{
Sql.Append("0 = 1");
}
else
{
Sql.Append("1 = 1");
}
return(node);
}
}
break;
default:
break;
}
#endregion
//根据不同的操作符，来生成不同的Sql。
switch (op)
{
case SqlColumnConstraintOperator.In:
case SqlColumnConstraintOperator.NotIn:
var column = node.Column;
base.AppendColumnUsage(node.Column);
var opSql = op == SqlColumnConstraintOperator.In ? "IN" : "NOT IN";
Sql.Append(" ").Append(opSql).Append(" (");
if (value is IEnumerable)
{
bool first = true;
bool needDelimiter = false;
bool isString = false;
int i = 0;
foreach (var item in value as IEnumerable)
{
if (++i > this.MaxItemsInInClause)
{
throw new TooManyItemsInInClauseException();
}
if (first)
{
first = false;
needDelimiter = item is string || item is DateTime || item is Guid;
isString = item is string;
}
else
{
Sql.Append(',');
}
if (needDelimiter)
{
//判断值否是为字符串，如果是查询语句增加N前缀
if (isString)
{
Sql.Append('N').Append('\'').Append(EscapeSpecialChar(item)).Append('\'');
}
else
{
Sql.Append('\'').Append(EscapeSpecialChar(item)).Append('\'');
}
}
else
{
Sql.Append(item);
}
}
}
else if (value is SqlNode)
{
Sql.AppendLine();
this.Indent++;
this.Visit(value as SqlNode);
this.Indent--;
Sql.AppendLine();
}
Sql.Append(')');
return(node);
default:
break;
}
return(base.VisitSqlColumnConstraint(node));
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pkColumn">需要指定主键列</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
public virtual SqlSelect ModifyToPagingTree(SqlSelect raw, SqlColumn pkColumn, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo)) { throw new ArgumentNullException("pagingInfo"); }
if (!raw.HasOrdered()) { throw new InvalidProgramException("必须排序后才能使用分页功能。"); }
//如果是第一页，则只需要使用 TOP 语句即可。
if (pagingInfo.PageNumber == 1)
{
raw.Top = pagingInfo.PageSize;
return raw;
}
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* SELECT TOP 10 *
* FROM ASN
* WHERE ASN.Id > 0 AND ASN.Id NOT IN(
* SELECT TOP 20 Id
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
* )
* ORDER BY ASN.AsnCode ASC
*
**********************************************************************/
var excludeSelect = new SqlSelect
{
Top = (pagingInfo.PageNumber - 1) * pagingInfo.PageSize,
Selection = pkColumn,
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy,
};
var res = new SqlSelect
{
Top = pagingInfo.PageSize,
Selection = raw.Selection,
From = raw.From,
OrderBy = raw.OrderBy,
};
var newWhere = new SqlColumnConstraint
{
Column = pkColumn,
Operator = SqlColumnConstraintOperator.NotIn,
Value = excludeSelect
};
if (raw.Where != null)
{
res.Where = new SqlBinaryConstraint
{
Left = raw.Where,
Opeartor = SqlBinaryConstraintType.And,
Right = newWhere
};
}
else
{
res.Where = newWhere;
}
return res;
}
