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

 #region DataTable筛选，排序返回符合条件行组成的新DataTable或直接用DefaultView按条件返回 /// 
 /// DataTable筛选，排序返回符合条件行组成的新DataTable或直接用DefaultView按条件返回 /// eg:SortExprDataTable(dt,"Sex='男'","Time Desc",1) /// 
 /// 传入的DataTable /// 筛选条件 /// 排序条件 /// 1,直接用DefaultView按条件返回,效率较高;2,DataTable筛选，排序返回符合条件行组成的新DataTable 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前几条数据 /// 
 /// 获取DataTable前几条数据 /// 
 /// 前N条数据 /// 源DataTable ///  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中指定列的数据 /// 
 /// 获取DataTable中指定列的数据 /// 
 /// 数据源 /// 新的DataTable的名词 /// 指定的列名集合 /// 返回新的DataTable 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 { /// 
 /// DataSet助手 /// 
 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.Colu
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：asp.net提取多层嵌套json数据的方法_实用技巧_
                                                                
下一篇：asp.net实现DropDownList,TreeView,ListBox的无限极分类目录树_实用技巧_