Convert DataTable to List C #

0

I need to convert the contents of DataTable to List , I'm new to C # and I do not know the syntax.

public List < VoCliente > ConsutaCliente() {
DataTable tabela = BDOracleCliente.ConsultaCliente();

List < VoCliente > teste = new List < VoCliente > ();

foreach(DataRow row in tabela.Rows) {
 //teste.Add((DataRow)row); ???
 teste = tabela ? ? ?
}

return teste;
}
    
asked by anonymous 07.11.2017 / 13:24

2 answers

1

Since you did not give details of how your column structure is in DataTable , and how are the VoCliente properties, I made a basic example with Linq . You only need to adjust to meet your need.

Filling the DataTable

  DataTable dt = new DataTable();
  DataColumn column;
  DataRow row;

  column = new DataColumn();
  column.DataType = System.Type.GetType("System.Int32");
  column.ColumnName = "id";
  dt.Columns.Add(column);

  column = new DataColumn();
  column.DataType = Type.GetType("System.String");
  column.ColumnName = "item";
  dt.Columns.Add(column);


  for (int i = 0; i < 10; i++) {
   row = dt.NewRow();
   row["id"] = i;
   row["item"] = "item " + i;
   dt.Rows.Add(row);
  }

Class Item:

 class Item
 {
        public int id { get; set; }
        public string item { get; set; }
 }

Passing the result to a List<Item>

  var items = dt.AsEnumerable().Select(linha => new Item
  {
     id = linha.Field<Int32>("id"),
     item = linha.Field<String>("item")
  }).ToList();
    
07.11.2017 / 13:49
1

You can create an extension for DataTables like the following.:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Dynamic;
using System.Xml.Serialization;
using System.Reflection;
using System.ComponentModel.DataAnnotations.Schema;

public static class DataTableExtensions
{
    private static Dictionary<Type, Dictionary<string, PropertyInfo>> types;

    static DataTableExtensions()
    {
        types = new Dictionary<Type, Dictionary<string, PropertyInfo>>();
    }

    public static List<T> ToList<T>(this DataTable dataTable) where T : class
    {   
        if (!types.ContainsKey(typeof(T)))
        {
            types.Add(typeof(T), typeof(T).GetProperties().ToDictionary(x => 
            {
                var column = x.GetCustomAttribute(typeof(ColumnAttribute)) as ColumnAttribute;
                return column == null ? x.Name : column.Name;
            }, x => x));
        }

        var list = new List<T>();
        var columns = dataTable.Columns.Cast<DataColumn>();
        var props = 
            from prop in types[typeof(T)]
            join column in columns on prop.Key equals column.ColumnName
            select new { 
                PropertyInfo = prop.Value, 
                Column = column 
            };

        foreach (var row in dataTable.Rows.Cast<DataRow>())
        {
            var item = Activator.CreateInstance<T>() as T;
            foreach(var prop in props)
            {
                var value = row[prop.Column.ColumnName];
                if (value != DBNull.Value)
                    prop.PropertyInfo.SetValue(item, value);
            }
            list.Add(item);
        }
        return list;
    }

    public static List<dynamic> ToList(this DataTable dataTable)
    {   
        var list = new List<dynamic>();
        var columns = dataTable.Columns.Cast<DataColumn>();

        foreach (var row in dataTable.Rows.Cast<DataRow>())
        {
            var item = new ExpandoObject() as IDictionary<string, object>;
            foreach(var column in columns)
            {
                var value = row[column.ColumnName];
                item.Add(column.ColumnName, value != DBNull.Value ? value : null);
            }
            list.Add(item);
        }
        return list;
    }
}

Here is an example of Usage:

using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.Xml.Serialization;
using System.ComponentModel.DataAnnotations.Schema;
using Newtonsoft.Json;

public class Entity
{
    public Guid EntityID { get; set; }
    public String Titulo { get; set; }
    public DateTime DataCriacao { get; set; }
    [Column("Detalhes")]
    public String Descricao { get; set; }
}

public class Program
{
    public static void Main()
    {
        var dataTable = new DataTable();
        dataTable.Columns.Add(new DataColumn() { DataType = typeof(Guid), ColumnName = "EntityID" });
        dataTable.Columns.Add(new DataColumn() { DataType = typeof(String), ColumnName = "Titulo" });
        dataTable.Columns.Add(new DataColumn() { DataType = typeof(DateTime), ColumnName = "DataCriacao" });
        dataTable.Columns.Add(new DataColumn() { DataType = typeof(String), ColumnName = "Detalhes" });

        for (int i = 0; i < 10; i++) {
            var row = dataTable.NewRow();
            row["EntityID"] = Guid.NewGuid();
            row["Titulo"] = "Titulo " + i;
            row["DataCriacao"] = DateTime.Now;
            row["Detalhes"] = "Descricao " + i;
            dataTable.Rows.Add(row);
        }

        var listA = dataTable.ToList<Entity>();
        Console.WriteLine(JsonConvert.SerializeObject(listA, Formatting.Indented));

        var listB = dataTable.ToList();
        Console.WriteLine(JsonConvert.SerializeObject(listB, Formatting.Indented));
    }
}

