public ExpressionClip Replace(string subString, string replaceString)
        {
            Check.Require(!string.IsNullOrEmpty(subString), "subString could not be null or empty!");
            Check.Require(!string.IsNullOrEmpty(replaceString), "replaceString could not be null or empty!");

            ExpressionClip expr = ExpressionFactory.CreateCloneExpression(this);
            StringBuilder  sb   = new StringBuilder();

            sb.Append("REPLACE(");
            SqlQueryUtils.AppendColumnName(sb, this.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);
        }
 internal protected 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 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);
        }
        public DbCommand CreateDeleteCommand(string tableName, WhereClip where)
        {
            Check.Require(!string.IsNullOrEmpty(tableName), "tableName could not be null or empty!");
            //if (where != null)
            //{
            //    Check.Require(where.From == null, "expr.From must be null in Delete");
            //}

            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);
        }
        public static string ValidColumnName(string columnName)
        {
            Check.Require(columnName != null, "columnName could not be null!");

            StringBuilder sb = new StringBuilder();

            SqlQueryUtils.AppendColumnName(sb, columnName);
            return(sb.ToString());
        }
 protected void InitColumnExpression(string columnName, DbType type)
 {
     if (this.sql.Length > 0)
     {
         this.sql = new StringBuilder();
     }
     SqlQueryUtils.AppendColumnName(this.sql, columnName);
     this.DbType = type;
 }
        public DbCommand CreateInsertCommand(string tableName, string[] columns, DbType[] types, object[] values)
        {
            Check.Require(!string.IsNullOrEmpty(tableName), "tableName could not be null or empty!");
            Check.Require(columns == null ||
                          (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("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)
                {
                    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)
                {
                    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         = values[i] == null ? DBNull.Value : values[i];
                    cmd.Parameters.Add(p);
                }
                sb.Append(')');
            }

            cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
            PrepareCommand(cmd);
            return(cmd);
        }
        private static string Func(string funcName, string columnName)
        {
            Check.Require(!string.IsNullOrEmpty(funcName), "funcName could not be null or empty!");
            Check.Require(!string.IsNullOrEmpty(columnName), "columnName could not be null or empty!");

            StringBuilder sb = new StringBuilder(funcName);

            sb.Append("(");
            SqlQueryUtils.AppendColumnName(sb, columnName);
            sb.Append(')');
            return(sb.ToString());
        }
        public static string DatePart(string columnName, DatePartType partType)
        {
            Check.Require(!string.IsNullOrEmpty(columnName), "columnName could not be null or empty!");

            StringBuilder sb = new StringBuilder("DATEPART(");

            sb.Append(partType.ToString());
            sb.Append(',');
            SqlQueryUtils.AppendColumnName(sb, columnName);
            sb.Append(')');
            return(sb.ToString());
        }
        internal protected 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 string Count(string columnName, bool isDistinct)
        {
            Check.Require(!string.IsNullOrEmpty(columnName), "columnName could not be null or empty!");

            StringBuilder sb = new StringBuilder();

            sb.Append("COUNT(");
            if (isDistinct)
            {
                sb.Append("DISTINCT ");
            }
            SqlQueryUtils.AppendColumnName(sb, columnName);
            sb.Append(')');
            return(sb.ToString());
        }
        //public static string SubString(string columnName, int start)
        //{
        //    Check.Require(!string.IsNullOrEmpty(columnName), "columnName could not be null or empty!");
        //    Check.Require(start >= 0, "start must >= 0!");

        //    StringBuilder sb = new StringBuilder("SUBSTRING(");
        //    SqlQueryUtils.AppendColumnName(sb, columnName);
        //    sb.Append(',');
        //    sb.Append(start + 1);
        //    sb.Append(",LEN(");
        //    SqlQueryUtils.AppendColumnName(sb, columnName);
        //    sb.Append(')');
        //    sb.Append(')');
        //    return sb.ToString();
        //}

        public static string SubString(string columnName, int start, int length)
        {
            Check.Require(!string.IsNullOrEmpty(columnName), "columnName could not be null or empty!");
            Check.Require(start >= 0, "start must >= 0!");
            Check.Require(length >= 0, "length must = 0!");

            StringBuilder sb = new StringBuilder("SUBSTRING(");

            SqlQueryUtils.AppendColumnName(sb, columnName);
            sb.Append(',');
            sb.Append(start + 1);
            sb.Append(',');
            sb.Append(length);
            sb.Append(')');
            return(sb.ToString());
        }
        public DbCommand CreateCustomSqlCommand(string sql, string[] paramNames, DbType[] paramTypes, object[] paramValues)
        {
            Check.Require(!string.IsNullOrEmpty(sql), "sql could not be null or empty!");
            Check.Require(paramNames == null ||
                          (paramNames != null && paramTypes != null && paramValues != null &&
                           paramNames.Length == paramTypes.Length && paramNames.Length == paramValues.Length),
                          "length of paramNames, paramTypes and paramValues should equal!");

            DbCommand cmd = fac.CreateCommand();

            cmd.CommandType = CommandType.Text;

            AddInputParametersToCommand(paramNames, paramTypes, paramValues, cmd);

            cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sql, leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
            PrepareCommand(cmd);
            return(cmd);
        }
        public void SetGroupBy(string[] columns)
        {
            if (columns == null || columns.Length == 0)
            {
                groupBy = null;
                return;
            }

            StringBuilder sb = new StringBuilder();

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

                if (i < columns.Length - 1)
                {
                    sb.Append(',');
                }
            }
            groupBy = sb.ToString();
        }
        public ExpressionClip SubString(int start)
        {
            Check.Require(start >= 0, "start must >= 0!");

            ExpressionClip expr      = ExpressionFactory.CreateCloneExpression(this);
            ExpressionClip cloneExpr = ExpressionFactory.CreateCloneExpression(this);
            StringBuilder  sb        = new StringBuilder("SUBSTRING(");

            SqlQueryUtils.AppendColumnName(sb, this.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 DbCommand CreateUpdateCommand(string tableName, WhereClip where, string[] columns, DbType[] types, object[] values)
        {
            Check.Require(!string.IsNullOrEmpty(tableName), "tableName could not be null or empty!");
            //if (where != null)
            //{
            //    Check.Require(where.From == null, "expr.From must be null in Update");
            //}
            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)
            {
                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);
        }