Load database to list in c#

Question

I am quering a database and am wondering if there is a way to load the results back into a list. The only way i curerntly know of how to get info from a database is within a datatable. I can always get it into a datatable and then loop through the rows in the datatable and add them to a list. But is there a way to directly get the result of a query into a list? This is my code:

OleDbCommand command= new OleDbCommand("SELECT ID, GRADE, PHONE from database WHERE PHONE=123456", conn);
conn.Open();
dt.Load(command.ExecuteReader());
conn.Close();

The result should be a list entry of the form (id, grade, phone):

61265,95,123456

Thanks

Answer 1

Consider you have a class

public ListData()
{
  public int Id{get;set;} //Assuming DB field is int
  public string Grade{get;set;}  //Assuming DB field is a string
  public string PhoneNo{get;set;} //Assuming DB field is a string
}

Then you can do the following:

list<ListData> theList =(from item in dt.AsEnumerable()
                         Select new ListData()
                              {
                                 Id= (int)item["ID"],
                                 Grade = (string)item["GRADE"],
                                 PhoneNo = (string)item["PHONE"]
                              }
                        ).ToList()

Note:

1. I just type it on the comment textbox so there might be some errors if you just copy-paste the code

2. You also need to handle null values if any on GRADE / PHONE

Answer 2

Have a look on this question.

Answer 3

You can use Table Adapter, e.g.

DataSet mSet = new DataSet();
OleDbTableAdapter mAdapter = new OleDbTableAdapter(myOleDbCommand); 
mAdapter.Fill(mSet); 
myOleDbCommand.Connection.Close();