/// <summary>
/// and columnName = value
/// </summary>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <returns></returns>
public SqlBuilderSingle Equals(string columnName, bool value)
{
var temp = Number;
WhereSQL.Append($" and [{columnName}] = @{columnName}{temp} ");
Parameters.Add($"{columnName}{temp}", value);
return(this);
}
/// <summary>
/// Checks that the <see cref="IFilter"/> WhereSQL passes basic syntax checks via <see cref="FilterSyntaxChecker"/>.
/// </summary>
/// <param name="notifier"></param>
public virtual void Check(ICheckNotifier notifier)
{
if (WhereSQL != null && WhereSQL.StartsWith("where ", StringComparison.CurrentCultureIgnoreCase))
{
notifier.OnCheckPerformed(new CheckEventArgs("Filters do not need to start with 'where' keyword, it is implicit", CheckResult.Fail));
}
new FilterSyntaxChecker(this).Check(notifier);
}
/// <summary>
/// and columnName = value
/// </summary>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <returns></returns>
public SqlBuilderSingle Equals(string columnName, long?value)
{
if (value != null && value != 0)
{
long temp = Number;
WhereSQL.Append($" and [{columnName}] = @{columnName}{temp} ");
Parameters.Add($"{columnName}{temp}", value);
}
return(this);
}
/// <summary>
/// and columnName = value
/// </summary>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <returns></returns>
public SqlBuilderSingle NotEquals(string columnName, string value)
{
if (string.IsNullOrEmpty(value))
{
return(this);
}
long temp = Number;
WhereSQL.Append($" and [{columnName}] != @{columnName}{temp} ");
Parameters.Add($"{columnName}{temp}", value);
return(this);
}
/// <summary>
/// and columnName in (values[1],values[2])
/// </summary>
/// <param name="columnName"></param>
/// <param name="values"></param>
/// <returns></returns>
public SqlBuilderSingle In(string columnName, IEnumerable <long> values)
{
if (values == null)
{
return(this);
}
long temp = Number;
WhereSQL.Append($" and [{columnName}] in (@{columnName}{temp})");
Parameters.Add($"{columnName}{temp}", values.ToList());
return(this);
}
/// <summary>
/// and columnName between beginValue and endValue
/// </summary>
/// <param name="columnName"></param>
/// <param name="beginValue"></param>
/// <param name="endValue"></param>
/// <returns></returns>
public SqlBuilderSingle BetweenDateByAccurate(string columnName, decimal?beginValue, decimal?endValue)
{
if (beginValue != null && beginValue != 0 && endValue != null && endValue != 0)
{
long temp1 = Number;
long temp2 = Number;
WhereSQL.Append($" and [{columnName}] between @{columnName}{temp1} and @{columnName}{temp2} ");
Parameters.Add($"{columnName}{temp1}", beginValue);
Parameters.Add($"{columnName}{temp2}", endValue);
}
return(this);
}
/// <summary>
/// and columnName between beginValue and endValue
/// </summary>
/// <param name="columnName"></param>
/// <param name="beginValue"></param>
/// <param name="endValue"></param>
/// <returns></returns>
public SqlBuilderSingle BetweenDateByDay(string columnName, DateTime?beginValue, DateTime?endValue)
{
if (beginValue != null && endValue != null)
{
long temp1 = Number;
long temp2 = Number;
WhereSQL.Append($" and [{columnName}] between @{columnName}{temp1} and @{columnName}{temp2} ");
Parameters.Add($"{columnName}{temp1}", beginValue.Value.ToString("yyyy-MM-dd"));
Parameters.Add($"{columnName}{temp2}", endValue.Value.ToString("yyyy-MM-dd 23:59:59"));
}
return(this);
}
/// <summary>
/// and columnName = value
/// </summary>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <returns></returns>
public SqlBuilderSingle Equals(string columnName, int?value)
{
if (value == null || value == 0)
{
return(this);
}
var temp = Number;
WhereSQL.Append($" and [{columnName}] = @{columnName}{temp} ");
Parameters.Add($"{columnName}{temp}", value);
return(this);
}
//::timestamp
/// <summary>
/// and columnName like '%value%'
/// </summary>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <returns></returns>
public SqlBuilderSingle Like(string columnName, string value)
{
if (string.IsNullOrEmpty(value))
{
return(this);
}
long temp = Number;
WhereSQL.Append($" and [{columnName}] like @{columnName}{temp} ");
Parameters.Add($"{columnName}{temp}", $"%{value}%");
return(this);
}
/// <summary>
/// and columnName::timestamp > '%value%'
/// </summary>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <returns></returns>
public SqlBuilderSingle GreetThanDate(string columnName, string value)
{
if (string.IsNullOrEmpty(value))
{
return(this);
}
long temp = Number;
WhereSQL.Append($" and [{columnName}] > @{columnName}{temp}::TIMESTAMP ");
Parameters.Add($"{columnName}{temp}", $"%{value}%");
return(this);
}
/// <summary>
/// and (columnNames[0] like '%value%' or columnNames[1] like '%value%' )
/// </summary>
/// <param name="columnNames"></param>
/// <param name="value"></param>
/// <returns></returns>
public SqlBuilderSingle LikeOr(IList <string> columnNames, string value)
{
if (string.IsNullOrEmpty(value) || columnNames == null || !columnNames.Any())
{
return(this);
}
var stringBuilder = new StringBuilder();
WhereSQL.Append(" and (");
foreach (var item in columnNames)
{
var temp = Number;
stringBuilder.Append($" or [{item}] like @{item}{temp} ");
Parameters.Add($"{item}{temp}", $"%{value}%");
}
WhereSQL.Append(stringBuilder.ToString().TrimStart(" or".ToCharArray()));
return(this);
}
/// <summary>
/// 获得where后面的sql
/// </summary>
/// <returns></returns>
public override string ToString()
{
return(" 1=1 " + WhereSQL.ToString());
}
