Return a list of a Entity class

Question

This is my Class:

public class Customer
   {
      public int ID { get; set; }

      public string Card_ID { get; set; }

      public double Cash { get; set; }

      public string Name { get; set; }

      public Image Photo { get; set; }
   }  

I need to make a SELECT on my database to get some information of the customer, I used to use DataSet but I saw it's not the best choice when it comes to performance. Also I need to read only 2 or 3 fields, I started this method:

public List<Customer> ConsultCustomerData(string cardID)
   {
      list<Customer> customer = null;
      string sql = "SELECT name, cash FROM customers WHERE card_id = @cardID";            
       try
          {
             MySqlCommand cmd = new MySqlCommand();
             cmd.CommandText = sql;
             cmd.CommandType = CommandType.Text;
             cmd.Parameters.Add(new MySqlParameter("@cardID", MySqlDbType.VarChar)).Value = cardID;

           }
        catch (Exception ex)
          {
            throw new Exception(ex.Message);
          }
   }  

Now I don't know how to continue...
How may I return those informations using List or IList ?

Answer 1

You can use command.ExecuteReader method and fill entity data using reader.

 public List<Customer> ConsultCustomerData(string cardID)
 {
    List<Customer> list = new List<Customer>();
    string sql = "SELECT name, cash FROM customers WHERE card_id = @cardID";        

    MySqlCommand cmd = new MySqlCommand();
    cmd.CommandText = sql;
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.Add(new MySqlParameter("@cardID", MySqlDbType.VarChar)).Value = cardID;

    using (IDbDataReader reader = cmd.ExecuteReader()) {
        while (reader.Read()) {
             list.Add(new Customer { 
                 Name = reader.GetString(0), 
                 Cash = reader.GetDouble(1) 
             });
        }
    }

    return list;
} 

Check:

1. ADO.NET IDbCommand interface and its MySql implementation
2. ADO.NET IDataReader interface and its MySql implementation