Catch the error from Stored Procedure in C#

Question

I have a stored procedure that is called to validate a user during login. If success it returns the user entity, and that works good! My question is if it doesn't work, I'll raise an error in the SP, How do I catch this error and use it in the best way? Right now I'm getting nullrefference, this is the code: Store 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 the code it looks like this, the 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 I should handle the exception?

Answer 1

   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 2

Your InnerException is probably null.

You should try to catch and deal with specific exceptions, in this case SqlExceptions.

Answer 3

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

Use the inbuilt StoredProcException in catch, that implies:

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

You can pass the Message as string instead of spEx.Message

Answer 4

I wouldn't generally raise an error from a SP unless it was actually a system problem with the operation. Entering the wrong username and password is a user problem, and one you need only deal with at the interface level, so I'd throw most of that SP away and deal with the two use cases (1 row or 0 rows returned) in the business layer or interface code. If 0 rows, throw up the "Wrong username or password" message to the client and if 1, log in.

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