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 @@ROWCOUNTdirectly 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.