DataSetHelper——操作DataSet的工具类

微软知识库里面有个DataSetHelper ，可以对DataSet中的DataTable进行Distinct、Group by、Join和Create。

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; } 
        } 
 
        #region Construction 
 
        public DataSetHelper() 
        { 
            ds = null; 
        } 
 
        public DataSetHelper(ref DataSet dataSet) 
        { 
            ds = dataSet; 
        } 
 
        #endregion 
 
        #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 / Distinct 
 
        Select Table Into 
 
        Group By Table 
 
        Join Tables 
 
        Create Table 
    }

引用自 ：http://www.cnblogs.com/jiezhi/archive/2005/01/05/86838.html