如何将数据表转换为通用列表?

小开

你可以用

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

dt.Select()将返回表中的所有行，作为一个数据箭头数组，并且List构造函数接受该对象数组作为初始填充列表的参数。

小开

使用c# 3.0和System.Data.DataSetExtensions.dll，

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

小开

最佳答案

如果你使用。net 3.5，你可以使用DataTableExtensions.AsEnumerable(一个扩展方法)，然后如果你真的需要List<DataRow>而不是IEnumerable<DataRow>，你可以调用Enumerable.ToList:

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

或

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

小开

一种更“神奇”的方式，而且不需要。net 3.5。

例如，如果DBDatatable返回一列guid (SQL中的uniqueidentifier)，那么您可以使用:

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

小开

DataTable.Select()没有按照它们在数据表中出现的顺序给出行。

如果顺序很重要，我觉得迭代数据箭头集合并形成一个列表是正确的方法，或者你也可以使用DataTable.Select(string filterexpression, string sort)的重载。

但是这种重载可能无法处理SQL提供的所有排序(比如按大小写排序……)。

小开

同样，使用3.5，你可以这样做:

dt.Select().ToList()

BRGDS

小开

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

小开

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

小开

如果你只想从"ID" int字段返回一个值列表，你可以使用…

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

小开

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

小开

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

小开

这招对我很管用: 至少需要。net Framework 3.5， 下面的代码显示DataRow变成了泛型。 . IEnumerable, comboBox1用于更好的说明

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;

小开

我已经从这个答案(https://stackoverflow.com/a/24588210/4489664)的代码中添加了一些修改，因为对于可空类型，它将返回异常

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

小开

使用System.Data命名空间，将得到.AsEnumerable()。

小开

你可以这样创建一个扩展函数:

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

小开

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", "Jame@hotmail.com", DateTime.Now.ToString());
table.Rows.Add(50, "luci", "luci@hotmail.com", DateTime.Now.ToString());
table.Rows.Add(10, "Andrey", "Andrey@hotmail.com", DateTime.Now.ToString());
table.Rows.Add(21, "Michael", "Michael@hotmail.com", DateTime.Now.ToString());
table.Rows.Add(100, "Steven", "Steven@hotmail.com", 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>

小开

我们可以使用泛型方法将DataTable转换为List，而不是手动将DataTable转换为List。

注意:DataTable的ColumnName和Type的PropertyName应该是相同的。

调用下面的方法:

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

小开

        /* 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
*/

下面是两个函数，如果我们传递a 数据表和用户定义的类。然后它将返回该类的List和DataTable数据

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

此方法将检查字段的数据类型，并将dataTable值转换为该数据类型。

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

要调用上面的方法，使用以下语法:

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

根据您的需求更改Student类名和dt值。在这种情况下，DataTable列的名称和类属性名称应该相同，否则该函数将无法正常工作。

小开

对于数据表到泛型列表，可以使用这样的泛型方法

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

小开

将DataTable转换为泛型Dictionary

public static Dictionary<object,IList<dynamic>> DataTable2Dictionary(DataTable dt)
{
Dictionary<object, IList<dynamic>> dict = new Dictionary<dynamic, IList<dynamic>>();


foreach(DataColumn column in dt.Columns)
{
IList<dynamic> ts = dt.AsEnumerable()
.Select(r => r.Field<dynamic>(column.ToString()))
.ToList();
dict.Add(column, ts);
}
return dict;
}

小开

使用扩展:

public static class Extensions
{
#region Convert Datatable To List
public static IList<T> ToList<T>(this DataTable table) where T : new()
{
IList<PropertyInfo> properties = typeof(T).GetProperties().ToList();
IList<T> result = new List<T>();


foreach (var row in table.Rows)
{
var item = CreateItemFromRow<T>((DataRow)row, properties);
result.Add(item);
}
return result;
}
private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()
{
T item = new T();
foreach (var property in properties)
{
property.SetValue(item, row[property.Name], null);
}
return item;
}
#endregion
}

小开

将DataTable行分配给类的泛型List

  List<Candidate> temp = new List<Candidate>();//List that holds the Candidate Class,
//Note:The Candidate class contains RollNo,Name and Department
//tb is DataTable
temp = (from DataRow dr in tb.Rows
select new Candidate()
{
RollNO = Convert.ToInt32(dr["RollNO"]),
Name = dr["Name"].ToString(),
Department = dr["Department"].ToString(),


}).ToList();

小开

将数据表转换为类的泛型列表的最简单方法

使用Newtonsoft.Json;

var json = JsonConvert.SerializeObject(dataTable);
var model = JsonConvert.DeserializeObject<List<ClassName>>(json);

小开

您可以使用以下两个通用函数

private 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())
{
if (pro.Name == column.ColumnName)
pro.SetValue(obj, dr[column.ColumnName].ToString(), null);
else
continue;
}
}
return obj;
}

