asp.net DataTable相关操作集锦(筛选,取前N条数据,去重复行,获取指定列数据等)

#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);
      //复制datatable dt结构不包含数据
      dt1 = dt.clone();
      foreach (datarow row in getrows)
      {
        dt1.rows.add(row.itemarray);
      }
      return dt1;
    default:
      return dt;
  }
}
#endregion

#region 获取datatable前几条数据
/// <summary>
/// 获取datatable前几条数据
/// </summary>
/// <param name="topitem">前n条数据</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中指定列的数据
/// <summary>
/// 获取datatable中指定列的数据
/// </summary>
/// <param name="dt">数据源</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("参数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中选择出不重复的行，
    /// 相当于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
  }
}