Fill class with bank result

Question

Fill class with bank result

Navigation

#1 by (1 votes)

1

Good afternoon everyone. I have a giant need to get things done.

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

I have an object Cliente with the property ID_CLIENTE .

Is there any way to populate the Cliente object with the DataTable data automatically, just passing the type of the Cliente object and it knows that the "ID_CLIENTE" column of DataTable has to be bound in the property "ID_CLIENTE" of object Cliente ?

Could I be 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;
}

sql-server c#

asked by anonymous 22.12.2016 / 22:01

1 answer

Problem when performing join on tables with generalization Exception 'The input string was not in an incorrect format' when doing calculation

score 1 · Accepted Answer

Ideal solution is to make the list of the type you need with the code just 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;

Note: in your SQL has been 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 ):

NOTE: The fields of the class have to be the same as the field names and types to work at # p>

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

Code withdrawn SOen

How to use?

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

Another way might look like this:

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: