set item category

//sp
ALTER PROCEDURE [dbo].[usp_SetItemCategory]
    @ncName            nvarchar(100),
    @ncDesctiption  nvarchar(256),
    @ncImage image,
    @StatusID int,
    @IsActive bit

       

AS
BEGIN
--Check to make sure the Name  does not already exist
    DECLARE @existing AS int = 0
    SELECT @existing=count(Name)
    FROM dbo.tbl_itemCategory
    WHERE Name=@ncName

    IF @existing > 0
    BEGIN
        RAISERROR ('Name is already exists', 1, 1)
        RETURN 2
    END

    --Attempt insert
    INSERT INTO dbo.tbl_itemCategory
                (Name,
                 Desctiption,
                 StatusID,
                 Image,
                 IsActive)
            VALUES       
            (@ncName,
            @ncDesctiption,
            @StatusID,
            @ncImage,
            @IsActive)

            --check to see if insert occured
           --and return status
           IF @@ROWCOUNT = 1
                RETURN 1
           ELSE
                RETURN 0
        
END

//data access
        public int SetItemCategory(string name, string description, byte[] pic, int statusID, bool isActive)
        {

            int return_value = 0;
            SqlConnection Conn = DBConn.GetDbConnection();
            try
            {
                SqlCommand cmd = new SqlCommand("usp_SetItemCategory", Conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ncName", name);
                cmd.Parameters.AddWithValue("@ncDesctiption", description);
                cmd.Parameters.AddWithValue("@ncImage", pic);
                cmd.Parameters.AddWithValue("@StatusID", statusID);
                cmd.Parameters.AddWithValue("@IsActive", isActive);

                //get the return value from sp
                SqlParameter returnParam = new SqlParameter();
                returnParam.ParameterName = "returnValue";
                returnParam.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(returnParam);

                cmd.ExecuteNonQuery();

                if (cmd.Parameters["returnValue"].Value.ToString() == "2")
                {
                    return_value = 2;
                }
                else if (cmd.Parameters["returnValue"].Value.ToString() == "1")
                {
                    return_value = 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Conn.Close();
            }
            return return_value;
        }