DataReader classes provide a forward-only, read-only cursor over your returned data. They are particularly useful when the need of fast querying arises. Remember the two keywords: forward-only, which means that once you’ve retrieved your data, you cannot turn back on the middle of an enumeration, and read-only, which means that the DataReader only supports selection of data.
Working with a DataReader is by all terms easy. Let’s see a quick example:
SqlConnection theConnection = new SqlConnection(@”Data Source=…”);
SqlCommand theCommand = new SqlCommand(“SELECT * FROM X”, theConnection);
theCommand.CommandType = CommandType.Text;
SqlDatReader theReader = theCommand.ExecuteReader();
for(int i=0; i<theReader.FieldCount; i++)
The code above needs little explanation. As you can see, we’ve acquired an instance of SqlDataReader by calling the SqlCommand’s ExecuteReader method. This method has one overload, which accepts a member of the CommandBehavior enumeration (topic of the next post). Then I created a loop for iterating over the columns of the result set. The FieldCount property gets the number of columns in the active result set (yes, MARS is available). Then I called the least-specific GetValue method of the DataReader. I could have called a whole lot of methods, depending upon what type I’d like to return, for example GetString, GetInt32, GetDouble… Each of these methods accept the number of column in which the data resides.
DataReaders require an active connection to do their work, and they have exclusive rights on that connection, so it’s very important to close them (call the Close method) after you’ve finished iterating. This is particularly important, because any possible return value would only be available when you’ve closed the DataReader.
When a DataReader returns multiple result sets, you should call the NextResult method to iterate over them. NextResult returns true as long as there’s another result set.