asp. net DataTable related operation highlights of screening taking the first N data removing duplicate rows obtaining specified column data etc.

  • 2021-07-24 10:47:58
  • OfStack

This article summarizes the asp. net DataTable related operations with examples. Share it for your reference, as follows:


#region DataTable Filter, sort returns a new consisting of qualified rows DataTable Or use it directly DefaultView Return by Condition 
/// <summary>
/// DataTable Filter, sort returns a new consisting of qualified rows DataTable Or use it directly DefaultView Return by Condition 
/// eg:SortExprDataTable(dt,"Sex=' Male '","Time Desc",1)
/// </summary>
/// <param name="dt"> Incoming DataTable</param>
/// <param name="strExpr"> Screening criteria </param>
/// <param name="strSort"> Sorting condition </param>
/// <param name="mode">1, Direct use DefaultView Return by Condition , High efficiency ;2,DataTable Filter, sort returns a new consisting of qualified rows DataTable</param>
public static DataTable SortDataTable(DataTable dt, string strExpr, string strSort, int mode)
{
  switch (mode)
  {
    case 1:
      // Method 1 Direct use DefaultView Return by Condition 
      dt.DefaultView.RowFilter = strExpr;
      dt.DefaultView.Sort = strSort;
      return dt;
    case 2:
      // Method 2   DataTable Filter, sort returns a new consisting of qualified rows DataTable
      DataTable dt1 = new DataTable();
      DataRow[] GetRows = dt.Select(strExpr, strSort);
      // Duplicate DataTable dt Structure does not contain data 
      dt1 = dt.Clone();
      foreach (DataRow row in GetRows)
      {
        dt1.Rows.Add(row.ItemArray);
      }
      return dt1;
    default:
      return dt;
  }
}
#endregion


#region  Get DataTable The first few pieces of data 
/// <summary>
///  Get DataTable The first few pieces of data 
/// </summary>
/// <param name="TopItem"> Front N Bar data </param>
/// <param name="oDT"> Source 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  Get DataTable Data for the specified column in the 
/// <summary>
///  Get DataTable Data for the specified column in the 
/// </summary>
/// <param name="dt"> Data source </param>
/// <param name="tableName"> New DataTable Nouns of </param>
/// <param name="strColumns"> The specified collection of column names </param>
/// <returns> Returns the new DataTable</returns>
public static DataTable GetTableColumn(DataTable dt, string tableName, params string[] strColumns)
{
  DataTable dtn = new DataTable();
  if (dt == null)
  {
    throw new ArgumentNullException(" Parameter dt Cannot be for 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 Assistant 
  /// </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>
    ///  According to fieldName From sourceTable Select a non-duplicate line in the, 
    ///  Equivalent to select distinct fieldName from sourceTable
    /// </summary>
    /// <param name="tableName"> Table name </param>
    /// <param name="sourceTable"> Source DataTable</param>
    /// <param name="fieldName"> Column name </param>
    /// <returns>1 New without duplicate lines DataTable The column includes only fieldName Specified column </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>
    ///  According to fieldName From sourceTable Select a non-duplicate line in the, 
    ///  Equivalent to select distinct fieldName1,fieldName2,,fieldNamen from sourceTable
    /// </summary>
    /// <param name="tableName"> Table name </param>
    /// <param name="sourceTable"> Source DataTable</param>
    /// <param name="fieldNames"> Array of column names </param>
    /// <returns>1 New without duplicate lines DataTable The column includes only fieldNames Columns specified in </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>
    ///  According to fieldName From sourceTable Select a non-duplicate line in the, 
    ///  And contains sourceTable Gets all the columns in the. 
    /// </summary>
    /// <param name="tableName"> Table name </param>
    /// <param name="sourceTable"> Source table </param>
    /// <param name="fieldName"> Field </param>
    /// <returns>1 New without duplicate lines 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>
    ///  According to fieldNames From sourceTable Select a non-duplicate line in the, 
    ///  And contains sourceTable Gets all the columns in the. 
    /// </summary>
    /// <param name="tableName"> Table name </param>
    /// <param name="sourceTable"> Source table </param>
    /// <param name="fieldNames"> Field </param>
    /// <returns>1 New without duplicate lines 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>
    ///  Press sort Sort by rowFilter Filter sourceTable , 
    ///  Duplicate fieldList Data for the fields specified in the new DataTable And return to it 
    /// </summary>
    /// <param name="tableName"> Table name </param>
    /// <param name="sourceTable"> Source table </param>
    /// <param name="fieldList"> Field list </param>
    /// <param name="rowFilter"> Filter condition </param>
    /// <param name="sort"> Sort </param>
    /// <returns> New 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
  }
}

For more readers interested in asp. net, please check the topics of this site: "asp. net Operation json Skills Summary", "asp. net String Operation Skills Summary", "asp. net Operation XML Skills Summary", "asp. net File Operation Skills Summary", "asp. net ajax Skills Summary" and "asp. net Cache Operation Skills Summary".

I hope this paper is helpful to everyone's asp. net programming.


Related articles: