1

What's the best practice way to enforce a unique constraint (on an email address for example) when inserting (or updating) a user record in dapper?

To prevent a race condition I am guessing you would just try the insert and let SQL Server return an error if the constraint was violated - then handle that? I can find no examples of how to do this using Dapper - or any thoughts on best practice?

thx.

niico
  • 11,206
  • 23
  • 78
  • 161

1 Answers1

3

This simply isn't a question that dapper intends to answer. Dapper just runs SQL. But sure, a unique constraint at the DB sounds fine - the exception filters in C# 6 is really helpful for handling specific SQL exceptions:

catch(SqlException ex) when (ex.Number == 2627 || ex.Number == 2601)
{ ... }
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • It always feels like a failure 'letting an exception happen' - rather than having a structure setup to handle the situation. Still maybe this is the best / simplest solution to a relatively hard problem. What I don't want to be doing is adding a ton of complexity. (also a race condition is extremely unlikely). – niico Jun 04 '16 at 22:43
  • @niico well, the cleaner approach is an `insert ... where not exists ...` - and check the `@@rowcount`; any use? – Marc Gravell Jun 04 '16 at 23:35
  • Yes I guess that's the other option - allowing an exception seems pretty common too and its efficiency may just edge it. Thanks. – niico Jun 05 '16 at 14:58
  • 1
    Just for reference for anyone else finding this - this thread covers a TSQL option: http://stackoverflow.com/questions/4707063/how-can-i-do-an-insert-where-not-exists – niico Jun 05 '16 at 15:29