DataTable中进行Distinct、Group by、Join、Create

Code
using System; 
using System.Collections; 
using System.Data; 
 
namespace Common 
{ 
    /**//**//**//**//**//**//**//// <summary> 
    /// DataSet助手 
    /// </summary> 
    public class DataSetHelper 
    { 
        private class FieldInfo 
        { 
            public string RelationName; 
            public string FieldName; 
            public string FieldAlias; 
            public string Aggregate; 
        } 
 
        private DataSet ds; 
        private ArrayList m_FieldInfo; 
        private string m_FieldList; 
        private ArrayList GroupByFieldInfo; 
        private string GroupByFieldList; 
 
        public DataSet DataSet 
        { 
            get { return ds; } 
        } 
 
        ConstructionConstructionConstruction#region Construction 
 
        public DataSetHelper() 
        { 
            ds = null; 
        } 
 
        public DataSetHelper(ref DataSet dataSet) 
        { 
            ds = dataSet; 
        } 
 
        #endregion 
 
        Private MethodsPrivate MethodsPrivate Methods#region Private Methods 
 
        private bool ColumnEqual(object objectA, object objectB) 
        { 
            if ( objectA == DBNull.Value && objectB == DBNull.Value ) 
            { 
                return true; 
            } 
            if ( objectA == DBNull.Value || objectB == DBNull.Value ) 
            { 
                return false; 
            } 
            return ( objectA.Equals( objectB ) ); 
        } 
 
        private bool RowEqual(DataRow rowA, DataRow rowB, DataColumnCollection columns) 
        { 
            bool result = true; 
            for ( int i = 0; i < columns.Count; i++ ) 
            { 
                result &= ColumnEqual( rowA[ columns[ i ].ColumnName ], rowB[ columns[ i ].ColumnName ] ); 
            } 
            return result; 
        } 
 
        private void ParseFieldList(string fieldList, bool allowRelation) 
        { 
            if ( m_FieldList == fieldList ) 
            { 
                return; 
            } 
            m_FieldInfo = new ArrayList(); 
            m_FieldList = fieldList; 
            FieldInfo Field; 
            string[] FieldParts; 
            string[] Fields = fieldList.Split( ',' ); 
            for ( int i = 0; i <= Fields.Length - 1; i++ ) 
            { 
                Field = new FieldInfo(); 
                FieldParts = Fields[ i ].Trim().Split( ' ' ); 
                switch ( FieldParts.Length ) 
                { 
                    case 1: 
                        //to be set at the end of the loop 
                        break; 
                    case 2: 
                        Field.FieldAlias = FieldParts[ 1 ]; 
                        break; 
                    default: 
                        return; 
                } 
                FieldParts = FieldParts[ 0 ].Split( '.' ); 
                switch ( FieldParts.Length ) 
                { 
                    case 1: 
                        Field.FieldName = FieldParts[ 0 ]; 
                        break; 
                    case 2: 
                        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 ); 
            } 
        } 
 
        private DataTable CreateTable(string tableName, DataTable sourceTable, string fieldList) 
        { 
            DataTable dt; 
            if ( fieldList.Trim() == "" ) 
            { 
                dt = sourceTable.Clone(); 
                dt.TableName = tableName; 
            } 
            else 
            { 
                dt = new DataTable( tableName ); 
                ParseFieldList( fieldList, false ); 
                DataColumn dc; 
                foreach ( FieldInfo Field in m_FieldInfo ) 
                { 
                    dc = sourceTable.Columns[ Field.FieldName ]; 
                    DataColumn column = new DataColumn(); 
                    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 ); 
            } 
            return dt; 
        } 
 
        private void InsertInto(DataTable destTable, DataTable sourceTable, 
                                string fieldList, string rowFilter, string sort) 
        { 
            ParseFieldList( fieldList, false ); 
            DataRow[] rows = sourceTable.Select( rowFilter, sort ); 
            DataRow destRow; 
            foreach ( DataRow sourceRow in rows ) 
            { 
                destRow = destTable.NewRow(); 
                if ( fieldList == "" ) 
                { 
                    foreach ( DataColumn dc in destRow.Table.Columns ) 
                    { 
                        if ( dc.Expression == "" ) 
                        { 
                            destRow[ dc ] = sourceRow[ dc.ColumnName ]; 
                        } 
                    } 
                } 
                else 
                { 
                    foreach ( FieldInfo field in m_FieldInfo ) 
                    { 
                        destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ]; 
                    } 
                } 
                destTable.Rows.Add( destRow ); 
            } 
        } 
 
