Convert Datatable to List C#

Asked

Viewed 5,083 times

0

I need to convert the contents of DataTable for a List, I’m new to C# and I don’t 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;
}
c# asp.net-mvc asp.net
  • 1

    Post also the structure of the class and the DataTable.

2 answers

1

you can create an extension for Datatables as 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));
    }
}

Follows 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, like the DataTable has a column with the different name of the property in the class, I needed to decorate the class with ColumnAttribute.

  • Does our answer accept any type in the conversion? I say because I am implementing a generic method in the extension of my Datatable!

  • @Marconi, I think you’ll have to do some tests, for example, I just added a small correction if the cell value is DBNull.Value. Another thing you can investigate, is the behavior of this extension if the column type is different from the property type in the object, type the column Datatype is System.String and the property is System.Int32. But overall, the above solution is very generic and accepts most of the types.

  • I leave my +1, I want to test and learn how it works well! Pity that the OP did not see this answer.

  • 1

    @Marconi I believe he saw it, but how is it starting with .net/C#, I believe you’re not familiar with extensions, linq, reflection, generic, etc. Maybe if you expose your extensions to DataTable as a package of Nuget, I believe he’ll be more comfortable to use.

0


How did you give detail of how the structure of your columns is DataTable, and how the property of VoCliente, I did a basic example with Linq. It is only necessary to adjust to anticipate your need.

Filling in 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);
  }

Item class:

 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();

Browser other questions tagged c# asp.net-mvc asp.net

You are not signed in. Login or sign up in order to post.