SqlDataReader issues

Question

The code performs as needed when done like this.

SqlDataReader sqlRead = selectCommand.ExecuteReader(CommandBehavior.SingleRow);

if (sqlRead.Read())
{
    string text = sqlRead["USERNAME"].ToString();

    MessageBox.Show(text);
    connection.Close();
}

Whenever written without the if and just the variable declaration like this

string text = sqlRead["USERNAME"].ToString();

MessageBox.Show(text);
connection.Close();

I get this error:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: Invalid attempt to read when no data is present.

Why can't I simply assign the value to the variable? Is it because nothing was actually read into the reader?

Answer 1

That's just how DataReader is written. You must first call the Read method before you can get values from it.
See Retrieving Data Using a DataReader Microsoft docs:

You use the Read method of the DataReader object to obtain a row from the results of the query. You can access each column of the returned row by passing the name or ordinal reference of the column to the DataReader.

However, in your case, I would recommend not to use ExecuteReader() in the first place. Since you are only interested in one value, you should use ExecuteScalar instead, and make sure your SQL statement actually selects the value you want (if, for instance, you are selecting more than one column or more than one row, the ExecuteScalar will return the value of the first column in the first row, ignoring all other values that might be returned from the SQL statement).

var text = "";
var userName = selectCommand.ExecuteScalar();
if(userName != null && userName != DbNull.Value)
{
    text = userName.ToString();
}