欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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程序设计有所帮助。

上一篇:

下一篇: