Raise user defined SQL error and capture it in code

Question

I'm trying to raise a user defined error from one stored procedure to a c# code. I have a stored procedure that assign employees to users, if you already assigned an employee to user 1 for example and you try to do the same thing again, the stored procedure should raise an error saying, "This association already exists" and return a code, for example 1 and a description of the error. The problem is that in c sharp is not passing by the catch part. here is the code:

public bool InsertUser(Guid userId, string[][] IDs, AppParams myParams)
{
    bool flag = false;
    SqlTransaction transaction = null;

    using (var dbConn = new SqlConnection(this.ConnectionString))
    {
        using (var cmd = new SqlCommand())
        {
            try
            {
                dbConn.Open();
                cmd.Connection = dbConn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "InsertUser";

                transaction = dbConn.BeginTransaction();
                cmd.Transaction = transaction;

                for (int i = 0; i < IDs.Length; i++)
                {
                    flag = false;
                    cmd.Parameters.Clear();

                    cmd.Parameters.AddWithValue("@UserId", userId);
                    cmd.Parameters.AddWithValue("@EmployeeId", IDs[i][0]);
                    cmd.Parameters.AddWithValue("@CompanyId", myParams.ApplicationId);
                    cmd.Parameters.AddWithValue("@ModifiedUserId", myParams.User.UserId);

                    //add output parameter
                    cmd.Parameters.Add("@ID", SqlDbType.Int, 4);
                    cmd.Parameters["@ID"].Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@ReturnValue", SqlDbType.Int, 4);
                    cmd.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add("@ErrorMsg", SqlDbType.VarChar, 300);
                    cmd.Parameters["@ErrorMsg"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

                    var returnValue = Convert.ToInt32(cmd.Parameters["@ReturnValue"].Value);
                    flag = returnValue.Equals(0);

                    if (!flag)
                    {                                                                
                        if (cmd.Parameters["@ErrorMsg"].Value != DBNull.Value)
                        {
                            this.ErrorModel.HasError = true;
                            this.ErrorModel.ErrorMessage = cmd.Parameters["@ErrorMsg"].Value.ToString();
                        }

                        transaction.Rollback();                     
                        return false;                                
                    }
                }
                transaction.Commit();           
            }

            catch (SqlException sqlex)
            {               
                this.ErrorModel.HasError = true;
                this.ErrorModel.ErrorMessage = sqlex.Message;
            }
            catch (Exception ex)
            {           
                this.ErrorModel.HasError = true;
                this.ErrorModel.ErrorMessage = ex.Message;
            }           
        }
    }
    return flag;
}

Am I doing something wrong?

here is my SQL code:

ALTER PROCEDURE [dbo].[InsertUser]
(
    @UserId             uniqueidentifier,
    @EmployeeId         int,
    @CompanyId          uniqueidentifier,
    @SystemStatusId     int = 1,
    @ModifiedByUserId   uniqueidentifier,
    @ID                 int output,
    @ErrorMsg           nvarchar(300) = NULL output
)
AS
BEGIN

    DECLARE @ReturnVal int

    SET NOCOUNT ON;

    SET @ReturnVal = 0;
    SET @ErrorMsg = null;

    --  check for existing combination
    IF EXISTS(SELECT 1 FROM [dbo].[UserRel]
               WHERE [UserId] = @UserId 
                 AND [EmployeeId] = @EmployeeId
                 AND [CompanyId] = @CompanyId
                 AND [SystemStatusId] = 1)
    BEGIN
            SET @ReturnVal = 1;
            SET @ErrorMsg = 'Item already Exist in Database'            
            RAISERROR @ErrorMsg, 0, 0
            GOTO ProcedureExit;
    END

    -- Insert statement
    INSERT [dbo].[UserRel]
    (
        [UserId],
        [EmployeeId],
        [CompanyId],
        [SystemStatusId],
        [ModifiedByUserId],
        [ModifiedDate]
    )
    VALUES
    (
        @UserId,
        @EmployeeId,
        @CompanyId,
        @SystemStatusId,
        @ModifiedByUserId,
        sysdatetimeoffset()
    )

    IF( @@ERROR <> 0 )
        BEGIN
            SET @ReturnVal = 2;
            SET @ErrorMsg = 'Failed to INSERT [dbo].[InsertUser]'
            GOTO ProcedureExit;
        END
    ELSE
        SET @ID = SCOPE_IDENTITY();

ProcedureExit:    
        RETURN @ReturnVal;

END

Answer 1

The problem is that in c sharp is not passing by the catch part.

Because in the C#, there was no exception. You simply checked the return value of the stored procedure, and then decided to rollback and return false;. If you want an exception: throw an exception.

Alternatively, if the SQL issues a raiserror with severity 16 or higher, an exception will be observed automatically.

Incidentally, it is not clear that your transaction is correctly terminated in the case of a genuine exception. I would suggest moving the rollback code into the catch block. Since you do the exact same thing regardless of which exception you catch, there is no benefit in the catch (SqlException sqlex) block - so you might as well remove that.