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); }
private static string Func(string funcName, string columnName) { StringBuilder sb = new StringBuilder(funcName); sb.Append("("); SqlQueryUtils.AppendColumnName(sb, columnName); sb.Append(')'); 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 static string DatePart(string columnName, DatePartType partType) { StringBuilder sb = new StringBuilder("DATEPART("); sb.Append(partType.ToString()); sb.Append(','); SqlQueryUtils.AppendColumnName(sb, columnName); sb.Append(')'); return(sb.ToString()); }
public static string SubString(string columnName, int start, int length) { 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) { 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 static string Count(string columnName, bool isDistinct) { StringBuilder sb = new StringBuilder(); sb.Append("COUNT("); if (isDistinct) { sb.Append("DISTINCT "); } SqlQueryUtils.AppendColumnName(sb, columnName); sb.Append(')'); return(sb.ToString()); }
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); }
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 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]); sb.Append(','); } groupBy = sb.ToString().TrimEnd(','); }
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 DbCommand CreateDeleteCommand(string tableName, WhereClip where) { 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 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 CustomQueryTable(SubQuery subQuery) : base(subQuery.ToString()) { this.tableName = subQuery.ToString(); SqlQueryUtils.AddParameters(this.parameters, subQuery); }