Update with Dapper using dynamic column name

Question

Update with Dapper using dynamic column name

asp.net-mvc c# dapper sql

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.

NathÃ¡lia Neves

Popular Answer

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.

teo van kot

View more on Stack Overflow

jean · Accepted Answer · 2/22/2017 1:44:40 PM

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.

Update with Dapper using dynamic column name

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator