1
using
System;
2
using
System.Collections;
3
using
System.Data;
4
5
namespace
Common
6
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
7![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
8
/// DataSet助手
9
/// </summary>
10
public class DataSetHelper
11![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
12
private class FieldInfo
13![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
14
public string RelationName;
15
public string FieldName;
16
public string FieldAlias;
17
public string Aggregate;
18
}
19
20
private DataSet ds;
21
private ArrayList m_FieldInfo;
22
private string m_FieldList;
23
private ArrayList GroupByFieldInfo;
24
private string GroupByFieldList;
25
26
public DataSet DataSet
27![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
28![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
get
{ return ds; }
29
}
30
31![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
构造方法#region 构造方法
32
33
public DataSetHelper()
34![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
35
ds = null;
36
}
37
38
public DataSetHelper(ref DataSet dataSet)
39![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
40
ds = dataSet;
41
}
42
43
#endregion
44
45![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
私有方法#region 私有方法
46
47![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
48
/// 比较两列
49
/// </summary>
50
/// <param name="objectA"></param>
51
/// <param name="objectB"></param>
52
/// <returns></returns>
53
private bool ColumnEqual(object objectA, object objectB)
54![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
55
if ( objectA == DBNull.Value && objectB == DBNull.Value )
56![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
57
return true;
58
}
59
if ( objectA == DBNull.Value || objectB == DBNull.Value )
60![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
61
return false;
62
}
63
return ( objectA.Equals( objectB ) );
64
}
65
66![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
67
/// 比较两行
68
/// </summary>
69
/// <param name="rowA">A表的行</param>
70
/// <param name="rowB">B表的行</param>
71
/// <param name="columns">所对应的列</param>
72
/// <returns></returns>
73
private bool RowEqual(DataRow rowA, DataRow rowB, DataColumnCollection columns)
74![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
75
bool result = true;
76
for ( int i = 0; i < columns.Count; i++ )
77![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
78
result &= ColumnEqual( rowA[ columns[ i ].ColumnName ], rowB[ columns[ i ].ColumnName ] );
79
}
80
return result;
81
}
82
83![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
84
/// 暂时不知道
85
/// </summary>
86
/// <param name="fieldList"></param>
87
/// <param name="allowRelation"></param>
88
private void ParseFieldList(string fieldList, bool allowRelation)
89![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
90
if ( m_FieldList == fieldList )
91![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
92
return;
93
}
94
m_FieldInfo = new ArrayList();
95
m_FieldList = fieldList;
96
FieldInfo Field;
97
string[] FieldParts;
98
string[] Fields = fieldList.Split( ',' );
99
for ( int i = 0; i <= Fields.Length - 1; i++ )
100![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
101
Field = new FieldInfo();
102
FieldParts = Fields[ i ].Trim().Split( ' ' );
103
switch ( FieldParts.Length )
104![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
105
case 1:
106
//to be set at the end of the loop
107
break;
108
case 2:
109
Field.FieldAlias = FieldParts[ 1 ];
110
break;
111
default:
112
return;
113
}
114
FieldParts = FieldParts[ 0 ].Split( '.' );
115
switch ( FieldParts.Length )
116![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
117
case 1:
118
Field.FieldName = FieldParts[ 0 ];
119
break;
120
case 2:
121
if ( allowRelation == false )
122![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
123
return;
124
}
125
Field.RelationName = FieldParts[ 0 ].Trim();
126
Field.FieldName = FieldParts[ 1 ].Trim();
127
break;
128
default:
129
return;
130
}
131
if ( Field.FieldAlias == null )
132![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
133
Field.FieldAlias = Field.FieldName;
134
}
135
m_FieldInfo.Add( Field );
136
}
137
}
138
139![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
140
/// 创建DataTable
141
/// </summary>
142
/// <param name="tableName">表名</param>
143
/// <param name="sourceTable">源表</param>
144
/// <param name="fieldList"></param>
145
/// <returns></returns>
146
private DataTable CreateTable(string tableName, DataTable sourceTable, string fieldList)
147![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
148
DataTable dt;
149
if ( fieldList.Trim() == "" )
150![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
151
dt = sourceTable.Clone();
152
dt.TableName = tableName;
153
}
154
else
155![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
156
dt = new DataTable( tableName );
157
ParseFieldList( fieldList, false );
158
DataColumn dc;
159
foreach ( FieldInfo Field in m_FieldInfo )
160![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
161
dc = sourceTable.Columns[ Field.FieldName ];
162
DataColumn column = new DataColumn();
163
column.ColumnName = Field.FieldAlias;
164
column.DataType = dc.DataType;
165
column.MaxLength = dc.MaxLength;
166
column.Expression = dc.Expression;
167
dt.Columns.Add( column );
168
}
169
}
170
if ( ds != null )
171![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
172
ds.Tables.Add( dt );
173
}
174
return dt;
175
}
176
177![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
178
/// 插入表
179
/// </summary>
180
/// <param name="destTable">DataTable</param>
181
/// <param name="sourceTable">源DataTable</param>
182
/// <param name="fieldList"></param>
183
/// <param name="rowFilter"></param>
184
/// <param name="sort"></param>
185
private void InsertInto(DataTable destTable, DataTable sourceTable,
186
string fieldList, string rowFilter, string sort)
187![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
188
ParseFieldList( fieldList, false );
189
DataRow[] rows = sourceTable.Select( rowFilter, sort );
190
DataRow destRow;
191
foreach ( DataRow sourceRow in rows )
192![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
193
destRow = destTable.NewRow();
194
if ( fieldList == "" )
195![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
196
foreach ( DataColumn dc in destRow.Table.Columns )
197![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
198
if ( dc.Expression == "" )
199![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
200
destRow[ dc ] = sourceRow[ dc.ColumnName ];
201
}
202
}
203
}
204
else
205![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
206
foreach ( FieldInfo field in m_FieldInfo )
207![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
208
destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ];
209
}
210
}
211
destTable.Rows.Add( destRow );
212
}
213
}
214
215![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
216
/// 暂时不知道
217
/// </summary>
218
/// <param name="FieldList"></param>
219
private void ParseGroupByFieldList(string FieldList)
220![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
221
if ( GroupByFieldList == FieldList )
222![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
223
return;
224
}
225
GroupByFieldInfo = new ArrayList();
226
FieldInfo Field;
227
string[] FieldParts;
228
string[] Fields = FieldList.Split( ',' );
229
for ( int i = 0; i <= Fields.Length - 1; i++ )
230![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
231
Field = new FieldInfo();
232
FieldParts = Fields[ i ].Trim().Split( ' ' );
233
switch ( FieldParts.Length )
234![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
235
case 1:
236
//to be set at the end of the loop
237
break;
238
case 2:
239
Field.FieldAlias = FieldParts[ 1 ];
240
break;
241
default:
242
return;
243
}
244
245
FieldParts = FieldParts[ 0 ].Split( '(' );
246
switch ( FieldParts.Length )
247![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
248
case 1:
249
Field.FieldName = FieldParts[ 0 ];
250
break;
251
case 2:
252
Field.Aggregate = FieldParts[ 0 ].Trim().ToLower();
253
Field.FieldName = FieldParts[ 1 ].Trim( ' ', ')' );
254
break;
255
default:
256
return;
257
}
258
if ( Field.FieldAlias == null )
259![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
260
if ( Field.Aggregate == null )
261![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
262
Field.FieldAlias = Field.FieldName;
263
}
264
else
265![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
266
Field.FieldAlias = Field.Aggregate + "of" + Field.FieldName;
267
}
268
}
269
GroupByFieldInfo.Add( Field );
270
}
271
GroupByFieldList = FieldList;
272
}
273
274![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
275
/// 创建一个分组DataTable
276
/// </summary>
277
/// <param name="tableName">表名</param>
278
/// <param name="sourceTable">DataTable</param>
279
/// <param name="fieldList">分组字段</param>
280
/// <returns></returns>
281
private DataTable CreateGroupByTable(string tableName, DataTable sourceTable, string fieldList)
282![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
283
if ( fieldList == null || fieldList.Length == 0 )
284![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
285
return sourceTable.Clone();
286
}
287
else
288![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
289
DataTable dt = new DataTable( tableName );
290
ParseGroupByFieldList( fieldList );
291
foreach ( FieldInfo Field in GroupByFieldInfo )
292![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
293
DataColumn dc = sourceTable.Columns[ Field.FieldName ];
294
if ( Field.Aggregate == null )
295![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
296
dt.Columns.Add( Field.FieldAlias, dc.DataType, dc.Expression );
297
}
298
else
299![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
300
dt.Columns.Add( Field.FieldAlias, dc.DataType );
301
}
302
}
303
if ( ds != null )
304![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
305
ds.Tables.Add( dt );
306
}
307
return dt;
308
}
309
}
310
311
private void InsertGroupByInto(DataTable destTable, DataTable sourceTable, string fieldList,
312
string rowFilter, string groupBy)
313![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
314
if ( fieldList == null || fieldList.Length == 0 )
315![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
316
return;
317
}
318
ParseGroupByFieldList( fieldList );
319
ParseFieldList( groupBy, false );
320
DataRow[] rows = sourceTable.Select( rowFilter, groupBy );
321
DataRow lastSourceRow = null, destRow = null;
322
bool sameRow;
323
int rowCount = 0;
324
foreach ( DataRow sourceRow in rows )
325![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
326
sameRow = false;
327
if ( lastSourceRow != null )
328![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
329
sameRow = true;
330
foreach ( FieldInfo Field in m_FieldInfo )
331![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
332
if ( !ColumnEqual( lastSourceRow[ Field.FieldName ], sourceRow[ Field.FieldName ] ) )
333![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
334
sameRow = false;
335
break;
336
}
337
}
338
if ( !sameRow )
339![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
340
destTable.Rows.Add( destRow );
341
}
342
}
343
if ( !sameRow )
344![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
345
destRow = destTable.NewRow();
346
rowCount = 0;
347
}
348
rowCount += 1;
349
foreach ( FieldInfo field in GroupByFieldInfo )
350![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
351
switch ( field.Aggregate.ToLower() )
352![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
353
case null:
354
case "":
355
case "last":
356
destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ];
357
break;
358
case "first":
359
if ( rowCount == 1 )
360![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
361
destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ];
362
}
363
break;
364
case "count":
365
destRow[ field.FieldAlias ] = rowCount;
366
break;
367
case "sum":
368
destRow[ field.FieldAlias ] = Add( destRow[ field.FieldAlias ], sourceRow[ field.FieldName ] );
369
break;
370
case "max":
371
destRow[ field.FieldAlias ] = Max( destRow[ field.FieldAlias ], sourceRow[ field.FieldName ] );
372
break;
373
case "min":
374
if ( rowCount == 1 )
375![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
376
destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ];
377
}
378
else
379![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
380
destRow[ field.FieldAlias ] = Min( destRow[ field.FieldAlias ], sourceRow[ field.FieldName ] );
381
}
382
break;
383
}
384
}
385
lastSourceRow = sourceRow;
386
}
387
if ( destRow != null )
388![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
389
destTable.Rows.Add( destRow );
390
}
391
}
392
393
private object Min(object a, object b)
394![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
395
if ( ( a is DBNull ) || ( b is DBNull ) )
396![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
397
return DBNull.Value;
398
}
399
if ( ( (IComparable) a ).CompareTo( b ) == -1 )
400![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
401
return a;
402
}
403
else
404![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
405
return b;
406
}
407
}
408
409
private object Max(object a, object b)
410![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
411
if ( a is DBNull )
412![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
413
return b;
414
}
415
if ( b is DBNull )
416![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
417
return a;
418
}
419
if ( ( (IComparable) a ).CompareTo( b ) == 1 )
420![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
421
return a;
422
}
423
else
424![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
425
return b;
426
}
427
}
428
429
private object Add(object a, object b)
430![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
431
if ( a is DBNull )
432![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
433
return b;
434
}
435
if ( b is DBNull )
436![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
437
return a;
438
}
439
return ( (decimal) a + (decimal) b );
440
}
441
442
private DataTable CreateJoinTable(string tableName, DataTable sourceTable, string fieldList)
443![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
444
if ( fieldList == null )
445![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
446
return sourceTable.Clone();
447
}
448
else
449![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
450
DataTable dt = new DataTable( tableName );
451
ParseFieldList( fieldList, true );
452
foreach ( FieldInfo field in m_FieldInfo )
453![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
454
if ( field.RelationName == null )
455![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
456
DataColumn dc = sourceTable.Columns[ field.FieldName ];
457
dt.Columns.Add( dc.ColumnName, dc.DataType, dc.Expression );
458
}
459
else
460![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
461
DataColumn dc = sourceTable.ParentRelations[ field.RelationName ].ParentTable.Columns[ field.FieldName ];
462
dt.Columns.Add( dc.ColumnName, dc.DataType, dc.Expression );
463
}
464
}
465
if ( ds != null )
466![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
467
ds.Tables.Add( dt );
468
}
469
return dt;
470
}
471
}
472
473
private void InsertJoinInto(DataTable destTable, DataTable sourceTable,
474
string fieldList, string rowFilter, string sort)
475![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
476
if ( fieldList == null )
477![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
478
return;
479
}
480
else
481![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
482
ParseFieldList( fieldList, true );
483
DataRow[] Rows = sourceTable.Select( rowFilter, sort );
484
foreach ( DataRow SourceRow in Rows )
485![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
486
DataRow DestRow = destTable.NewRow();
487
foreach ( FieldInfo Field in m_FieldInfo )
488![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
489
if ( Field.RelationName == null )
490![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
491
DestRow[ Field.FieldName ] = SourceRow[ Field.FieldName ];
492
}
493
else
494![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
495
DataRow ParentRow = SourceRow.GetParentRow( Field.RelationName );
496
DestRow[ Field.FieldName ] = ParentRow[ Field.FieldName ];
497
}
498
}
499
destTable.Rows.Add( DestRow );
500
}
501
}
502
}
503
504
#endregion
505
506![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
SelectDistinct / Distinct#region SelectDistinct / Distinct
507
508![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
509
/// 按照fieldName从sourceTable中选择出不重复的行,
510
/// 相当于select distinct fieldName from sourceTable
511
/// </summary>
512
/// <param name="tableName">表名</param>
513
/// <param name="sourceTable">源DataTable</param>
514
/// <param name="fieldName">列名</param>
515
/// <returns>一个新的不含重复行的DataTable,列只包括fieldName指明的列</returns>
516
public DataTable SelectDistinct(string tableName, DataTable sourceTable, string fieldName)
517![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
518
DataTable dt = new DataTable( tableName );
519
dt.Columns.Add( fieldName, sourceTable.Columns[ fieldName ].DataType );
520
521
object lastValue = null;
522
foreach ( DataRow dr in sourceTable.Select( "", fieldName ) )
523![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
524
if ( lastValue == null || !( ColumnEqual( lastValue, dr[ fieldName ] ) ) )
525![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
526
lastValue = dr[ fieldName ];
527![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
dt.Rows.Add( new object[]
{lastValue} );
528
}
529
}
530
if ( ds != null && !ds.Tables.Contains( tableName ) )
531![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
532
ds.Tables.Add( dt );
533
}
534
return dt;
535
}
536
537![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
538
/// 按照fieldName从sourceTable中选择出不重复的行,
539
/// 相当于select distinct fieldName1,fieldName2,,fieldNamen from sourceTable
540
/// </summary>
541
/// <param name="tableName">表名</param>
542
/// <param name="sourceTable">源DataTable</param>
543
/// <param name="fieldNames">列名数组</param>
544
/// <returns>一个新的不含重复行的DataTable,列只包括fieldNames中指明的列</returns>
545
public DataTable SelectDistinct(string tableName, DataTable sourceTable, string[] fieldNames)
546![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
547
DataTable dt = new DataTable( tableName );
548
object[] values = new object[fieldNames.Length];
549
string fields = "";
550
for ( int i = 0; i < fieldNames.Length; i++ )
551![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
552
dt.Columns.Add( fieldNames[ i ], sourceTable.Columns[ fieldNames[ i ] ].DataType );
553
fields += fieldNames[ i ] + ",";
554
}
555
fields = fields.Remove( fields.Length - 1, 1 );
556
DataRow lastRow = null;
557
foreach ( DataRow dr in sourceTable.Select( "", fields ) )
558![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
559
if ( lastRow == null || !( RowEqual( lastRow, dr, dt.Columns ) ) )
560![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
561
lastRow = dr;
562
for ( int i = 0; i < fieldNames.Length; i++ )
563![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
564
values[ i ] = dr[ fieldNames[ i ] ];
565
}
566
dt.Rows.Add( values );
567
}
568
}
569
if ( ds != null && !ds.Tables.Contains( tableName ) )
570![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
571
ds.Tables.Add( dt );
572
}
573
return dt;
574
}
575
576![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
577
/// 按照fieldName从sourceTable中选择出不重复的行,
578
/// 并且包含sourceTable中所有的列。
579
/// </summary>
580
/// <param name="tableName">表名</param>
581
/// <param name="sourceTable">源表</param>
582
/// <param name="fieldName">字段</param>
583
/// <returns>一个新的不含重复行的DataTable</returns>
584
public DataTable Distinct(string tableName, DataTable sourceTable, string fieldName)
585![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
586
DataTable dt = sourceTable.Clone();
587
dt.TableName = tableName;
588
589
object lastValue = null;
590
foreach ( DataRow dr in sourceTable.Select( "", fieldName ) )
591![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
592
if ( lastValue == null || !( ColumnEqual( lastValue, dr[ fieldName ] ) ) )
593![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
594
lastValue = dr[ fieldName ];
595
dt.Rows.Add( dr.ItemArray );
596
}
597
}
598
if ( ds != null && !ds.Tables.Contains( tableName ) )
599![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
600
ds.Tables.Add( dt );
601
}
602
return dt;
603
}
604
605![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
606
/// 按照fieldNames从sourceTable中选择出不重复的行,
607
/// 并且包含sourceTable中所有的列。
608
/// </summary>
609
/// <param name="tableName">表名</param>
610
/// <param name="sourceTable">源表</param>
611
/// <param name="fieldNames">字段</param>
612
/// <returns>一个新的不含重复行的DataTable</returns>
613
public DataTable Distinct(string tableName, DataTable sourceTable, string[] fieldNames)
614![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
615
DataTable dt = sourceTable.Clone();
616
dt.TableName = tableName;
617
string fields = "";
618
for ( int i = 0; i < fieldNames.Length; i++ )
619![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
620
fields += fieldNames[ i ] + ",";
621
}
622
fields = fields.Remove( fields.Length - 1, 1 );
623
DataRow lastRow = null;
624
foreach ( DataRow dr in sourceTable.Select( "", fields ) )
625![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
626
if ( lastRow == null || !( RowEqual( lastRow, dr, dt.Columns ) ) )
627![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
628
lastRow = dr;
629
dt.Rows.Add( dr.ItemArray );
630
}
631
}
632
if ( ds != null && !ds.Tables.Contains( tableName ) )
633![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
634
ds.Tables.Add( dt );
635
}
636
return dt;
637
}
638
639
#endregion
640
641![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
Select Table Into#region Select Table Into
642
643![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//**//**//// <summary>
644
/// 按sort排序,按rowFilter过滤sourceTable,
645
/// 复制fieldList中指明的字段的数据到新DataTable,并返回之
646
/// </summary>
647
/// <param name="tableName">表名</param>
648
/// <param name="sourceTable">源表</param>
649
/// <param name="fieldList">字段列表</param>
650
/// <param name="rowFilter">过滤条件</param>
651
/// <param name="sort">排序</param>
652
/// <returns>新DataTable</returns>
653
public DataTable SelectInto(string tableName, DataTable sourceTable,
654
string fieldList, string rowFilter, string sort)
655![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
656
DataTable dt = CreateTable( tableName, sourceTable, fieldList );
657
InsertInto( dt, sourceTable, fieldList, rowFilter, sort );
658
return dt;
659
}
660
661
#endregion
662
663![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
Group By Table#region Group By Table
664
665
public DataTable SelectGroupByInto(string tableName, DataTable sourceTable, string fieldList,
666
string rowFilter, string groupBy)
667![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
668
DataTable dt = CreateGroupByTable( tableName, sourceTable, fieldList );
669
InsertGroupByInto( dt, sourceTable, fieldList, rowFilter, groupBy );
670
return dt;
671
}
672
673
#endregion
674
675![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
Join Tables#region Join Tables
676
677
public DataTable SelectJoinInto(string tableName, DataTable sourceTable, string fieldList, string rowFilter, string sort)
678![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
679
DataTable dt = CreateJoinTable( tableName, sourceTable, fieldList );
680
InsertJoinInto( dt, sourceTable, fieldList, rowFilter, sort );
681
return dt;
682
}
683
684
#endregion
685
686![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
Create Table#region Create Table
687
688
public DataTable CreateTable(string tableName, string fieldList)
689![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
690
DataTable dt = new DataTable( tableName );
691
DataColumn dc;
692
string[] Fields = fieldList.Split( ',' );
693
string[] FieldsParts;
694
string Expression;
695
foreach ( string Field in Fields )
696![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
697
FieldsParts = Field.Trim().Split( " ".ToCharArray(), 3 ); // allow for spaces in the expression
698
// add fieldname and datatype
699
if ( FieldsParts.Length == 2 )
700![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
701
dc = dt.Columns.Add( FieldsParts[ 0 ].Trim(), Type.GetType( "System." + FieldsParts[ 1 ].Trim(), true, true ) );
702
dc.AllowDBNull = true;
703
}
704
else if ( FieldsParts.Length == 3 ) // add fieldname, datatype, and expression
705![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
706
Expression = FieldsParts[ 2 ].Trim();
707
if ( Expression.ToUpper() == "REQUIRED" )
708![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
709
dc = dt.Columns.Add( FieldsParts[ 0 ].Trim(), Type.GetType( "System." + FieldsParts[ 1 ].Trim(), true, true ) );
710
dc.AllowDBNull = false;
711
}
712
else
713![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
714
dc = dt.Columns.Add( FieldsParts[ 0 ].Trim(), Type.GetType( "System." + FieldsParts[ 1 ].Trim(), true, true ), Expression );
715
}
716
}
717
else
718![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
719
return null;
720
}
721
}
722
if ( ds != null )
723![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
724
ds.Tables.Add( dt );
725
}
726
return dt;
727
}
728
729
public DataTable CreateTable(string tableName, string fieldList, string keyFieldList)
730![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
731
DataTable dt = CreateTable( tableName, fieldList );
732
string[] KeyFields = keyFieldList.Split( ',' );
733
if ( KeyFields.Length > 0 )
734![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
735
DataColumn[] KeyFieldColumns = new DataColumn[KeyFields.Length];
736
int i;
737
for ( i = 1; i == KeyFields.Length - 1; ++i )
738![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
739
KeyFieldColumns[ i ] = dt.Columns[ KeyFields[ i ].Trim() ];
740
}
741
dt.PrimaryKey = KeyFieldColumns;
742
}
743
return dt;
744
}
745
746
#endregion
747
}
748
}
749
转载于:https://www.cnblogs.com/Dot-Boy/archive/2008/10/25/1319562.html