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