DataSetHelper 操作dataset 辅助类（2） DataTable中进行Distinct、Group by、Join、Create

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
 
        #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
 
        #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
 
        #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
 
        #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
 
        #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 目标包含聚合所有输入仅单个记录。

参考地址：http://support.microsoft.com/kb/326145
http://topic.csdn.net/t/20060514/14/4750499.html