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.

Answer 2

while (query.Read())
{
  Console.WriteLine($"First column {query[0]}");
  Console.WriteLine($"Named column {query["put a columnname here"]}");
}

Read() will give you the first and next rows.

Answer 3

var dt=new DataTable();
dt.Load(myDataReader);
list<DataRow> dr=dt.AsEnumerable().ToList();

OR

 public DataReaderToListModel()  
        {  
            StudentList = new ObservableCollection<StudentModel>();  
            LoadData();  
        }  

public ObservableCollection<StudentModel> StudentList { get; set; }

 #region Varible declarations  
        private SqlConnection _sqlConnection;  
        private SqlCommand _sqlCommand;  
        private SqlDataReader _sqlDataReader;  
        private string _strsqlcommand;   
 #endregion 

  #region Method for Getting the data from Database  
        public void LoadData()  
        {  
            try  
            {  
            // Connection Strings 
            // Connecting to DB 
             _sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString);  
                if (_sqlConnection.State != ConnectionState.Open)  
                    _sqlConnection.Open();  
                _strsqlcommand = "Your Query";
                _sqlDataReader = _sqlCommand.ExecuteReader();  
                var studentmodellist = new ObservableCollection<StudentModel>();  
  
                while (_sqlDataReader.Read())  
                {  
                    var studentModel = new StudentModel  
                    {  
                        StudentId  = _sqlDataReader.GetInt32(_sqlDataReader.GetOrdinal("StudentId ")),  
                        Name= _sqlDataReader.GetString(_sqlDataReader.GetOrdinal("Name")),  
                        Age= _sqlDataReader.GetInt32(_sqlDataReader.GetOrdinal("Age")),  
                        Major = _sqlDataReader.GetString(_sqlDataReader.GetOrdinal("Major"))  
                    };  
                    studentmodellist.Add(studentModel);  
                }  
  
                StudentList = studentmodellist;  
                _sqlConnection.Close();  
            }  
            catch (Exception exception)  
            {  
                MessageBox.Show("DataLoading Failed beacause of following  Reason \n" + exception.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);  
            }  
        }   
        #endregion  

Please refer SO Thread for more information