public static string ValidColumnName(string columnName)
{
StringBuilder sb = new StringBuilder();
SqlQueryUtils.AppendColumnName(sb, columnName);
return(sb.ToString());
}
public DbCommand CreateSelectCommand(WhereClip where, string[] columns)
{
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 void SetOrderBy(KeyValuePair <string, bool>[] columns)
{
if (columns == null || columns.Length == 0)
{
orderBy = null;
return;
}
StringBuilder sb = new StringBuilder();
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i].Key);
if (columns[i].Value)
{
sb.Append(" DESC");
}
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
orderBy = sb.ToString();
}
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 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 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 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);
}