Executing Insert Statement with Dapper

Question

I am trying to use the method explained by @MarcGravell in the below post and comments

How to insert a C# List to database using Dapper.NET

I am trying to go from

 string sql2 = string.Format(@"INSERT INTO #cusipSedolList (cusipSedol) values (@A)");
 IDbConnection con = new SqlConnection(_connectionString);
 using (con)
 {
 con.Open();
 foreach (var cusipSedol in cusipSedolsParameter)
 {
 con.Execute(sql2, new { A = cusipSedol.CusipSedol });
 }
 }

to

 string sql2 = string.Format(@"INSERT INTO #cusipSedolList (cusipSedol) values (@CusipSedol)");
 IDbConnection con = new SqlConnection(_connectionString);
 using (con)
 {
 con.Open();
 con.Execute(sql2, new {cusipSedolsParameter});
 }

with

 var cusipSedolsParameter = new List<CusipSedols>();
 public class CusipSedols
 {
 public string CusipSedol { get; set; }
 }

but I am getting the error that I have to declare @CusipSedol if it has more than one CusipSedol.

Answer 1

In your second example, you pass the argument new {cusipSedolsParameter} which is an anonymous type object with a single member named cusipSedolsParameter.

Instead, you just want to pass your list. Your call should look like:

con.Execute(sql2, cusipSedolsParameter);

Then Dapper will enumerate over your list and perform your query for each entry, matching query parameters against the properties of the type of the entry.

• eltropico: I changed it to queryResults = con.Query<SecData>(sqlNew, cusipSedolsParameter);but I still get the error: Must declare the scalar variable "@CusipSedol".
• Marc Gravell: @eltropico it should work fine; can you confirm that CusipSedol is a public property, not a field, and not internal, protected or private ?
• eltropico: @Marc thank you very much for your help. It is working now. The reason it didn't work was a select statement right after the insert into statement in the same sql string in my actual code. Thank you for conforming that my posted logic was correct so I was able to find the mistake.