Where and How to use DataReader

by top54u.com 15 May, 2008

Inside ADO.Net | ASP .NET Tutorials

ASP.Net Error and Exception Handling for ADO.Net

Catching and Handling ASP.Net Errors and Exception in ADO.Net

ADO.Net Data Access Model for ASP.Net-Part 1

ADO.Net Performance in ASP.Net

ASP.Net Random Numbers

AJAX GridView Paging and Sorting using C# in ASP.Net

ASP.Net Gridview Edit Update Cancel Commands

Spotlight.....

How to use DataReader?

Here we will use SqlDataReader to learn the use of Data Reader in data access code of ASP.Net with C#. For using SqlDataReader object you have to code little bit more than the code for DataSet. You also have to work according to the following guidelines to make your code managed and in-control:

The connection used for retrieving the data with SqlDataReader remains open until the data reader is fetching records from the sql database and you cannot close or use the connection for accessing the data for other purposes.
You have to close the connection explicitly as soon as possible after retrieving the data.
A single connection does not allow the multiple data reader working at a single time. So you can use only 1 SqlDataReader working at a time for a single open connection.
For closing the Sql Connection immediately after usage you can pass CommandBehavior.CloseConnection enumerated value to the ExecuteReader method of SqlCommand object. This enumerated value enables the SqlCommand object to close the sql connection immediately after the data retrieval.
You have to use the type conversion before passing to variables according the type of column value read by SqlDataReader.

C# Code to Access Data with SqlDataReader

/// <summary>
/// connection string retrieved from
/// web.config connectionstring section
/// </summary>
string connectionString = ConfigurationManager.ConnectionStrings[ "NorthwindConnectionString" ].ConnectionString;

SqlConnection sqlConn = new SqlConnection(connectionString);

sqlConn.Open();

SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);

SqlDataReader sqlReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

Where to use Data Reader (SqlDataReader)

You can use the data reader obtained by ExecuteReader method of SqlCommand:

When you want reduce the memory usage of web application.
When you are dealing with large amount of data that cannot be cached at a single request.
When you are dealing with BLOBS.
When you are binding data retrieved to any server control that supports IEnumerable generic interface.
When you need optimized performance and speed for your ASP.Net web application.
When you want to reduce the memory overhead of your web application.