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(1,"001","李小龙");
dt.Rows.Add(2,"002","猛龙过江");

 

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") == 1).Sum(a => a.Field<decimal>("ProductTax") * a.Field<int>("Quantity"));
    int TotalLockProductQuantity = groupItem.Where(a => a.Field<ulong>("IsInventoryLock") == 1).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") > 1)
};

//分组求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() > 1
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 > 0)
{
PropertyInfo[] propertys = list[0].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) != -1) 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 < 1)
            {
                return dt;
            }
            //取出第一个实体的所有Propertie
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            //生成DataTable的structure
            //生产代码中，应将生成的DataTable结构Cache起来，此处略
            for (int i = 0; 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 = 0; 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[0]);
var mCount=rows.Sum(r=>r.ItemArray.Count(cell=>cell=="1"));

int[] mArray=new int[]{1,3,4,1};
var nCount = mArray.Count(i =>i == 1);

1、DataTable筛选指定列,并是否去重复

string[] strCols = { "InNO", "ShortName", "ProductName", "SKU", "BoxNumber", "InStorageCount", "ReceiveTime", };
DataTable dt = AppDataSet.Tables[0].DefaultView.ToTable("table1", false, strCols);

DataRow[] rowsCopy = dt .Select("", "Sort Asc");
if (rowsCopy.Length == 0) { return; }
dt = rowsCopy.CopyToDataTable();

2、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();
}
}

3、

数组匹配指定行退出
private string GetProvinceText(string province)
{
string[] mProvince = { "安徽省", "北京", "重庆", "福建省", "甘肃省", "广西", "广东省", "贵州省", "河北省", "河南省", "海南省", "湖北省", "湖南省", "黑龙江省", "吉林省", "江西省", "江苏省", "宁夏", "内蒙古", "辽宁省", "青海省", "上海", "山西省", "山东省", "四川省", "陕西省", "天津", "西藏", "新疆", "云南省", "浙江省" };
int index = -1;
for (int i = 0; i < mProvince.Length; i++)
{
var item = mProvince[i];
if (item.ToString().Contains(province) && !string.IsNullOrEmpty(province))
{
index = i;
break;
}
}
return index==-1?"":mProvince[index];
}


4、dtatatable筛选是否包含

if (AppDataSet.Tables["TA_LogisticsPZDetail"].Select("len(Col_356) >0").Length > 0)
{
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;

13、
从DataTable筛选指定数量的数据，赋值到另外一个表，并从当前DataTable中删除
private DataTable GetQPRows(DataTable dt, int increaseCount)
{
int currentRow = dt.Rows.Count;
if (currentRow == 0) return null;
DataTable dtTemp = dt.Clone();
try
{
if (currentRow < increaseCount) increaseCount = currentRow;
for (int i = 0; i < increaseCount; i++)
{
dtTemp.ImportRow(dt.Rows[i]);
}
dtTemp.AcceptChanges();

for (int i = 0; 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用法:

{"942":[{"id":942,"name":"2段"},{"id":942,"name":"2"}]}
 List<T>转Dictionary<T,T>
 
 1. ToDictionary “一对一”的关系
    paramExt.SeletedProps.GroupBy(e=>e.NameID).ToDictionary(e=>e.Key,e=>e.ToList())
    
 2.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);
                }
            }