.NET DataTable and List Generic Conversion
- 1. Three methods of converting DataTable to List
generic
- The first one: DataTable is converted to a List
object collection - The second type: DataTable is converted into a List collection
- The third method: convert DataTable to List
- 2. Convert List
Generics to DataTable
In .NET development, DataTable is often used to extract data from relational databases. However, we often encounter DataTable and List generic types, and the mutual conversion operation between the two, because the DataTable data collection is not like List, and the corresponding T type is specified, so it is not as convenient as the List collection when operating.
Therefore, in order to facilitate the mutual conversion between the two collections, the following class is hereby written to record the exchange between the two.
1. Three methods of converting DataTable to List generics
Type 1: Convert DataTable to List object collection
/// <summary>
/// The first type: DataTable is converted to a List<T> object collection
/// </summary>
/// <typeparam name="TResult">Type</typeparam>
/// <param name="dt">DataTable</param>
/// <returns></returns>
public static List<TResult> DataTableToList<TResult>(this DataTable dt) where TResult : class, new()
{<!-- -->
//Create a list of properties
List<PropertyInfo> prlist = new List<PropertyInfo>();
//Get the type instance of TResult Reflection entry
Type t = typeof(TResult);
//Get all the Public properties of TResult and find out the property (PropertyInfo) with the same column name of TResult property and DataTable and add it to the property list
Array.ForEach<PropertyInfo>(t.GetProperties(), p => {<!-- --> if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); }) ;
//Create the returned collection
List<TResult> oblist = new List<TResult>();
foreach (DataRow row in dt.Rows)
{<!-- -->
//Create an instance of TResult
TResult ob = new TResult();
//Find the corresponding data and assign
prlist.ForEach(p => {<!-- --> if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
//Put it into the returned collection.
oblist. Add(ob);
}
return oblist;
}
Second: Convert DataTable to List collection
Among them: the isStoreDB formal parameter is to consider whether the DataTale data converted from List should be stored in the database. In the SQL Server data, the time types Date and DateTime have different ranges, and the Date time range is from January 1st to December 31st, 9999. The DateTime time range is from January 1, 1753 to December 31, 9999. If the time outside the range is stored in the database, an exception will occur. Therefore, with a time limit, the default is to store the data in the database.
/// <summary>
/// Convert DataTable to List collection
/// </summary>
/// <typeparam name="T">entity object</typeparam>
/// <param name="dt">datatable table</param>
/// <param name="isStoreDB">Whether it is stored in the database datetime field, the date field is fine, no need to judge when taking it out</param>
/// <returns>Return list collection</returns>
private static List<T> DataTableToList<T>(DataTable dt, bool isStoreDB = true)
{<!-- -->
/ / Confirm that the parameters are valid
if (dt == null || dt. Rows. Count <= 0)
{<!-- -->
return null;
}
List<T> list = new List<T>();
Type type = typeof(T);
//List<string> listColums = new List<string>();
PropertyInfo[] pArray = type.GetProperties(); //collection property array
foreach (DataRow row in dt.Rows)
{<!-- -->
T entity = Activator.CreateInstance<T>(); //New object instance
foreach (PropertyInfo p in pArray)
{<!-- -->
if (!dt.Columns.Contains(p.Name) || row[p.Name] == null || row[p.Name] == DBNull.Value)
{<!-- -->
continue; //If there is no collection attribute in the DataTable column or the field content is empty, jump out of the loop and proceed to the next loop
}
if (isStoreDB & amp; & amp; p.PropertyType == typeof(DateTime) & amp; & amp; Convert.ToDateTime(row[p.Name]) < Convert.ToDateTime("1753-01-01"))
{<!-- -->
continue;
}
try
{<!-- -->
var obj = Convert.ChangeType(row[p.Name], p.PropertyType);//Force the type, convert the table field type to the collection field type
p. SetValue(entity, obj, null);
}
catch (Exception)
{<!-- -->
// throw;
}
}
list. Add(entity);
}
return list;
}
Third: Convert DataTable to List
/// <summary>
/// Convert DataTable to List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt">DataTable data source</param>
/// <returns></returns>
private static List<T> DataTable2List<T>(DataTable dt)
{<!-- -->
/ / Confirm that the parameters are valid
if (dt == null || dt. Rows. Count <= 0)
{<!-- -->
return null;
}
IList<T> list = new List<T>(); // instantiate a list
// Write here to get all public properties of type T. Note that this is only to get the public properties of type T, not public methods, nor public fields, and of course not private properties
PropertyInfo[] tMembersAll = typeof(T).GetProperties();
for (int i = 0; i < dt. Rows. Count; i ++ )
{<!-- -->
//Create a generic object. Why create a generic object here? Is because at the moment I'm not sure about the type of the generic.
T t = Activator. CreateInstance<T>();
// Get all public properties of the t object type. But I don’t recommend writing this statement in the for loop, because it needs to be obtained once without a loop, which takes up resources, so it is recommended to write it outside
//PropertyInfo[] tMembersAll = t.GetType().GetProperties();
for (int j = 0; j < dt.Columns.Count; j++ )
{<!-- -->
//traverse tMembersAll
foreach (PropertyInfo tMember in tMembersAll)
{<!-- -->
//Take the name of column j in the dt table, and convert the name to uppercase letters. The meaning of the whole code is: if the column name and the attribute name are the same, assign the value
if (dt.Columns[j].ColumnName.ToUpper().Equals(tMember.Name.ToUpper()))
{<!-- -->
//dt.Rows[i][j] means to take the jth column of the i-th row in the dt table; DBNull refers to the value when a field is not set in the database, which is equivalent to the "null value" in the database ".
if (dt.Rows[i][j] != DBNull.Value)
{<!-- -->
//SetValue means: set the specified property to the specified value. tMember is a public member of T generic object t. The meaning of the whole code is: assign dt.Rows[i][j] to the tMember member of t object. For parameter details, please refer to http://msdn.microsoft.com /zh-cn/library/3z2t396t(v=vs.100).aspx/html
tMember. SetValue(t, Convert. ToString(dt. Rows[i][j]), null);
}
else
{<!-- -->
tMember. SetValue(t, null, null);
}
break;//Note that the break here is written in the if statement, which means that if the column name is the same as the attribute name and has been assigned, then I will jump out of the foreach loop and proceed to the next loop of j + 1
}
}
}
list. Add(t);
}
dt. Dispose();
return list.ToList();
}
2. Convert List generics to DataTable
/// <summary>
/// Convert List<T> to DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataList">generic List collection data</param>
/// <returns>Return DataTable</returns>
public System.Data.DataTable listToDataTable<T>(List<T> dataList) where T : class, new()
{<!-- -->
if (dataList == null || !dataList.Any() || dataList.Count == 0)
{<!-- -->
return null;
}
Type type = typeof(T);
using (System.Data.DataTable dt = new System.Data.DataTable(type.Name))
{<!-- -->
PropertyInfo[] propertyInfoArray = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (T t in dataList. Where(t => t != null))
{<!-- -->
DataRow row = dt. NewRow();
for (int i = 0, j = propertyInfoArray. Length; i < j; i ++ )
{<!-- -->
PropertyInfo propertyInfo = propertyInfoArray[i];
string name = propertyInfo.Name;
if (dt. Columns[name] == null)
{<!-- -->
System.Data.DataColumn column = new System.Data.DataColumn(name, propertyInfo.PropertyType);
dt.Columns.Add(column);
}
row[name] = propertyInfo. GetValue(t, null);
}
dt.Rows.Add(row);
}
return dt;
}
}