转自 [http://www.huiyaosoft.com/html/datatablelinqquery.htm](http://www.huiyaosoft.com/html/datatablelinqquery.htm)<br />本章节主要讲解DataTable的查询、统计汇总功能。

一、构造DataGridView

C# Code

1. region 构造DGV

2. DataGridViewTextBoxColumn textCol = new DataGridViewTextBoxColumn();
3. textCol.HeaderText = “Name”;
4. textCol.Width = 100;
5. textCol.DataPropertyName = “name”;
6. this.dataGridView1.Columns.Add(textCol);
7. textCol = new DataGridViewTextBoxColumn();
8. textCol.HeaderText = “Quantity”;
9. textCol.Width = 100;
10. textCol.DataPropertyName = “quantity”;
11. this.dataGridView1.Columns.Add(textCol);

12. endregion
    

    构造DataTable，并给DataGridView赋值
    C# Code

13. region 构造数据表

14. dt = new DataTable(“chart”);
15. DataColumn col = new DataColumn();
16. col.ColumnName = “name”;
17. col.DataType = typeof(System.String);
18. dt.Columns.Add(col);
19. col = new DataColumn();
20. col.ColumnName = “quantity”;
21. col.DataType = typeof(System.Int32);
22. dt.Columns.Add(col);
23. Random rand = new Random();
24. DataRow row = dt.NewRow();
25. row[“name”] = “青岛”;
26. row[“quantity”] = rand.Next(1,100);
27. dt.Rows.Add(row);
28. row = dt.NewRow();
29. row[“name”] = “武汉”;
30. row[“quantity”] = rand.Next(1, 100);
31. dt.Rows.Add(row);
32. row = dt.NewRow();
33. row[“name”] = “武汉经济技术开发区”;
34. row[“quantity”] = rand.Next(1, 100);
35. dt.Rows.Add(row);
36. row = dt.NewRow();
37. row[“name”] = “武汉”;
38. row[“quantity”] = rand.Next(1, 100);
39. dt.Rows.Add(row);
40. row = dt.NewRow();
41. row[“name”] = “北京”;
42. row[“quantity”] = rand.Next(1, 100);
43. dt.Rows.Add(row);
44. row = dt.NewRow();
45. row[“name”] = “深圳”;
46. row[“quantity”] = rand.Next(1, 100);
47. dt.Rows.Add(row);
48. row = dt.NewRow();
49. row[“name”] = “上海”;
50. row[“quantity”] = rand.Next(1, 100);
51. dt.Rows.Add(row);
52. row = dt.NewRow();
53. row[“name”] = “广州”;
54. row[“quantity”] = rand.Next(1, 100);
55. dt.Rows.Add(row);
56. row = dt.NewRow();
57. row[“name”] = “成都”;
58. row[“quantity”] = rand.Next(1, 100);
59. dt.Rows.Add(row);
60. row = dt.NewRow();
61. row[“name”] = “重庆”;
62. row[“quantity”] = rand.Next(1, 100);
63. dt.Rows.Add(row);
64. row = dt.NewRow();
65. row[“name”] = “宁波”;
66. row[“quantity”] = rand.Next(1, 100);
67. dt.Rows.Add(row);
68. row = dt.NewRow();
69. row[“name”] = “南京”;
70. row[“quantity”] = rand.Next(1, 100);
71. dt.Rows.Add(row);

72. endregion

73. this.dataGridView1.DataSource = dt;
    DataTable Select查询
    C# Code
74. DataRow[] drArr = dt.Select(“name=’武汉’”);
75. DataTable dtNew = dt.Clone();
76. for (int i = 0; i < drArr.Length; i++)

77. {

    dtNew.Rows.Add(drArr[i].ItemArray);
    

78. }

79. this.dataGridView1.DataSource = dtNew;

    二、DataTable Select 模糊查询

    C# Code

80. DataRow[] drArr = dt.Select(“name like ‘%北京%’”);

81. DataTable dtNew = dt.Clone();
82. for (int i = 0; i < drArr.Length; i++)

83. {

    dtNew.ImportRow(drArr[i]);
    

84. }

85. this.dataGridView1.DataSource = dtNew;
    DataTable 显示某个状态的记录行
    C# Code
86. DataRow[] drArr = dt.Select(“name like ‘%%’”, “quantity”, DataViewRowState.Added);
87. DataTable dtNew = dt.Clone();
88. for (int i = 0; i < drArr.Length; i++)

89. {

    dtNew.ImportRow(drArr[i]);
    

90. }

91. this.dataGridView1.DataSource = dtNew;

    三、DataTable 多重条件查询，排序

    C# Code

92. DataRow[] drArr = dt.Select(“name like ‘%%’ or (quantity > 10 and quantity < 50)”, “quantity desc”, DataViewRowState.Added);

93. DataTable dtNew = dt.Clone();
94. for (int i = 0; i < drArr.Length; i++)

95. {

    dtNew.ImportRow(drArr[i]);
    

96. }

97. this.dataGridView1.DataSource = dtNew;
    DataTable Linq 查询
    C# Code

98. var query = from t in dt.AsEnumerable()

        where t.Field<int>("quantity") > 50
    

        orderby t.Field<int> ("quantity") descending
    

        select new tt
    

        {
    

            Name = t.Field<string>("name"),
    

            Quantity = t.Field<int>("quantity")
    

        };
    

99. this.dataGridView1.DataSource = query.ToList();
    C# Code

100. var query = from t in dt.AsEnumerable()

         where t.Field<int>("quantity") > 50
     

         orderby t.Field<int>("quantity") descending
     

         select new
     

         {
     

             Name = t.Field<string>("name"),
     

             Quantity = t.Field<int>("quantity")
     

         };
     

101. this.dataGridView1.DataSource = query.ToList();
     C# Code

102. var query = from t in dt.AsEnumerable()

         //where t.Field<int>("quantity") > 10
     

         orderby new { t1 = t.Field<string>("name"), t2 = t.Field<int>("quantity") } descending
     

         select new tt
     

         {
     

             Name = t.Field<string>("name"),
     

             Quantity = t.Field<int>("quantity")
     

         };
     

103. this.dataGridView1.DataSource = query.Where(p => p.Quantity > 10).ToList();
     

四、DataTable Linq group by 查询

C# Code

1. var query = from t in dt.AsEnumerable()

       group t by t.Field<string>("name") into m
   

       select new
   

       {
   

           name = m.Key,
   

           quantity = m.Sum(n => n.Field<int?>("quantity"))
   

       };
   

2. this.dataGridView1.DataSource = query.ToList();
   C# Code

3. var query = from t in dt.AsEnumerable()

       group t by new { t1 = t.Field<string>("name"), t2 = t.Field<string>("name") } into m
   

       select new
   

       {
   

           name = m.Key.t2,
   

           quantity = m.Sum(n => n.Field<int?>("quantity"))
   

       };
   

4. this.dataGridView1.DataSource = query.ToList();
   

五、DataTable Linq Max,Min,Sum,Avg计算

C# Code

1. decimal? query = (from t in dt.AsEnumerable()

             select t.Field<decimal>("quantity")).Sum();
   

2. decimal? query = (from t in dt.AsEnumerable()

             select t.Field<decimal>("quantity")).Max();
   

3. decimal? query = (from t in dt.AsEnumerable()

             select t.Field<decimal>("quantity")).Min();
   

4. decimal? query = (from t in dt.AsEnumerable()

             select t.Field<decimal>("quantity")).Count();
   

5. string exp = “name like ‘武汉%’”;

6. Console.WriteLine(“max:{0},avg:{1},min:{2},count:{3}”

   , dt.Compute("max(quantity)", exp).ToString()
   

   , dt.Compute("avg(quantity)", exp).ToString()
   

   , dt.Compute("min(quantity)", exp).ToString()
   

   , dt.Compute("count(quantity)", exp).ToString());
   

六、DataTable Count,Sum,Avg,Min,Max计算

C# Code

1. Console.WriteLine(“count:{0},sum:{1},avg:{2},min:{3},max:{4}”

   , dt.Compute("count(name)", null).ToString()
   

   , dt.Compute("sum(quantity)", null).ToString()
   

   , dt.Compute("avg(quantity)", null).ToString()
   

   , dt.Compute("min(quantity)", null).ToString()
   

   , dt.Compute("max(quantity)", null).ToString());
   

七、DataTable Expression表达式，列之间可运算

C# Code

1. DataColumn col = new DataColumn();
2. col.ColumnName = “ll”;
3. col.DataType = typeof(System.Int32);
4. dt.Columns.Add(col);
5. col.Expression = “len(name)”;
6. this.dataGridView1.DataSource = dt;