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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With