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