Adding CASE WHEN to Update Functions #757
Description
This weekend I have successfully created the CASE WHEN function that allows developers to build the case when and/or lines using arrays within the main parts array. I would like to share my branch to you for you to test and see and maybe publish so I can get it back to my workplace since they are dependent on SQL Kata. I've added the readme below and also the main file with changes.
- New Update and UpdateAsync upgrade: CASE
** A new feature added to allow developers to programmatically set CASE WHEN when assigning values. Feature includes grouping in sub statements () or
** to allow condition to point to a column variable instead of a direct paramater value. SQL injection friendly
** Original Update Statement for multiple records using anonymous objects:
*** foreach (var item in data)
*** {
*** object obj = new
*** {
*** MyField = item.Value
*** };
*** cnt += await QueryFactory.Query(tableName).Where("Id", item.Id).UpdateAsync(value);
*** }
*** return cnt;
** New Update with select case using multi-level array systems
** version 1 : allows is equal condition only for now
** For the Else it will always fill with name of field itself , self assigning.
** This happens if format is wrong as well.
** The else protects you fro your field to be set back to NULL
*** Warning: Limitation is requires , Suggest 200 rows for low number columns,
*** 25 for higher number columns or clauses.
var datac = data.Chunk(200); // breaking data up to 200 rows
//each holds for each update set, which allows multiple value setting as older Update
List<object[]> cases = [];
if (datac.Any()) foreach (var d in datac)
{
try
{
foreach (var item in d) //Build case when statement , standard 3
{
cases.Add(["Id", item.Id, item.Value]);
}
object obj = new
{
MyField= cases.ToArray()
};
cases.Clear();
//if data set is smaller than whole table , best to use in statement to reduce cost
cnt += await QueryFactory.Query(tableName)
.WhereIn("Id", d.Select(dd => dd.Id).ToArray())
.UpdateAsync(value);
}
catch { throw; }
finally { cases.Clear(); }
}
else cases.Clear();
return cnt;
**standard: Case WHEN x = A then Y... END:
*** In your cases array the flow is [x,A,Y].
*** Assignmet value is always last.
** Available Feaure 1 : While its common to do 3 items for basic, when can extend the criteria with AND and OR
** It combine, the array column after the orevioud criteria field must be an AND or OR, unless using , () or * explained later
*** Note: Assignmet value is always last. you can use AND,&&,& or OR,||,|, <>. Not case sensitive.
*** Case WHEN x = A AND z = B then Y ... END:
*** In your cases array the flow is [x,A,"AND",z,B,Y]
*** Case WHEN x = A OR z = B then Y ... END:
*** Array the flow is [x,A,"OR",z,B,Y]
** Available Feaure 2 : Subset (). This allows seperating your "And" & "Or" blocks
*** ex: case when (a = 1 or a = 5) and (b = 7 and c = 2)
*** This can be placed anywhere before the assignment column or * assignment column,
*** if you forget to add the ) to close, the engine
*** will compensate.
*** Case WHEN (x = A AND z = B) OR J = C then Y ... END:
*** Array the flow is ["(",x,A,"AND",z,B,")","OR",j,c,Y]
*** Case WHEN (x = A OR z = B) AND (J = C AND K = D) then Y ... END:
*** Array the flow is ["(",x,A,"OR",z,B,")","AND","(",j,c,"AND",k,d,")" Y]
** Available Feaure 3 : To Another Column Field (). This allows criteria to check if column equals another column (field)
*** Case WHEN (colx = colb AND colz = colx) then Y ... END:
*** Array the flow is [,colx,',colb,"AND",colz,colx, Y]
code 👍 private void SetUpCaseWhenUpdatePart(SqlResult ctx, List parts, string columnName, object[] value)
{
StringBuilder casewrap = new StringBuilder($"{Wrap(columnName)} = ");
bool hasOne = false;
foreach (var item in value)
{
if (item is object[] i && i.Length >= 3)
{
int indent = 0;
object val = i.Last();
var subparts = i.Take(i.Length-1).ToArray();
int pointer = 0;
bool substart = true;
bool start = true;
bool setasfield = false;
bool criteriaValue = false;
var field = string.Empty;
while (pointer <= (subparts.Length - 1))
{
var piece = subparts[pointer].ToString().ToUpperInvariant().Trim();
if (pointer > 0 && !substart)
{
if (!VERB.SpecialChar.Any(s => s == piece) && criteriaValue)
{
pointer = subparts.Length;
break;
}
else if (VERB.AndOpertors.Any(s => s == piece))
{
casewrap.Append(" ").Append(VERB.And).Append(" ");
pointer++;
substart = true;
continue;
}
else if (VERB.OrOpertors.Any(s => s == piece))
{
casewrap.Append(" ").Append(VERB.Or).Append(" ");
pointer++;
substart = true;
continue;
}
}
if (!criteriaValue && VERB.AndOrOpertors.Any(s => s == piece))
{
pointer = subparts.Length;
break;
}
else if (piece == VERB.StartParenth)
{
indent++;
pointer++; casewrap.Append(VERB.StartParenth);
continue;
}
else if (piece == VERB.EndParenth)
{
if (indent > 0)
{
indent--;
casewrap.Append(VERB.EndParenth);
pointer++;
continue;
}
}
if (substart && !string.IsNullOrEmpty(field))
{
criteriaValue = true;
}
if (piece == VERB.PushField && criteriaValue)
{
setasfield = true;
pointer++;
continue;
}
else if (string.IsNullOrEmpty(field))
{
field = piece;
}
if (substart && criteriaValue && !string.IsNullOrEmpty(field))
{
if (!hasOne && start)
{
casewrap.Append(VERB.CaseWhen);
hasOne = true;
}
else if (start)
{
casewrap.Append(" ").Append(VERB.When);
}
casewrap.Append($" {field} = {(setasfield ? subparts[pointer] : Parameter(ctx, subparts[pointer]))}");
substart = false;
setasfield = false;
start = false;
criteriaValue = false;
field = string.Empty;
}
pointer++;
}
if (indent > 0 && hasOne)
{
casewrap.Append("".PadLeft(indent, ')'));
}
if (hasOne)
{
casewrap.Append($" {VERB.Then} {Parameter(ctx, val)}");
}
}
}
if (!hasOne)
{
casewrap.Append($"{Wrap(columnName)}");
}
else
{
casewrap.Append($" {VERB.Else} {Wrap(columnName)} {VERB.End}");
}
parts.Add(casewrap.ToString());
casewrap.Length = 0;
}
private void SetUpDirectUpdatePart(SqlResult ctx, List<string> parts, string columnName, object value)
{
parts.Add($"{Wrap(columnName)} = {Parameter(ctx,value)}");
}
constants are located in different file for better literal string memory handling.
in update code:
for (var i = 0; i < toUpdate.Columns.Count; i++)
{
var values = toUpdate.Values[i];
if (values is object[] v && v.Length > 0)
{
SetUpCaseWhenUpdatePart(ctx, parts, toUpdate.Columns[i], v);
}
else if (values is not Array)
{
SetUpDirectUpdatePart(ctx, parts, toUpdate.Columns[i], values);
}
else throw new MissingMemberException();
}
my email is azuzu77@yahoo.com or pazuzufraney@gmail.com
Thomas Franey