public static SqlClause AddNotBetween(this SqlClause sqlClause,
[NotNull] string columnName,
[CanBeNull] IDbDataParameter parameter,
[CanBeNull] IDbDataParameter parameter2)
{
if (parameter == null && parameter2 == null)
{
return(sqlClause);
}
if (parameter == null)
{
return(sqlClause.AddGreater(columnName, parameter2));
}
if (parameter2 == null)
{
return(sqlClause.AddLess(columnName, parameter));
}
sqlClause.Add(columnName + " not between " + parameter.ParameterName + " and " + parameter2.ParameterName);
sqlClause.Add(parameter);
sqlClause.Add(parameter2);
return(sqlClause);
}
public static SqlClause Add([NotNull] this SqlClause source,
[CanBeNull] string sql,
[NotNull] IEnumerable <IDbDataParameter> parameters)
{
source.Add(sql);
source.Add(parameters);
return(source);
}
public static SqlClause AddNotBitOr(this SqlClause sqlClause,
[NotNull] string columnName,
[CanBeNull] IDbDataParameter parameter)
{
if (parameter == null)
{
return(sqlClause);
}
sqlClause.Add(columnName + " & " + parameter.ParameterName + " <= 0");
sqlClause.Add(parameter);
return(sqlClause);
}
/// <summary>
/// Adds "<paramref name="columnName" /> not in (<paramref name="parameters" />.ParameterName, ...)"
/// expression to the clause.
/// </summary>
public static SqlClause AddNotIn(this SqlClause sqlClause,
[NotNull] string columnName,
[CanBeNull] IEnumerable <IDbDataParameter> parameters)
{
if (parameters == null)
{
return(sqlClause);
}
var list = parameters as IReadOnlyList <IDbDataParameter>;
if (list == null)
{
list = parameters.ToList();
}
if (list.Count == 0)
{
return(sqlClause);
}
if (list.Count == 1)
{
return(sqlClause.AddNotEquals(columnName, list[0]));
}
var expression = new StringBuilder(columnName);
expression.Append(" not in (");
for (var k = 0; k < list.Count; k++)
{
var p = list[k];
if (k > 0)
{
expression.Append(", ");
}
expression.Append(p.ParameterName);
sqlClause.Add(p);
}
expression.Append(')');
sqlClause.Add(expression.ToString());
return(sqlClause);
}
public static SqlClause AddPredicate(this SqlClause sqlClause,
[NotNull] string columnName,
[NotNull] string operand,
[CanBeNull] IDbDataParameter parameter)
{
if (parameter == null)
{
return(sqlClause);
}
sqlClause.Add(columnName + operand + parameter.ParameterName);
sqlClause.Add(parameter);
return(sqlClause);
}
public static SqlClause Add([NotNull] this SqlClause source, [NotNull] IEnumerable <IDbDataParameter> parameters)
{
foreach (var parameter in parameters)
{
source.Add(parameter);
}
return(source);
}
public static SqlClause Add([NotNull] this SqlClause source, [NotNull] SqlClause sqlClause, bool trimmed = false)
{
if (sqlClause == null)
{
throw new ArgumentNullException("sqlClause");
}
var sql = sqlClause.GetSql();
if (trimmed)
{
sql = sql.Trim(' ', '\t', '\n', '\r');
}
source.Add(sql);
source.Add(sqlClause.GetParameters());
return(source);
}
public static SqlClause AddNotExists(this SqlClause sqlClause, [CanBeNull] SqlClause clause)
{
if (clause == null)
{
return(sqlClause);
}
sqlClause.Add(new SqlClause("not exists (").Add(clause).Add(")"));
return(sqlClause);
}
public static SqlClause GroupBy(params string[] columns)
{
var sql_clause = new SqlClause().AsColumnsListClause("group by");
if (columns != null && columns.Length > 0)
{
foreach (var column in columns)
{
sql_clause.Add(column, column);
}
}
return(sql_clause);
}
public static SqlClause Insert(string tableName, params string[] columns)
{
var sql_clause = new SqlClause();
sql_clause.AsStatementsClause("insert into " + tableName, indent: null);
sql_clause.RenderIfEmpty = true;
if (columns != null && columns.Length > 0)
{
sql_clause.Add(InsertColumns(columns));
}
return(sql_clause);
}
public static SqlClause Statement([NotNull] params SqlClause[] clauses)
{
if (clauses == null)
{
throw new ArgumentNullException("clauses");
}
var sql_clause = new SqlClause();
foreach (var clause in clauses)
{
sql_clause.Add(clause);
}
return(sql_clause);
}
/// <summary>
/// Builds the sql statement based on current information.
/// </summary>
/// <returns></returns>
public SqlClause Build()
{
if (this.Select.IsEmpty)
{
this.Select.Add("*");
}
var result = new SqlClause();
if (this.prefix != null)
{
result.Add(this.prefix);
}
result.Add(this.Select);
result.Add(this.From);
if (this.where != null)
{
result.Add(this.where);
}
if (this.groupBy != null)
{
result.Add(this.groupBy);
}
if (this.having != null)
{
result.Add(this.having);
}
if (this.orderBy != null)
{
result.Add(this.orderBy);
}
if (this.suffix != null)
{
result.Add(this.suffix);
}
return(result);
}
public static SqlClause InsertColumns(params string[] columns)
{
var sql_clause = new SqlClause();
sql_clause.Prefix = "(" + Environment.NewLine;
sql_clause.ExpressionsPrefix = "\t";
sql_clause.ExpressionsSeparator = "," + Environment.NewLine + "\t";
sql_clause.ExpressionsSuffix = Environment.NewLine;
sql_clause.Suffix = ")" + Environment.NewLine;
foreach (var column in columns)
{
sql_clause.Add(column);
}
return(sql_clause);
}
public static SqlClause CTE(string cteName, SqlClause innerStatement = null)
{
var sql_clause = new SqlClause();
sql_clause.Prefix = ";with " + cteName + " as (" + Environment.NewLine;
sql_clause.ExpressionsPrefix = "\t";
sql_clause.ExpressionsSeparator = Environment.NewLine + "\t";
sql_clause.ExpressionsSuffix = Environment.NewLine;
sql_clause.Suffix = ")" + Environment.NewLine;
if (innerStatement != null)
{
sql_clause.Add(innerStatement, trimmed: true);
}
return(sql_clause);
}
public static SqlClause Add([NotNull] this SqlClause source, [NotNull] params IDbDataParameter[] parameters)
{
return(source.Add((IEnumerable <IDbDataParameter>)parameters));
}
public static SqlClause AddIsNotNull(this SqlClause sqlClause, [NotNull] string columnName)
{
sqlClause.Add(columnName + " is not null");
return(sqlClause);
}