public DbCommand CreateUpdateCommand(string tableName, WhereClip where, string[] columns, DbType[] types, object[] values) { 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 = GetValue(p.DbType, 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); }
/// <summary> /// Initializes a new instance of the <see cref="OrderByClip"/> class. /// </summary> /// <param name="item">The item.</param> /// <param name="descend">if set to <c>true</c> [descend].</param> public OrderByClip(ExpressionClip item, bool descend) { //Check.Require(!ExpressionClip.IsNullOrEmpty(item), "item could not be null or empty."); string itemStr = item.ToString(); while (itemStr.IndexOf("] [") > 0) { itemStr = itemStr.Substring(itemStr.IndexOf("] [") + 2); } orderBys.Add(new KeyValuePair <string, bool>(itemStr, descend)); }
public WhereClip NotIn(ExpressionClip subQuery) { WhereClip where = new WhereClip(); where.Sql = string.Format("{0} NOT IN ({1})", this.ToString(), subQuery.ToString()); if (subQuery.Parameters.Count > 0) { Dictionary <string, KeyValuePair <DbType, object> > .Enumerator en = subQuery.Parameters.GetEnumerator(); while (en.MoveNext()) { where.Parameters.Add('@' + en.Current.Key.TrimStart(SqlQueryUtils.PrefixCharArray), new KeyValuePair <DbType, object>(en.Current.Value.Key, en.Current.Value.Value)); } } return(where); }
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); }