目录
一、DataTable & List 相互转换
1.DataTable 转换为 List
using System.Data;
using System.Reflection;
/// <summary>
/// DataTable转换为List<T>对象
/// </summary>
/// <param name="dataTable">DataTable 对象</param>
/// <returns>List<T>集合</returns>
public static List<T> DataTableToList<T>(DataTable dataTable) where T : class, new()
{
// 定义集合
List<T> ts = new List<T>();
//定义一个临时变量
string tempName = string.Empty;
//遍历DataTable中所有的数据行
foreach (DataRow dr in dataTable.Rows)
{
T t = new T();
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
//遍历该对象的所有属性
foreach (System.Reflection.PropertyInfo pi in propertys)
{
tempName = pi.Name;//将属性名称赋值给临时变量
//检查DataTable是否包含此列(列名==对象的属性名)
if (dataTable.Columns.Contains(tempName))
{
//取值
object value = dr[tempName];
//如果非空,则赋给对象的属性
if (value != DBNull.Value)
{
pi.SetValue(t, value, null);
}
}
}
//对象添加到泛型集合中
ts.Add(t);
}
return ts;
}
2.List 转换为 DataTable
需要引用Newtonsoft.Json包
using Newtonsoft.Json;
using System.Data;
/// <summary>
/// List<T>转换为DataTable
/// </summary>
/// <param name="list">请求的list</param>
/// <returns></returns>
public static DataTable ListToDataTable<T>(List<T> list)
{
//创建一个空表
DataTable dt = new DataTable("tableName");
//创建传入对象名称的列
foreach (var item in list.FirstOrDefault().GetType().GetProperties())
{
dt.Columns.Add(item.Name);
}
//循环存储
foreach (var item in list)
{
//新加行
DataRow value = dt.NewRow();
//根据DataTable中的值,进行对应的赋值
foreach (DataColumn dtColumn in dt.Columns)
{
int i = dt.Columns.IndexOf(dtColumn);
//基元元素,直接复制,对象类型等,进行序列化
if (value.GetType().IsPrimitive)
{
value[i] = item.GetType().GetProperty(dtColumn.ColumnName).GetValue(item);
}
else
{
value[i] = JsonConvert.SerializeObject(item.GetType().GetProperty(dtColumn.ColumnName).GetValue(item));
}
}
dt.Rows.Add(value);
}
return dt;
}
二、DataTable & Json 相互转换
1.DataTable 转换为 Json字符串
需要引用Newtonsoft.Json包
using Newtonsoft.Json;
using System.Collections;
using System.Data;
/// <summary>
/// DataTable转换为Json字符串
/// </summary>
/// <param name="dataTable">DataTable 对象</param>
/// <returns>JSON字符串</returns>
public static string DataTableToJson(DataTable dataTable)
{
//定义ArrayList
ArrayList arrayList = new ArrayList();
//遍历DataTable中所有的数据行
foreach (DataRow dr in dataTable.Rows)
{
//定义字典【列名,值】
Dictionary<string, object> columnsDic = new Dictionary<string, object>();
//遍历DataTable中所有的数据列
foreach (DataColumn dc in dataTable.Columns)
{
//字典添加列名,值
columnsDic.Add(dc.ColumnName, dr[dc.ColumnName]);
}
//arrayList添加字典
arrayList.Add(columnsDic);
}
//序列化
return JsonConvert.SerializeObject(arrayList);
}
2.Json 转换为 DataTable
using System.Data;
using System.Text.RegularExpressions;
/// <summary>
/// Json转换为DataTable
/// </summary>
/// <param name="strJson">得到的json</param>
/// <returns></returns>
public static DataTable JsonToDataTable(string strJson)
{
//转换json格式
strJson = strJson.Replace(",\"", "*\"").Replace("\":", "\"#").ToString();
//取出表名
var rg = new Regex(@"(?<={)[^:]+(?=:\[)", RegexOptions.IgnoreCase);
string strName = rg.Match(strJson).Value;
DataTable tb = null;
//去除表名
strJson = strJson.Substring(strJson.IndexOf("[") + 1);
strJson = strJson.Substring(0, strJson.IndexOf("]"));
//获取数据
rg = new Regex(@"(?<={)[^}]+(?=})");
MatchCollection mc = rg.Matches(strJson);
for (int i = 0; i < mc.Count; i++)
{
string strRow = mc[i].Value;
string[] strRows = strRow.Split('*');
//创建表
if (tb == null)
{
tb = new DataTable();
tb.TableName = strName;
foreach (string str in strRows)
{
var dc = new DataColumn();
string[] strCell = str.Split('#');
if (strCell[0].Substring(0, 1) == "\"")
{
int a = strCell[0].Length;
dc.ColumnName = strCell[0].Substring(1, a - 2);
}
else
{
dc.ColumnName = strCell[0];
}
tb.Columns.Add(dc);
}
tb.AcceptChanges();
}
//增加内容
DataRow dr = tb.NewRow();
for (int r = 0; r < strRows.Length; r++)
{
dr[r] = strRows[r].Split('#')[1].Trim().Replace(",", ",").Replace(":", ":").Replace("\"", "");
}
tb.Rows.Add(dr);
tb.AcceptChanges();
}
return tb;
}