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());
}
internal void SetHaving(WhereClip where)
{
if (string.IsNullOrEmpty(where.Sql))
{
Having = string.Empty;
return;
}
Having = where.Sql;
SqlQueryUtils.AddParameters(parameters, where);
}
/// <summary>
/// Initializes a new instance of the <see cref="QueryColumn"/> class.
/// </summary>
/// <param name="dbColName">Name of the database col.</param>
/// <param name="type">The type.</param>
/// <param name="entityPropertyName">Name of the entity property.</param>
/// <param name="isPk">if set to <c>true</c> [is pk].</param>
/// <param name="isAutoIncrement">是否为自增长属性</param>
/// <remarks>
/// <para>创建：Teddy</para>
/// <para>日期：2018/3/22</para>
/// </remarks>
public QueryColumn(string dbColName, DbType type, string entityPropertyName, bool isPk, bool isAutoIncrement)
{
Check.Require(!string.IsNullOrEmpty(dbColName), "dbColName could not be null or empty.");
SqlQueryUtils.AppendColumnName(this.sql, dbColName);
this.entityPropertyName = entityPropertyName;
this.DbType = type;
this.IsPK = isPk;
this.IsAutoIncrement = isAutoIncrement;
}
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());
}
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 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 CreateBatchInsertCommand(string tableName, string[] columnNames, DbType[] columnTypes, object[][] rows)
{
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 (columnNames == null || columnNames.Length == 0)
{
sb.Append("DEFAULT VALUES");
}
else
{
sb.Append('(');
for (int i = 0; i < columnNames.Length; ++i)
{
if (columnNames[i].Trim()[0] == '[')
{
sb.Append(columnNames[i].Replace("[", leftToken.ToString()).Replace("]", rightToken.ToString()));
}
else
{
sb.Append(leftToken);
sb.Append(columnNames[i].TrimStart(leftToken).TrimEnd(rightToken));
sb.Append(rightToken);
}
if (i < columnNames.Length - 1)
{
sb.Append(',');
}
}
sb.Append(")");
sb.Append(GetValuesDatas(columnTypes, rows, cmd));
}
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
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 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(')');
//sb.Append(" [COUNT_");
//sb.Append(columnName.Replace("*", "ALL").Replace(" ", "_").Replace(".", "_").Replace("[", string.Empty).Replace("]", string.Empty));
//sb.Append(']');
return(sb.ToString());
}
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(')');
//if (funcName == "SUM" || funcName == "AVG" || funcName == "MIN" || funcName == "MAX")
//{
// sb.Append(" [");
// sb.Append(funcName);
// sb.Append("_");
// sb.Append(columnName.Replace("*", "ALL").Replace(" ", "_").Replace(".", "_").Replace("[", string.Empty).Replace("]", string.Empty));
// sb.Append(']');
//}
return(sb.ToString());
}
internal 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 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);
}
