Retrieve data source data by using the DataReader
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.
I'm a software developer professionalizing in the .NET platform and iOs development. Here you can find my notes for Microsoft certifications.
Everyone who seeks, finds
- .NET (35)
- .NET 70-536 (35)
- Configuration, Diagnostic, Management and Installation Features (6)
- Globalization, Drawing and Text Manipulation (2)
- Improving the Security of a .NET Framework Application (5)
- Interoperability, Reflection and Mailing Functionality (3)
- Serialization and IO Functionality (7)
- Service Processes, Threading and Application Domains (2)
- System Types and Collections (6)
- .NET 70-536 (35)
- ADO.NET (24)
- ASP.NET (68)
- Application Architecture (1)
- ASP.NET 70-562 (44)
- PRO ASP.NET 70-564 (20)
- Design Patterns (10)
- Objective-C (4)
- Personal (4)
- Silverlight (6)
- SQL Server (43)
- 70-433 (27)
- 70-451 (16)
- WCF (8)
- Windows Azure (8)
- WPF (20)