Introduction

In ASP.Net Performance tuning is the most important task while working ADO.Net Data Access Components. When you write a code to access data from any data source using ADO.Net Data Providers, the ASP.Net server controls i.e. Data-Bound Controls you use consume lots of server memory to store the data retrieved in the memory. So, there are number of scenarios for data access and each one is handled with its appropriate performance and measured solutions according to the ADO.Net Data Access code.

ADO.Net Scenarios in ASP.Net Data Access

Following are the functional scenarios for data access code in ASP.Net web applications:

Checking the Item existence in Data Source:
A single Boolean return type result is sufficient to check whether or not a record with a particular primary key exists in the data source.

Fetching Multiple Records:
Retrieving and iterating over the multiple row set of records fetched from the database.

Fetching a Single Record:
Retrieving a single row associated with a specified primary key.

Retrieving a Single Item:
Retrieving a single item from a particular row of specified relation.

Data Access Options in ADO.Net

There are number of options in data access components of ASP.Net well suited for the different types of scenarios to access data from the database. Following are the options:

• SqlDataReader
  
  
• SqlDataAdapter
  
  
• SqlCommand
  
  
• XmlReader
  
  
• DataSet
  
  
• DataTable

Above SqlDataReader, SqlDataAdapter and SqlCommand are the classes working for SqlClient provider, similarly different .Net data providers have their own associated provider classes e.g. OleDbConnection, OleDbCommand and so on. .Net Data Provider with prefixes such as OleDb, Odbc, Oracle, Sql etc are objects for their corresponding data providers.

DataSet is designed for storing the DataTable in-memory database records. DataSet is a collection of DataTable. DataAdapter of any .Net Data Provider can be used to fill the DataSet that in turn can used to bind the data with data-bound controls. On the other hand SqlDataReader or any .Net Data provider specific DataReader works as a ferry between data layer and .Net web application to fetch a single row each time.