Exemple #1
0
        public DbCommand CreateDeleteCommand(string tableName, WhereClip where)
        {
            Check.Require(!string.IsNullOrEmpty(tableName), "tableName could not be null or empty!");

            DbCommand cmd = fac.CreateCommand();
            cmd.CommandType = CommandType.Text;

            StringBuilder sb = new StringBuilder("DELETE FROM ");
            sb.Append(leftToken);
            sb.Append(tableName.TrimStart(leftToken).TrimEnd(rightToken));
            sb.Append(rightToken);

            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;
        }
Exemple #2
0
        public static WhereClip operator |(WhereClip left, WhereClip right)
        {
            Check.Require(((object)left) != null, "left could not be null.");
            Check.Require(((object)right) != null, "right could not be null.");

            WhereClip newWhere = new WhereClip();
            newWhere.Or(left);
            newWhere.Or(right);
            return newWhere;
        }
Exemple #3
0
 public static bool IsNullOrEmpty(WhereClip where)
 {
     return ((object)where) == null || where.ToString().Length == 0;
 }
Exemple #4
0
 public static WhereClip operator !(WhereClip right)
 {
     WhereClip newWhere = new WhereClip();
     newWhere.Or(right).Not();
     return newWhere;
 }
Exemple #5
0
        public WhereClip NotIn(ExpressionClip subQuery)
        {
            Check.Require(!ExpressionClip.IsNullOrEmpty(subQuery), "subQuery could not be null or empty.");

            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("@:?".ToCharArray()), new KeyValuePair<DbType, object>(en.Current.Value.Key, en.Current.Value.Value));
                }
            }
            return where;
        }
Exemple #6
0
        public WhereClip NotIn(params object[] objs)
        {
            Check.Require(objs != null && objs.Length > 0, "objs could not be null or empty.");

            WhereClip where = new WhereClip();
            foreach (object obj in objs)
            {
                where.Or(this == obj);
            }

            return where.Not();
        }
Exemple #7
0
        public static WhereClip operator >=(object left, ExpressionClip right)
        {
            WhereClip where = new WhereClip();
            if (left == null || left == DBNull.Value)
            {
                where.And(right, QueryOperator.LessOrEqual, null);
            }
            else
            {
                where.And(new ExpressionClip(right.DbType, left),
                    QueryOperator.GreaterOrEqual, right);
            }

            return where;
        }
Exemple #8
0
 public abstract DbCommand CreateSelectRangeCommand(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool identyColumnIsNumber);
Exemple #9
0
        public static WhereClip operator >=(ExpressionClip left, ExpressionClip right)
        {
            WhereClip where = new WhereClip();
            if (ExpressionClip.IsNullOrEmpty(right))
            {
                where.And(left, QueryOperator.GreaterOrEqual, null);
            }
            else if (ExpressionClip.IsNullOrEmpty(left))
            {
                where.And(right, QueryOperator.LessOrEqual, null);
            }
            else
            {
                where.And(left, QueryOperator.GreaterOrEqual, right);
            }

            return where;
        }
Exemple #10
0
        public static WhereClip operator !=(object left, ExpressionClip right)
        {
            WhereClip where = new WhereClip();
            if (left == null || left == DBNull.Value)
            {
                where = where.And(right, QueryOperator.IsNULL, null).Not();
            }
            else
            {
                where.And(new ExpressionClip(right.DbType, left),
                    QueryOperator.NotEqual, right);
            }

            return where;
        }
Exemple #11
0
        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;
        }
Exemple #12
0
        public FromClip Join(string tableOrViewName, string aliasName, WhereClip onWhere)
        {
            Check.Require(!string.IsNullOrEmpty(tableOrViewName), "tableName could not be null or empty!");
            Check.Require(!string.IsNullOrEmpty(aliasName), "aliasName could not be null or empty!");
            Check.Require(((object)onWhere) != null && onWhere.From == null, "onWhere could not be null, onWhere.From must be null in Join!");

            if (joins.ContainsKey(aliasName))
            {
                throw new NameDuplicatedException("In joins list: aliasName - " + aliasName);
            }

            joins.Add(aliasName, new KeyValuePair<string, WhereClip>(tableOrViewName, onWhere));
            joinTypes.Add(JoinType.Inner);

            return this;
        }
Exemple #13
0
 public FromClip Join(string tableOrViewName, WhereClip onWhere)
 {
     return Join(tableOrViewName, tableOrViewName, onWhere);
 }
Exemple #14
0
        public DbCommand CreateUpdateCommand(string tableName, WhereClip where, string[] columns, DbType[] types, object[] values)
        {
            Check.Require(!string.IsNullOrEmpty(tableName), "tableName could not be null or empty!");
            Check.Require(columns != null && types != null && values != null && columns.Length == types.Length && columns.Length == values.Length,
                "length of columns, types and values must equal!");

            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 = values[i] == null ? DBNull.Value : 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;
        }
Exemple #15
0
        public override object Clone()
        {
            WhereClip where = new WhereClip(this.from);
            where.groupBy = this.groupBy;
            where.isNot = this.isNot;
            where.orderBy = this.orderBy;
            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("@:?".ToCharArray()), '@' + newParamName);
                where.parameters.Add('@' + newParamName, new KeyValuePair<DbType, object>(en.Current.Value.Key, value));
            }
            where.sql = new StringBuilder(tempSql);
            return where;
        }
Exemple #16
0
        public static WhereClip operator >=(ExpressionClip left, object right)
        {
            WhereClip where = new WhereClip();
            if (right == null || right == DBNull.Value)
            {
                where.And(left, QueryOperator.GreaterOrEqual, null);
            }
            else
            {
                where.And(left, QueryOperator.GreaterOrEqual,
                    new ExpressionClip(left.DbType, right));
            }

            return where;
        }
Exemple #17
0
        public WhereClip Or(WhereClip where)
        {
            if (WhereClip.IsNullOrEmpty(where) || where.Sql.Length == 0)
            {
                return this;
            }

            bool toWrapBrackets = false;

            if (sql.Length > 0)
            {
                sql.Append(" OR ");
                toWrapBrackets = true;
            }

            if (where.isNot && where.sql.Length > 0)
            {
                sql.Append("NOT ");
            }

            if (where.sql.Length > 0)
            {
                sql.Append(where.sql);
            }

            SqlQueryUtils.AddParameters(parameters, where);

            if (toWrapBrackets)
                this.sql = new StringBuilder("(" + this.sql.ToString() + ")");

            return this;
        }
Exemple #18
0
        public DbCommand CreateSelectCommand(WhereClip where, string[] columns)
        {
            Check.Require(((object)where) != null && where.From != null, "expr and expr.From could not be null!");
            Check.Require(columns != null && columns.Length > 0, "columns could not be null or empty!");

            DbCommand cmd = fac.CreateCommand();
            cmd.CommandType = CommandType.Text;

            StringBuilder sb = new StringBuilder("SELECT ");
            for (int i = 0; i < columns.Length; ++i)
            {
                SqlQueryUtils.AppendColumnName(sb, columns[i]);

                if (i < columns.Length - 1)
                {
                    sb.Append(',');
                }
            }
            sb.Append(" FROM ");
            sb.Append(where.ToString());

            AddExpressionParameters(where, cmd);

            cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
            PrepareCommand(cmd);
            return cmd;
        }