C# DataTable、Json和List的互相转换

DataTable常规用法

DataTable dt = new DataTable();
//DataTable dt = new DataTable("tbName");//定义DataTable名称

//添加列,建议全部使用string
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("name", typeof(string));

// 添加一行数据
DataRow row1 = dt.NewRow();
row1["id"] = "1";
row1["name"] = "John";
dt.Rows.Add(row1);

//添加另外一条数据
DataRow row2 = dt.Rows.Add();
row2["id"] = "2";
row2["name"] = "Lucy";

// 添加另一行数据
dt.Rows.Add("3", "Jane");

//获取所有列名
var dtColumns = dt.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToList();

//获取某一列集合
string[] ids = dt.AsEnumerable().Select(s => s["id"].ToString()).ToArray();
var Ids= dt.AsEnumerable().Select(x => x.Field<string>("id")).ToList();
//使用聚合函数
string maxId = dt.AsEnumerable().Max(s => Convert.ToInt32(s["id"])).ToString();

//移除列
//dt.Columns.Remove("name");

//判断列名是否存在
bool columnExists = dataTable.Columns.Contains(columnName);

//获取Count
int count = dt.Rows.Count;
Console.WriteLine(count);

//遍历行
foreach (DataRow row in dt.Rows){}
foreach (DataRow row in dt.AsEnumerable())
{
    string id = row.Field<string>("id");
    string name = row["name"].ToString();

    //重新赋值
    row["name"]="newname";
}
for (int i = 0; i < dt.Rows.Count; i++)
{
    string id = dt.Rows[i].Field<string>("id");
    string name = dt.Rows[i]["name"].ToString();

    //Console.WriteLine(id);
    //Console.WriteLine(name);
}

//遍历列
foreach (DataColumn col in dt.Columns)
{
    //获取列的Type
    Console.WriteLine(col.DataType);
    //获取列的名称
    Console.WriteLine(col.ColumnName);
    if (col.ColumnName == "name")
    {
        //重命名列
        col.ColumnName = "name1";
    }
}

//判断是否是空值
row[columnName] == DBNull.Value || string.IsNullOrEmpty(row[columnName].ToString())
/// <summary>
/// 判断DataRow的某一列是是否为空
/// </summary>
/// <param name="row">DataRow</param>
/// <param name="columnName">列名</param>
/// <returns>true:为空;false:不为空</returns>
public static bool VerifyDataRowNullValue(DataRow row, string columnName)
{
	if (row == null || !row.Table.Columns.Contains(columnName))
	{
		return true;
	}
	return row[columnName] == DBNull.Value || string.IsNullOrEmpty(row[columnName].ToString());
}

//------------------------------------------------------
DataTable dt = new DataTable();
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("name", typeof(string));

// 添加另一行数据
dt.Rows.Add("1", "Jane");
dt.Rows.Add("1", "lucy");
dt.Rows.Add("2", "22");
dt.Rows.Add("2", "223");
dt.Rows.Add("3", "33");
dt.Rows.Add("3", "334");

//LINQ查询
var query = from row in dt.AsEnumerable()
         where int.Parse(row.Field<string>("id")) > 1
         select new {name= row.Field<string>("name") };
var  list=query.ToList();   

//id分组获取name集合
var dic = dt.AsEnumerable().GroupBy(row => row.Field<string>("id")).ToDictionary(k => k.Key, v => v.Select(row => row.Field<string>("name")).ToArray());
var name= dt.AsEnumerable().GroupBy(row => row.Field<string>("name")).OrderByDescending(group => group.Count()).Select(group => group.Key).First();

//转字典
Dictionary<string, string> dic = dt.AsEnumerable().ToDictionary(row => row["Id"].ToString(), row => row["Name"].ToString());

//克隆复制dt
//方法1
DataTable dt2= dt.Copy();
//方法2
var newdt = dt.Clone();
foreach (DataRow dr in dt.Rows)
{
    newdt.Rows.Add(dr.ItemArray);
}

//克隆复制列
DataRow row = dt.NewRow();
//方法1
row.ItemArray = dt.Rows[0].ItemArray.Clone() as object[];
//方法2
row.ItemArray = dt.AsEnumerable().First().ItemArray.Clone() as object[];

Select语法

基本比较运算符：

• =：等于
• <> 或 !=：不等于
• <：小于
• >：大于
• <=：小于等于
• >=：大于等于

逻辑运算符：

• AND：逻辑与
• OR：逻辑或
• NOT：逻辑非

函数和运算符：

• IS NULL：判断是否为 NULL
• IS NOT NULL：判断是否不为 NULL
• IN：判断值是否在指定范围内

