An SqlDataReader is the type that is good for reading data in the most efficient manner. You can't use it for writing data. SqlDataReaders are often described as the fast-forward firehose-like streams of the data.

The forward only design of the SqlDataReader is what enables it to be fast enough. It doesn't have the overhead associated with traversing of the data or writing it back to the data source. Therefore, if your only requirement for the group of data is for reading one time and you want a fastest method possible, the SqlDataReader is a best choice. Also, if the amount of data you need to read is larger than what you would prefer to hold in the memory beyond an single call, then the streaming behavior of the SqlDataReader would be a good and best choice.

The SqlDataReader returns data via the sequential stream. To read this data, you must pull data from the table in row-by-row. Once the row has been read, the previous row is no longer available. To read that row again, you would have to create the new instance of the SqlDataReader and read through the data stream again.

The typical method of reading from the data stream returned by the SqlDataReader is to iterate through each row with the help of while loop.

Notice the call to Read on the SqlDataReader, rdr, in the while loop condition in the example code shown above. The return value of Read is the type bool and returns true as long as there are more records to read. After the last record in the data stream has been read, Read returns the false value.

In previous Tutorial, we extracted the first column from the row by using a SqlDataReader indexer(rdr[0]). You can extract each column of the row with a numeric index like this, but it is not very readable. The example above uses a string indexer, where the string is the column name from the SQL query the table column name if you used an asterisk, *. String indexers are much more readable, making the code easier to maintain.