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筛选，排序返回符合条件行组成的新DataTableDataTable 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 Constructionpublic DataSetHelper（）{ds = null;}public DataSetHelper（ref DataSet dataSet）{ds = dataSet;}#endregion#region Private Methodsprivate 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 loopbreak;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 loopbreak;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 Tablepublic 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 Tablespublic 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 Tablepublic 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 datatypeif （ 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}}

更多关于asp.net相关内容感兴趣的读者可查看本站专题：《asp.net操作json技巧总结》、《asp.net字符串操作技巧汇总》、《asp.net操作XML技巧总结》、《asp.net文件操作技巧汇总》、《asp.net ajax技巧总结专题》及《asp.net缓存操作技巧总结》。

希望本文所述对大家asp.net程序设计有所帮助。