How to convert SqlDataReader result to generic list List<T>

Question

I'm trying to fetch records form Azure database using Ado.net I used SqlDataReader class for that. Even though the data fetch is successful, I don't know how to convert it into a generic list.

protected List<T> GetList<T>()
        {
            try
            {
                using (var query = ExecuteReader())
                {
                         // What Goes Here ?
                }

            }
            finally
            {
                if (_sqlCommand.Connection.State == ConnectionState.Open)
                {
                    _sqlCommand.Connection.Close();
                }
            }
        }

ExecuteReader method,

protected SqlDataReader ExecuteReader()
        {
            if (_sqlCommand.Connection.State != ConnectionState.Open)
            {
                _sqlCommand.Connection.Open();
            }

            return _sqlCommand.ExecuteReader();
        }

The Data Model,

public class Student
    {
        [EntityKey]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string Major { get; set; }

    }

NOTE: I would like to know if there any other easy ways as well

Answer 1

SqlDataReader isn't a container, it's a cursor used to load data. It can't be converted to any container type. The application code must use it to load the results and then construct the objects and place them in a list. This is described in the ADO.NET docs, eg in Retrieving data using a DataReader:

    var list=new List<Student>();
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            var student=new Student();
            student.Id=reader.GetInt32(0);
            student.Name = reader.GetString(1));
            ...
        }
    }
    else
    {
        Console.WriteLine("No rows found.");
    }

That's a lot of boilerplate, which is why ORMs like Entity Framework or micro-ORMs like Dapper are used to execute queries and map the results to objects.

Using Dapper, all this code can be replaced with :

var sql="Select * from Students where Major=@major";
var students=connection.Query<Student>(sql,new {major="Computer Science"});

Dapper will create a parameterized query with the @major parameter, execute it, construct Student objects from the results and return them as an IEnumerable<Student>. It even takes care of opening and disposing the connection.

Dapper works by using Reflection to identify a type's properties, use their names to load the correct fields and assign them to the objects it creates.