How do you convert a DataTable into a generic list

问题:

Currently, I\'m using:

DataTable dt = CreateDataTableInSomeWay();

List<DataRow> list = new List<DataRow>(); 
foreach (DataRow dr in dt.Rows)
{
    list.Add(dr);
}

Is there a better/magic way?

回答1:

If you\'re using .NET 3.5, you can use DataTableExtensions.AsEnumerable (an extension method) and then if you really need a List<DataRow> instead of just IEnumerable<DataRow> you can call Enumerable.ToList:

IEnumerable<DataRow> sequence = dt.AsEnumerable();

or

using System.Linq;
...
List<DataRow> list = dt.AsEnumerable().ToList();

回答2:

List<Employee> emp = new List<Employee>();

//Maintaining DataTable on ViewState
//For Demo only

DataTable dt = ViewState[\"CurrentEmp\"] as DataTable;

//read data from DataTable 
//using lamdaexpression


emp = (from DataRow row in dt.Rows

   select new Employee
   {
       _FirstName = row[\"FirstName\"].ToString(),
       _LastName = row[\"Last_Name\"].ToString()

   }).ToList();

回答3:

With C# 3.0 and System.Data.DataSetExtensions.dll,

List<DataRow> rows = table.Rows.Cast<DataRow>().ToList();

回答4:

You could use

List<DataRow> list = new List<DataRow>(dt.Select());

dt.Select() will return all rows in your table, as an array of datarows, and the List constructor accepts that array of objects as an argument to initially fill your list with.

回答5:

If you just want a list of values from the \"ID\" int field returned, you could use...

List<int> ids = (from row in dt.AsEnumerable() select Convert.ToInt32(row[\"ID\"])).ToList();

回答6:

You can create a extension function as :

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


var output = yourDataInstance.ToListof<targetModelType>();

回答7:

using System.Data;


var myEnumerable = myDataTable.AsEnumerable();

List<MyClass> myClassList =
    (from item in myEnumerable
     select new MyClass{
         MyClassProperty1 = item.Field<string>(\"DataTableColumnName1\"),
         MyClassProperty2 = item.Field<string>(\"DataTableColumnName2\")
    }).ToList();

回答8:

Again, using 3.5 you may do it like:

dt.Select().ToList()

BRGDS

回答9:

I have added some modification to the code from this answer (https://stackoverflow.com/a/24588210/4489664) because for nullable Types it will return exception

public static List<T> DataTableToList<T>(this DataTable table) where T: new()
{
    List<T> list = new List<T>();
    var typeProperties = typeof(T).GetProperties().Select(propertyInfo => new
        {
            PropertyInfo = propertyInfo,
            Type = Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType
        }).ToList();

    foreach (var row in table.Rows.Cast<DataRow>())
    {
        T obj = new T();
        foreach (var typeProperty in typeProperties)
        {
            object value = row[typeProperty.PropertyInfo.Name];
            object safeValue = value == null || DBNull.Value.Equals(value)
                ? null
                : Convert.ChangeType(value, typeProperty.Type);

            typeProperty.PropertyInfo.SetValue(obj, safeValue, null);
        }
        list.Add(obj);
    }
    return list;
}

回答10:

A more \'magic\' way, and doesn\'t need .NET 3.5.

If, for example, DBDatatable was returning a single column of Guids (uniqueidentifier in SQL) then you could use:

Dim gList As New List(Of Guid)
gList.AddRange(DirectCast(DBDataTable.Select(), IEnumerable(Of Guid)))

回答11:

// this is better suited for expensive object creation/initialization
IEnumerable<Employee> ParseEmployeeTable(DataTable dtEmployees)
{
    var employees = new ConcurrentBag<Employee>();

    Parallel.ForEach(dtEmployees.AsEnumerable(), (dr) =>
    {
        employees.Add(new Employee() 
        {
            _FirstName = dr[\"FirstName\"].ToString(),
            _LastName = dr[\"Last_Name\"].ToString()
        });
    });

    return employees;
}

回答12:

DataTable.Select() doesnt give the Rows in the order they were present in the datatable.

If order is important I feel iterating over the datarow collection and forming a List is the right way to go or you could also use overload of DataTable.Select(string filterexpression, string sort).

But this overload may not handle all the ordering (like order by case ...) that SQL provides.

回答13:

Here\'s a DataTable extension method that converts a DataTable to a generic list.

https://gist.github.com/gaui/a0a615029f1327296cf8

Usage:

List<Employee> emp = dtTable.DataTableToList<Employee>();

回答14:

DataTable dt;   // datatable should contains datacolumns with Id,Name

List<Employee> employeeList=new List<Employee>();  // Employee should contain  EmployeeId, EmployeeName as properties

foreach (DataRow dr in dt.Rows)
{
    employeeList.Add(new Employee{EmployeeId=dr.Id,EmplooyeeName=dr.Name});
}

回答15:

        /* This is a generic method that will convert any type of DataTable to a List 
         * 
         * 
         * Example :    List< Student > studentDetails = new List< Student >();  
         *              studentDetails = ConvertDataTable< Student >(dt);  
         *
         * Warning : In this case the DataTable column\'s name and class property name
         *           should be the same otherwise this function will not work properly
         */

The following are the two functions in which if we pass a DataTable and a user defined class. It will then return the List of that class with the DataTable data.

        public static List<T> ConvertDataTable<T>(DataTable dt)
        {
            List<T> data = new List<T>();
            foreach (DataRow row in dt.Rows)
            {
                T item = GetItem<T>(row);
                data.Add(item);
            }
            return data;
        }


        private static T GetItem<T>(DataRow dr)
        {
            Type temp = typeof(T);
            T obj = Activator.CreateInstance<T>();

            foreach (DataColumn column in dr.Table.Columns)
            {
                foreach (PropertyInfo pro in temp.GetProperties())
                {
                   //in case you have a enum/GUID datatype in your model
                   //We will check field\'s dataType, and convert the value in it.
                    if (pro.Name == column.ColumnName){                
                    try
                    {
                        var convertedValue = GetValueByDataType(pro.PropertyType, dr[column.ColumnName]);
                        pro.SetValue(obj, convertedValue, null);
                    }
                    catch (Exception e)
                    {         
                       //ex handle code                   
                        throw;
                    }
                        //pro.SetValue(obj, dr[column.ColumnName], null);
                }
                    else
                        continue;
                }
            }
            return obj;
        }

This method will check the datatype of field, and convert dataTable value in to that datatype.

    private static object GetValueByDataType(Type propertyType, object o)
    {
        if (o.ToString() == \"null\")
        {
            return null;
        }
        if (propertyType == (typeof(Guid)) || propertyType == typeof(Guid?))
        {
            return Guid.Parse(o.ToString());
        }
        else if (propertyType == typeof(int) || propertyType.IsEnum) 
        {
            return Convert.ToInt32(o);
        }
        else if (propertyType == typeof(decimal) )
        {
            return Convert.ToDecimal(o);
        }
        else if (propertyType == typeof(long))
        {
            return Convert.ToInt64(o);
        }
        else if (propertyType == typeof(bool) || propertyType == typeof(bool?))
        {
            return Convert.ToBoolean(o);
        }
        else if (propertyType == typeof(DateTime) || propertyType == typeof(DateTime?))
        {
            return Convert.ToDateTime(o);
        }
        return o.ToString();
    }

To call the preceding method, use the following syntax:

List< Student > studentDetails = new List< Student >();  
studentDetails = ConvertDataTable< Student >(dt); 

Change the Student class name and dt value based on your requirements. In this case the DataTable column\'s name and class property name should be the same otherwise this function will not work properly.

回答16:

This worked for me: Need at least .Net Framework 3.5, Code below displays DataRow turned to Generic.IEnumerable, comboBox1 has been used for a better illustration.

using System.Linq;

DataTable dt = new DataTable();            
dt = myClass.myMethod();                 
List<object> list = (from row in dt.AsEnumerable() select (row[\"name\"])).ToList();
comboBox1.DataSource = list;

回答17:

Use System.Data namespace then you will get .AsEnumerable().

回答18:

Output

public class ModelUser
{
    #region Model

    private string _username;
    private string _userpassword;
    private string _useremail;
    private int _userid;

    /// <summary>
    /// 
    /// </summary>
    public int userid
    {
        set { _userid = value; }
        get { return _userid; }
    }


    /// <summary>
    /// 
    /// </summary>

    public string username
    {
        set { _username = value; }
        get { return _username; }
    }

    /// <summary>
    /// 
    /// </summary>
    public string useremail
    {
        set { _useremail = value; }
        get { return _useremail; }
    }

    /// <summary>
    /// 
    /// </summary>
    public string userpassword
    {
        set { _userpassword = value; }
        get { return _userpassword; }
    }
    #endregion Model
}

public List<ModelUser> DataTableToList(DataTable dt)
{
    List<ModelUser> modelList = new List<ModelUser>();
    int rowsCount = dt.Rows.Count;
    if (rowsCount > 0)
    {
        ModelUser model;
        for (int n = 0; n < rowsCount; n++)
        {
            model = new ModelUser();

            model.userid = (int)dt.Rows[n][\"userid\"];
            model.username = dt.Rows[n][\"username\"].ToString();
            model.useremail = dt.Rows[n][\"useremail\"].ToString();
            model.userpassword = dt.Rows[n][\"userpassword\"].ToString();

            modelList.Add(model);
        }
    }
    return modelList;
}

static DataTable GetTable()
{
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable();
    table.Columns.Add(\"userid\", typeof(int));
    table.Columns.Add(\"username\", typeof(string));
    table.Columns.Add(\"useremail\", typeof(string));
    table.Columns.Add(\"userpassword\", typeof(string));

    // Here we add five DataRows.
    table.Rows.Add(25, \"Jame\", \"[e-mail]\", DateTime.Now.ToString());
    table.Rows.Add(50, \"luci\", \"[e-mail]\", DateTime.Now.ToString());
    table.Rows.Add(10, \"Andrey\", \"[e-mail]\", DateTime.Now.ToString());
    table.Rows.Add(21, \"Michael\", \"[e-mail]\", DateTime.Now.ToString());
    table.Rows.Add(100, \"Steven\", \"[e-mail]\", DateTime.Now.ToString());
    return table;
}

protected void Page_Load(object sender, EventArgs e)
{
    List<ModelUser> userList = new List<ModelUser>();

    DataTable dt = GetTable();

    userList = DataTableToList(dt);

    gv.DataSource = userList;
    gv.DataBind();
}[enter image description here][1]

</asp:GridView>
</div>

回答19:

We can use a Generic Method for converting DataTable to List instead of manually converting a DataTable to List.

Note: DataTable\'s ColumnName and Type\'s PropertyName should be same.

Call the below Method:

long result = Utilities.ConvertTo<Student>(dt ,out listStudent);

// Generic Method
public class Utilities
{
    public static long ConvertTo<T>(DataTable table, out List<T> entity)
    {
        long returnCode = -1;
        entity = null;

        if (table == null)
        {
            return -1;
        }

        try
        {
            entity = ConvertTo<T>(table.Rows);
            returnCode = 0;
        }

        catch (Exception ex)
        {
            returnCode = 1000;
        }

        return returnCode;
    }

    static List<T> ConvertTo<T>(DataRowCollection rows)
    {
        List<T> list = null;
        if (rows != null)
        {
            list = new List<T>();

            foreach (DataRow row in rows)
            {
                T item = CreateItem<T>(row);
                list.Add(item);
            }
        }

        return list;
    }

    static T CreateItem<T>(DataRow row)
    {
        string str = string.Empty;
        string strObj = string.Empty;

        T obj = default(T);

        if (row != null)
        {
            obj = Activator.CreateInstance<T>();
            strObj = obj.ToString();
            NameValueCollection objDictionary = new NameValueCollection();

            foreach (DataColumn column in row.Table.Columns)
            {
                PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);

                if (prop != null)
                {
                    str = column.ColumnName;

                    try
                    {
                        objDictionary.Add(str, row[str].ToString());
                        object value = row[column.ColumnName];
                        Type vType = obj.GetType();

                        if (value == DBNull.Value)
                        {
                            if (vType == typeof(int) || vType == typeof(Int16)
                                                     || vType == typeof(Int32)
                                                     || vType == typeof(Int64)
                                                     || vType == typeof(decimal)
                                                     || vType == typeof(float)
                                                     || vType == typeof(double))
                            {
                                value = 0;
                            }

                            else if (vType == typeof(bool))
                            {
                                value = false;
                            }

                            else if (vType == typeof(DateTime))
                            {
                                value = DateTime.MaxValue;
                            }

                            else
                            {
                                value = null;
                            }

                            prop.SetValue(obj, value, null);
                        }

                        else
                        {
                            prop.SetValue(obj, value, null);
                        }
                    }

                    catch(Exception ex)
                    {

                    }
                }
            }

            PropertyInfo ActionProp = obj.GetType().GetProperty(\"ActionTemplateValue\");

            if (ActionProp != null)
            {
                object ActionValue = objDictionary;
                ActionProp.SetValue(obj, ActionValue, null);
            }
        }

        return obj;
    }
}

回答20:

https://www.nuget.org/packages/AD.GenericConversion/

Checkout this library for conversion you will find out all type of conversion here like :-

1. DataTable to GenericList
2. Generic type to DataTable
3. Json to DataTable,Generic list, Generic type
4. DataTable to Json

回答21:

You can use a generic method like that for datatable to generic list

public static List<T> DataTableToList<T>(this DataTable table) where T : class, new()
{
    try
    {
        List<T> list = new List<T>();

        foreach (var row in table.AsEnumerable())
        {
            T obj = new T();

            foreach (var prop in obj.GetType().GetProperties())
            {
                try
                {
                    PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
                    if (propertyInfo.PropertyType.IsEnum)
                    {
                        propertyInfo.SetValue(obj, Enum.Parse(propertyInfo.PropertyType, row[prop.Name].ToString()));
                    }
                    else
                    {
                        propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                    }                          
                }
                catch
                {
                    continue;
                }
            }

            list.Add(obj);
        }

        return list;
    }
    catch
    {
        return null;
    }
}