asp.net DataTable相關(guān)操作集錦(篩選,取前N條數(shù)據(jù),去重復(fù)行,獲取指定列數(shù)據(jù)等)
本文實(shí)例總結(jié)了asp.net DataTable相關(guā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); //復(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> /// 按照f(shuō)ieldName從sourceTable中選擇出不重復(fù)的行, /// 相當(dāng)于select distinct fieldName from sourceTable /// </summary> /// <param name="tableName">表名</param> /// <param name="sourceTable">源DataTable</param> /// <param name="fieldName">列名</param> /// <returns>一個(gè)新的不含重復(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> /// 按照f(shuō)ieldName從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>一個(gè)新的不含重復(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> /// 按照f(shuō)ieldName從sourceTable中選擇出不重復(fù)的行, /// 并且包含sourceTable中所有的列。 /// </summary> /// <param name="tableName">表名</param> /// <param name="sourceTable">源表</param> /// <param name="fieldName">字段</param> /// <returns>一個(gè)新的不含重復(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> /// 按照f(shuō)ieldNames從sourceTable中選擇出不重復(fù)的行, /// 并且包含sourceTable中所有的列。 /// </summary> /// <param name="tableName">表名</param> /// <param name="sourceTable">源表</param> /// <param name="fieldNames">字段</param> /// <returns>一個(gè)新的不含重復(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過(guò)濾sourceTable, /// 復(fù)制fieldList中指明的字段的數(shù)據(jù)到新DataTable,并返回之 /// </summary> /// <param name="tableName">表名</param> /// <param name="sourceTable">源表</param> /// <param name="fieldList">字段列表</param> /// <param name="rowFilter">過(guò)濾條件</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 } }
更多關(guān)于asp.net相關(guān)內(nèi)容感興趣的讀者可查看本站專題:《asp.net操作json技巧總結(jié)》、《asp.net字符串操作技巧匯總》、《asp.net操作XML技巧總結(jié)》、《asp.net文件操作技巧匯總》、《asp.net ajax技巧總結(jié)專題》及《asp.net緩存操作技巧總結(jié)》。
希望本文所述對(duì)大家asp.net程序設(shè)計(jì)有所幫助。
- ASP.NET中DataTable與DataSet之間的轉(zhuǎn)換示例
- Asp.net中DataTable導(dǎo)出到Excel的方法介紹
- asp.net 讀取Excel數(shù)據(jù)到DataTable的代碼
- ASP.NET DataTable去掉重復(fù)行的2種方法
- Asp.net實(shí)現(xiàn)選擇性的保留DataTable中的列
- ASP.NET中實(shí)現(xiàn)根據(jù)匿名類、datatable、sql生成實(shí)體類
- asp.net實(shí)現(xiàn)數(shù)據(jù)從DataTable導(dǎo)入到Excel文件并創(chuàng)建表的方法
- asp.net使用DataTable構(gòu)造Json字符串的方法
- asp.net異步獲取datatable并顯示的實(shí)現(xiàn)方法
- 在ASP.NET 2.0中操作數(shù)據(jù)之六十八:為DataTable添加額外的列
相關(guān)文章
一次.net?core異步線程設(shè)置超時(shí)時(shí)間的實(shí)戰(zhàn)記錄
這篇文章主要給大家介紹了關(guān)于.net?core異步線程設(shè)置超時(shí)時(shí)間的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2022-02-02MVC使用Log4Net進(jìn)行錯(cuò)誤日志記錄學(xué)習(xí)筆記4
這篇文章主要為大家詳細(xì)介紹了MVC使用Log4Net進(jìn)行錯(cuò)誤日志記錄,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2016-09-09ASP.NET中的Menu控件的應(yīng)用及XmlDataSource的了解
以前一直以為菜單都是通過(guò)sitemap制作的,最近看到項(xiàng)目中的方法是使用XmlDataSource榜定的,很是好奇,于是研究了一下,感覺(jué)還不錯(cuò)哦,感興趣的朋友也可以了解下啊,或許本文所提供的對(duì)你學(xué)習(xí)有所幫助呢2013-02-02ASP.NET Core 1.0 部署 HTTPS(.NET Core 1.0)
這篇文章主要為大家詳細(xì)介紹了ASP.NET Core 1.0 部署 HTTPS(.NET Core 1.0),感興趣的小伙伴們可以參考一下2016-07-07ASP.NET 通過(guò)攔截器記錄錯(cuò)誤日志的示例代碼
這篇文章主要介紹了ASP.NET 通過(guò)攔截器記錄錯(cuò)誤日志的示例代碼,幫助大家更好的理解和學(xué)習(xí)使用.NET技術(shù),感興趣的朋友可以了解下2021-04-04asp.net core webapi項(xiàng)目配置全局路由的方法示例
這篇文章主要介紹了asp.net core webapi項(xiàng)目配置全局路由的方法示例,小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2018-09-09