C# - Converting DataReader to DataTable

Question

I want to convert a DataReader to DataTable to display all customers in a list(demoClients) from the Database.

Currently I have this:

   using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Threading.Tasks;
    using MySqlConnector;
    using ProjectDatabase.Controllers;
    using System.Data;
    using System.Data.Common;
    namespace ProjectDatabase.Models
    {
        public class demoClientsQuery
        {
            public AppDb Db { get; }
    
            public demoClientsQuery(AppDb db)
            {
                Db = db;
            }
    
            public async Task<demoClients> FindAllClientsAsync(int id)
            {
                using var cmd = Db.conDemo.CreateCommand();
                cmd.CommandText = @"Query";
                cmd.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "@id_customer",
                    DbType = DbType.Int32,
                    Value = id,
                });
                
                MySqlDataReader dataReader = cmd.ExecuteReader();
                DataTable dataTable = new DataTable();
                dataTable.Load(dataReader);
                var result = await ReadAllAsync(dataTable);            
                return result.Count > 0 ? result[0] : null;
            }
    
    
            public async Task<List<demoClients>> LatestClientsAsync()
            {
                using var cmd = Db.conDemo.CreateCommand();
                cmd.CommandText = @"Query";
    
                var aa = await LatestClientsAsync2();
                
                MySqlDataReader dataReader = cmd.ExecuteReader();
                DataTable dataTable = new DataTable();
                dataTable.Load(dataReader);            
                return await ReadAllAsync(dataTable);
            }
    
            public async Task<DataTable> LatestClientsAsync2()
            {
                using var cmd = Db.conDemo.CreateCommand();
                cmd.CommandText = @"Query";
    
    
                //return await ReadAllAsync(await cmd.ExecuteReaderAsync());
                return await ToDataTable(cmd);
            }
    
            private async Task<DataTable> ToDataTable(MySqlCommand cmd)
            {
    
    
                cmd.CommandType = CommandType.Text;
                using (DbDataAdapter dataAdapter = new MySqlDataAdapter(cmd))
                {
                    cmd.CommandType = CommandType.Text;                               
                    DataTable data = new DataTable();                
                    dataAdapter.Fill(data);
                    
                    return data;
                }
               
            }
    
            private async Task<List<demoClients>> ReadAllAsync(DataTable dataTable)
            {
                var adb_demo_clients = new List<demoClients>();
                using (dataTable)
                {
                    
                    foreach (DataRow dr in dataTable.Rows)
                    {
                        int id_customer = Convert.ToInt32(dr["id_customer"]);
                        string delivery_person_name = Convert.ToString(dr["delivery_person_name"]);
                        string firstname = Convert.ToString(dr["firstname"]);
                        string lastname = Convert.ToString(dr["lastname"]);
                        string email = Convert.ToString(dr["email"]);
                    }
                    
                }
                return adb_demo_clients;
            }
       
        }
    }

The only problem I am having is that it doesn't return nothing, it returns empty, something in my code is not returning my data from my database.

Any help would be appreciated.

Thank you for your time.

Answer 1

You have to add item to your adb_demo_clients list, inside the foreach, on the ReadAllAsync method:

private async Task<List<demoClients>> ReadAllAsync(DataTable dataTable)
{
    var adb_demo_clients = new List<demoClients>();
    using (dataTable)
    {       
        foreach (DataRow dr in dataTable.Rows)
        {
            demoClients d = new demoClients();

            d.id_customer = Convert.ToInt32(dr["id_customer"]);
            d.delivery_person_name = Convert.ToString(dr["delivery_person_name"]);

            // all fields that you need
            ...
            
            // Add the item in your List
            adb_demo_clients.add(d);
        }
        
    }
    return adb_demo_clients;
}