Follow the output.:

var listA = [
  {
    "EntityID": "1bbd4f4a-1614-4af9-a3f8-0798674502eb",
    "Titulo": "Titulo 0",
    "DataCriacao": "2017-11-07T14:22:51.9708648",
    "Descricao": "Descricao 0"
  },
  {
    "EntityID": "9a2d301e-f5e7-4a70-a5bf-200010a7f665",
    "Titulo": "Titulo 1",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Descricao": "Descricao 1"
  },
  {
    "EntityID": "4ac9b4cc-57c2-4c46-b124-4c36638858ef",
    "Titulo": "Titulo 2",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Descricao": "Descricao 2"
  },
  {
    "EntityID": "e4609c4c-cbe0-416f-bdcd-f03d17c69046",
    "Titulo": "Titulo 3",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Descricao": "Descricao 3"
  },
  {
    "EntityID": "9d8ebc9b-b153-40a2-b9e6-0bbb1e6f08c5",
    "Titulo": "Titulo 4",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Descricao": "Descricao 4"
  },
  {
    "EntityID": "ec25f555-3d5e-4355-9d9a-5ab453dde269",
    "Titulo": "Titulo 5",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Descricao": "Descricao 5"
  },
  {
    "EntityID": "dea54049-45c5-45ab-8ab4-ef099561aa37",
    "Titulo": "Titulo 6",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Descricao": "Descricao 6"
  },
  {
    "EntityID": "58df83fb-e341-479e-b5d5-d7d4b0e14f98",
    "Titulo": "Titulo 7",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Descricao": "Descricao 7"
  },
  {
    "EntityID": "97db14e4-fa79-412c-a2ae-913e0ce6824f",
    "Titulo": "Titulo 8",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Descricao": "Descricao 8"
  },
  {
    "EntityID": "38e49b63-d3d9-4add-bab2-959c24b3599c",
    "Titulo": "Titulo 9",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Descricao": "Descricao 9"
  }
];
var listB = [
  {
    "EntityID": "1bbd4f4a-1614-4af9-a3f8-0798674502eb",
    "Titulo": "Titulo 0",
    "DataCriacao": "2017-11-07T14:22:51.9708648",
    "Detalhes": "Descricao 0"
  },
  {
    "EntityID": "9a2d301e-f5e7-4a70-a5bf-200010a7f665",
    "Titulo": "Titulo 1",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Detalhes": "Descricao 1"
  },
  {
    "EntityID": "4ac9b4cc-57c2-4c46-b124-4c36638858ef",
    "Titulo": "Titulo 2",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Detalhes": "Descricao 2"
  },
  {
    "EntityID": "e4609c4c-cbe0-416f-bdcd-f03d17c69046",
    "Titulo": "Titulo 3",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Detalhes": "Descricao 3"
  },
  {
    "EntityID": "9d8ebc9b-b153-40a2-b9e6-0bbb1e6f08c5",
    "Titulo": "Titulo 4",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Detalhes": "Descricao 4"
  },
  {
    "EntityID": "ec25f555-3d5e-4355-9d9a-5ab453dde269",
    "Titulo": "Titulo 5",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Detalhes": "Descricao 5"
  },
  {
    "EntityID": "dea54049-45c5-45ab-8ab4-ef099561aa37",
    "Titulo": "Titulo 6",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Detalhes": "Descricao 6"
  },
  {
    "EntityID": "58df83fb-e341-479e-b5d5-d7d4b0e14f98",
    "Titulo": "Titulo 7",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Detalhes": "Descricao 7"
  },
  {
    "EntityID": "97db14e4-fa79-412c-a2ae-913e0ce6824f",
    "Titulo": "Titulo 8",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Detalhes": "Descricao 8"
  },
  {
    "EntityID": "38e49b63-d3d9-4add-bab2-959c24b3599c",
    "Titulo": "Titulo 9",
    "DataCriacao": "2017-11-07T14:22:52.0333271",
    "Detalhes": "Descricao 9"
  }
];

Note that because DataTable has a column with a different name of the property in the class, I needed to decorate the class with ColumnAttribute .

    
07.11.2017 / 15:30