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