How to select data into List<T> instead of DataTable?

Question

This is how currently I'm selecting data from database:

public DataTable GetData()
{
    DataTable table = new DataTable("Table");
    using (SqlConnection connection = new SqlConnection("Connection string"))
    {
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandType = System.Data.CommandType.Text;
        command.CommandText = "query string";

        connection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        adapter.Fill(table);
    }
    return table;
}

But it return DataTable and I want to select List instead of DataTable. Like this:

public List<MyClass> GetData()
{
    DataTable table = new DataTable("Table");
    using (SqlConnection connection = new SqlConnection("Connection string"))
    {
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandType = System.Data.CommandType.Text;
        command.CommandText = "query string";

        connection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        adapter.Fill(table);
    }
    ...
    return [List of MyClass];
}

How can I do this?

Thanks!

Answer 1

If you want to use the DataRowCollection to populate a list of custom objects, you could use LINQ and an object initializer:

var lst = table.AsEnumerable().Select(r =>
    new MyObject
    {
        FirstProperty = r.Field<int>("FirstProperty"),
        OtherProperty = r.Field<string>("OtherProperty")
    }).ToList(); 

Answer 2

I'd recommend you to use dapper-dot-net, if you do not want to dig into LINQ to SQL or Entity Framework. Fumbling around yourself with IDataReader to materialise your result isn't worth the effort in most cases.