常用语法

//字符串一定要加单引号
DataRow[] result = dataTable.Select("Gender = 'Male'");
DataRow[] result = dataTable.Select("Age > 30");
DataRow[] result = dataTable.Select("Age > 30 AND Gender = 'Male'");
//Like语法
DataRow[] result = dataTable.Select("Name LIKE 'John%'");
//判断非空
DataRow[] result = dataTable.Select("ColumnName IS NOT NULL");
//IN多值判断
DataRow[] result = dataTable.Select("ColumnName IN (value1, value2, value3)");

//Not语法
// 选择不满足 Age 大于等于 18 的行
DataRow[] result = dataTable.Select("NOT Age >= 18");

// 选择不满足 Name 以 "John" 开头的行
DataRow[] result = dataTable.Select("NOT Name LIKE 'John%'");

// 选择不满足 Age 大于 30 并且 Gender 为 "Male" 的行
DataRow[] result = dataTable.Select("NOT (Age > 30 AND Gender = 'Male')");

DataTable 转换 Json

Newtonsoft.Json

string json = JsonConvert.SerializeObject(dt, new DataTableConverter());

自定义

        /// <summary>
        /// DataTable转换JSON
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public string DataTableToJson(DataTable dt)
        {
            DataSet ds = new DataSet();
            ds.Merge(dt);
            StringBuilder JsonString = new StringBuilder();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                JsonString.Append("[");
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    JsonString.Append("{");
                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        if (j < ds.Tables[0].Columns.Count - 1)
                        {
                            JsonString.Append("\"" + ds.Tables[0].Columns[j].ColumnName.ToString() + "\":" + "\"" + ds.Tables[0].Rows[i][j].ToString() + "\",");
                        }
                        else if (j == ds.Tables[0].Columns.Count - 1)
                        {
                            JsonString.Append("\"" + ds.Tables[0].Columns[j].ColumnName.ToString() + "\":" + "\"" + ds.Tables[0].Rows[i][j].ToString() + "\"");
                        }
                    }
                    if (i == ds.Tables[0].Rows.Count - 1)
                    {
                        JsonString.Append("}");
                    }
                    else
                    {
                        JsonString.Append("},");
                    }
                }
                JsonString.Append("]");
                return JsonString.ToString();
            }
            else
            {
                return null;
            }
        }

Json 转换 DataTable

Newtonsoft.Json

string json = "[{\"ID\":\"1\",\"Name\":\"2\"}]";
DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, typeof(DataTable), new DataTableConverter());

List 转换 DataTable

Newtonsoft.Json

List<User> list = new List<User>();
string json = Newtonsoft.Json.JsonConvert.SerializeObject(list);
DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, typeof(DataTable), new DataTableConverter());

自定义

        /// <summary>
        /// List转换DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        public DataTable ToDataTable<T>(IList<T> data)
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
                table.Columns.Add(props[i].Name, props[i].PropertyType);

            object[] values = new object[props.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }
                table.Rows.Add(values);
            }
            return table;
        }

DataTable 转换 List

        public static List<T> DataTableToList<T>(DataTable dt) where T : class, new()
        {
            const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance;
            var objectProperties = typeof(T).GetProperties(flags);

            #region 修改dtColumn列名与实体类对应
            //foreach (DataColumn col in dt.Columns)
            //{
            //    var dtColumnName = col.ColumnName;
            //    string[] dtColumnNames = { dtColumnName, /*dtColumnName.Replace("_", "")*/ };
            //    foreach (PropertyInfo pi in objectProperties)
            //    {
            //        if (dtColumnNames.Contains(pi.Name, StringComparer.OrdinalIgnoreCase))
            //        {
            //            col.ColumnName = pi.Name;
            //        }
            //    }
            //}
            #endregion

            var columnNames = dt.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToList();
            var list = dt.AsEnumerable().Select(dataRow =>
            {
                var instanceOfT = Activator.CreateInstance<T>();
                var propertiesList = objectProperties.Where(properties => columnNames.Contains(properties.Name)
                && properties.CanWrite
                && dataRow[properties.Name] != null
                && dataRow[properties.Name] != DBNull.Value);
                foreach (var properties in propertiesList)
                {
                    var type = properties.PropertyType;
                    if (type.IsGenericType && type.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                    {
                        type = Nullable.GetUnderlyingType(type);
                    }
                    var value = Convert.ChangeType(dataRow[properties.Name], type);
                    //properties.SetValue(instanceOfT, dataRow[properties.Name], null);
                    properties.SetValue(instanceOfT, value, null);
                }
                return instanceOfT;
            }).ToList();
            return list;
        }