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; }
        }

        Construction#region Construction

        public DataSetHelper()
        {
            ds = null;
        }

        public DataSetHelper(ref DataSet dataSet)
        {
            ds = dataSet;
        }

        #endregion

        Private 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 / 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 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 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 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 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
    }

posted on 2005-01-05 17:05 风满袖 阅读(3387) 评论(11)  编辑 收藏 引用 收藏至365Key 所属分类: C#&Asp.net

评论

 

if ( ds != null && ds.Tables.Contains( tableName ) )
{
ds.Tables.Add( dt );
}
改了原来的代码，明显有错误么。  回复   

# re: DataSetHelper——操作DataSet的工具类 2005-07-18 09:16 James

@arsser:
thx,的确是一个明显的错误。
我的原意是：

if ( ds != null && !ds.Tables.Contains( tableName ) )
{
ds.Tables.Add( dt );
}

public DataTable Distinct(string tableName, DataTable sourceTable, string[] fieldNames)
是我自己加的方法。

我已经修改了错误的地方。
再次感谢，我太粗心了。  回复   

# re: DataSetHelper——操作DataSet的工具类 2006-02-23 10:42 芸

你应该不是实现Group by 功能吧，应该是Sort by 功能。  回复   

# re: DataSetHelper——操作DataSet的工具类 2006-03-06 09:06 format

private DataTable CreateGroupByTable(string tableName, DataTable sourceTable, string fieldList)
这里是私有方法了，我参考微软的代码，这个应该是提供GroupBy功能的，应该是public吧。  回复   

# re: DataSetHelper——操作DataSet的工具类 2006-03-06 14:06 风满袖

@format

CreateGroupByTable是私有的.
SelectGroupByInto方法是共有的.
  回复   

# re: DataSetHelper——操作DataSet的工具类 2006-04-04 04:10 梁广永

能提供源码吗？
谢谢！
whycome@gmail.com  回复   

# re: DataSetHelper——操作DataSet的工具类 2006-04-11 17:53 风满袖

@梁广永
源码我全部贴出来了  回复   

# re: DataSetHelper——操作DataSet的工具类 2006-04-17 20:53 yezie

郁闷，我都是 未将对象引用设置到对象的实例
怎么用？举个简单例子  回复   

# re: DataSetHelper——操作DataSet的工具类 2006-04-21 10:53 路过-疑惑

public DataSetHelper(ref DataSet dataSet)
{
ds = dataSet;
}


请问为什么要传递ref参数？调用ds.Tables不出异常吗？  回复   

# re: DataSetHelper——操作DataSet的工具类 2006-09-05 15:52 周奔驰[匿名]

ref我觉得没必要 又不是值类型  回复   

# re: DataSetHelper——操作DataSet的工具类 2006-10-25 09:47 ztchen

不错，但明显coding的功力还欠火候，多进步吧  回复     

#  re: DataSetHelper——操作DataSet的工具类 2005-07-17 16:09 arsser