SELECT query returning exception in C# method

Question

I'm newbie in C# and Npgsql and I'm trying to make a search in a certain table but I'm getting an exception.

public User Login(Account c)
{
    User usr = new User();
    using (NpgsqlConnection con = new NpgsqlConnection(strConnection))
    {
        try
        {
            con.Open();
            NpgsqlCommand command = new NpgsqlCommand();
            command.Connection = con;
            command.CommandText = "SELECT name FROM public.user WHERE c_id IN (SELECT id FROM public.account WHERE email=@Email AND password=@Password)";
            command.Parameters.AddWithValue("Email", c.Email);
            command.Parameters.AddWithValue("Password", c.Password);
            NpgsqlDataReader dr = command.ExecuteReader();

            if (dr.HasRows)
            {
                usr.Name = dr["name"].ToString();
            }

        }
        catch (Exception ex)
        {
            throw ex;
        }
    }    
    return usr;
}

Bellow the exception:

System.InvalidOperationException: 'No row is available'

I did the same thing but using PgAdmin4, and i've got one line. Exactly what i want to get.

SELECT name FROM public.user WHERE c_id IN (SELECT id FROM public.account WHERE email='[email protected]' AND password='user05');

How do i fix this?

Answer 1

You have to call while dr.Read(); like this:

if (dr.HasRows)
{
    dr.Read();
    usr.Name = dr["name"].ToString();
}