Question
I'm trying to figure out if there's a way to update an object in one shot with Dapper rather than having to write out every variable/field alignment. Here's an example of what I'm doing now, which is to explicitly spell out each field:
public string UpdateAttributes(List<ItemAttribute> attributesList)
{
try
{
using (IfxConnection con = new IfxConnection(WebConfigurationManager.AppSettings["LOKICONN"].ToString()))
{
con.Open();
foreach (ItemAttribute item in attributesList)
{
con.Execute("update oe_cnvwrk set cwr_response = ?, cwr_uom = ? where cwr_genero = ? and cwr_line = ?",
new { cwr_response = item.cwr_response, cwr_uom = item.cwr_uom, cwr_genero = item.cwr_genero, cwr_line = item.cwr_line });
}
con.Close();
return "success";
}
}
catch (Exception x)
{
return x.ToString();
}
}
Is there a way to skip spelling out each variable and simply reference the object? Or a better way to approach this period? Dapper allows for dynamically creating an object with a query, and for populating the values of a pre-defined object, but for updating an existing object I'm not finding any documentation or examples. With a larger object that becomes a bit of a pain, as does maintenance if the table and object need to be changed.
Accepted Answer
This might work:
using (IfxConnection con = new IfxConnection(WebConfigurationManager.AppSettings["LOKICONN"].ToString()))
{
con.Execute("update oe_cnvwrk set cwr_response = ?cwr_response?, cwr_uom = ?cwr_uom? where cwr_genero = ?cwr_genero? and cwr_line = ?cwr_line?", attributesList);
return "success";
}
Changes:
- no need to open/close the connection
- passes the sequence directly; no need to loop
- uses the special dapper
?foo?syntax, which maps named members from the available data to positional SQL; this will be re-written to use positional?sql, but adding the expected parameters in the expected order
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow