Click here to Skip to main content
15,867,834 members
Articles / All Topics

DataSet vs. DataReader

Rate me:
Please Sign up or sign in to vote.
4.32/5 (22 votes)
12 Jul 2012CPOL2 min read 131.9K   19   8
A comparison of the two.

Best Practices developing applications: Part 1

There are many reasons why we follow best practices. My own thoughts have changed over the course of my career. Early in my career as a junior developer I strictly followed the patterns and practices of my seniors and leads, and I assumed that their advice would be better than my own.

But with the passage of time my perceptions started to change because of research. As we have to research when our application consumes lot of time in executing and also when its scalability becomes a question. Before doing so I always used DataSet to get the data from the database. Whether it is for large application or too small, but then I found something was doing wrong.

DataSet is a collection of in memory tables and datareader provides the ability to expose the data from database.

Both are very widely used in asp.net applications to get/fetch the data from the database. But for a scalable, fast, and reliable application one has to know the best practices in developing application.

DataSet

We should use when the application is:

  • Windows application
  • Not too large data
  • Returning multiple tables
  • If, to be serialized
  • Disconnected architecture
  • To return from a WCF  service
  • To send across layers
  • Caching the data
  • Do not need to open or close connection

Code snippet using dataset:

C#
public DataSet GetRecord(Guid id, string procedureName)
{
    DataSet resultSet = new DataSet();
    SqlConnection connection = new SqlConnection(System.Configuration.
    ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    SqlCommand command = new SqlCommand(procedureName, connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters["pID"].Value = id.ToString();
    IDataAdapter adapter = new SqlDataAdapter(command);
    try
    {
        adapter.Fill(resultSet);
    }
    catch (Exception ex)
    {
        throw new PerformanceException(ex.Message, ex.InnerException);
    }
    return resultSet;
}

DataReader

DataReader is a stream which is readonly and forward only. It fetches the record from databse and stores in the network buffer and gives whenever requests. DataReader releasese the records as query executes and do not wait for the entire query to execute. Therefore it is very fast as compare to the dataset. It releases only when read method is called.

Its usages:

  • Web application
  • Large data
  • Returning multiple tables
  • For Fast data access
  • Needs explicitly closed
  • Output parameter value will only available after close
  • returns only a row after read

Code sample for DataReader:

C#
 public SqlDataReader GetRecord(Guid id, string procedureName)
{

    SqlDataReader resultReader = null;
    SqlConnection connection = new SqlConnection(
      ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    SqlCommand command = new SqlCommand(procedureName, connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters["pID"].Value = id.ToString();
    try
    {
        connection.Open();
        resultReader = command.ExecuteReader(CommandBehavior.CloseConnection);
    }
    catch (Exception ex)
    {
        if (resultReader != null || connection.State == ConnectionState.Open)
        {
            resultReader.Close();
            connection.Close();
        }
        throw new PerformanceException(ex.Message, ex.InnerException);
    }

    return resultReader;
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
My name is Muhammad Hussain. I'm a Software Engineer.
blog

Comments and Discussions

 
QuestionBetter performance and supports sorting Pin
Pittsburger29-Oct-16 0:16
professionalPittsburger29-Oct-16 0:16 
SuggestionGood Explanation but... Pin
ZahidAshiq20-Jan-15 1:45
ZahidAshiq20-Jan-15 1:45 
QuestionDataset big advantage versus datareader Pin
Member 1123858621-Nov-14 4:31
Member 1123858621-Nov-14 4:31 
AnswerRe: Dataset big advantage versus datareader Pin
enter faker3-Jan-17 14:30
enter faker3-Jan-17 14:30 
GeneralMy vote of 3 Pin
Prashant Singh Thakur30-Jun-14 2:39
Prashant Singh Thakur30-Jun-14 2:39 
GeneralMy vote of 3 Pin
mohitsharma77919-Jul-13 1:25
mohitsharma77919-Jul-13 1:25 
Could have explained bit more in advance.
GeneralMy vote of 3 Pin
Maria Daniel Deepak15-Mar-13 16:25
Maria Daniel Deepak15-Mar-13 16:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.