c# sqldatareader no data

Question

I have little problem here. I try to use SqlDataReader to read data from my database. Code is like this:

        internal static User GetUser(string login)
    {
        SqlConnection conn = new SqlConnection(DataBaseUtility.ConnectionString);

        conn.Open();

        string sql = "SELECT * FROM BookMenagerDB.dbo.users";
        //where UserLogin like @login
        try
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@login", login);
            User user = new User();
            SqlDataReader sqlDataReader = cmd.ExecuteReader();
            user.Id = (int)sqlDataReader["UserId"];
            user.Login = sqlDataReader["UserLogin"].ToString();
            user.PasswordHash = sqlDataReader["UserPassword"].ToString();
            user.Salt = (byte[])sqlDataReader["UserPasswordSalt"];
            return user;
        }

        catch (SqlException ex)
        {
            MessageBox.Show("GetUser fail");
        }
        conn.Close();
        return new User();
    }

I try sql query and I get data but line "user.Id = (int)sqlDataReader["UserId"];" show me there is no data in sqlDataReader. I have no idea what the problem is.

enter image description here

is it like `//where UserLogin like @login` in your real code? or just comment it in here? — Ashkan Mobayen Khiabani, Oct 07 '18 at 21:41
`sqlDataReader.Read()`. And don't forget to check the return value. And read up on how to use `using`. — Jeroen Mostert, Oct 07 '18 at 21:44
What do you expect to happen if `BookMenagerDB.dbo.users` contains many users? — Dour High Arch, Oct 07 '18 at 21:47

score 3 · Answer 1 · edited Oct 07 '18 at 21:48

You need to call the Read() function on your SqlDataReader to advance the reader to the first or next record from the database. Also you should use disposing to release connection and resources.

public User GetUser(string login)
{
    User user = new User();
    const string sql = "SELECT * FROM BookMenagerDB.dbo.users";
    using (SqlConnection connection = new SqlConnection(DataBaseUtility.ConnectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        connection.Open();
        command.Parameters.AddWithValue("@login", login);
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.Read())
            {
                user.Id = (int)reader["UserId"];
                user.Login = reader["UserLogin"].ToString();
                user.PasswordHash = reader["UserPassword"].ToString();
                user.Salt = (byte[])reader["UserPasswordSalt"];
            }
        }
    }
    return user;
}

score 0 · Answer 2 · answered Oct 07 '18 at 21:44

After ExecuteReader() you should Read() to get records:

   User user = new User();
   SqlDataReader sqlDataReader = cmd.ExecuteReader();
   if(sqlDataReader.Read())
   {
       user.Id = (int)sqlDataReader["UserId"];
       user.Login = sqlDataReader["UserLogin"].ToString();
       user.PasswordHash = sqlDataReader["UserPassword"].ToString();
       user.Salt = (byte[])sqlDataReader["UserPasswordSalt"];
    }

c# sqldatareader no data

2 Answers2