Convert Datatable to generic IEnumerable<T>

Question

I am trying to convert from DataTable to IEnumerable<T>. But what I get is IEnumerable<DataRow>:

using (var con = new SqlConnection(Connection.ToString()))
{
    con.Open();

    using (var cmd = con.CreateCommand())
    {
        cmd.CommandTimeout = int.MaxValue;
        cmd.CommandText = sqlCommand;

        var reader = cmd.ExecuteReader();
        DataTable tbl = new DataTable();
        tbl.Load(reader);

        var res = tbl.AsEnumerable().ToList();  // IEnumerable<DataRow>:
    }
}

What I would like to do is:

protected async Task<IEnumerable<T>> QuerySqlCmdReadRows<T>(string sqlCommand)
{
    using (var con = new SqlConnection(Connection.ToString()))
    {
        con.Open();

        using (var cmd = con.CreateCommand())
        {
            cmd.CommandTimeout = int.MaxValue;
            cmd.CommandText = sqlCommand;

            var reader = cmd.ExecuteReader();
            DataTable tbl = new DataTable();
            tbl.Load(reader);

            return tbl.AsEnumerable().ToList(); 
        }
    }
}

Is it possible to get IEnumerable<T> like I can do in Entity Framework?

var studentList = ctx.SqlQuery("Select * from Students")
                     .ToList<Student>();

Answer 1

You could create an extension method that convert it for you. Given you have properties on the query that matches the properties on your generic T type, you could use reflection to perform it! For sample (see the comments on the code):

public static class DataTableExtensions
{
    public static IEnumerable<T> ToGenericList<T>(this DataTable dataTable)
    {
        var properties = typeof(T).GetProperties().Where(x => x.CanWrite).ToList();

        var result = new List<T>();

        // loop on rows
        foreach (DataRow row in dataTable.Rows)
        {
            // create an instance of T generic type.
            var item = Activator.CreateInstance<T>();

            // loop on properties and columns that matches properties
            foreach (var prop in properties)
                foreach (DataColumn column in dataTable.Columns)                    
                    if (prop.Name == column.ColumnName)
                    {
                        // Get the value from the datatable cell
                        object value = row[column.ColumnName];

                        // Set the value into the object
                        prop.SetValue(item, value);
                        break;
                    }


            result.Add(item);
        }

        return result;
    }
}

And given you have a model like this:

public class Student 
{
    public int Id { get; set; } 
    public string Code { get; set; } 
    public string FirstName { get; set; } 
    public string LastName { get; set; } 
    // other possible properties
}

You can use the extension method like this:

protected async Task<IEnumerable<T>> QuerySqlCmdReadRows<T>(string sqlCommand)
{
    using (var con = new SqlConnection(Connection.ToString()))
    {
        con.Open();
        using (var cmd = con.CreateCommand())
        {
            cmd.CommandText = sqlCommand;

            DataTable dtResult = new DataTable();

            using (var reader = await cmd.ExecuteReaderAsync())
               dtResult.Load(reader);

            return dtResult.ToGenericList<T>();
        }
    }
}

// just and sample
var students = QuerySqlCmdReadRows<Students>("select Id, Code, FirstName, LastName from Students");