How to enforce a certain number or range of affected rows in SQL Server or ASP.NET C#

for some SQL queries I know the e开发者_运维百科xpected number of rows that must be affected by UPDATE, DELETE or INSERT statements. It could be a number such as 1 or a range such as 0-1 or 1-2 etc.

What is the best way to enforce that this is the number of affected rows and that otherwise an exception is thrown?

Also, can I limit the number of rows affected for UPDATE/DELETE/INSERT to say 1 row and otherwise get an exception?

Thanks

You can use TOP in insert, update and delete to enforce the number of rows affected. There is no exception thrown with this.

declare @T table (id int)

insert top(1) into @T values
(1),
(2),
(3)

If you want exception you can use @@ROWCOUNT in a transaction.

declare @Min int = 2
declare @Max int = 3

begin transaction

insert into Table1 values
(1),
(2),
(3),
(4)

if not @@rowcount between @Min and @Max
begin
  rollback
  -- Throw exception here, RAISERROR()
end
else
begin
  commit
end  

For UPDATE, INSERT, and DELETE statements, ExecuteNonQuery returns the number of rows affected by the command.

I suggest you wrap the call in a transaction and if the result is not as expected rollback the transaction and throw an exception.

You can also use @@ROWCOUNT to do the same inside the query.

Use @@ROWCOUNT directly after your statement. If not equal to what you want, use RAISERROR.

One small warning however, RAISERROR has a severity parameter. Use a number between 11 and 19. Lower then 11 is considered a warning. Higher than 19 can only be done by sysadmins, is a fatal error and terminates your connection.

I think at the query level, the thing to use is either @@ROWCOUNT within the procedure or similar outside, but you need to use transactions.

If you want to go a little lower in the database (since the above technique would only protect queries designed to use such a framework), then you can use triggers on tables and get a count of the INSERTED or DELETED pseudo-tables and use RAISERRROR to raise an appropriate error.