About datareader.read()

[Osamede.Zhang]

I have some code like this:
SqlCommand cmd = new SqlCommand("get_storeid_byuser", cn);

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@username", SqlDbType.VarChar, 50).Value =
"osamede";
cn.Open();
SqlDataReader reader = cmd.ExecuteReader();

The StoredProcedure "get_storeid_byuser" should return a int.

I just want to know Why I have to call reader.read() before to read
data:
reader.read();
int value=(int)reader["id"]
But not
int value=(int)reader["id"];

 

[Mark Rae [MVP]]

The StoredProcedure "get_storeid_byuser" should return a int.

I just want to know Why I have to call reader.read() before to read
data:
reader.read();
int value=(int)reader["id"]
But not
int value=(int)reader["id"];

Because that's just the way it works...

With a DataReader, you read each record one at a time, and reading one
record advances the reader to the next etc...

However, when a DataReader is first opened, it hasn't fetched any records
yet, although it does know if the underlying query which was used to
populate it has returned any records or not - you inspect this in the
..HasRows property.

So, therefore you need to call the Read() method to advance to the first
record.

 

[bruce barker]

this is because sqlserver returns resultsets. each resultset contains n
rows of n columns of data. you start at first resultset before the first
row. the reader is actually at the resultset where it can read meta info
about the rows.

remember the data is all coming back from a network stream (think of it
as reading a file, and you start at byte 0). after the last resultset
comes the return value of the proc.

-- bruce (sqlwork.com)

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

I have some code like this:
SqlCommand cmd = new SqlCommand("get_storeid_byuser", cn);

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@username", SqlDbType.VarChar, 50).Value =
"osamede";
cn.Open();
SqlDataReader reader = cmd.ExecuteReader();

The StoredProcedure "get_storeid_byuser" should return a int.

I just want to know Why I have to call reader.read() before to read
data:
reader.read();
int value=(int)reader["id"]
But not
int value=(int)reader["id"];

The DataReader does not start by reading the first record automatically.
You use the Read method both for reading and checking if there are any
more data to read.

You can compare this to the Recordset object in ADO, which automatically
reads the first record. This leaves the object in two possible states,
and you have to check the EOF property to see if the object is usable or
not. There are thousands of questions in forums about the error message
that people get because they are trying to read from a recordset that
doesn't contain any data.

Also, the method MoveNext is used to read the next record, and you check
the EOF property to see if there were any more data. It's quite common
to forget the MoveNext call in a loop, which will cause the loop to read
the first record over and over a billion times, either using 100% for
several minutes until the script times out, or creating a result that
fills the memory. There are thousand of questions in forums about this
too...

Compared to that, the Read method is quite convenient. It's MoveNext and
EOF rolled into one, so you can't forget to step forward. Also the
DataReader is always in the same state from the start. If you forget to
call Read, you will always get the same error message, you don't get
different results for different data.