正文
DataTable,List，Dictonary互转，筛选及相关写法
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
1.创建自定义DataTable
/// 创建自定义DataTable(一) 根据列名字符串数组，
/// </summary>
/// <param name="sList">列名字符串数组</param>
/// <returns>DataTable</returns>
public static DataTable CreateSelfDataTable(String[] sList)
{
DataTable _dtSelf = new DataTable(); foreach (String s in sList)
{
_dtSelf.Columns.Add(s);
}
_dtSelf.AcceptChanges(); return _dtSelf;
} --创建自定义DataTable(二)
DataTable dt=new DataTable();
dt.Columns.AddRange(new[]
{
new DataColumn("col1",typeof(int)),
new DataColumn("col2",typeof(string)),
new DataColumn("col3",typeof(string))
}); dt.Rows.Add(,"","李小龙");
dt.Rows.Add(,"","猛龙过江");
2.DataTable 分组求count
foreach (DataRow dr in DT.Rows)
{
////求GSCount(根据ManageNo,Company分组，求每个出车编码下不同公司个数)(去除重复数据)
DataTable dtTemp =
DT.AsEnumerable().Cast<DataRow>().GroupBy(p => new { t1 = p.Field<string>("ManageNo"), t2 = p.Field<string>("Company") }).Select(p => p.FirstOrDefault()).CopyToDataTable(); var GSCount = from r in dtTemp.AsEnumerable()
where r.Field<string>("ManageNo") == dr["ManageNo"].ToStrValue()
group r by r.Field<string>("ManageNo") into m
select new
{
manageNo = m.Key,
mValue = m.Count()
};
var mGSCount = GSCount.First().mValue; ////求SamCount(根据ManageNo,Company分组求相同公司区间数)(不去重)
var SamCount = from r in DT.AsEnumerable()
group r by new { t1 = r.Field<string>("ManageNo"), t2 = r.Field<string>("Company") } into m
select new
{
manageNo = m.Key.t1,
company = m.Key.t2,
mValue = m.Count()
};
var mSamCount = SamCount.First().mValue; dr["ShareFee"] = GSCount.First().mValue;
}
DT.AcceptChanges();
lamber表达式分组,汇总
var dtGroups = dt.AsEnumerable().GroupBy(a => new { PackageId = a.Field<int>("PackageId")
, ProductCode = a.Field<string>("ProductCode"), ItemId = a.Field<int>("ItemId") });
foreach (var groupItem in dtGroups)
{
decimal TotalLockTax = groupItem.Where(a => a.Field<ulong>("IsInventoryLock") == ).Sum(a => a.Field<decimal>("ProductTax") * a.Field<int>("Quantity"));
int TotalLockProductQuantity = groupItem.Where(a => a.Field<ulong>("IsInventoryLock") == ).Sum(a => a.Field<int>("Quantity"));
decimal PayAmount = groupItem.Sum(a => a.Field<decimal>("PayAmount"));
}
3.Linq分组求count,sum,avg where等值
//分组求count
var mCount = from m in dt.AsEnumerable()
group m by m.Field<string>("col2") into n
select new
{
键 = n.Key,
值 = n.Count()
};
//带条件计数
var mCountWhere = from m in dt.AsEnumerable()
group m by m.Field<string>("col2") into n
select new
{
键 = n.Key,
值 = n.Count(j => j.Field<int>("col1") > )
}; //分组求Sum
var mSum = from m in dt.AsEnumerable()
group m by m.Field<string>("col2") into n
select new
{
键 = n.Key,
值 = n.Sum(j => j.Field<int>("col1"))
};
//分组求最大
var mMax = from m in dt.AsEnumerable()
group m by m.Field<string>("col2") into n
select new
{
键 = n.Key,
值 = n.Max(j => j.Field<int>("col1"))
};
//分组求平均值
var mAvg = from m in dt.AsEnumerable()
group m by new
{
键 = m.Field<int>("col1"),
值 = m.Field<string>("col2") } into n
select new
{
键 = n.Key,
值 = n.Average(j => j.Field<int>("col1"))
}; var mWhere = from m in dt.AsEnumerable()
group m by m.Field<string>("col2") into g
where g.Count() >
select new
{
g.Key,
g
}; foreach (var item in mWhere)
{
Console.WriteLine(item.Key+"");
}
4. List 和DataTable互转
var IdSource = dt.AsEnumerable().Select(e => e.Field<int>("id"));
/// <summary>
/// 将集合类转换成DataTable
/// </summary>
/// <param name="list">集合</param>
/// <returns></returns>
private static DataTable ToDataTableTow(IList list)
{
DataTable result = new DataTable();
if (list.Count > )
{
PropertyInfo[] propertys = list[].GetType().GetProperties(); foreach (PropertyInfo pi in propertys)
{
result.Columns.Add(pi.Name, pi.PropertyType);
}
foreach (object t in list)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
object obj = pi.GetValue(t, null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
/// <summary>
/// DataTable 转换为List 集合
/// </summary>
/// <typeparam name="TResult">类型</typeparam>
/// <param name="dt">DataTable</param>
/// <returns></returns>
public static List<T> ToList<T>(this DataTable dt) where T : class, new()
{
//创建一个属性的列表
List<PropertyInfo> prlist = new List<PropertyInfo>();
//获取TResult的类型实例 反射的入口 Type t = typeof(T); //获得TResult 的所有的Public 属性 并找出TResult属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表
Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -) prlist.Add(p); }); //创建返回的集合 List<T> oblist = new List<T>(); foreach (DataRow row in dt.Rows)
{
//创建TResult的实例
T ob = new T();
//找到对应的数据 并赋值
prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
//放入到返回的集合中.
oblist.Add(ob);
}
return oblist;
}
/// <summary>
/// 将泛类型集合List类转换成DataTable
/// </summary>
/// <param name="list">泛类型集合</param>
/// <returns></returns>
public static DataTable ListToDataTable<T>(List<T> entitys)
{
DataTable dt = new DataTable();
//检查实体集合不能为空
if (entitys == null || entitys.Count < )
{
return dt;
}
//取出第一个实体的所有Propertie
Type entityType = entitys[].GetType();
PropertyInfo[] entityProperties = entityType.GetProperties(); //生成DataTable的structure
//生产代码中，应将生成的DataTable结构Cache起来，此处略
for (int i = ; i < entityProperties.Length; i++)
{
dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
//dt.Columns.Add(entityProperties[i].Name);
}
//将所有entity添加到DataTable中
foreach (object entity in entitys)
{
//检查所有的的实体都为同一类型
if (entity.GetType() != entityType)
{
throw new Exception("要转换的集合元素类型不一致");
}
object[] entityValues = new object[entityProperties.Length];
for (int i = ; i < entityProperties.Length; i++)
{
entityValues[i] = entityProperties[i].GetValue(entity, null);
}
dt.Rows.Add(entityValues);
}
return dt;
}
5.lamber表达式汇总
var rows=dt.Rows.Cast<DataRow>();
var resultGroupByCol1=rows.GroupBy(r=>r.ItemArray[]);
var mCount=rows.Sum(r=>r.ItemArray.Count(cell=>cell=="")); int[] mArray=new int[]{,,,};
var nCount = mArray.Count(i =>i == ); 、DataTable筛选指定列,并是否去重复 string[] strCols = { "InNO", "ShortName", "ProductName", "SKU", "BoxNumber", "InStorageCount", "ReceiveTime", };
DataTable dt = AppDataSet.Tables[].DefaultView.ToTable("table1", false, strCols); DataRow[] rowsCopy = dt .Select("", "Sort Asc");
if (rowsCopy.Length == ) { return; }
dt = rowsCopy.CopyToDataTable(); 、DataTable汇总 dtCopy.Compute("Min(PID)", null) ;
遍历DataTable，并删除相关行
private void AddTreeHR(TreeNode pNode, string pid)
{
DataRow[] rows = dtCopy.Select("PID=" + pid);
foreach (DataRow rowf in rows)
{
string deptName = (rowf["DeptName"] + string.Empty); TreeNode node = new TreeNode();
node.Tag = rowf["UniqueID"] + string.Empty;
pNode.Nodes.Add(node);
node.Text = deptName;
AddTreeHR(node, rowf["UniqueID"] + string.Empty); //递归添加子节点 dtCopy.Rows.Remove(rowf);
dtCopy.AcceptChanges();
}
} 、 数组匹配指定行退出
private string GetProvinceText(string province)
{
string[] mProvince = { "安徽省", "北京", "重庆", "福建省", "甘肃省", "广西", "广东省", "贵州省", "河北省", "河南省", "海南省", "湖北省", "湖南省", "黑龙江省", "吉林省", "江西省", "江苏省", "宁夏", "内蒙古", "辽宁省", "青海省", "上海", "山西省", "山东省", "四川省", "陕西省", "天津", "西藏", "新疆", "云南省", "浙江省" };
int index = -;
for (int i = ; i < mProvince.Length; i++)
{
var item = mProvince[i];
if (item.ToString().Contains(province) && !string.IsNullOrEmpty(province))
{
index = i;
break;
}
}
return index==-?"":mProvince[index];
} 、dtatatable筛选是否包含 if (AppDataSet.Tables["TA_LogisticsPZDetail"].Select("len(Col_356) >0").Length > )
{
datarow["Col_267"] = "是";
}
6、
DataTable转DataView筛选不包含的
DataView dvTemp = ds2.Tables[0].DefaultView;
if (cob_col089.Text.StringEqualsForSimplified("是"))
{
dvTemp.RowFilter = "col_089 LIKE '%回单%' ";
}
else if (cob_col089.Text.StringEqualsForSimplified("否"))
{
dvTemp.RowFilter = " ISNULL(col_089,'') NOT LIKE '%回单%' ";
7、DataSet添加table
foreach (DataRow row in ds3.Tables[0].Rows)
{
DataSet.Tables[0].Rows.Add(row["DD_111"], row["DD_003"], row["DD_014"], row["DD_018"], time, row["DD_004"], 1, row["DD_002"]);
}
8、DataTabe指定列顺序
TempDataTable.Columns["Col_133"].SetOrdinal(31);
9、DataTable转DataView筛选不包含的
DataView dvTemp = ds2.Tables[0].DefaultView;
if (cob_col089.Text.StringEqualsForSimplified("否"))
{
dvTemp.RowFilter = " ISNULL(col_089,'') NOT LIKE '%回单%' ";
}
10、
数组判断一个元素是否在数组中存在
string[] mArray = { "V","B","C","AB","H"};
if(mArray.Contains("A"))
{
Console.WriteLine("存在");
}
或
mArray.Count(X => X == "A")>0
11、
DataTable转List，List转数组,LinQ求数组中重复的个数
List<string> listCZDepart =dt.AsEnumerable().Select(d=>d.Field<string>("CZDepart")).ToList();
string[] arrCZDepart = string.Join("-", listCZDepart.ToArray()).Split('-');
var res = from n in arrCZDepart
group n by n into g
where g.Count() > 1
select g;
if (res != null && res.Count()>=1)
{
var gr = res.First();
if (gr != null && gr.Count() >= 1)
{
MessageBoxShow("输入的名称:'" + gr.Key + "'已存在");
return false;
}
}
12、
将DataTable中的某列转换成数组或者List
using System.Linq;
string[] arrRate = dtRate.AsEnumerable().Select(d => d.Field<string>("arry")).ToArray(); List<string> litRate = dtRate.AsEnumerable().Select(d => d.Field<string>("arry")).ToList();
通过数组获取此行所有值
DataRow newDr = dr.Table.NewRow();
newDr.ItemArray = dr.ItemArray; 、
从DataTable筛选指定数量的数据，赋值到另外一个表，并从当前DataTable中删除
private DataTable GetQPRows(DataTable dt, int increaseCount)
{
int currentRow = dt.Rows.Count;
if (currentRow == ) return null;
DataTable dtTemp = dt.Clone();
try
{
if (currentRow < increaseCount) increaseCount = currentRow;
for (int i = ; i < increaseCount; i++)
{
dtTemp.ImportRow(dt.Rows[i]);
}
dtTemp.AcceptChanges(); for (int i = ; i < dtTemp.Rows.Count; i++)
{
dt.Rows[i].Delete();
}
dt.AcceptChanges();
}
catch (Exception)
{ return null;
} return dtTemp;
}
14、键值对排序
14.1
var dicSort = from objDic in Dic orderby objDic.Value ascending select objDic;
foreach (var item in Dic.OrderByDescending(i=>i.Value).ThenBy(i=>i.Key))
{
NVoice += item.Value+" ";
}
14.2 ToDictionary用法:
{"":[{"id":,"name":"2段"},{"id":,"name":""}]}
List<T>转Dictionary<T,T>
. ToDictionary “一对一”的关系
paramExt.SeletedProps.GroupBy(e=>e.NameID).ToDictionary(e=>e.Key,e=>e.ToList())
.ToLookup “一对多”的关系
var dic = ticketlist.ToLookup(i => i.OrderID);
foreach (var item in dic)
{
Console.WriteLine("订单号:" + item.Key);
foreach (var item1 in item)
{
Console.WriteLine("\t\t" + item1.TicketNo + " " + item1.Description);
}
}
