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

本文实例总结了asp.net DataTable相关操作。，具体如下：

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