I'm adding a new method in my generic repository to delete a record directly from the db, instead of going through the DbConext
and then calling its SaveChanges
.
So I did:
public virtual void Delete(int id)
{
var connection = dataContext.GetDatabase().Connection;
var query = string.Format("DELETE FROM {0} WHERE id = {1}", tableName, id);
connection.Execute(query);
}
My current code that deletes entities using the DbContext
handles DbUpdateException
, and this exception bubbles up to the client.
Does Dapper's Execute
extension also throws this exception?
no it doesn't; if you want that, you should be doing things like select @@rowcount
after the delete
in the tsql, using ExecuteScalar<int>
, and checking that the number returned is 1
. If you want timestamp checking, you would include that in the where
clause and as a parameter
never, never, never concatenate inputs into the data part of SQL; it creates a SQL injection risk, and destroys all query / operation caches - the first reason is all you should ever need, though. There is a bit of a caveat here around things like table names, but you should be white-listing there. Note that dapper supports full parameterization (easy parameter handling is one of the main reasons to use it!)
For example, I would be doing something like:
public YourType(string tableName) { // constructor
WhiteList.AssertValid(tableName); // throws if not allowed
deleteCommand = $"DELETE FROM [{tableName}] WHERE id = @id; SELECT @@ROWCOUNT;";
}
private readonly string deleteCommand;
public virtual void Delete(int id)
{
var connection = dataContext.GetDatabase().Connection;
int count = connection.ExecuteScalar<int>(deleteCommand, new { id });
if(count != 0) throw new DbUpdateException();
}
Alternatively, use an add-on tool like dapper-contrib that does all this stuff for you.
I know this is not an answer to actual question but imho, you should go for
Finding the reason for DbUpdateException
That way, you can override Execute method, get to the root cause and solve it for good.