Method that fetches rows from database and maps it to a Model class with SqlDataReader So, I have a project that has a lot of methods that look alike. 
In the below method as you can see it just fetches a bunch of rows from database and maps it to a model class. 

In this  case ParkingSlipDetails class. 
There are several such methods in the project that do the exact same thing only with a different set of data and a different model class. There is no complicated business logic here. Just pure fetching data and mapping it to a model.

Something like this:

  public CustomReturn &lt; List &lt; ParkingSlipDetails &gt;&gt; GetAllParkingSlips(int offset, int fetch)
    {
        SqlConnection sqlConnection = null;
        try
        {
            List &lt; ParkingSlipDetails &gt; ParkingSlipDetailsList = new List &lt; ParkingSlipDetails &gt; ();
            sqlConnection = (SqlConnection) sqlHelper.CreateConnection(); //sqlHelper is a nugget and not in my control
            sqlConnection.Open();
            SqlCommand cmd = new SqlCommand(Constants.GetAllParkingSlips, sqlConnection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add("@CreatedBy", SqlDbType.NVarChar).Value = gepservice.GetUserContext().UserId;
            cmd.Parameters.Add("@OffsetRows", SqlDbType.NVarChar).Value = offset;
            cmd.Parameters.Add("@FetchRows", SqlDbType.NVarChar).Value = fetch;
            var refCountdr = (RefCountingDataReader) sqlHelper.ExecuteReader(cmd);
            var sqlDr = (SqlDataReader) refCountdr.InnerReader;
            if (sqlDr != null)
            {
                while (sqlDr.Read())
                {
                    ParkingSlipDetails ParkingSlipDetails = new ParkingSlipDetails();
                    ParkingSlipDetails.Id = Convert.ToInt32(sqlDr[FileUploadConstants.Id]);
                    ParkingSlipDetails.FileName = Convert.ToString(sqlDr[FileUploadConstants.FileName]);
                    ParkingSlipDetails.FileUri = Convert.ToString(sqlDr[FileUploadConstants.FileURI]);
                    ParkingSlipDetails.PONumber = Convert.ToString(sqlDr[FileUploadConstants.DocumentNumber]);
                    ParkingSlipDetails.UploadDate = Convert.IsDBNull(sqlDr[FileUploadConstants.UploadDate]) ? DateTime.UtcNow: Convert.ToDateTime(sqlDr[FileUploadConstants.UploadDate]);
                    ParkingSlipDetails.FileStatus = (Status)(Convert.ToInt16(sqlDr[FileUploadConstants.FileStatus]));
                    ParkingSlipDetails.ReceiptNumber = Convert.ToString(sqlDr[FileUploadConstants.RecieptNumber].ToString());
                    ParkingSlipDetails.TotalRows = Convert.ToInt32(sqlDr[FileUploadConstants.TotalRows]);
                    ParkingSlipDetails.CreatedBy = gepservice.GetUserContext().UserId;
                    ParkingSlipDetailsList.Add(ParkingSlipDetails);
                }
            }
            return new CustomReturn &lt; List &lt; ParkingSlipDetails &gt;&gt; (ParkingSlipDetailsList);
        }
        catch(Exception ex)
        {
            LogError("ManageParkingSlipDataAccess", "GetAllParkingSlips", "Error", ex);
            return null
        }
        finally
        {
            if (sqlConnection != null)
            sqlConnection.Close();
        }
    }


I think there is a lot of scope for refactoring here since there is a lot of places where I would repeat :

sqlConnection = (SqlConnection)sqlHelper.CreateConnection(); 
sqlConnection.Open();


and 

var refCountdr = (RefCountingDataReader) sqlHelper.ExecuteReader(cmd);

var sqlDr = (SqlDataReader) refCountdr.InnerReader;
if (sqlDr != null) {
  while (sqlDr.Read())    
  {

  }
}


And I also think the looping is repetitive for each method. Each such method has a loop that maps object to cmd.Parameters

How do I separate the looping and mapping? Should I create an extension method on ParkingSlipDetails Class that takes SqlDataReader and returns an instance of ParkingSlipDetails? Even then I will be repeating reader creation and lopping in each model class.
To quote Mark Seemann from the following article Small methods are easy to troubleshoot


  Write small methods. How small? Small enough that any unhandled exception is easy to troubleshoot.


Break the function up into smaller more manageable chunks that focus on a single concern as much as possible. 

Yes, the net effect of this is a lot of additional functions. They are however easier to read and maintain.

Another benefit of the many small methods it that it would help identify areas that can be generalized into reusable services that help reduce repeated code, allowing it (the code) to be more DRY (Don't Repeat Yourself)

Lets start with getting the connection

private SqlConnection createConnection() {
    var connection = (SqlConnection) sqlHelper.CreateConnection(); //sqlHelper is a nugget and not in my
    connection.Open();
    return connection;
}


This can now be wrapped in a using block that will dispose of the connection once out of scope

using(SqlConnection connection = createConnection()) {

    //...
}


While there is nothing wrong with creating the command manually, I personal prefer to let the connection do it rather than newing one. Again wrapping in a using block, and populating.

//...

using(SqlCommand command = connection.CreateCommand()) {
    command.CommandText = Constants.GetAllParkingSlips;
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Parameters.Add("@CreatedBy", SqlDbType.NVarChar).Value = gepservice.GetUserContext().UserId;
    command.Parameters.Add("@OffsetRows", SqlDbType.NVarChar).Value = offset;
    command.Parameters.Add("@FetchRows", SqlDbType.NVarChar).Value = fetch;

    //...
}


That could have also been refactored into its own method, but in my opinion it would have ended up with too many arguments. 

This could be fixed with an aggregated value object.

public class ParkingSlipQuery {
    public int FetchRows { get; set; }
    public int OffsetRows  { get; set; }
    public string CreatedBy { get; set; }
}


for example

private SqlCommand createCommand(SqlConnection connection, ParkingSlipQuery query) {
    SqlCommand command = connection.CreateCommand();
    command.CommandText = Constants.GetAllParkingSlips;
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Parameters.Add("@CreatedBy", SqlDbType.NVarChar).Value = query.CreatedBy;
    command.Parameters.Add("@OffsetRows", SqlDbType.NVarChar).Value = query.OffsetRows;
    command.Parameters.Add("@FetchRows", SqlDbType.NVarChar).Value = query.FetchRows;
    return command;
}


and implemented

var query = new ParkingSlipQuery {
    CreatedBy = gepservice.GetUserContext().UserId,
    OffsetRows = offset,
    FetchRows = fetch
};
using(SqlCommand command = createCommand(connection, query)) {
    //...
}


The execution of the command and subsequent reader result however can be moved

private SqlDataReader executeReader(SqlCommand command) {
    var refCountdr = (RefCountingDataReader) sqlHelper.ExecuteReader(cmd);
    return (SqlDataReader) refCountdr.InnerReader;
}


As you have realized by now, you should know what is coming next about building the model to populate the collection.

private ParkingSlipDetails getDetails(SqlDataReader reader) {
    ParkingSlipDetails details = new ParkingSlipDetails();
    details.Id = Convert.ToInt32(reader[FileUploadConstants.Id]);
    details.FileName = Convert.ToString(reader[FileUploadConstants.FileName]);
    details.FileUri = Convert.ToString(reader[FileUploadConstants.FileURI]);
    details.PONumber = Convert.ToString(reader[FileUploadConstants.DocumentNumber]);
    details.UploadDate = Convert.IsDBNull(reader[FileUploadConstants.UploadDate]) ? DateTime.UtcNow: Convert.ToDateTime(reader[FileUploadConstants.UploadDate]);
    details.FileStatus = (Status)(Convert.ToInt16(reader[FileUploadConstants.FileStatus]));
    details.ReceiptNumber = Convert.ToString(reader[FileUploadConstants.RecieptNumber].ToString());
    details.TotalRows = Convert.ToInt32(reader[FileUploadConstants.TotalRows]);
    details.CreatedBy = gepservice.GetUserContext().UserId;

    return details;
}

private List&lt;ParkingSlipDetails&gt; getDetailsList(SqlDataReader reader, string userId) {
    List&lt;ParkingSlipDetails&gt; detailsList = new List&lt;ParkingSlipDetails&gt;();
    if (reader != null) {
        while (reader.Read()) {
            ParkingSlipDetails details = getDetails(reader);
            details.CreatedBy = userId;
            detailsList.Add(details);
        }
    }
    return detailsList;
}


Finally, to avoid null reference errors, the method should try to avoid return null. An empty collection is safer to check than a null collection.

This results in refactor looking like

public CustomReturn&lt;List&lt;ParkingSlipDetails&gt;&gt; GetAllParkingSlips(int offset, int fetch) {
    List&lt;ParkingSlipDetails&gt; detailsList = new List&lt;ParkingSlipDetails&gt;();
    try {
        using(SqlConnection connection = createConnection()) {
            string userId = gepservice.GetUserContext().UserId;
            var query = new ParkingSlipQuery {
                CreatedBy = userId,
                OffsetRows = offset,
                FetchRows = fetch
            };
            using(SqlCommand command = createCommand(collection, query)) {
                using(SqlDataReader reader = executeReader(command)) {
                    detailsList = getDetailsList(reader, userId);
                }
            }
        }
    } catch(Exception ex) {
        LogError("ManageParkingSlipDataAccess", "GetAllParkingSlips", "Error", ex);
        detailsList = new List&lt;ParkingSlipDetails&gt;();
    }
    return new CustomReturn&lt;List&lt;ParkingSlipDetails&gt;&gt;(detailsList);
}


The using block will handle the closing and disposal of the connect and the other disposable members.

If following explicit dependency principle, it could be refactored further down by delegating the argument values to the caller.

public CustomReturn&lt;List&lt;ParkingSlipDetails&gt;&gt; GetAllParkingSlips(ParkingSlipQuery query) {
    List&lt;ParkingSlipDetails&gt; detailsList = new List&lt;ParkingSlipDetails&gt;();
    try {
        using(SqlConnection connection = createConnection()) {                
            using(SqlCommand command = createCommand(collection, query)) {
                using(SqlDataReader reader = executeReader(command)) {
                    detailsList = getDetailsList(reader, query.CreatedBy);
                }
            }
        }
    } catch(Exception ex) {
        LogError("ManageParkingSlipDataAccess", "GetAllParkingSlips", "Error", ex);
        detailsList = new List&lt;ParkingSlipDetails&gt;();
    }
    return new CustomReturn&lt;List&lt;ParkingSlipDetails&gt;&gt;(detailsList);
}


with the following support methods

private SqlConnection createConnection() {
    var connection = (SqlConnection) sqlHelper.CreateConnection(); //sqlHelper is a nugget and not in my
    connection.Open();
    return connection;
}

private SqlCommand createCommand(SqlConnection connection, ParkingSlipQuery query) {
    SqlCommand command = connection.CreateCommand();
    command.CommandText = Constants.GetAllParkingSlips;
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Parameters.Add("@CreatedBy", SqlDbType.NVarChar).Value = query.CreatedBy;
    command.Parameters.Add("@OffsetRows", SqlDbType.NVarChar).Value = query.OffsetRows;
    command.Parameters.Add("@FetchRows", SqlDbType.NVarChar).Value = query.FetchRows;
    return command;
}

private SqlDataReader executeReader(SqlCommand command) {
    var refCountdr = (RefCountingDataReader) sqlHelper.ExecuteReader(cmd);
    return (SqlDataReader) refCountdr.InnerReader;
}

private List&lt;ParkingSlipDetails&gt; getDetailsList(SqlDataReader reader, string userId) {
    List&lt;ParkingSlipDetails&gt; detailsList = new List&lt;ParkingSlipDetails&gt;();
    if (reader != null) {
        while (reader.Read()) {
            ParkingSlipDetails details = getDetails(reader);
            details.CreatedBy = userId;
            detailsList.Add(details);
        }
    }
    return detailsList;
}

private ParkingSlipDetails getDetails(SqlDataReader reader) {
    ParkingSlipDetails details = new ParkingSlipDetails();

    details.Id = Convert.ToInt32(reader[FileUploadConstants.Id]);
    details.FileName = Convert.ToString(reader[FileUploadConstants.FileName]);
    details.FileUri = Convert.ToString(reader[FileUploadConstants.FileURI]);
    details.PONumber = Convert.ToString(reader[FileUploadConstants.DocumentNumber]);
    details.UploadDate = Convert.IsDBNull(reader[FileUploadConstants.UploadDate]) ? DateTime.UtcNow: Convert.ToDateTime(reader[FileUploadConstants.UploadDate]);
    details.FileStatus = (Status)(Convert.ToInt16(reader[FileUploadConstants.FileStatus]));
    details.ReceiptNumber = Convert.ToString(reader[FileUploadConstants.RecieptNumber].ToString());
    details.TotalRows = Convert.ToInt32(reader[FileUploadConstants.TotalRows]);

    return details;
}