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