asp.net DataTable相关操作集锦(筛选,取前N条数据,去重复行,获取指定列数据等)
程序员文章站
2023-12-15 20:37:34
本文实例总结了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程序设计有所帮助。