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.

Where is your MySqlConnection and your connectionString? Are you able to connect to the database? Or do you have problems to convert your return in datatable? — Matheus Dasuke
– Matheus Dasuke, Commented Jun 23, 2021 at 15:29
TIL that DataTable derives from IDisposable and you can put it into a using statement — Rand Random
– Rand Random, Commented Jun 23, 2021 at 15:32
You know that your method ReadAllAsync only reads the datatable rows but never adds anything to the list adb_demo_clients ? — Rand Random
– Rand Random, Commented Jun 23, 2021 at 15:34
foreach (DataRow dr in dataTable.Rows) { demoClients d = new demoClients(); d.Id = Convert.ToInt32(dr["id_customer"]);; //fill all you fields that you need so after that you have to add in your list adb_demo_clients.add(d); (before you close your foreach) — Matheus Dasuke
– Matheus Dasuke, Commented Jun 23, 2021 at 16:44

Matheus Dasuke · Accepted Answer · 2021-06-23 16:59:44Z

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;
}

answered Jun 23, 2021 at 16:59

Matheus Dasuke

2613 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# - Converting DataReader to DataTable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related