DataSetHelper——操作DataSet的工具类

微软知识库里面有个 DataSetHelper ，可以对DataSet中的DataTable进行Distinct、Group by、Join和Create。 我进行了整理，并添加了一些小的特性，代码如下：

using System;
using System.Collections;
using System.Data;

namespace Common
{
/**////<summary>
///DataSet助手
///</summary>
publicclassDataSetHelper
{
privateclassFieldInfo
{
publicstringRelationName;
publicstringFieldName;
publicstringFieldAlias;
publicstringAggregate;
}

privateDataSetds;
privateArrayListm_FieldInfo;
privatestringm_FieldList;
privateArrayListGroupByFieldInfo;
privatestringGroupByFieldList;

publicDataSetDataSet
{
get{returnds;}
}

Construction#regionConstruction

publicDataSetHelper()
{
ds=null;
}

publicDataSetHelper(refDataSetdataSet)
{
ds=dataSet;
}

#endregion

PrivateMethods#regionPrivateMethods

privateboolColumnEqual(objectobjectA,objectobjectB)
{
if(objectA==DBNull.Value&&objectB==DBNull.Value)
{
returntrue;
}
if(objectA==DBNull.Value||objectB==DBNull.Value)
{
returnfalse;
}
return(objectA.Equals(objectB));
}

privateboolRowEqual(DataRowrowA,DataRowrowB,DataColumnCollectioncolumns)
{
boolresult=true;
for(inti=0;i<columns.Count;i++)
{
result&=ColumnEqual(rowA[columns[i].ColumnName],rowB[columns[i].ColumnName]);
}
returnresult;
}

privatevoidParseFieldList(stringfieldList,boolallowRelation)
{
if(m_FieldList==fieldList)
{
return;
}
m_FieldInfo=newArrayList();
m_FieldList=fieldList;
FieldInfoField;
string[]FieldParts;
string[]Fields=fieldList.Split(',');
for(inti=0;i<=Fields.Length-1;i++)
{
Field=newFieldInfo();
FieldParts=Fields[i].Trim().Split('');
switch(FieldParts.Length)
{
case1:
//tobesetattheendoftheloop
break;
case2:
Field.FieldAlias=FieldParts[1];
break;
default:
return;
}
FieldParts=FieldParts[0].Split('.');
switch(FieldParts.Length)
{
case1:
Field.FieldName=FieldParts[0];
break;
case2:
if(allowRelation==false)
{
return;
}
Field.RelationName=FieldParts[0].Trim();
Field.FieldName=FieldParts[1].Trim();
break;
default:
return;
}
if(Field.FieldAlias==null)
{
Field.FieldAlias=Field.FieldName;
}
m_FieldInfo.Add(Field);
}
}

privateDataTableCreateTable(stringtableName,DataTablesourceTable,stringfieldList)
{
DataTabledt;
if(fieldList.Trim()=="")
{
dt=sourceTable.Clone();
dt.TableName=tableName;
}
else
{
dt=newDataTable(tableName);
ParseFieldList(fieldList,false);
DataColumndc;
foreach(FieldInfoFieldinm_FieldInfo)
{
dc=sourceTable.Columns[Field.FieldName];
DataColumncolumn=newDataColumn();
column.ColumnName=Field.FieldAlias;
column.DataType=dc.DataType;
column.MaxLength=dc.MaxLength;
column.Expression=dc.Expression;
dt.Columns.Add(column);
}
}
if(ds!=null)
{
ds.Tables.Add(dt);
}
returndt;
}

privatevoidInsertInto(DataTabledestTable,DataTablesourceTable,
stringfieldList,stringrowFilter,stringsort)
{
ParseFieldList(fieldList,false);
DataRow[]rows=sourceTable.Select(rowFilter,sort);
DataRowdestRow;
foreach(DataRowsourceRowinrows)
{
destRow=destTable.NewRow();
if(fieldList=="")
{
foreach(DataColumndcindestRow.Table.Columns)
{
if(dc.Expression=="")
{
destRow[dc]=sourceRow[dc.ColumnName];
}
}
}
else
{
foreach(FieldInfofieldinm_FieldInfo)
{
destRow[field.FieldAlias]=sourceRow[field.FieldName];
}
}
destTable.Rows.Add(destRow);
}
}

privatevoidParseGroupByFieldList(stringFieldList)
{
if(GroupByFieldList==FieldList)
{
return;
}
GroupByFieldInfo=newArrayList();
FieldInfoField;
string[]FieldParts;
string[]Fields=FieldList.Split(',');
for(inti=0;i<=Fields.Length-1;i++)
{
Field=newFieldInfo();
FieldParts=Fields[i].Trim().Split('');
switch(FieldParts.Length)
{
case1:
//tobesetattheendoftheloop
break;
case2:
Field.FieldAlias=FieldParts[1];
break;
default:
return;
}

FieldParts=FieldParts[0].Split('(');
switch(FieldParts.Length)
{
case1:
Field.FieldName=FieldParts[0];
break;
case2:
Field.Aggregate=FieldParts[0].Trim().ToLower();
Field.FieldName=FieldParts[1].Trim('',')');
break;
default:
return;
}
if(Field.FieldAlias==null)
{
if(Field.Aggregate==null)
{
Field.FieldAlias=Field.FieldName;
}
else
{
Field.FieldAlias=Field.Aggregate+"of"+Field.FieldName;
}
}
GroupByFieldInfo.Add(Field);
}
GroupByFieldList=FieldList;
}

privateDataTableCreateGroupByTable(stringtableName,DataTablesourceTable,stringfieldList)
{
if(fieldList==null||fieldList.Length==0)
{
returnsourceTable.Clone();
}
else
{
DataTabledt=newDataTable(tableName);
ParseGroupByFieldList(fieldList);
foreach(FieldInfoFieldinGroupByFieldInfo)
{
DataColumndc=sourceTable.Columns[Field.FieldName];
if(Field.Aggregate==null)
{
dt.Columns.Add(Field.FieldAlias,dc.DataType,dc.Expression);
}
else
{
dt.Columns.Add(Field.FieldAlias,dc.DataType);
}
}
if(ds!=null)
{
ds.Tables.Add(dt);
}
returndt;
}
}

privatevoidInsertGroupByInto(DataTabledestTable,DataTablesourceTable,stringfieldList,
stringrowFilter,stringgroupBy)
{
if(fieldList==null||fieldList.Length==0)
{
return;
}
ParseGroupByFieldList(fieldList);
ParseFieldList(groupBy,false);
DataRow[]rows=sourceTable.Select(rowFilter,groupBy);
DataRowlastSourceRow=null,destRow=null;
boolsameRow;
introwCount=0;
foreach(DataRowsourceRowinrows)
{
sameRow=false;
if(lastSourceRow!=null)
{
sameRow=true;
foreach(FieldInfoFieldinm_FieldInfo)
{
if(!ColumnEqual(lastSourceRow[Field.FieldName],sourceRow[Field.FieldName]))
{
sameRow=false;
break;
}
}
if(!sameRow)
{
destTable.Rows.Add(destRow);
}
}
if(!sameRow)
{
destRow=destTable.NewRow();
rowCount=0;
}
rowCount+=1;
foreach(FieldInfofieldinGroupByFieldInfo)
{
switch(field.Aggregate.ToLower())
{
casenull:
case"":
case"last":
destRow[field.FieldAlias]=sourceRow[field.FieldName];
break;
case"first":
if(rowCount==1)
{
destRow[field.FieldAlias]=sourceRow[field.FieldName];
}
break;
case"count":
destRow[field.FieldAlias]=rowCount;
break;
case"sum":
destRow[field.FieldAlias]=Add(destRow[field.FieldAlias],sourceRow[field.FieldName]);
break;
case"max":
destRow[field.FieldAlias]=Max(destRow[field.FieldAlias],sourceRow[field.FieldName]);
break;
case"min":
if(rowCount==1)
{
destRow[field.FieldAlias]=sourceRow[field.FieldName];
}
else
{
destRow[field.FieldAlias]=Min(destRow[field.FieldAlias],sourceRow[field.FieldName]);
}
break;
}
}
lastSourceRow=sourceRow;
}
if(destRow!=null)
{
destTable.Rows.Add(destRow);
}
}

privateobjectMin(objecta,objectb)
{
if((aisDBNull)||(bisDBNull))
{
returnDBNull.Value;
}
if(((IComparable)a).CompareTo(b)==-1)
{
returna;
}
else
{
returnb;
}
}

privateobjectMax(objecta,objectb)
{
if(aisDBNull)
{
returnb;
}
if(bisDBNull)
{
returna;
}
if(((IComparable)a).CompareTo(b)==1)
{
returna;
}
else
{
returnb;
}
}

privateobjectAdd(objecta,objectb)
{
if(aisDBNull)
{
returnb;
}
if(bisDBNull)
{
returna;
}
return((decimal)a+(decimal)b);
}

privateDataTableCreateJoinTable(stringtableName,DataTablesourceTable,stringfieldList)
{
if(fieldList==null)
{
returnsourceTable.Clone();
}
else
{
DataTabledt=newDataTable(tableName);
ParseFieldList(fieldList,true);
foreach(FieldInfofieldinm_FieldInfo)
{
if(field.RelationName==null)
{
DataColumndc=sourceTable.Columns[field.FieldName];
dt.Columns.Add(dc.ColumnName,dc.DataType,dc.Expression);
}
else
{
DataColumndc=sourceTable.ParentRelations[field.RelationName].ParentTable.Columns[field.FieldName];
dt.Columns.Add(dc.ColumnName,dc.DataType,dc.Expression);
}
}
if(ds!=null)
{
ds.Tables.Add(dt);
}
returndt;
}
}

privatevoidInsertJoinInto(DataTabledestTable,DataTablesourceTable,
stringfieldList,stringrowFilter,stringsort)
{
if(fieldList==null)
{
return;
}
else
{
ParseFieldList(fieldList,true);
DataRow[]Rows=sourceTable.Select(rowFilter,sort);
foreach(DataRowSourceRowinRows)
{
DataRowDestRow=destTable.NewRow();
foreach(FieldInfoFieldinm_FieldInfo)
{
if(Field.RelationName==null)
{
DestRow[Field.FieldName]=SourceRow[Field.FieldName];
}
else
{
DataRowParentRow=SourceRow.GetParentRow(Field.RelationName);
DestRow[Field.FieldName]=ParentRow[Field.FieldName];
}
}
destTable.Rows.Add(DestRow);
}
}
}

#endregion

SelectDistinct/Distinct#regionSelectDistinct/Distinct

/**////<summary>
///按照fieldName从sourceTable中选择出不重复的行，
///相当于selectdistinctfieldNamefromsourceTable
///</summary>
///<paramname="tableName">表名</param>
///<paramname="sourceTable">源DataTable</param>
///<paramname="fieldName">列名</param>
///<returns>一个新的不含重复行的DataTable，列只包括fieldName指明的列</returns>
publicDataTableSelectDistinct(stringtableName,DataTablesourceTable,stringfieldName)
{
DataTabledt=newDataTable(tableName);
dt.Columns.Add(fieldName,sourceTable.Columns[fieldName].DataType);

objectlastValue=null;
foreach(DataRowdrinsourceTable.Select("",fieldName))
{
if(lastValue==null||!(ColumnEqual(lastValue,dr[fieldName])))
{
lastValue=dr[fieldName];
dt.Rows.Add(newobject[]{lastValue});
}
}
if(ds!=null&&!ds.Tables.Contains(tableName))
{
ds.Tables.Add(dt);
}
returndt;
}

/**////<summary>
///按照fieldName从sourceTable中选择出不重复的行，
///相当于selectdistinctfieldName1,fieldName2,,fieldNamenfromsourceTable
///</summary>
///<paramname="tableName">表名</param>
///<paramname="sourceTable">源DataTable</param>
///<paramname="fieldNames">列名数组</param>
///<returns>一个新的不含重复行的DataTable，列只包括fieldNames中指明的列</returns>
publicDataTableSelectDistinct(stringtableName,DataTablesourceTable,string[]fieldNames)
{
DataTabledt=newDataTable(tableName);
object[]values=newobject[fieldNames.Length];
stringfields="";
for(inti=0;i<fieldNames.Length;i++)
{
dt.Columns.Add(fieldNames[i],sourceTable.Columns[fieldNames[i]].DataType);
fields+=fieldNames[i]+",";
}
fields=fields.Remove(fields.Length-1,1);
DataRowlastRow=null;
foreach(DataRowdrinsourceTable.Select("",fields))
{
if(lastRow==null||!(RowEqual(lastRow,dr,dt.Columns)))
{
lastRow=dr;
for(inti=0;i<fieldNames.Length;i++)
{
values[i]=dr[fieldNames[i]];
}
dt.Rows.Add(values);
}
}
if(ds!=null&&!ds.Tables.Contains(tableName))
{
ds.Tables.Add(dt);
}
returndt;
}

/**////<summary>
///按照fieldName从sourceTable中选择出不重复的行，
///并且包含sourceTable中所有的列。
///</summary>
///<paramname="tableName">表名</param>
///<paramname="sourceTable">源表</param>
///<paramname="fieldName">字段</param>
///<returns>一个新的不含重复行的DataTable</returns>
publicDataTableDistinct(stringtableName,DataTablesourceTable,stringfieldName)
{
DataTabledt=sourceTable.Clone();
dt.TableName=tableName;

objectlastValue=null;
foreach(DataRowdrinsourceTable.Select("",fieldName))
{
if(lastValue==null||!(ColumnEqual(lastValue,dr[fieldName])))
{
lastValue=dr[fieldName];
dt.Rows.Add(dr.ItemArray);
}
}
if(ds!=null&&!ds.Tables.Contains(tableName))
{
ds.Tables.Add(dt);
}
returndt;
}

/**////<summary>
///按照fieldNames从sourceTable中选择出不重复的行，
///并且包含sourceTable中所有的列。
///</summary>
///<paramname="tableName">表名</param>
///<paramname="sourceTable">源表</param>
///<paramname="fieldNames">字段</param>
///<returns>一个新的不含重复行的DataTable</returns>
publicDataTableDistinct(stringtableName,DataTablesourceTable,string[]fieldNames)
{
DataTabledt=sourceTable.Clone();
dt.TableName=tableName;
stringfields="";
for(inti=0;i<fieldNames.Length;i++)
{
fields+=fieldNames[i]+",";
}
fields=fields.Remove(fields.Length-1,1);
DataRowlastRow=null;
foreach(DataRowdrinsourceTable.Select("",fields))
{
if(lastRow==null||!(RowEqual(lastRow,dr,dt.Columns)))
{
lastRow=dr;
dt.Rows.Add(dr.ItemArray);
}
}
if(ds!=null&&!ds.Tables.Contains(tableName))
{
ds.Tables.Add(dt);
}
returndt;
}

#endregion

SelectTableInto#regionSelectTableInto

/**////<summary>
///按sort排序，按rowFilter过滤sourceTable，
///复制fieldList中指明的字段的数据到新DataTable，并返回之
///</summary>
///<paramname="tableName">表名</param>
///<paramname="sourceTable">源表</param>
///<paramname="fieldList">字段列表</param>
///<paramname="rowFilter">过滤条件</param>
///<paramname="sort">排序</param>
///<returns>新DataTable</returns>
publicDataTableSelectInto(stringtableName,DataTablesourceTable,
stringfieldList,stringrowFilter,stringsort)
{
DataTabledt=CreateTable(tableName,sourceTable,fieldList);
InsertInto(dt,sourceTable,fieldList,rowFilter,sort);
returndt;
}

#endregion

GroupByTable#regionGroupByTable

publicDataTableSelectGroupByInto(stringtableName,DataTablesourceTable,stringfieldList,
stringrowFilter,stringgroupBy)
{
DataTabledt=CreateGroupByTable(tableName,sourceTable,fieldList);
InsertGroupByInto(dt,sourceTable,fieldList,rowFilter,groupBy);
returndt;
}

#endregion

JoinTables#regionJoinTables

publicDataTableSelectJoinInto(stringtableName,DataTablesourceTable,stringfieldList,stringrowFilter,stringsort)
{
DataTabledt=CreateJoinTable(tableName,sourceTable,fieldList);
InsertJoinInto(dt,sourceTable,fieldList,rowFilter,sort);
returndt;
}

#endregion

CreateTable#regionCreateTable

publicDataTableCreateTable(stringtableName,stringfieldList)
{
DataTabledt=newDataTable(tableName);
DataColumndc;
string[]Fields=fieldList.Split(',');
string[]FieldsParts;
stringExpression;
foreach(stringFieldinFields)
{
FieldsParts=Field.Trim().Split("".ToCharArray(),3);//allowforspacesintheexpression
//addfieldnameanddatatype
if(FieldsParts.Length==2)
{
dc=dt.Columns.Add(FieldsParts[0].Trim(),Type.GetType("System."+FieldsParts[1].Trim(),true,true));
dc.AllowDBNull=true;
}
elseif(FieldsParts.Length==3)//addfieldname,datatype,andexpression
{
Expression=FieldsParts[2].Trim();
if(Expression.ToUpper()=="REQUIRED")
{
dc=dt.Columns.Add(FieldsParts[0].Trim(),Type.GetType("System."+FieldsParts[1].Trim(),true,true));
dc.AllowDBNull=false;
}
else
{
dc=dt.Columns.Add(FieldsParts[0].Trim(),Type.GetType("System."+FieldsParts[1].Trim(),true,true),Expression);
}
}
else
{
returnnull;
}
}
if(ds!=null)
{
ds.Tables.Add(dt);
}
returndt;
}

publicDataTableCreateTable(stringtableName,stringfieldList,stringkeyFieldList)
{
DataTabledt=CreateTable(tableName,fieldList);
string[]KeyFields=keyFieldList.Split(',');
if(KeyFields.Length>0)
{
DataColumn[]KeyFieldColumns=newDataColumn[KeyFields.Length];
inti;
for(i=1;i==KeyFields.Length-1;++i)
{
KeyFieldColumns[i]=dt.Columns[KeyFields[i].Trim()];
}
dt.PrimaryKey=KeyFieldColumns;
}
returndt;
}

#endregion
}