/// <summary> /// Translate the filters in current query to IoT Hub SQL query condition /// Full text searching, paging and sorting are not supported by the IoT Hub SQL query until now /// </summary> /// <returns>The query condition, or empty string if no valid filter found</returns> public string GetSQLCondition() { if (IsAdvanced) { return(AdvancedClause); } var filters = Clauses?. Where(filter => !string.IsNullOrWhiteSpace(filter.ColumnName))?. Select(filter => { string op = null; switch (filter.ClauseType) { case ClauseType.EQ: op = "="; break; case ClauseType.NE: op = "!="; break; case ClauseType.LT: op = "<"; break; case ClauseType.GT: op = ">"; break; case ClauseType.LE: op = "<="; break; case ClauseType.GE: op = ">="; break; case ClauseType.IN: op = "IN"; break; default: throw new NotSupportedException(); } var value = filter.ClauseValue; // For syntax reason, the value should be surrounded by '' // This feature will be skipped if the value is a number. To compare a number as string, user should surround it by '' manually if (filter.ClauseType == ClauseType.IN) { var items = value.TrimStart('[').TrimEnd(']').Split(','); for (var i = 0; i < items.Length; i++) { var item = items[i].Trim(); items[i] = AddQuoteIfNeeded(filter.ClauseDataType, item); } value = FormattableString.Invariant($"[{string.Join(", ", items)}]"); } else { value = AddQuoteIfNeeded(filter.ClauseDataType, value); } var twinPropertyName = filter.ColumnName; if (this.IsProperties(twinPropertyName)) { twinPropertyName = FormattableString.Invariant($"properties.{filter.ColumnName}"); } return(FormattableString.Invariant($"{twinPropertyName} {op} {value}")); }).ToList(); return(filters == null ? string.Empty : string.Join(" AND ", filters)); }
public IResultCommand Build() { StringBuilder sql = new StringBuilder(); sql.Clear(); var from = Clauses.Where(x => x.Name == "From").Select(x => x.Value).FirstOrDefault(); var columns = (object[])Clauses.Where(x => x.Name == "Columns").FirstOrDefault().Value; var values = (object[])Clauses.Where(x => x.Name == "Values").FirstOrDefault().Value; var wheres = (object[])Clauses.Where(x => x.Name == "Where").ToArray(); IDictionary <string, object> dictionary = new Dictionary <string, object>(); int i = 0; string p = string.Empty; switch (Command) { case "Insert": { sql.AppendFormat("INSERT INTO {0}", from); sql.AppendFormat("({0})", string.Join(",", columns)); sql.Append(" VALUES("); while (i < columns.Length) { if (i > 0) { sql.Append(", "); } p = $"@p{i}"; sql.Append(p); dictionary.Add(p, values.GetValue(i)); i++; } sql.Append(")"); break; } case "Update": { sql.AppendFormat("UPDATE {0} SET ", from); while (i < columns.Length) { if (i > 0) { sql.Append(", "); } p = $"@p{i}"; sql.Append($"{columns.GetValue(i)}={p}"); dictionary.Add(p, values.GetValue(i)); i++; } if (wheres != null) { int j = 0; while (j < wheres.Length) { dynamic where = (dynamic)wheres.GetValue(j); if (j == 0) { sql.Append(" WHERE"); } if (j > 0) { sql.Append($" {where.Value.TypeLogical}"); } if (where.Value.Comparation == "IN") { sql.Append($" {where.Value.Name} IN("); byte s = 0; foreach (var value in where.Value.Values) { p = $"@p{i++}"; if (s == 1) { sql.Append(","); } sql.Append($"{p}"); dictionary.Add(p, value); s = 1; } sql.Append(")"); } else if (where.Value.Comparation == "BETWEEN") { p = $"@p{i}"; sql.Append($" {where.Value.Name} BETWEEN {p} AND "); dictionary.Add(p, where.Value.Initial); p = $"@p{++i}"; sql.Append($"{p}"); dictionary.Add(p, where.Value.End); } else if (where.Value.Comparation == "ISNULL") { sql.Append($" {where.Value.Name} IS NULL"); } else { p = $"@p{i}"; sql.Append($" {where.Value.Name} {where.Value.Comparation} {p}"); dictionary.Add(p, where.Value.Value); } i++; j++; } } break; } } return(new ResultCommand(sql.ToString(), CreateObjectWithValues(dictionary))); }
/// <summary> /// Remove all clauses for a component. /// </summary> /// <param name="component"></param> /// <returns></returns> public Q Clear(string component) { Clauses = Clauses.Where(x => x.Component != component).ToList(); return((Q)this); }
/// <summary> /// Get the list of clauses for a component. /// </summary> /// <returns></returns> public List <C> Get <C>(string component) where C : AbstractClause { return(Clauses.Where(x => x.Component == component).Cast <C>().ToList()); }
public string Count() { return(string.Format("SELECT COUNT(*) FROM ({0}) s", string.Join("\r\n", Clauses.Where(o => o.Name != "ORDER BY").Select(o => o.ToString())))); }