protected void ToSqlQuery(string columnname, ComparisonOperator oper, object values)
{
if (!(values is Array objectArray) || objectArray.Length == 0)
{
var nullstr = (oper == ComparisonOperator.In || oper == ComparisonOperator.NotIn) ? "(NULL)" : "NULL";
SqlQuery = $"{columnname} {oper.ToSqlComperationOperator(true)} {nullstr}";
return;
}
switch (oper)
{
case ComparisonOperator.Between when objectArray.Length != 2:
throw new ArgumentException("Array length must be 2 for Between Comparison Operator");
case ComparisonOperator.Between:
var param0 = "@" + Guid.NewGuid();
var param1 = "@" + Guid.NewGuid();
SqlQuery = $"{columnname} {oper.ToSqlComperationOperator(values.IsNull())} {param0} AND {param1}";
Parameters.Add(param0, ((Array)values).GetValue(0));
Parameters.Add(param1, ((Array)values).GetValue(1));
return;
case ComparisonOperator.In:
case ComparisonOperator.NotIn:
var sbIn = new StringBuilder($"{columnname} {oper.ToSqlComperationOperator(values.IsNull())} (");
for (var i = 0; i < ((Array)values).Length; i++)
{
var paramName = "@" + Guid.NewGuid();
sbIn.Append(paramName);
if (i < ((Array)values).Length - 1)
{
sbIn.Append(',');
}
Parameters.Add(paramName, ((Array)values).GetValue(i));
}
sbIn.Append(")");
SqlQuery = sbIn.ToString();
return;
}
var sb = new StringBuilder();
for (var i = 0; i < ((Array)values).Length; i++)
{
var val = ((Array)values).GetValue(i);
var paramName = "@" + Guid.NewGuid();
sb.Append($"{columnname} {oper.ToSqlComperationOperator(val.IsNull())} {paramName}");
if (i < ((Array)values).Length - 1)
{
sb.Append(" AND ");
}
Parameters.Add(paramName, val);
}
SqlQuery = sb.ToString();
}