/// <summary>
/// 添加SQL Having查询语句并返回当前SQL语句创建类
/// </summary>
/// <param name="condition">Having条件语句</param>
/// <returns>当前SQL语句创建类</returns>
internal SqlCommandBuilder AppendHaving(ISqlCondition condition)
{
if (condition != null)
{
this._stringBuilder.Append("HAVING ").Append(condition.ToString()).Append(' ');
}
return(this);
}
/// <summary>
/// 创建新的Sql Not条件语句
/// </summary>
/// <param name="cmd">Sql语句</param>
/// <param name="condition">条件语句</param>
/// <exception cref="ArgumentNullException">Sql条件语句不能为空</exception>
/// <returns>Sql Not条件语句</returns>
internal static SqlNotCondition Create(AbstractSqlCommandWithWhere cmd, ISqlCondition condition)
{
if (condition == null)
{
throw new ArgumentNullException("condition");
}
return(new SqlNotCondition(cmd, condition));
}
/// <summary>
/// 添加SQL Where查询语句并返回当前SQL语句创建类
/// </summary>
/// <param name="condition">Where条件语句</param>
/// <returns>当前SQL语句创建类</returns>
internal SqlCommandBuilder AppendWhere(ISqlCondition condition)
{
if (condition != null)
{
this._stringBuilder.Append("WHERE ").Append(condition.ToString()).Append(' ');
}
return(this);
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.UseDistinct, baseCommand.InternalGetQueryFieldList());
if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)//分页模式
{
/*
* SELECT * FROM
* (
* SELECT *, ROWNUM RN
* FROM (SELECT * FROM TABLE_NAME)
* WHERE ROWNUM <= 30
* )
* WHERE RN > 20
*/
SelectCommand innestCommand = baseCommand.Database.InternalCreateSelectCommand(baseCommand, baseCommand.TableName);
innestCommand.InternalSetQueryFieldList(baseCommand);
innestCommand.InternalSetJoinList(baseCommand);
innestCommand.InternalSetWhereCondition(baseCommand);
innestCommand.InternalSetGroupByFieldList(baseCommand);
innestCommand.InternalSetHavingCondition(baseCommand);
innestCommand.InternalSetOrderList(baseCommand);
SelectCommand innerCommand = baseCommand.Database.InternalCreateSelectCommand(baseCommand, innestCommand, "");
innerCommand.InternalSetWhereCondition(innerCommand.ConditionBuilder.LessThanOrEqualColumn("ROWNUM", (baseCommand.RecordStart + baseCommand.PageSize).ToString()));
innerCommand.InternalQuerys(baseCommand.QueryFields);
innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction(baseCommand, "ROWNUM", "RN"));
sb.AppendSelectFrom(innerCommand.GetCommandText(), true);
sb.AppendWhere(baseCommand.ConditionBuilder.GreaterThanColumn("RN", baseCommand.RecordStart.ToString()));
}
else//正常模式
{
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.InternalGetJoinList());
ISqlCondition where = baseCommand.WhereCondition;
if (baseCommand.PageSize > 0)
{
where = baseCommand.ConditionBuilder.And(where, baseCommand.ConditionBuilder.LessThanOrEqualColumn("ROWNUM", baseCommand.PageSize.ToString()));
}
sb.AppendWhere(where);
sb.AppendSelectGroupBys(baseCommand.InternalGetGroupByFieldList());
sb.AppendHaving(baseCommand.InternalGetHavingCondition());
sb.AppendSelectOrderBys(baseCommand.InternalGetOrderList(), orderReverse);
}
return(sb.ToString());
}
/// <summary>
/// 使用Or连接条件
/// </summary>
/// <param name="condition">过滤条件</param>
public ISqlBuilder Or(string condition)
{
if (condition.IsEmpty())
{
return(this);
}
if (_condition == null)
{
_condition = SqlCondition.Create(condition);
return(this);
}
_condition = new OrCondition(_condition.GetCondition(), condition);
return(this);
}
/// <summary>
/// 初始化Sql Not条件语句类
/// </summary>
/// <param name="baseCommand">源Sql语句</param>
/// <param name="baseCondition">基础Sql条件语句</param>
private SqlNotCondition(AbstractSqlCommandWithWhere baseCommand, ISqlCondition baseCondition)
: base(baseCommand)
{
this._baseCondition = baseCondition;
}
public static string And(ISqlCondition condition) => condition != null ? $"{S.And} {condition.ToSql()}" : null;
/// <summary>
/// 添加SQL Where查询语句并返回当前SQL语句创建类
/// </summary>
/// <param name="condition">Where条件语句</param>
/// <returns>当前SQL语句创建类</returns>
internal SqlCommandBuilder AppendWhere(ISqlCondition condition)
{
if (condition != null)
{
this._stringBuilder.Append("WHERE ").Append(condition.GetClauseText()).Append(' ');
}
return this;
}
public void TestEnds() {
_condition = SqlCondition.Create( "a", "@", Operator.Ends );
Assert.AreEqual( "a Like @a", _condition.GetCondition() );
}
public void TestGreater() {
_condition = SqlCondition.Create( "a", "@", Operator.Greater );
Assert.AreEqual( "a>@a", _condition.GetCondition() );
}
/// <summary>
/// 设置指定查询的语句并返回当前语句
/// </summary>
/// <param name="having">查询语句</param>
/// <returns>当前语句</returns>
public SelectCommand Having(ISqlCondition having)
{
this._having = having;
return this;
}
/// <summary>
/// 使用Or连接条件
/// </summary>
/// <param name="condition">过滤条件</param>
public ISqlBuilder Or( ISqlCondition condition ) {
return Or( condition.GetCondition() );
}
public ISqlOn AndNot(ISqlCondition clause) => And(SqlCondition.Not(clause));
public ISqlOn And(ISqlCondition clause) => Chain(() => AndRaw = clause);
public SqlOn(ISqlCondition clause)
{
ConditionRaw = clause;
}
public ISqlWhere OrNot(ISqlCondition clause) => Chain(() => SqlCondition.Not(clause));
public ISqlWhere AndNot(ISqlCondition clause) => Chain(() => AndRaw = SqlCondition.Not(clause));
public SqlWhere(ISqlCondition clause)
{
ConditionRaw = clause;
}
/// <summary>
/// 添加SQL Having查询语句并返回当前SQL语句创建类
/// </summary>
/// <param name="condition">Having条件语句</param>
/// <returns>当前SQL语句创建类</returns>
internal SqlCommandBuilder AppendHaving(ISqlCondition condition)
{
if (condition != null)
{
this._stringBuilder.Append("HAVING ").Append(condition.GetClauseText()).Append(' ');
}
return this;
}
/// <summary>
/// 设置查询语句
/// </summary>
/// <param name="where">查询语句</param>
internal void InternalSetWhereCondition(ISqlCondition where)
{
this._where = where;
}
/// <summary>
/// 设置指定查询的语句并返回当前语句
/// </summary>
/// <param name="where">查询语句</param>
/// <returns>当前语句</returns>
public AbstractSqlCommandWithWhere Where(Func <SqlConditionBuilder, ISqlCondition> where)
{
this._where = where (this._conditionBuilder);
return(this);
}
public ISqlOn Or(ISqlCondition clause) => Chain(() => OrRaw = clause);
/// <summary>
/// 设置指定查询的语句并返回当前语句
/// </summary>
/// <param name="where">查询语句</param>
/// <returns>当前语句</returns>
public SelectCommand Where(ISqlCondition where)
{
this._where = where;
return this;
}
public ISqlOn OrNot(ISqlCondition clause) => Or(SqlCondition.Not(clause));
public ISqlUpdate WhereNot(ISqlCondition clause) => Where(SqlCondition.Not(clause));
public ISqlSelect WhereNot(ISqlCondition clause) => Where(SqlCondition.Not(clause));
public void TestLessEqual() {
_condition = SqlCondition.Create( "a", "@", Operator.LessEqual );
Assert.AreEqual( "a<=@a", _condition.GetCondition() );
}
/// <summary>
/// 使用And连接条件
/// </summary>
/// <param name="condition">过滤条件</param>
public ISqlBuilder And(ISqlCondition condition)
{
return(And(condition.GetCondition()));
}
public ISqlSelect Where(ISqlCondition clause) => Where(new SqlWhere(clause));
public static string Or(ISqlCondition condition) => condition != null ? $"{S.Or} {condition.ToSql()}" : null;
/// <summary>
/// 设置指定查询的语句并返回当前语句
/// </summary>
/// <param name="where">查询语句</param>
/// <returns>当前语句</returns>
/// <example>
/// <code lang="C#">
/// <![CDATA[
/// IDatabase db = DatabaseFactory.CreateDatabase();
/// UpdateCommand cmd = db.CreateUpdateCommand("tbl_Users")
/// .Set("UserName", "admin");
///
/// cmd.Where(SqlCondition.Equal(cmd, "UserID", 1));
///
/// //UPDATE tbl_Users SET UserName = @UserName WHERE UserID = @UserID
/// //@UserName = "******"
/// //@UserID = 1
///
/// Boolean success = cmd.Result() > 0;
/// ]]>
/// </code>
/// </example>
public UpdateCommand Where(ISqlCondition where)
{
this._where = where;
return this;
}
/// <summary>
/// 设置查询语句
/// </summary>
/// <param name="command">另一个语句</param>
internal void InternalSetWhereCondition(AbstractSqlCommandWithWhere command)
{
this._where = command._where;
}
/// <summary>
/// 使用Or连接条件
/// </summary>
/// <param name="condition">过滤条件</param>
public ISqlBuilder Or(ISqlCondition condition)
{
return(Or(condition.GetCondition()));
}
/// <summary>
/// 设置查询语句
/// </summary>
/// <param name="where">查询语句</param>
internal void InternalSetWhereCondition(ISqlCondition where)
{
this._where = where;
}
/// <summary>
/// 设置指定查询的语句并返回当前语句
/// </summary>
/// <param name="where">查询语句</param>
/// <returns>当前语句</returns>
public AbstractSqlCommandWithWhere Where(ISqlCondition where)
{
this._where = where;
return this;
}
/// <summary>
/// 设置指定查询的语句并返回当前语句
/// </summary>
/// <param name="where">查询语句</param>
/// <returns>当前语句</returns>
public AbstractSqlCommandWithWhere Where(Func<SqlConditionBuilder, ISqlCondition> where)
{
this._where = where(this._conditionBuilder);
return this;
}
/// <summary>
/// 创建新的Sql Not条件语句
/// </summary>
/// <param name="cmd">Sql语句</param>
/// <param name="condition">条件语句</param>
/// <exception cref="ArgumentNullException">Sql条件语句不能为空</exception>
/// <returns>Sql Not条件语句</returns>
internal static SqlNotCondition Create(AbstractSqlCommandWithWhere cmd, ISqlCondition condition)
{
if (condition == null)
{
throw new ArgumentNullException("condition");
}
return new SqlNotCondition(cmd, condition);
}
/// <summary>
/// 设置指定查询的语句并返回当前语句
/// </summary>
/// <param name="where">查询语句</param>
/// <returns>当前语句</returns>
/// <example>
/// <code lang="C#">
/// <![CDATA[
/// IDatabase db = DatabaseFactory.CreateDatabase();
/// UpdateCommand cmd = db.CreateUpdateCommand("tbl_Users")
/// .Set("UserName", "admin");
///
/// cmd.Where(SqlCondition.Equal(cmd, "UserID", 1));
///
/// //UPDATE tbl_Users SET UserName = @UserName WHERE UserID = @UserID
/// //@UserName = "******"
/// //@UserID = 1
///
/// Boolean success = cmd.Result() > 0;
/// ]]>
/// </code>
/// </example>
public new UpdateCommand Where(ISqlCondition where)
{
this._where = where;
return(this);
}
/// <summary>
/// 设置查询语句
/// </summary>
/// <param name="command">另一个语句</param>
internal void InternalSetWhereCondition(AbstractSqlCommandWithWhere command)
{
this._where = command._where;
}
/// <summary>
/// 设置指定查询的语句并返回当前语句
/// </summary>
/// <param name="having">查询语句</param>
/// <returns>当前语句</returns>
public SelectCommand Having(ISqlCondition having)
{
this._having = having;
return(this);
}
/// <summary>
/// 设置指定查询的语句并返回当前语句
/// </summary>
/// <param name="where">查询语句</param>
/// <returns>当前语句</returns>
public AbstractSqlCommandWithWhere Where(ISqlCondition where)
{
this._where = where;
return(this);
}
/// <summary>
/// 设置指定查询的语句并返回当前语句
/// </summary>
/// <param name="where">查询语句</param>
/// <returns>当前语句</returns>
public SelectCommand Where(ISqlCondition where)
{
this._where = where;
return(this);
}
/// <summary>
/// 使用And连接条件
/// </summary>
/// <param name="condition">过滤条件</param>
public ISqlBuilder And( ISqlCondition condition ) {
return And( condition.GetCondition() );
}
public ISqlDelete Where(ISqlCondition clause) => Where(new SqlWhere(clause));
/// <summary>
/// 使用Or连接条件
/// </summary>
/// <param name="condition">过滤条件</param>
public ISqlBuilder Or( string condition ) {
if ( condition.IsEmpty() )
return this;
if ( _condition == null ) {
_condition = SqlCondition.Create( condition );
return this;
}
_condition = new OrCondition( _condition.GetCondition(), condition );
return this;
}
public ISqlUpdate Where(ISqlCondition clause) => Where(new SqlWhere(clause));
/// <summary>
/// 初始化Sql Not条件语句类
/// </summary>
/// <param name="baseCommand">源Sql语句</param>
/// <param name="baseCondition">基础Sql条件语句</param>
private SqlNotCondition(AbstractSqlCommandWithWhere baseCommand, ISqlCondition baseCondition)
: base(baseCommand)
{
this._baseCondition = baseCondition;
}
/// <summary>
/// 初始化Sql Not条件语句类
/// </summary>
/// <param name="baseCommand">源Sql语句</param>
/// <param name="baseCondition">基础Sql条件语句</param>
internal SqlNotCondition(AbstractSqlCommand baseCommand, ISqlCondition baseCondition)
: base(baseCommand)
{
this._baseCondition = baseCondition;
}