Return one row from stored procedure in asp.net

I one to create a stored procedure to retrieve one row in the table:

create procedure LogInUser
    @username nvarchar(64),
    @password nvarchar(64),
    @succeed bit out,   
    @not_exist_err bit out
as
declar开发者_如何学编程e @exist_user nvarchar(64)
select @exist_user = username from users
where username = @username

if @exist_user is null
begin
    set @succeed = 0
    set @not_exist_err = 1
    return
end
else
begin
    select * from users
    where username = @username and password = @password 
end
return

First, I verify if a user exist or not by using:

select @exist_user = username from users
where username = @username

Then get the row with every column:

select * from users
where username = @username and password = @password 

However, by using SqlDataReader, the program won't enter the while loop to retrieve the information.

It just don't enter the loop. I don't understand why, even I executed it using SQL Server like this, it did return a row:

declare @succeedResult bit
declare @existErr bit
exec LogInUser @username=admin, @password =admin, @succeed = @succeedResult, @not_exist_err = @existErr

EDIT: The C# method which executes the store procedure:

public User LogIn(User usr)
{
   SqlConnection conn = A2.Controller.Utils.conn;
   SqlCommand loginCmd = new SqlCommand("LogInUser", conn);
   loginCmd.CommandType = CommandType.StoredProcedure;

   User result = new User();

   try
   {
       conn.Open();
       loginCmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = usr.Username;
       loginCmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = usr.Password;
       loginCmd.Parameters.Add("@succeed", SqlDbType.Bit).Direction = ParameterDirection.Output;
       loginCmd.Parameters.Add("@not_exist_err", SqlDbType.Bit).Direction = ParameterDirection.Output;                
       SqlDataReader dr = loginCmd.ExecuteReader();              

       if (loginCmd.Parameters["@succeed"].Value != DBNull.Value){
          Console.WriteLine("User does not exist");
          SqlParameter notExistErr = loginCmd.Parameters["@not_exist_err"];

          if (notExistErr.Value != DBNull.Value){
             throw new NotExistException("The username or password is incorrect.", "Users");
          }
       }

       while (dr.Read()) {
           Console.WriteLine("Looping dr");
           result.Username = (string) dr["username"];
           result.Password = (string) dr["password"];
           result.FirstName = (string) dr["first_name"];
           result.MiddleName = (string) dr["middle_name"];
           result.LastName = (string) dr["last_name"];
           result.ManagerID = (int) dr["manager_id"];
           result.IsAdmin = (int) dr["is_admin"];

           return result;
        }
        Console.WriteLine("Done reading");
    }             
    finally {
       if (conn.State == ConnectionState.Open) conn.Close();
    }
    return result;
 }

Output parameters are only filled after you read the last row from the last result set in the procedure. So this part won't work:

SqlDataReader dr = loginCmd.ExecuteReader();              
if (loginCmd.Parameters["@succeed"].Value != DBNull.Value){

At this point loginCmd.Parameters["@succeed"].Value is not set: it will be set only after you've read the last row, and dr.Read() has returned false.

As a best practice, don't use output parameters in stored procedures that return rowsets.

You can check whether your query returns the result or is not using dr.HasRows, and it should look like...

SqlDataReader dr = loginCmd.ExecuteReader();              
if (!dr.HasRows)
{
    Console.WriteLine("User does not exist");
    SqlParameter notExistErr = loginCmd.Parameters["@not_exist_err"];
    if (notExistErr.Value != DBNull.Value)
    {
        throw new NotExistException("The username or password is incorrect.", "Users");
    }
}
else
{
    while (dr.Read())
    {
        Console.WriteLine("Looping dr");
        result.Username = (string) dr["@username"];
        result.Password = (string) dr["@password"];
        result.FirstName = (string) dr["@first_name"];
        result.MiddleName = (string) dr["@middle_name"];
        result.LastName = (string) dr["@last_name"];
        result.ManagerID = (int) dr["@manager_id"];
        result.IsAdmin = (int) dr["@is_admin"];
        return result;
    }
}

Something is strange -- I would expect your test to look like this:

declare @succeedResult bit
declare @existErr bit
exec LogInUser @username=N'admin', @password =N'admin', @succeed = @succeedResult, @not_exist_err = @existErr

But this brings up the simple answer.... are you calling your function with valid (ie exist) for username AND password?