1 public partial class Form4 : Form
2 {
3 public Form4()
4 {
5 InitializeComponent();
6 }
7
8 private void button1_Click(object sender, EventArgs e)
9 {
10 //创建DataTable
11 DataTable dt = new DataTable();
12 dt.TableName = "TestDB";
13 dt.Columns.Add("ID", typeof(System.Int32));
14 dt.Columns.Add("Name", typeof(System.String));
15 dt.Columns.Add("Pwd", typeof(System.String));
16 dt.Columns.Add("Age", typeof(System.Int32));
17 dt.Rows.Add(1, "PC", "123456", 26);
18 dt.Rows.Add(2, "SHY", "7777777", 27);
19 dt.Rows.Add(3, "SZY", "333333", 28);
20 //
21 DataTable dt1 = new DataTable();
22 dt1.TableName = "TestDB";
23 dt1.Columns.Add("ID", typeof(System.Int32));
24 dt1.Columns.Add("Name", typeof(System.String));
25 dt1.Columns.Add("Pwd", typeof(System.String));
26 dt1.Columns.Add("Age", typeof(System.Int32));
27 dt1.Rows.Add(1, "PC1", "123456", 26);
28 dt1.Rows.Add(2, "SHY", "777777", 27);
29 dt1.Rows.Add(3, "SZY", "333333", 28);
30
31 //单子段Groupby
32 var query = from d in dt.AsEnumerable()
33 group d by d.Field<string>("Name") into m
34 select new
35 {
36 Name = m.Key,
37 Pwd = m.First().Field<string>("Pwd"),
38 Age = m.First().Field<Int32>("Age"),
39 Count = m.Count()
40 };
41
42 //多子段GroupBy
43 var query1 = from d in dt.AsEnumerable()
44 group d by new { t1 = d.Field<string>("Name"), t2 = d.Field<string>("Pwd") } into m
45 orderby m.Key.t1
46 select new
47 {
48 Name = m.Key.t1,
49 Pwd = m.Key.t2,
50 Age = m.First().Field<Int32>("Age"),
51 Count = m.Count()
52 };
53 //orderby 单个子段
54 var query2 = from d in dt.AsEnumerable()
55 where d.Field<Int32>("Age") > 28
56 orderby d.Field<string>("Name") descending
57 select d;
58 //orderby 多个子段
59 var query11 = from d in dt.AsEnumerable()
60 where d.Field<Int32>("Age") > 28
61 orderby d.Field<string>("Name") descending, d.Field<Int32>("Age") descending
62 select d;
63 //DataTable dt2 = query2.CopyToDataTable<DataRow>();
64 var query4 = query.Skip(2); //直接跳到某条，开始读取数据
65 var query5 = query.Take(10); //读取前几条数据
66 var query6 = query4.Intersect(query5); //取两个集合的交集
67 var query7 = query4.Except(query5); //取两个集合的差集
68
69 //查询数据集 转换成 DataTable
70 DataTable dt4 = dt.Select("").CopyToDataTable<DataRow>(); //
71
72 //编辑查询后的结果
73 var query3 = from d in dt.AsEnumerable()
74 where d.Field<Int32>("Age")>28
75 orderby d.Field<string>("Name") descending
76 select new
77 {
78 ID = d.Field<Int32>("ID"),
79 Name = d.Field<string>("Name"),
80 Pwd = d.Field<string>("Pwd"),
81 Age = d.Field<Int32>("Age")+100
82 };
83 query3 = query3.OrderBy(x => x.Age);
84 foreach (var i in query3)
85 {
86 Console.WriteLine(i.ID + "-" + i.Name + "-" + i.Pwd + "-" + i.Age);
87 }
88
89 //两个DataTable 取 差集
90 List<DataRow> lsta = new List<DataRow>();
91 foreach (DataRow r in dt.Rows)
92 lsta.Add(r);
93 List<DataRow> lstb = new List<DataRow>();
94 foreach (DataRow r in dt1.Rows)
95 lstb.Add(r);
96 //Except方法，a.Except(b) a不在b的范围内的数据 和 b.Except(a) b不在a的范围内的数据 得到的结果不同
97 //这里需要注意，取差集的时候，使用到了IEqualityCompare<DataRow>,用一个类继承了这个接口
98 var v = lstb.Except(lsta, new C());
99
100 DataTable dt3 = dt.Clone();
101 foreach (DataRow r in v.ToList<DataRow>())
102 dt3.ImportRow(r);
103
104 this.dataGridView1.DataSource = dt3;
105
106 //--------------------------------------------------------------------------
107
108 string[,] infoArr = new string[,] { { "1", "百度", "baidu", "201303" },
109 { "2", "迅雷", "xunlei", "201302" },
110 { "3", "谷歌", "guge", "201301" } };
111
112 DataRow row;
113 ClientStruct cs = new ClientStruct();
114 DataTable dtTable = new DataTable();
115 dtTable.Columns.Add(cs.ID);
116 dtTable.Columns.Add(cs.Name);
117 dtTable.Columns.Add(cs.Company);
118 dtTable.Columns.Add(cs.CreatedDate);
119 for (int i = 0; i < 3; i++)
120 {
121 row = dtTable.NewRow();
122 row[cs.ID] = infoArr[i, 0];
123 row[cs.Name] = infoArr[i, 1];
124 row[cs.Company] = infoArr[i, 2];
125 row[cs.CreatedDate] = infoArr[i, 3];
126 dtTable.Rows.Add(row);
127 }
128 //遍历DataTable，取出所有的ID
129 List<string> lstID = (from d in dtTable.AsEnumerable()
130 select d.Field<string>(cs.ID)).ToList<string>();
131
132 //DataTable 转换成 泛型集合
133 //遍历DataTable，将其中的数据对应到ClientStruct中：
134 List<ClientStruct> list = (from x in dtTable.AsEnumerable()
135 orderby x.Field<string>(cs.Company)
136 select new ClientStruct
137 {
138 ID = x.Field<string>(cs.ID),
139 Name = x.Field<string>(cs.Name),
140 Company = x.Field<string>(cs.Company),
141 CreatedDate = x.Field<string>(cs.CreatedDate)
142 }).ToList<ClientStruct>();
143
144 //遍历DataTable，并将上面的List结果存储到Dictionary中：
145 Dictionary<string, ClientStruct> dic = list.ToDictionary(p => p.Company);
146 //p作为string键值来存储
147 }
148 }
149
150 class C : IEqualityComparer<DataRow>
151 {
152
153 string col = "Name";//比较哪个字段
154 string col1 = "Pwd";
155 public bool Equals(DataRow x, DataRow y)
156 {
157
158 return x[col]==y[col] && x[col1]==y[col1];
159 }
160
161 public int GetHashCode(DataRow obj)
162 {
163 return obj.ToString().GetHashCode();
164 }
165 }
166
167 class ClientStruct
168 {
169 public string ID = "ID";
170 public string Name = "Name";
171 public string Company = "Company";
172 public string CreatedDate = "CreatedDate";
173 }
浙公网安备 33010602011771号