        private void ParseGroupByFieldList(string FieldList) 
        { 
            if ( GroupByFieldList == FieldList ) 
            { 
                return; 
            } 
            GroupByFieldInfo = new ArrayList(); 
            FieldInfo Field; 
            string[] FieldParts; 
            string[] Fields = FieldList.Split( ',' ); 
            for ( int i = 0; i <= Fields.Length - 1; i++ ) 
            { 
                Field = new FieldInfo(); 
                FieldParts = Fields[ i ].Trim().Split( ' ' ); 
                switch ( FieldParts.Length ) 
                { 
                    case 1: 
                        //to be set at the end of the loop 
                        break; 
                    case 2: 
                        Field.FieldAlias = FieldParts[ 1 ]; 
                        break; 
                    default: 
                        return; 
                } 
 
                FieldParts = FieldParts[ 0 ].Split( '(' ); 
                switch ( FieldParts.Length ) 
                { 
                    case 1: 
                        Field.FieldName = FieldParts[ 0 ]; 
                        break; 
                    case 2: 
                        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; 
        } 
 
        private DataTable CreateGroupByTable(string tableName, DataTable sourceTable, string fieldList) 
        { 
            if ( fieldList == null || fieldList.Length == 0 ) 
            { 
                return sourceTable.Clone(); 
            } 
            else 
            { 
                DataTable dt = new DataTable( tableName ); 
                ParseGroupByFieldList( fieldList ); 
                foreach ( FieldInfo Field in GroupByFieldInfo ) 
                { 
                    DataColumn dc = 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 ); 
                } 
                return dt; 
            } 
        } 
 
        private void InsertGroupByInto(DataTable destTable, DataTable sourceTable, string fieldList, 
                                       string rowFilter, string groupBy) 
        { 
            if ( fieldList == null || fieldList.Length == 0 ) 
            { 
                return; 
            } 
            ParseGroupByFieldList( fieldList );  
            ParseFieldList( groupBy, false );  
            DataRow[] rows = sourceTable.Select( rowFilter, groupBy ); 
            DataRow lastSourceRow = null, destRow = null; 
            bool sameRow; 
            int rowCount = 0; 
            foreach ( DataRow sourceRow in rows ) 
            { 
                sameRow = false; 
                if ( lastSourceRow != null ) 
                { 
                    sameRow = true; 
                    foreach ( FieldInfo Field in m_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 ( FieldInfo field in GroupByFieldInfo ) 
                { 
                    switch ( field.Aggregate.ToLower() ) 
                    { 
                        case null:  
                        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 ); 
            } 
        } 
 
        private object Min(object a, object b) 
        { 
            if ( ( a is DBNull ) || ( b is DBNull ) ) 
            { 
                return DBNull.Value; 
            } 
            if ( ( (IComparable) a ).CompareTo( b ) == -1 ) 
            { 
                return a; 
            } 
            else 
            { 
                return b; 
            } 
        } 
 
        private object Max(object a, object b) 
        { 
            if ( a is DBNull ) 
            { 
                return b; 
            } 
            if ( b is DBNull ) 
            { 
                return a; 
            } 
            if ( ( (IComparable) a ).CompareTo( b ) == 1 ) 
            { 
                return a; 
            } 
            else 
            { 
                return b; 
            } 
        } 
 
        private object Add(object a, object b) 
        { 
            if ( a is DBNull ) 
            { 
                return b; 
            } 
            if ( b is DBNull ) 
            { 
                return a; 
            } 
            return ( (decimal) a + (decimal) b ); 
        } 
 
        private DataTable CreateJoinTable(string tableName, DataTable sourceTable, string fieldList) 
        { 
            if ( fieldList == null ) 
            { 
                return sourceTable.Clone(); 
            } 
            else 
            { 
                DataTable dt = new DataTable( tableName ); 
                ParseFieldList( fieldList, true ); 
                foreach ( FieldInfo field in m_FieldInfo ) 
                { 
                    if ( field.RelationName == null ) 
                    { 
                        DataColumn dc = sourceTable.Columns[ field.FieldName ]; 
                        dt.Columns.Add( dc.ColumnName, dc.DataType, dc.Expression ); 
                    } 
                    else 
                    { 
                        DataColumn dc = sourceTable.ParentRelations[ field.RelationName ].ParentTable.Columns[ field.FieldName ]; 
                        dt.Columns.Add( dc.ColumnName, dc.DataType, dc.Expression ); 
                    } 
                } 
                if ( ds != null ) 
                { 
                    ds.Tables.Add( dt ); 
                } 
                return dt; 
            } 
        } 
 
        private void InsertJoinInto(DataTable destTable, DataTable sourceTable, 
                                    string fieldList, string rowFilter, string sort) 
        { 
            if ( fieldList == null ) 
            { 
                return; 
            } 
            else 
            { 
                ParseFieldList( fieldList, true ); 
                DataRow[] Rows = sourceTable.Select( rowFilter, sort ); 
                foreach ( DataRow SourceRow in Rows ) 
                { 
                    DataRow DestRow = destTable.NewRow(); 
                    foreach ( FieldInfo Field in m_FieldInfo ) 
                    { 
                        if ( Field.RelationName == null ) 
                        { 
                            DestRow[ Field.FieldName ] = SourceRow[ Field.FieldName ]; 
                        } 
                        else 
                        { 
                            DataRow ParentRow = SourceRow.GetParentRow( Field.RelationName ); 
                            DestRow[ Field.FieldName ] = ParentRow[ Field.FieldName ]; 
                        } 
                    } 
                    destTable.Rows.Add( DestRow ); 
                } 
            } 
        } 
 
        #endregion 
 
        SelectDistinct / DistinctSelectDistinct / DistinctSelectDistinct / Distinct#region SelectDistinct / Distinct 
 
        /**//**//**//**//**//**//**//// <summary> 
        /// 按照fieldName从sourceTable中选择出不重复的行， 
        /// 相当于select distinct fieldName from sourceTable 
        /// </summary> 
        /// <param name="tableName">表名</param> 
        /// <param name="sourceTable">源DataTable</param> 
        /// <param name="fieldName">列名</param> 
        /// <returns>一个新的不含重复行的DataTable，列只包括fieldName指明的列</returns> 
        public DataTable SelectDistinct(string tableName, DataTable sourceTable, string fieldName) 
        { 
            DataTable dt = new DataTable( tableName ); 
            dt.Columns.Add( fieldName, sourceTable.Columns[ fieldName ].DataType ); 
 
            object lastValue = null; 
            foreach ( DataRow dr in sourceTable.Select( "", fieldName ) ) 
            { 
                if ( lastValue == null || !( ColumnEqual( lastValue, dr[ fieldName ] ) ) ) 
                { 
                    lastValue = dr[ fieldName ]; 
                    dt.Rows.Add( new object[]{lastValue} ); 
                } 
            } 
            if ( ds != null && !ds.Tables.Contains( tableName ) ) 
            { 
                ds.Tables.Add( dt ); 
            } 
            return dt; 
        } 
 
        /**//**//**//**//**//**//**//// <summary> 
        /// 按照fieldName从sourceTable中选择出不重复的行， 
        /// 相当于select distinct fieldName1,fieldName2,,fieldNamen from sourceTable 
        /// </summary> 
        /// <param name="tableName">表名</param> 
        /// <param name="sourceTable">源DataTable</param> 
        /// <param name="fieldNames">列名数组</param> 
        /// <returns>一个新的不含重复行的DataTable，列只包括fieldNames中指明的列</returns> 
        public DataTable SelectDistinct(string tableName, DataTable sourceTable, string[] fieldNames) 
        { 
            DataTable dt = new DataTable( tableName ); 
            object[] values = new object[fieldNames.Length]; 
            string fields = ""; 
            for ( int i = 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 ); 
            DataRow lastRow = null; 
            foreach ( DataRow dr in sourceTable.Select( "", fields ) ) 
            { 
                if ( lastRow == null || !( RowEqual( lastRow, dr, dt.Columns ) ) ) 
                { 
                    lastRow = dr; 
                    for ( int i = 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 ); 
            } 
            return dt; 
        } 
 
        /**//**//**//**//**//**//**//// <summary> 
        /// 按照fieldName从sourceTable中选择出不重复的行， 
        /// 并且包含sourceTable中所有的列。 
        /// </summary> 
        /// <param name="tableName">表名</param> 
        /// <param name="sourceTable">源表</param> 
        /// <param name="fieldName">字段</param> 
        /// <returns>一个新的不含重复行的DataTable</returns> 
        public DataTable Distinct(string tableName, DataTable sourceTable, string fieldName) 
        { 
            DataTable dt = sourceTable.Clone(); 
            dt.TableName = tableName; 
 
            object lastValue = null; 
            foreach ( DataRow dr in sourceTable.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 ); 
            } 
            return dt; 
        } 
 
        /**//**//**//**//**//**//**//// <summary> 
        /// 按照fieldNames从sourceTable中选择出不重复的行， 
        /// 并且包含sourceTable中所有的列。 
        /// </summary> 
        /// <param name="tableName">表名</param> 
        /// <param name="sourceTable">源表</param> 
        /// <param name="fieldNames">字段</param> 
        /// <returns>一个新的不含重复行的DataTable</returns> 
        public DataTable Distinct(string tableName, DataTable sourceTable, string[] fieldNames) 
        { 
            DataTable dt = sourceTable.Clone(); 
            dt.TableName = tableName; 
            string fields = ""; 
            for ( int i = 0; i < fieldNames.Length; i++ ) 
            { 
                fields += fieldNames[ i ] + ","; 
            } 
            fields = fields.Remove( fields.Length - 1, 1 ); 
            DataRow lastRow = null; 
            foreach ( DataRow dr in sourceTable.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 ); 
            } 
            return dt; 
        } 
 
        #endregion 
 
        Select Table IntoSelect Table IntoSelect Table Into#region Select Table Into 
 
        /**//**//**//**//**//**//**//// <summary> 
        /// 按sort排序，按rowFilter过滤sourceTable， 
        /// 复制fieldList中指明的字段的数据到新DataTable，并返回之 
        /// </summary> 
        /// <param name="tableName">表名</param> 
        /// <param name="sourceTable">源表</param> 
        /// <param name="fieldList">字段列表</param> 
        /// <param name="rowFilter">过滤条件</param> 
        /// <param name="sort">排序</param> 
        /// <returns>新DataTable</returns> 
        public DataTable SelectInto(string tableName, DataTable sourceTable, 
                                    string fieldList, string rowFilter, string sort) 
        { 
            DataTable dt = CreateTable( tableName, sourceTable, fieldList ); 
            InsertInto( dt, sourceTable, fieldList, rowFilter, sort ); 
            return dt; 
        } 
 
        #endregion 
 
        Group By TableGroup By TableGroup By Table#region Group By Table 
 
        public DataTable SelectGroupByInto(string tableName, DataTable sourceTable, string fieldList, 
                                           string rowFilter, string groupBy) 
        { 
            DataTable dt = CreateGroupByTable( tableName, sourceTable, fieldList ); 
            InsertGroupByInto( dt, sourceTable, fieldList, rowFilter, groupBy ); 
            return dt; 
        } 
 
        #endregion 
 
        Join TablesJoin TablesJoin Tables#region Join Tables 
 
        public DataTable SelectJoinInto(string tableName, DataTable sourceTable, string fieldList, string rowFilter, string sort) 
        { 
            DataTable dt = CreateJoinTable( tableName, sourceTable, fieldList ); 
            InsertJoinInto( dt, sourceTable, fieldList, rowFilter, sort ); 
            return dt; 
        } 
 
        #endregion 
 
        Create TableCreate TableCreate Table#region Create Table 
 
        public DataTable CreateTable(string tableName, string fieldList) 
        { 
            DataTable dt = new DataTable( tableName ); 
            DataColumn dc; 
            string[] Fields = fieldList.Split( ',' ); 
            string[] FieldsParts; 
            string Expression; 
            foreach ( string Field in Fields ) 
            { 
                FieldsParts = Field.Trim().Split( " ".ToCharArray(), 3 ); // allow for spaces in the expression 
                // add fieldname and datatype 
                if ( FieldsParts.Length == 2 ) 
                { 
                    dc = dt.Columns.Add( FieldsParts[ 0 ].Trim(), Type.GetType( "System." + FieldsParts[ 1 ].Trim(), true, true ) ); 
                    dc.AllowDBNull = true; 
                } 
                else if ( FieldsParts.Length == 3 ) // add fieldname, datatype, and expression 
                { 
                    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 
                { 
                    return null; 
                } 
            } 
            if ( ds != null ) 
            { 
                ds.Tables.Add( dt ); 
            } 
            return dt; 
        } 
 
        public DataTable CreateTable(string tableName, string fieldList, string keyFieldList) 
        { 
            DataTable dt = CreateTable( tableName, fieldList ); 
            string[] KeyFields = keyFieldList.Split( ',' ); 
            if ( KeyFields.Length > 0 ) 
            { 
                DataColumn[] KeyFieldColumns = new DataColumn[KeyFields.Length]; 
                int i; 
                for ( i = 1; i == KeyFields.Length - 1; ++i ) 
                { 
                    KeyFieldColumns[ i ] = dt.Columns[ KeyFields[ i ].Trim() ]; 
                } 
                dt.PrimaryKey = KeyFieldColumns; 
            } 
            return dt; 
        } 
 
        #endregion 
    } 
}
调用：
dt = dsHelper.SelectGroupByInto("OrderSummary", ds.Tables["Employees"],
    "EmployeeID,sum(Amount) Total,min(Amount) Min,max(Amount) Max", "EmployeeID<5", "EmployeeID");

此调用示例创建新 DataTable 具有四个字段 （EmployeeID、 总计、, Min 和 Max） 以及 OrderSummary TableName 。 这些四个字段具有相同数据类型， EmployeeID 和 Orders 表中数量字段。 然后本示例从 Orders DataTable , 读取记录并将记录写入 OrderSummary DataTable 。 OrderSummary DataTable 包含 EmployeeID 域和三个不同的聚合的金额字段上筛选， EmployeeID EmployeeID 5 和， 是上分组 （和按） < "。 如果 GroupBy 参数是空白, DataTable 目标包含聚合所有输入仅单个记录