Does Execute extension throw DbUpdateException?

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?

score 1 · Accepted Answer · answered Mar 22 '16 at 08:20

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.

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

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.

edited May 23 '17 at 12:07

Community

1
1

answered Mar 22 '16 at 08:03

uTeisT

2,256
14
26

In my scenario it might happen that the db admin "unwittingly" changes something in the db (eg, foreign key, add/change columns, etc). Therefore I need to catch the exception so we can locate the problem and solve it. – Ivan-Mark Debono Mar 22 '16 at 08:19

Does Execute extension throw DbUpdateException?

2 Answers2