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

Question

This is how I now select data from the 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 returns a 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

I would recommend you use dapper-dot-net if you don't want to dig into LINQ to SQL or Entity Framework. Damaging yourself with IDataReader to materialize your result is not worth the effort in most cases.

Answer 2

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

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

Answer 3

Try this code.

 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); List<MyClass> list=new List<MyClass>(); foreach(DataRow row in table) { MyClass instance = new MyClass(); instance.ID = row["ID"]; //similarly for rest of the properties list.Add(instance); } } return list; } 

Answer 4

If you use the ADO.NET approach, you will return the data table and you can convert it to a list or IEnumberable.

Alternatively, you can learn ORM tools like nHibernate or use LINQ to SQL