Catch an error from a stored procedure in C #

Question

I have a stored procedure that is called to verify the user during login. If success returns a custom object and this works well! My question is if this does not work, I will throw an error in SP. How can I catch this error and use it in the best way? Right now I get nullrefference, this is the code: Saving procedure:

ALTER PROCEDURE getEmployee
    (
    @username nvarchar(50),
    @password nvarchar(50)
    )
AS
DECLARE @Error_MSG nvarchar(50)
BEGIN

IF EXISTS (select * from Employee where eUsername = @username AND pword = @password)
begin
    select * from Employee where eUsername = @username AND pword = @password

    END

    ELSE
    BEGIN
    SET @Error_MSG = 'Wrong password, or user doesnt exist'
    RAISERROR (@Error_MSG, 11,1)
    END
END

And in code, it looks like this: SP is getEmployee

ActivityDatabaseDataContext dc = new ActivityDatabaseDataContext();
        Employee emp;
        public bool logIn(string piUsername, string piPassword)
        {
            try
            {
                emp = dc.getEmployee(piUsername, piPassword).Single();
            }
            catch (Exception ex)
            {
                errorMsg = ex.Message + ex.InnerException.Message;
            }
            if (emp != null)
            {
                AppHelper.AppHelper.setUser(emp);
                return true;
            }
            else
            {
                return false;
            }

My question is, how should I handle the exception?

Answer 1

SP, . - , , SP (1 0 , ) - . 0 , " " 1, .

ALTER PROCEDURE getEmployee 
( 
    @username nvarchar(50), 
    @password nvarchar(50) 
) 
AS
BEGIN
    select * from Employee where eUsername = @username AND pword = @password
END

Answer 2

InnerException, , null.

, SqlExceptions.

Answer 3

   ALTER PROCEDURE getEmployee
        (
        @username nvarchar(50),
        @password nvarchar(50)
        )
    AS
    BEGIN

    select * from Employee where eUsername = @username AND pword = @password

    END

...

SqlCommand cmd = new SqlCommand("getEmployee", conn);
cmd.AddWithValue('@username', name);
cmd.AddWithValue('@password', pass);

SqlAdapter da = new SqlAdapter(cmd);
DataSet ds= new DataSet();
da.Fill(ds);

if (ds.Table.Count > 0 && ds.Table.Rows.Count == 1)  {
    // success
} else {
    // fail
}

Answer 4

  IF(@Count>0)  
  BEGIN   
  SELECT  @RetVal = 6  
        , @ErrMsg = 'A description with the same name exists. Please provide a unique name.'  
  GOTO ERROR            
  END 

StoredProcException catch, :

        catch (StoredProcException spEx)
        {
            switch (spEx.ReturnValue)
            {
                case 6:
                    UserMessageException umEx= new UserMessageException(spEx.Message);
                    throw umEx;
            }
        }

spEx.Message