asp.net DataTable相关操作集锦(筛选,取前N条数据,去重复行,获取指定列数据等)
程序员文章站
2023-12-15 20:32:40
本文实例总结了asp.net datatable相关操作。分享给大家供大家参考,具体如下:
#region datatable筛选,排序返回符合条件行组成的新da...
本文实例总结了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 } }
更多关于asp.net相关内容感兴趣的读者可查看本站专题:《asp.net操作json技巧总结》、《asp.net字符串操作技巧汇总》、《asp.net操作xml技巧总结》、《asp.net文件操作技巧汇总》、《asp.net ajax技巧总结专题》及《asp.net缓存操作技巧总结》。
希望本文所述对大家asp.net程序设计有所帮助。