Reading Data with the SqlDataReader

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.

Creating a SqlDataReader Object

Getting an instance of the SqlDataReader is little different than the way you instantiate other ADO.NET objects. You must call ExecuteReader on the command object, like this:

SqlDataReader rdr = cmd.ExecuteReader();

The ExecuteReader method of the SqlCommand object, returns the SqlDataReader instance. Creating the SqlDataReader with a new operator doesn't do anything for you. As you learned in previous tutorials, the SqlCommand object references the connection and the SQL statement necessary for the SqlDataReader to obtain the data.

Reading Data

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.

while (rdr.Read())
{
string contact = (string)rdr["ContactName"];
string company = (string)rdr["CompanyName"];
string city = (string)rdr["City"];
Console.Write("{0,-25}", contact);
Console.Write("{0,-20}", city);
Console.Write("{0,-25}", company);
Console.WriteLine();
}

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.

Share And Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.

Keywords: Reading Data with the SqlDataReader, vb net sqldatareader, asp net sqldatareader, asp net data, vb net data,sqldatareader c#, sqldatareader net, sqldatareader example, using sqldatareader, msdn sqldatareader, sqldatareader count, sqldatareader close, sqldatareader getstring, data net.

HTML Quizes

XML Quizes