scalar variable and simple delete statement

.net c# dapper

Question

I have method DeleteAuthors which receives id of type integer. I want using dapper to delete all data from that table which BookId is equal to id received as parametar.

private void DeleteAuthors(int id)
{
    this.db.Query("DELETE FROM Author WHERE BookId = @id");
}

Must declare the scalar variable "@id".

So I've tried with this

private void DeleteAuthors(int id)
{
   this.db.Query("DELETE FROM Author WHERE BookId = @Id", new { Id = id});
}

and error is

An unhandled exception of type 'System.ArgumentException' occurred in Dapper.dll

Additional information: When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id

id parametar variable is correctly populated with int value. I know this is simple but I'm struggle here.

Accepted Answer

Use db.Execute instead of db.Query if you want to delete records:

int numDel = db.Execute("DELETE FROM Author WHERE BookId = @BookId;", new { BookId = Id }, null, null, null);

But you should use the using statement to ensure that the connection gets disposed/closed as soon as possible (even on error):

int numDel = 0;
using (SqlConnection db = new SqlConnection("ConnectionString"))
{
    db.Open();
    numDel = db.Execute("DELETE FROM Author WHERE BookId = @BookId;", new { BookId = Id }, null, null, null);
}

Popular Answer

not sure about replacing @ in query

but following will also work:

private void DeleteAuthors(int id)
{
    this.db.Query(string.Format("DELETE FROM Author WHERE BookId = {0}",id));
}


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why