How to Easily Convert Datareader to List≪T≫

How can I easily convert DataReader to List T ?

I have seen systems that use Reflection and attributes on Properties or fields to maps DataReaders to objects. (A bit like what LinqToSql does.) They save a bit of typing and may reduce the number of errors when coding for DBNull etc. Once you cache the generated code they can be faster then most hand written code as well, so do consider the “high road” if you are doing this a lot.

See "A Defense of Reflection in .NET" for one example of this.

You can then write code like

class CustomerDTO  
{
    [Field("id")]
    public int? CustomerId;

    [Field("name")]
    public string CustomerName;
}

...

using (DataReader reader = ...)
{    
   List<CustomerDTO> customers = reader.AutoMap<CustomerDTO>()
                                    .ToList();
}

(AutoMap(), is an extension method)

---

@Stilgar, thanks for a great comment

If are able to you are likely to be better of using NHibernate, EF or Linq to Sql, etc However on old project (or for other (sometimes valid) reasons, e.g. “not invented here”, “love of stored procs” etc) It is not always possible to use a ORM, so a lighter weight system can be useful to have “up your sleeves”

If you every needed too write lots of IDataReader loops, you will see the benefit of reducing the coding (and errors) without having to change the architecture of the system you are working on. That is not to say it’s a good architecture to start with..

I am assuming that CustomerDTO will not get out of the data access layer and composite objects etc will be built up by the data access layer using the DTO objects.

---

A few years after I wrote this answer Dapper entered the world of .NET, it is likely to be a very good starting point for writing your onw AutoMapper, maybe it will completely remove the need for you to do so.

Convert to List from DataReader in .net

Well, in your case, you need a class that can hold your columns:

public class MyClass
{
   public int Id { get; set; }
   public int TypeId { get; set; }
   public bool AllowedSMS { get; set; }
   public string TimeSpan { get; set; }  
   public decimal Price { get; set; }
   public string TypeName { get; set; } 
}

Of course, since you didn't define what datatype those columns are, this is just a guess - you might need to adapt to your concrete needs.

And now, you need to iterate over the SqlDataReader and fetch the values from the data reader and populate your newly created class:

using (SqlDataReader dr = cmd.ExecuteReader())
{
   List<MyClass> results = new List<MyClass>();

   while(dr.Read())
   {
       MyClass newItem = new MyClass();

       newItem.Id = dr.GetInt32(0);  
       newItem.TypeId = dr.GetInt32(1);  
       newItem.AllowedSMS = dr.GetBoolean(2);  
       newItem.TimeSpan = dr.GetString(3);
       newItem.Price = dr.GetDecimal(4);
       newItem.TypeName = dr.GetString(5);

       results.Add(newItem);
   }
}

// return the results here, or bind them to a gridview or something....

How to put values from DataReader into List T ?

SqlDataReader cannot be accessed in the way you have described as it does not implement IEnumerable. Instead you need to access each field individually:

SqlDataReader puanoku = cmd2.ExecuteReader();
List<int> puann = new List<int>();
while (puanoku.Read())
{
    for (int i = 0; i < puanoku.FieldCount; i++)
    {
        if (puanoku.GetFieldType(i) == typeof(int))
        {
            // Do something here
        }
    }
}

If you've only selected one column and are certain that it is an int then you can simplify the code like so:

SqlDataReader puanoku = cmd2.ExecuteReader();
List<int> puann = new List<int>();
while (puanoku.Read())
{
    int value = puanoku.GetInt32(1);

    // Do something with the int here...
}

This article may be of some use.

How to convert SqlDataReader result to generic list List T

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.

Returning Data Rows to List string with SqlDataReader

The best option for you to do this task is DataTable, But you don't want to use it. So, the net option will be, Create a class based on the query-output then use a List<objectOftheClass>. But in your case, the Input query will be changed all times so a common class will not be meaningful Since you are trying to make it generic. So the option you can follow is List<List<string>> or List<List<object>>. As per this the method signature will be like the following:

public static List<object[]> loadSQL(string query, string connectString)
{
    List<object[]> dataList = new List<object[]>();

    using (SqlConnection connection = new SqlConnection(connectString))
    {
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    object[] tempRow = new object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        tempRow[i] = reader[i];
                    }
                    dataList.Add(tempRow);
                }
            }
        }
    }
    return dataList;
}

Why List<object>? why not `List?:

The reader will give you the column data as the same type of column in the table. If it is object then you need not convert it every time.

** Note:-** Change String to string for the arguments in the method signature. You can find a reason here

Is there any simple way to populate list model from datareader?

You can use third party library like Dapper, that is a simple object mapper for .Net.

You can check a basic sample of usage below:

public class Dog
{
    public string Name { get; set; }
    public float? Weight { get; set; }    
}

var dog = connection.Query<Dog>("SOME QUERY", new { Name = "DOGNAME" });

---

Related Topics