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.
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.
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.