Mounting run-time updates

My technique is to use a variable to represent the separator and always concatenate the next value after the separator; but the separator is initialized empty and only receives the separator text after the first value is added to the query.

In a loop , it looks something like this:

sql = "update tabela set ";
paramPattern = "%s = '%s'";
separador = "";

// campos a atualizar, separados por vírgula:

for(campo : campos) {
    sql += separador + String.format(paramPattern, campo.nome, campo.valor);
    separador = ", ";
}

// cláusula where:

sql += " where ";
separador = "";

for(condicao : condicoes) {
    sql += separador + condicao;
    separador = " and ";
}

The idea also works if you do not have field and condition lists, but instead use if s. It looks something like this:

sql = "update tabela set ";
paramPattern = "%s = '%s'";
separador = "";

// campos a atualizar, separados por vírgula:

if (atualizarCampo1) {
    sql += separador + String.format(paramPattern, campo1.nome, campo1.valor);
    separador = ", ";
}
if (atualizarCampo2) {
    sql += separador + String.format(paramPattern, campo2.nome, campo2.valor);
    separador = ", ";
}
// outros campos...

// cláusula where:

sql += " where ";
separador = "";

if (filtrarPorData) {
    sql += separador + "DATA between :data1 and :data2";
    separador = " AND ";
}
if (filtrarPorStatus) {
    sql += separador + "STATUS = :status";
    separador = " AND ";
}
// outras condições...

If there is a possibility that there is no filter (command without where clause), simply fill in the conditions in another variable, and only concatenate it to sql if it ends other than empty after passing through conditions. For example:

sql = "select * from tabela";
where = "";
separador = "";

if (filtrarPorData) {
    where += separador + "DATA between :data1 and :data2";
    separador = " AND ";
}
if (filtrarPorStatus) {
    where += separador + "STATUS = :status";
    separador = " AND ";
}
// outras condições...

if (!"".equals(where)) {
    sql += " where " + where;
}

I'd rather do that than add something in the query ( where 1 = 1 ) just by code physiology. And I also prefer to add something unnecessary to the string and then remove it (in this case, an extra comma).

And of course this is a pseudo code just to demonstrate the idea of the separator as variable. I hope you always use parameters instead of concatenating values in the query.

In C # use the Join() method, it will mount by setting the tab the right way.

In Java use the join() .

I do not know if modern Delphi has something ready, but it's easy to make a Join() :

function Join(const Texts : array of string; const Separator : string) : string;
var
    i : Integer;
begin
    Result := Texts[low(Texts)];
    for i := low(Texts) + 1 to high(Texts) do
        Result := Separator + Result + Texts[i];
end;

Note that it starts by taking the first element of the array before the loop (this is the secret to not leaving a tab left over) and then always places a separator pair and new item. >     

Based on this response I've made an adaptation for you that allows you to mount update at runtime, see how was:

static string buildUpdate(string table, string nomeCampoId, string idCampo, Dictionary<string, object> parameters)
{           
    StringBuilder builder = new StringBuilder("Update " + table + " SET ");

    foreach (KeyValuePair<string, object> param in parameters)            
        builder.Append(param.Key).Append(" = ").Append(param.Value).Append(",");            

    builder.Remove(builder.Length - 1, 1); /*Aqui remove a virgula.*/
    builder.Append(" WHERE ").Append(nomeCampoId).Append(" = ").Append(idCampo);
    return builder.ToString();
}

To use the buildUpdate() method you need to build your dictionary to populate it with fields and values, see how the implementation got:

static void Main(string[] args)
{
    Dictionary<string, object> parameters = new Dictionary<string, object>();
    parameters.Add("campoNome", "'Maria'");
    parameters.Add("campoIdade", 25);
    parameters.Add("campoGenero", "'Feminino'");

    string update = buildUpdate("Pessoa", "idPessoa", "2",parameters);
    Console.WriteLine(update);
}

The buildUpdate() method will generate the following output on the console:

  

Update Person SET fieldName = 'Maria', fieldIty = 25, fieldGenero =   'Female' WHERE idPessoa = 2

You will need to adapt it, but I believe it is already a path.

I usually add the conditions with the comma, and at the end remove the last character of the string. In the case of AND works the same way only removing 3 characters.

public String constroiCondicao1(String... condicoes){
    if(condicoes == null)
        return "";
    StringBuilder sb = new StringBuilder();
    sb.append("WHERE ");
    for(String cond : condicoes){
        sb.append(cond + " AND ");
    }
    return sb.toString().substring(0, sb.length()-4);
}

// OU

public String constroiCondicao2(String... condicoes){
    if(condicoes == null)
        return "";
    return "WHERE " + String.join(" AND ", condicoes);
}