How And Where To Use Sql Command ExecuteReader

Introduction

In my previous two articles I have explained about ExecuteNonQuery and ExecuteScalar. Where and how to use them in ADO.NET and now here I will explain about ExecuteReader.

When your stored procedure returns a set of rows then you need to hold the returned values in an object. Here you need DataSet or DataTable or a List to hold the returned values.

In this particular case its advisable to use ExecuteReader.

Behind The Scene

When you have written a select query in your procedure or function in sql and it returns a number of rows then you need to hold those list of data in a .net object. 

Basically what happened in the background this ExecuteReader keeps the connection to the database opened until you closes it. And each time this reader reads a different row and returns it back. Here you need to hold those values in a .net data structure lets say DataTabe/DataSet. 

You keep on adding one by one row in the DataTable/DataSet and once done dispose the Reader object.

Here is a simple example how I am getting the data from the database and binding those to a list using DataReader.

  internal SalesClaimList GetUserPointsForSalesClaim(Int32 userId)
  {
      Database db;
      try
      {
          SalesClaimList salesClaimList = null;
          SalesClaim salesClaim = null;

          salesClaimList = new SalesClaimList();
          salesClaimList.ClaimItems = new List();

          db = DatabaseFactory.CreateDatabase();
          DbCommand dbCommand =     db.GetStoredProcCommand(Constants.StoredProcedures.GetUserPointsForSalesClaim);
          db.AddInParameter(dbCommand, Constants.StoredProcedures.GetUserPointsForSalesClaimPameters.UserId, DbType.Int32, userId);
          
          using (IDataReader dataReader = db.ExecuteReader(dbCommand))
          {
              while (dataReader.Read())
              {
                  salesClaim = new SalesClaim();

                  salesClaim.UserTotalPoints = Convert.ToInt32(dataReader["ProgramPoints"]);
                  salesClaim.UserAvailablePoints = Convert.ToInt32(dataReader["PointChange"]);
                  salesClaim.UserSpentPoints = salesClaim.UserTotalPoints - salesClaim.UserAvailablePoints;
                  salesClaimList.ClaimItems.Add(salesClaim);
              }

              dataReader.Close();
          }

          return salesClaimList;
      }
      catch (Exception ex)
      {
          throw ex;
      }
      finally
      {
          db = null;
      }
  }

Why to use  ExecuteNonQuery  instead of using ExecuteReader and ExecuteScalar.

ExecuteReader needs a DataSet or a List to hold the return values, so it is unnecessary to declare a DataSet for updating/inserting/deleting something in the database. So its best practice to use ExecuteNonQuery  instead that need no DataSet to instantiate.

ExecuteScalar holds a single value that is returned from the stored procedure. This can be used in case of ExecutenonQuery where you are passing some return value from database by a select statement in the same procedure.

** The basic difference between "ExecuteScalar", "ExecuteReader" and "ExecuteNonQuery" is that the first two need at least a select statement in the query but the last one need not any select statement in the query.

Happy Coding...