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

SqlDataReader isn't a container, it's a cursor used to load data. You don't convert it to a List, you use it to load the rows, construct the objects and add them to a list. Or use an ORM or Dapper and have them do this for you — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 13, 2022 at 8:43
@MitchWheat that's not what was asked. If the application works with objects a DataTable won't help — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 13, 2022 at 8:52
The data reader results can't be converted to a strongly typed object without reflection. That's what microORMs like Dapper already do, along with optimizations that reduce the cost of reflection, eg caching types and PropertyInfo objects so they can be reused — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 13, 2022 at 8:56

Panagiotis Kanavos · Accepted Answer · 2022-05-13 08:57:17Z

4

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.

edited May 13, 2022 at 8:57

answered May 13, 2022 at 8:51

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

harsha_wije97 Over a year ago

Thank You so much for the answer. But there is a one problem of implementing the code. There are so many models, so that I'm using stored procedures to perform queries. In a situation like that how can I get results ?

Panagiotis Kanavos Over a year ago

You can use stored procedures with Dapper

Rene · Accepted Answer · 2022-05-13 08:35:27Z

0

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.

answered May 13, 2022 at 8:35

Rene

593 bronze badges

1 Comment

Panagiotis Kanavos Over a year ago

This doesn't answer the question. It's only the start of an actual answer

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related