Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update with Dapper using dynamic column name

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.

like image 548
Nathália Neves Avatar asked Feb 22 '17 13:02

Nathália Neves


2 Answers

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.

like image 69
teo van kot Avatar answered Oct 19 '22 22:10

teo van kot


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.

like image 35
jean Avatar answered Oct 19 '22 21:10

jean