然后像下面这样使用

List<StudentScanExamsDTO> studentDetails = ConvertDataTable<StudentScanExamsDTO>(dt);

小开

lPerson = dt.AsEnumerable().Select(s => new Person()
{
Name = s.Field<string>("Name"),
SurName = s.Field<string>("SurName"),
Age = s.Field<int>("Age"),
InsertDate = s.Field<DateTime>("InsertDate")
}).ToList();

链接到工作的DotNetFiddle例子

    using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Data.DataSetExtensions;


public static void Main()
{
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("SurName", typeof(string));
dt.Columns.Add("Age", typeof(int));
dt.Columns.Add("InsertDate", typeof(DateTime));


var row1= dt.NewRow();
row1["Name"] = "Adam";
row1["SurName"] = "Adam";
row1["Age"] = 20;
row1["InsertDate"] = new DateTime(2020, 1, 1);
dt.Rows.Add(row1);


var row2 = dt.NewRow();
row2["Name"] = "John";
row2["SurName"] = "Smith";
row2["Age"] = 25;
row2["InsertDate"] = new DateTime(2020, 3, 12);
dt.Rows.Add(row2);


var row3 = dt.NewRow();
row3["Name"] = "Jack";
row3["SurName"] = "Strong";
row3["Age"] = 32;
row3["InsertDate"] = new DateTime(2020, 5, 20);
dt.Rows.Add(row3);


List<Person> lPerson = new List<Person>();
lPerson = dt.AsEnumerable().Select(s => new Person()
{
Name = s.Field<string>("Name"),
SurName = s.Field<string>("SurName"),
Age = s.Field<int>("Age"),
InsertDate = s.Field<DateTime>("InsertDate")
}).ToList();
        

foreach(Person pers in lPerson)
{
Console.WriteLine("{0} {1} {2} {3}", pers.Name, pers.SurName, pers.Age, pers.InsertDate);
}
}
    

public class Person
{
public string Name { get; set; }
public string SurName { get; set; }
public int Age { get; set; }
public DateTime InsertDate { get; set; }
}
}

小开

如果有人想创建自定义函数转换数据表到列表

class Program
{
static void Main(string[] args)
{
DataTable table = GetDataTable();
var sw = new Stopwatch();


sw.Start();
LinqMethod(table);
sw.Stop();
Console.WriteLine("Elapsed time for Linq Method={0}", sw.ElapsedMilliseconds);


sw.Reset();


sw.Start();
ForEachMethod(table);
sw.Stop();
Console.WriteLine("Elapsed time for Foreach method={0}", sw.ElapsedMilliseconds);


Console.ReadKey();
}


private static DataTable GetDataTable()
{
var table = new DataTable();
table.Columns.Add("ID", typeof(double));
table.Columns.Add("CategoryName", typeof(string));
table.Columns.Add("Active", typeof(double));


var rand = new Random();


for (int i = 0; i < 100000; i++)
{
table.Rows.Add(i, "name" + i,  rand.Next(0, 2));
}
return table;
}


private static void LinqMethod(DataTable table)
{
var list = table.AsEnumerable()
.Skip(1)
.Select(dr =>
new Category
{
Id = Convert.ToInt32(dr.Field<double>("ID")),
CategoryName = dr.Field<string>("CategoryName"),
IsActive =
dr.Field<double>("Active") == 1 ? true : false
}).ToList();
}
private static void ForEachMethod(DataTable table)
{
var categoryList = new List<Category>(table.Rows.Count);
foreach (DataRow row in table.Rows)
{
var values = row.ItemArray;
var category = new Category()
{
Id = Convert.ToInt32(values[0]),
CategoryName = Convert.ToString(values[1]),
IsActive = (double)values[2] == 1 ? true : false
};
categoryList.Add(category);
}
}


private class Category
{
public int Id { get; set; }
public string CategoryName { get; set; }
public bool IsActive { get; set; }
}
}

如果我们执行上面的代码，Foreach方法在56ms内完成，而linq one需要101ms(1000条记录)。所以使用Foreach方法比较好。来源:在c#中将数据表转换为列表的方法(附性能测试示例) < / p >

小开

尝试使用Newtonsoft Json:

var json = JsonConvert.SerializeObject(dataTable);
var YourConvertedDataType = JsonConvert.DeserializeObject<YourDataType>(json);

小开

要获取值的List而不是ItemArray，请执行以下操作:

List<string> s = dt.AsEnumerable().Select(x => x[0].ToString()).ToList();

上面假设您需要列0中的字符串值列表。