For 1 and 2:
in C#
int rowCount = cmd.ExecuteNonQuery()
if rowCount >0
{
Record is inserted
}
else
{
Record already exist.
}
For 3:
Declare @Error VARCHAR(1000) output
Begin Try
Begin Transaction
IF (@Mode='Insert')
Begin
If NOT Exists (Select ProductName From dbo.tblProduct WHERE ProductName = @ProductName)
Insert into tblProduct
(
ProductName ,
ProductCode )
Values
(
@ProductName ,
@ProductCode )
Commit
End Try
Begin Catch
SET @Error=(SELECT ERROR_MESSAGE())
ROLLBACK TRANSACTION
RAISERROR(@Error,16,1)
End Catch