asp.net DataTable相關(guān)操作集錦(篩選,取前N條數(shù)據(jù),去重復(fù)行,獲取指定列數(shù)據(jù)等)

#region DataTable篩選，排序返回符合條件行組成的新DataTable或直接用DefaultView按條件返回
/// summary>
/// DataTable篩選，排序返回符合條件行組成的新DataTable或直接用DefaultView按條件返回
/// eg:SortExprDataTable(dt,"Sex='男'","Time Desc",1)
/// /summary>
/// param name="dt">傳入的DataTable/param>
/// param name="strExpr">篩選條件/param>
/// param name="strSort">排序條件/param>
/// param name="mode">1,直接用DefaultView按條件返回,效率較高;2,DataTable篩選，排序返回符合條件行組成的新DataTable/param>
public static DataTable SortDataTable(DataTable dt, string strExpr, string strSort, int mode)
{
  switch (mode)
  {
    case 1:
      //方法一　直接用DefaultView按條件返回
      dt.DefaultView.RowFilter = strExpr;
      dt.DefaultView.Sort = strSort;
      return dt;
    case 2:
      //方法二　DataTable篩選，排序返回符合條件行組成的新DataTable
      DataTable dt1 = new DataTable();
      DataRow[] GetRows = dt.Select(strExpr, strSort);
      //復(fù)制DataTable dt結(jié)構(gòu)不包含數(shù)據(jù)
      dt1 = dt.Clone();
      foreach (DataRow row in GetRows)
      {
        dt1.Rows.Add(row.ItemArray);
      }
      return dt1;
    default:
      return dt;
  }
}
#endregion

#region 獲取DataTable前幾條數(shù)據(jù)
/// summary>
/// 獲取DataTable前幾條數(shù)據(jù)
/// /summary>
/// param name="TopItem">前N條數(shù)據(jù)/param>
/// param name="oDT">源DataTable/param>
/// returns>/returns>
public static DataTable DtSelectTop(int TopItem, DataTable oDT)
{
  if (oDT.Rows.Count  TopItem) return oDT;
  DataTable NewTable = oDT.Clone();
  DataRow[] rows = oDT.Select("1=1");
  for (int i = 0; i  TopItem; i++)
  {
    NewTable.ImportRow((DataRow)rows[i]);
  }
  return NewTable;
}
#endregion

#region 獲取DataTable中指定列的數(shù)據(jù)
/// summary>
/// 獲取DataTable中指定列的數(shù)據(jù)
/// /summary>
/// param name="dt">數(shù)據(jù)源/param>
/// param name="tableName">新的DataTable的名詞/param>
/// param name="strColumns">指定的列名集合/param>
/// returns>返回新的DataTable/returns>
public static DataTable GetTableColumn(DataTable dt, string tableName, params string[] strColumns)
{
  DataTable dtn = new DataTable();
  if (dt == null)
  {
    throw new ArgumentNullException("參數(shù)dt不能為null");
  }
  try
  {
    dtn = dt.DefaultView.ToTable(tableName, true, strColumns);
  }
  catch (Exception e)
  {
    throw new Exception(e.Message);
  }
  return dtn;
}
#endregion

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Collections;
using System.Text;
namespace GuanEasy
{
 /// 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
    #region SelectDistinct / Distinct
    /// summary>
    /// 按照fieldName從sourceTable中選擇出不重復(fù)的行，
    /// 相當(dāng)于select distinct fieldName from sourceTable
    /// /summary>
    /// param name="tableName">表名/param>
    /// param name="sourceTable">源DataTable/param>
    /// param name="fieldName">列名/param>
    /// returns>一個新的不含重復(fù)行的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中選擇出不重復(fù)的行，
    /// 相當(dāng)于select distinct fieldName1,fieldName2,,fieldNamen from sourceTable
    /// /summary>
    /// param name="tableName">表名/param>
    /// param name="sourceTable">源DataTable/param>
    /// param name="fieldNames">列名數(shù)組/param>
    /// returns>一個新的不含重復(fù)行的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中選擇出不重復(fù)的行，
    /// 并且包含sourceTable中所有的列。
    /// /summary>
    /// param name="tableName">表名/param>
    /// param name="sourceTable">源表/param>
    /// param name="fieldName">字段/param>
    /// returns>一個新的不含重復(fù)行的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中選擇出不重復(fù)的行，
    /// 并且包含sourceTable中所有的列。
    /// /summary>
    /// param name="tableName">表名/param>
    /// param name="sourceTable">源表/param>
    /// param name="fieldNames">字段/param>
    /// returns>一個新的不含重復(fù)行的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，
    /// 復(fù)制fieldList中指明的字段的數(shù)據(jù)到新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
  }
}