Fill class with bank result

Question

Good afternoon everyone. I have a huge need to be able to put together a situation.

I have an object DataTable with the result with the column "ID_CLIENTE".

I have an object Cliente with the property ID_CLIENTE.

Is there any way to fill the object Cliente with the data of DataTable automatically, just passing the object type Cliente and he knows that the column "ID_CLIENTE" of DataTable must be linked to the property "ID_CLIENTE" of the object Cliente ?

I made myself clear?

Client class

public class Cliente
{
    public int ID_CLIENTE { get; set; }
    public string NOME_CLIENTE { get; set; }
    public DateTime DT_NASC_CLIENTE { get; set; }
}

Loading Datatable

SqlConnection conexao = new SqlConnection("STRING_CONEXAO");
SqlCommand comando = new SqlCommand("SELECT * FROM TAB_CLIENTE", conexao);

DataTable dt = new DataTable();

try
{
    conexao.Open();
    dt.Load(comando.ExecuteReader());
    conexao.Close();
}
catch (Exception)
{
    if (conexao.State != ConnectionState.Closed)
        conexao.Close();
    throw;
}

Answer 1

Ideal solution, is to make the list of the type you need with the code below:

List<Cliente> clientes = new List<Cliente>();
SqlConnection conexao = new SqlConnection("STRING_CONEXAO");
SqlCommand comando = new SqlCommand("SELECT ID_CLIENTE,NOME_CLIENTE,DT_NASC_CLIENTE 
                                     FROM TAB_CLIENTE", conexao);

using (SqlDataReader reader = comando.ExecuteReader())
{
    if (reader.HasRows)
    {
        while (reader.Read())
        {
            clientes.Add(new Cliente
            {
                ID_CLIENTE = reader.GetInt32(0),
                NOME_CLIENTE = reader.GetString(1),
                DT_NASC_CLIENTE = reader.GetDateTime(2)
            });                        
        }
    }
}
return clientes;

_{Observing: in his SQL edited and put the 3 fields in order to be used in the code}

---

Out of curiosity if this DataTable comes from something that is not produced by you can use this code (which is a extension method using Reflection):

_{Observing: the class fields have to be equal to the same field names and types to run the Reflection}

Create a class with the following layout:

public static class MyExt
{
    public static List<T> ToListOf<T>(this DataTable dt)
    {
        const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance;
        var columnNames = dt.Columns.Cast<DataColumn>()
            .Select(c => c.ColumnName)
            .ToList();
        var objectProperties = typeof(T).GetProperties(flags);
        var targetList = dt.AsEnumerable().Select(dataRow =>
        {
            var instanceOfT = Activator.CreateInstance<T>();

            foreach (var properties in objectProperties
                 .Where(properties => columnNames.Contains(properties.Name)
                   && dataRow[properties.Name] != DBNull.Value))
            {
                properties.SetValue(instanceOfT, dataRow[properties.Name], null);
            }
            return instanceOfT;
        }).ToList();

        return targetList;
    }
}

_{Withdrawn code Soen}

How to use?

DataTable dt = new DataTable();
List<Cliente> clientes = dt.ToListOf<Cliente>();

---

Another way could be:

DataTable dt = new DataTable();
List<Cliente> result = dt.AsEnumerable()
            .Select(s => new  Cliente
            {
                ID_CLIENTE = s.Field<int>("ID_CLIENTE"),
                NOME_CLIENTE = s.Field<string>("NOME_CLIENTE"),
                DT_NASC_CLIENTE = s.Field<DateTime>("DT_NASC_CLIENTE")
            })
            .ToList();

References:

• Datatable class
• Converting Datatable to List of class
• Extension Methods (Programming Guide in C#)
• What is Reflection, why is it useful? How to use?
• Reflection (c#)