Update with Dapper using dynamic column name

Question

I need to do an update but the column name is dynamic.

Code Snippet:

using (var cn = Connection)
{
    var sql = @"UPDATE Teste
        SET @columnName = @parameter,
        DT_PROCESSAMENTO = @vDtProcessamento                                        
        WHERE ID = @ID";

    var resultado = cn.Execute(sql, new
    {
        columnName,
        parameter,
        ID
    });
}

Is it possible to pass the column name as a parameter? This code is what I did, but it does not work. No exceptions but the update does not work.

Answer 1

You should use String.Format:

var columnName = "Name";
var sql = String.Format(@"UPDATE Teste
                 SET {0} = @parameter                                       
                 WHERE ID = @ID", columnName);

But here you can get SQL injection.

So it's better to check that column name is really column name in your table.

Answer 2

No you cannot do that that way because the column name cannot be a variable. To do it you do need dynamic SQL like this:

using (var cn = Connection)
{
       var sql = $@"UPDATE Teste
                    SET {columnName} = @parameter,
                    DT_PROCESSAMENTO = @vDtProcessamento                                        
                    WHERE ID = @ID";

        var resultado = cn.Execute(sql, new
        {
                     parameter,
                     ID
        });
}

In the above snippet code you can combine @ to use linebreaks inside the string and $ to insert variables in the string - it's a bit clearer and shorter than using String.Format.

I already use something like this with dapper for a few specific scenarios.