Does Execute extension throw DbUpdateException?

c# dapper entity-framework

Question

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?

Accepted Answer

  1. 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

  2. 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.


Popular Answer

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.



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