I haven't seen many people doing this.
The problem here could be due to the scope is not defined correctly.
The best way would be to create a stored procedure which can be executed on single call to the DB. Your stored proc should look like-
CREATE PROCEDURE myProcName
(
@ID INT,
@ItemName VARCHAR(50),
@Price MONEY
)
AS
BEGIN
IF EXISTS(SELECT ID from Item where ID=@ID)
BEGIN
DELETE FROM Item WHERE ID=@ID;
INSERT INTO Item(ItemName, Price, ID)
VALUES (@ItemName, @Price, @ID)
END
END
Here the statements are properly blocked with
BEGIN..END
keywords.
However, you can also stick to your parameterised query approach in which case you need to make multiple calls to the DB.
The approach could be something like-
--Check the existance of data in the table with just the
SELECT
query and get the true/false value.
--If you get true value then go ahead to execute the
DELETE
command and then the
INSERT
command in another command object.
Hope, it helps :)