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); }