public static ExpressionClip operator !(ExpressionClip left)
{
ExpressionClip expr = new ExpressionClip().Append(QueryOperator.BitwiseNOT,
left);
return(expr);
}
public ExpressionClip Append(QueryOperator op, ExpressionClip right)
{
this.sql.Append(SqlQueryUtils.ToString(op));
this.sql.Append(SqlQueryUtils.ToString(right));
SqlQueryUtils.AddParameters(this.parameters, right);
return(this);
}
public static ExpressionClip operator /(object left, ExpressionClip right)
{
ExpressionClip expr = new ExpressionClip(right.dbType, left);
expr.Append(QueryOperator.Divide, right);
return(expr);
}
public ExpressionClip SubString(int start, int length)
{
ExpressionClip expr = (ExpressionClip)this.Clone();
expr.Sql = ColumnFormatter.SubString(expr.Sql, start, length);
return(expr);
}
public static ExpressionClip operator /(ExpressionClip left, ExpressionClip right)
{
ExpressionClip expr = (ExpressionClip)left.Clone();
expr.Append(QueryOperator.Divide, right);
return(expr);
}
public DbCommand CreateUpdateCommand(string tableName, WhereClip where, string[] columns, DbType[] types, object[] values)
{
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("UPDATE ");
sb.Append(leftToken);
sb.Append(tableName.TrimStart(leftToken).TrimEnd(rightToken));
sb.Append(rightToken);
sb.Append(' ');
sb.Append("SET ");
for (int i = 0; i < columns.Length; ++i)
{
if (columns[i].Trim()[0] == '[')
{
sb.Append(columns[i].Replace("[", leftToken.ToString()).Replace("]", rightToken.ToString()));
}
else
{
sb.Append(leftToken);
sb.Append(columns[i].TrimStart(leftToken).TrimEnd(rightToken));
sb.Append(rightToken);
}
sb.Append('=');
if (values[i] != null && values[i] is ExpressionClip)
{
ExpressionClip expr = (ExpressionClip)values[i];
sb.Append(expr.ToString());
AddExpressionParameters(expr, cmd);
}
else
{
string paramName = MakeUniqueParamNameWithPrefixToken();
sb.Append(paramName);
DbParameter p = cmd.CreateParameter();
p.ParameterName = paramName;
p.DbType = types[i];
p.Value = GetValue(p.DbType, values[i]);
cmd.Parameters.Add(p);
}
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
if ((!WhereClip.IsNullOrEmpty(where)) && where.Sql.Length > 0)
{
sb.Append(" WHERE ");
sb.Append(SqlQueryUtils.RemoveTableAliasNamePrefixes(where.Sql));
AddExpressionParameters(where, cmd);
}
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
public ExpressionClip Avg()
{
ExpressionClip expr = (ExpressionClip)this.Clone();
expr.Sql = ColumnFormatter.Avg(expr.Sql);
return(expr);
}
public static ExpressionClip GetCurrentUtcDate()
{
ExpressionClip expr = new ExpressionClip();
expr.Sql = ColumnFormatter.GetCurrentUtcDate();
expr.dbType = DbType.DateTime;
return(expr);
}
public ExpressionClip ToNumber()
{
ExpressionClip expr = (ExpressionClip)this.Clone();
expr.Sql = ColumnFormatter.ToNumber(expr.Sql);
return(expr);
}
public static ExpressionClip operator /(ExpressionClip left, object right)
{
ExpressionClip expr = (ExpressionClip)left.Clone();
expr.Append(QueryOperator.Divide,
new ExpressionClip(left.dbType, right));
return(expr);
}
public static ExpressionClip operator %(ExpressionClip left, ExpressionClip right)
{
ExpressionClip expr = (ExpressionClip)left.Clone();
expr.Append(QueryOperator.Modulo, right);
expr.dbType = DbType.Int32;
return(expr);
}
public static ExpressionClip operator %(object left, ExpressionClip right)
{
ExpressionClip expr = new ExpressionClip(right.dbType, left);
expr.Append(QueryOperator.Modulo, right);
expr.dbType = DbType.Int32;
return(expr);
}
public ExpressionClip BitwiseAnd(ExpressionClip right)
{
ExpressionClip expr = (ExpressionClip)this.Clone();
expr.Append(QueryOperator.BitwiseAND, right);
return(expr);
}
public ExpressionClip Count(bool isDistinct)
{
ExpressionClip expr = (ExpressionClip)this.Clone();
expr.Sql = ColumnFormatter.Count(expr.Sql, isDistinct);
expr.dbType = DbType.Int32;
return(expr);
}
public ExpressionClip GetDay()
{
ExpressionClip expr = (ExpressionClip)this.Clone();
expr.Sql = ColumnFormatter.DatePart(expr.Sql, ColumnFormatter.DatePartType.Day);
expr.dbType = DbType.Int32;
return(expr);
}
public ExpressionClip BitwiseXOr(object right)
{
ExpressionClip expr = (ExpressionClip)this.Clone();
expr.Append(QueryOperator.BitwiseXOR,
new ExpressionClip(this.dbType, right));
return(expr);
}
public ExpressionClip Alias(string aliasName)
{
ExpressionClip expr = (ExpressionClip)this.Clone();
if (expr.parameters.Count > 0)
{
expr.Sql = "(" + expr.Sql + ")";
}
expr.sql.Append(' ');
SqlQueryUtils.AppendColumnName(expr.sql, aliasName);
return(expr);
}
/// <summary>
/// Initializes a new instance of the <see cref="OrderByClip"/> class.
/// </summary>
/// <param name="item">The item.</param>
/// <param name="descend">if set to <c>true</c> [descend].</param>
public OrderByClip(ExpressionClip item, bool descend)
{
//Check.Require(!ExpressionClip.IsNullOrEmpty(item), "item could not be null or empty.");
string itemStr = item.ToString();
while (itemStr.IndexOf("] [") > 0)
{
itemStr = itemStr.Substring(itemStr.IndexOf("] [") + 2);
}
orderBys.Add(new KeyValuePair <string, bool>(itemStr, descend));
}
public WhereClip NotIn(ExpressionClip subQuery)
{
WhereClip where = new WhereClip();
where.Sql = string.Format("{0} NOT IN ({1})", this.ToString(), subQuery.ToString());
if (subQuery.Parameters.Count > 0)
{
Dictionary <string, KeyValuePair <DbType, object> > .Enumerator en = subQuery.Parameters.GetEnumerator();
while (en.MoveNext())
{
where.Parameters.Add('@' + en.Current.Key.TrimStart(SqlQueryUtils.PrefixCharArray), new KeyValuePair <DbType, object>(en.Current.Value.Key, en.Current.Value.Value));
}
}
return(where);
}
public WhereClip Or(ExpressionClip left, QueryOperator op, ExpressionClip right)
{
if (this.sql.Length > 0)
{
this.sql.Append(" OR ");
}
this.sql.Append(SqlQueryUtils.ToString(left));
this.sql.Append(SqlQueryUtils.ToString(op));
this.sql.Append(SqlQueryUtils.ToString(right));
SqlQueryUtils.AddParameters(this.parameters, left);
SqlQueryUtils.AddParameters(this.parameters, right);
return(this);
}
public static WhereClip operator !=(ExpressionClip left, ExpressionClip right)
{
WhereClip where = new WhereClip();
if (ExpressionClip.IsNullOrEmpty(right))
{
where = where.And(left, QueryOperator.IsNULL, null).Not();
}
else if (ExpressionClip.IsNullOrEmpty(left))
{
where = where.And(right, QueryOperator.IsNULL, null).Not();
}
else
{
where.And(left, QueryOperator.NotEqual, right);
}
return(where);
}
public static WhereClip operator <=(ExpressionClip left, ExpressionClip right)
{
WhereClip where = new WhereClip();
if (ExpressionClip.IsNullOrEmpty(right))
{
where = where.And(left, QueryOperator.LessOrEqual, null);
}
else if (ExpressionClip.IsNullOrEmpty(left))
{
where = where.And(right, QueryOperator.GreaterOrEqual, null);
}
else
{
where.And(left, QueryOperator.LessOrEqual, right);
}
return(where);
}
public ExpressionClip SubString(int start)
{
ExpressionClip expr = (ExpressionClip)this.Clone();
ExpressionClip cloneExpr = (ExpressionClip)this.Clone();
StringBuilder sb = new StringBuilder("SUBSTRING(");
SqlQueryUtils.AppendColumnName(sb, expr.Sql);
sb.Append(',');
sb.Append(start + 1);
sb.Append(",LEN(");
SqlQueryUtils.AppendColumnName(sb, cloneExpr.Sql);
sb.Append(')');
sb.Append(')');
expr.sql = sb;
SqlQueryUtils.AddParameters(expr.parameters, cloneExpr);
return(expr);
}
public ExpressionClip IndexOf(string subString)
{
ExpressionClip expr = (ExpressionClip)this.Clone();
StringBuilder sb = new StringBuilder();
sb.Append("CHARINDEX(@");
string paramName = MakeUniqueParamNameWithoutPrefixToken();
sb.Append(paramName);
sb.Append(',');
SqlQueryUtils.AppendColumnName(sb, expr.Sql);
sb.Append(')');
sb.Append("-1");
expr.sql = sb;
expr.dbType = DbType.Int32;
expr.parameters.Add('@' + paramName, new KeyValuePair <DbType, object>(this.dbType, subString));
return(expr);
}
public virtual object Clone()
{
ExpressionClip newExpr = new ExpressionClip();
newExpr.dbType = this.dbType;
string tempSql = this.sql.ToString();
Dictionary <string, KeyValuePair <DbType, object> > .Enumerator en = this.parameters.GetEnumerator();
while (en.MoveNext())
{
object value = en.Current.Value.Value;
if (value != null && value != DBNull.Value && value is ICloneable)
{
value = ((ICloneable)value).Clone();
}
string newParamName = MakeUniqueParamNameWithoutPrefixToken();
tempSql = tempSql.Replace('@' + en.Current.Key.TrimStart(SqlQueryUtils.PrefixCharArray), '@' + newParamName);
newExpr.Parameters.Add('@' + newParamName, new KeyValuePair <DbType, object>(en.Current.Value.Key, value));
}
newExpr.sql.Append(tempSql);
return(newExpr);
}
public ExpressionClip Replace(string subString, string replaceString)
{
ExpressionClip expr = (ExpressionClip)this.Clone();
StringBuilder sb = new StringBuilder();
sb.Append("REPLACE(");
SqlQueryUtils.AppendColumnName(sb, expr.Sql);
sb.Append(",@");
string paramName = MakeUniqueParamNameWithoutPrefixToken();
sb.Append(paramName);
sb.Append(",@");
string paramName2 = MakeUniqueParamNameWithoutPrefixToken();
sb.Append(paramName2);
sb.Append(')');
expr.sql = sb;
expr.dbType = DbType.Int32;
expr.parameters.Add('@' + paramName, new KeyValuePair <DbType, object>(this.dbType, subString));
expr.Parameters.Add('@' + paramName2, new KeyValuePair <DbType, object>(this.dbType, replaceString));
return(expr);
}
public DbCommand CreateInsertCommand(string tableName, string[] columns, DbType[] types, object[] values)
{
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("INSERT INTO ");
sb.Append(leftToken);
sb.Append(tableName.TrimStart(leftToken).TrimEnd(rightToken));
sb.Append(rightToken);
sb.Append(' ');
if (columns == null || columns.Length == 0)
{
sb.Append("DEFAULT VALUES");
}
else
{
sb.Append('(');
for (int i = 0; i < columns.Length; ++i)
{
if (columns[i].Trim()[0] == '[')
{
sb.Append(columns[i].Replace("[", leftToken.ToString()).Replace("]", rightToken.ToString()));
}
else
{
sb.Append(leftToken);
sb.Append(columns[i].TrimStart(leftToken).TrimEnd(rightToken));
sb.Append(rightToken);
}
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(") VALUES (");
for (int i = 0; i < columns.Length; ++i)
{
if (values[i] != null && values[i] is ExpressionClip)
{
ExpressionClip expr = (ExpressionClip)values[i];
sb.Append(expr.ToString());
AddExpressionParameters(expr, cmd);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
else
{
string paramName = MakeUniqueParamNameWithPrefixToken();
sb.Append(paramName);
if (i < columns.Length - 1)
{
sb.Append(',');
}
DbParameter p = cmd.CreateParameter();
p.ParameterName = paramName;
p.DbType = types[i];
p.Value = GetValue(p.DbType, values[i]);
cmd.Parameters.Add(p);
}
}
sb.Append(')');
}
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
public static bool IsNullOrEmpty(ExpressionClip expr)
{
return(((object)expr) == null || expr.sql.Length == 0);
}
