IDataReader.Read() (wrong number of rows)

[Marlon B. Rabara]

Testing my DAL and the following only returns one row:

SqlConnection cnn = new SqlConnection("Persist Security
Info=False;Integrated Security=SSPI;database=Northwind;server=RXSERVER;");

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from Employees";
cmd.CommandType = CommandType.Text;
cmd.Connection = cnn;

cnn.Open();
SqlDataReader datareader = cmd.ExecuteReader(CommandBehavior.SingleRow);

while (datareader.Read())
{
Console.WriteLine(datareader.GetValue(0));
}

datareader.Close();
cnn.Close();

************************

I get one row and the next call to Read() evaluates to false. I watched the
profiler and the SQL statement is being called correctly. However, I am only
getting one row back and that is it. There are I believe a total of 10 recs.
Can anyone see what I'm doing wrong? Am I missing something in the
connection string for data readers to work properly?

 

[One Handed Man [ OHM ]]

Could this have something to do with it ?

SqlDataReader datareader = cmd.ExecuteReader(CommandBehavior.SingleRow);

 

[Marlon B. Rabara]

Okay...went back to the documentation again. Yes.... this seems to be it on
the money. I initially believed that the SingleRow option allowed me to
access the columns out of order and read data from the stream one row at a
time (as opposed to Sequential).

I took out the SingleRow command behavior and just left it at default. This
works just fine.

Thanks!