This article summarizes DataTable-related operations. Share it for your reference, as follows:
#region DataTable filtering, sorting returns a new DataTable that meets the conditions, or directly use DefaultView to return according to the conditions./// <summary>
/// DataTable filter, sorting returns a new DataTable that meets the conditions, or directly use DefaultView to return according to the conditions/// eg:SortExprDataTable(dt,"Sex='Male'","Time Desc",1)/// </summary>
/// <param name="dt">Incoming DataTable</param>/// <param name="strExpr">Filters</param>/// <param name="strSort">Sorting conditions</param>/// <param name="mode">1, directly use DefaultView to return according to conditions, which is more efficient; 2, DataTable filter, sorting and returning a new DataTable that meets the conditions.</param>public static DataTable SortDataTable(DataTable dt, string strExpr, string strSort, int mode)
{
switch (mode)
{
case 1:
//Method 1: Use DefaultView directly to return according to the conditions = strExpr;
= strSort;
return dt;
case 2:
//Method 2 DataTable filtering, sorting returns a new DataTable composed of rows that meet the criteria. DataTable dt1 = new DataTable();
DataRow[] GetRows = (strExpr, strSort);
//Copy DataTable dt structure does not contain data dt1 = ();
foreach (DataRow row in GetRows)
{
();
}
return dt1;
default:
return dt;
}
}
#endregion
#region Get the first few data of DataTable/// <summary>
/// Get the first few data of DataTable/// </summary>
/// <param name="TopItem">N first data</param>/// <param name="oDT">Source DataTable</param>/// <returns></returns>
public static DataTable DtSelectTop(int TopItem, DataTable oDT)
{
if ( < TopItem) return oDT;
DataTable NewTable = ();
DataRow[] rows = ("1=1");
for (int i = 0; i < TopItem; i++)
{
((DataRow)rows[i]);
}
return NewTable;
}
#endregion
#region Gets the data of the specified column in the DataTable/// <summary>
/// Get the data of the specified column in the DataTable/// </summary>
/// <param name="dt">Data source</param>/// <param name="tableName">New DataTable noun</param>/// <param name="strColumns">Specified column name collection</param>/// <returns>Returns to new DataTable</returns>public static DataTable GetTableColumn(DataTable dt, string tableName, params string[] strColumns)
{
DataTable dtn = new DataTable();
if (dt == null)
{
throw new ArgumentNullException("The parameter dt cannot be null");
}
try
{
dtn = (tableName, true, strColumns);
}
catch (Exception e)
{
throw new Exception();
}
return dtn;
}
#endregion
using System;
using ;
using ;
using ;
using ;
using ;
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 == && objectB == )
{
return true;
}
if ( objectA == || objectB == )
{
return false;
}
return ( ( objectB ) );
}
private bool RowEqual(DataRow rowA, DataRow rowB, DataColumnCollection columns)
{
bool result = true;
for ( int i = 0; i < ; 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 = ( ',' );
for ( int i = 0; i <= - 1; i++ )
{
Field = new FieldInfo();
FieldParts = Fields[ i ].Trim().Split( ' ' );
switch ( )
{
case 1:
//to be set at the end of the loop
break;
case 2:
= FieldParts[ 1 ];
break;
default:
return;
}
FieldParts = FieldParts[ 0 ].Split( '.' );
switch ( )
{
case 1:
= FieldParts[ 0 ];
break;
case 2:
if ( allowRelation == false )
{
return;
}
= FieldParts[ 0 ].Trim();
= FieldParts[ 1 ].Trim();
break;
default:
return;
}
if ( == null )
{
= ;
}
m_FieldInfo.Add( Field );
}
}
private DataTable CreateTable(string tableName, DataTable sourceTable, string fieldList)
{
DataTable dt;
if ( () == "" )
{
dt = ();
= tableName;
}
else
{
dt = new DataTable( tableName );
ParseFieldList( fieldList, false );
DataColumn dc;
foreach ( FieldInfo Field in m_FieldInfo )
{
dc = [ ];
DataColumn column = new DataColumn();
= ;
= ;
= ;
= ;
( column );
}
}
if ( ds != null )
{
( dt );
}
return dt;
}
private void InsertInto(DataTable destTable, DataTable sourceTable,
string fieldList, string rowFilter, string sort)
{
ParseFieldList( fieldList, false );
DataRow[] rows = ( rowFilter, sort );
DataRow destRow;
foreach ( DataRow sourceRow in rows )
{
destRow = ();
if ( fieldList == "" )
{
foreach ( DataColumn dc in )
{
if ( == "" )
{
destRow[ dc ] = sourceRow[ ];
}
}
}
else
{
foreach ( FieldInfo field in m_FieldInfo )
{
destRow[ ] = sourceRow[ ];
}
}
( destRow );
}
}
private void ParseGroupByFieldList(string FieldList)
{
if ( GroupByFieldList == FieldList )
{
return;
}
GroupByFieldInfo = new ArrayList();
FieldInfo Field;
string[] FieldParts;
string[] Fields = ( ',' );
for ( int i = 0; i <= - 1; i++ )
{
Field = new FieldInfo();
FieldParts = Fields[ i ].Trim().Split( ' ' );
switch ( )
{
case 1:
//to be set at the end of the loop
break;
case 2:
= FieldParts[ 1 ];
break;
default:
return;
}
FieldParts = FieldParts[ 0 ].Split( '(' );
switch ( )
{
case 1:
= FieldParts[ 0 ];
break;
case 2:
= FieldParts[ 0 ].Trim().ToLower();
= FieldParts[ 1 ].Trim( ' ', ')' );
break;
default:
return;
}
if ( == null )
{
if ( == null )
{
= ;
}
else
{
= + "of" + ;
}
}
( Field );
}
GroupByFieldList = FieldList;
}
private DataTable CreateGroupByTable(string tableName, DataTable sourceTable, string fieldList)
{
if ( fieldList == null || == 0 )
{
return ();
}
else
{
DataTable dt = new DataTable( tableName );
ParseGroupByFieldList( fieldList );
foreach ( FieldInfo Field in GroupByFieldInfo )
{
DataColumn dc = [ ];
if ( == null )
{
( , , );
}
else
{
( , );
}
}
if ( ds != null )
{
( dt );
}
return dt;
}
}
private void InsertGroupByInto(DataTable destTable, DataTable sourceTable, string fieldList,
string rowFilter, string groupBy)
{
if ( fieldList == null || == 0 )
{
return;
}
ParseGroupByFieldList( fieldList );
ParseFieldList( groupBy, false );
DataRow[] rows = ( 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[ ], sourceRow[ ] ) )
{
sameRow = false;
break;
}
}
if ( !sameRow )
{
( destRow );
}
}
if ( !sameRow )
{
destRow = ();
rowCount = 0;
}
rowCount += 1;
foreach ( FieldInfo field in GroupByFieldInfo )
{
switch ( () )
{
case null:
case "":
case "last":
destRow[ ] = sourceRow[ ];
break;
case "first":
if ( rowCount == 1 )
{
destRow[ ] = sourceRow[ ];
}
break;
case "count":
destRow[ ] = rowCount;
break;
case "sum":
destRow[ ] = Add( destRow[ ], sourceRow[ ] );
break;
case "max":
destRow[ ] = Max( destRow[ ], sourceRow[ ] );
break;
case "min":
if ( rowCount == 1 )
{
destRow[ ] = sourceRow[ ];
}
else
{
destRow[ ] = Min( destRow[ ], sourceRow[ ] );
}
break;
}
}
lastSourceRow = sourceRow;
}
if ( destRow != null )
{
( destRow );
}
}
private object Min(object a, object b)
{
if ( ( a is DBNull ) || ( b is DBNull ) )
{
return ;
}
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 ();
}
else
{
DataTable dt = new DataTable( tableName );
ParseFieldList( fieldList, true );
foreach ( FieldInfo field in m_FieldInfo )
{
if ( == null )
{
DataColumn dc = [ ];
( , , );
}
else
{
DataColumn dc = [ ].[ ];
( , , );
}
}
if ( ds != null )
{
( 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 = ( rowFilter, sort );
foreach ( DataRow SourceRow in Rows )
{
DataRow DestRow = ();
foreach ( FieldInfo Field in m_FieldInfo )
{
if ( == null )
{
DestRow[ ] = SourceRow[ ];
}
else
{
DataRow ParentRow = ( );
DestRow[ ] = ParentRow[ ];
}
}
( DestRow );
}
}
}
#endregion
#region SelectDistinct / Distinct
/// <summary>
/// Select the unduplicated row from sourceTable according to fieldName. /// Equivalent to select distinct fieldName from sourceTable /// </summary>
/// <param name="tableName">Table name</param> /// <param name="sourceTable">SourceDataTable</param> /// <param name="fieldName">Column name</param> /// <returns>A new DataTable without duplicate rows, the column only includes the column specified by fieldName</returns> public DataTable SelectDistinct(string tableName, DataTable sourceTable, string fieldName)
{
DataTable dt = new DataTable( tableName );
( fieldName, [ fieldName ].DataType );
object lastValue = null;
foreach ( DataRow dr in ( "", fieldName ) )
{
if ( lastValue == null || !( ColumnEqual( lastValue, dr[ fieldName ] ) ) )
{
lastValue = dr[ fieldName ];
( new object[]{lastValue} );
}
}
if ( ds != null && !( tableName ) )
{
( dt );
}
return dt;
}
/// <summary>
/// Select the unduplicated row from sourceTable according to fieldName. /// Equivalent to select distinct fieldName1,fieldName2,,fieldNamen from sourceTable /// </summary>
/// <param name="tableName">Table name</param> /// <param name="sourceTable">SourceDataTable</param> /// <param name="fieldNames">Column name array</param> /// <returns>A new DataTable without duplicate rows, the column only includes the columns specified in fieldNames</returns> public DataTable SelectDistinct(string tableName, DataTable sourceTable, string[] fieldNames)
{
DataTable dt = new DataTable( tableName );
object[] values = new object[];
string fields = "";
for ( int i = 0; i < ; i++ )
{
( fieldNames[ i ], [ fieldNames[ i ] ].DataType );
fields += fieldNames[ i ] + ",";
}
fields = ( - 1, 1 );
DataRow lastRow = null;
foreach ( DataRow dr in ( "", fields ) )
{
if ( lastRow == null || !( RowEqual( lastRow, dr, ) ) )
{
lastRow = dr;
for ( int i = 0; i < ; i++ )
{
values[ i ] = dr[ fieldNames[ i ] ];
}
( values );
}
}
if ( ds != null && !( tableName ) )
{
( dt );
}
return dt;
}
/// <summary>
/// Select the unduplicated row from sourceTable according to fieldName. /// and contains all columns in sourceTable. /// </summary>
/// <param name="tableName">Table name</param> /// <param name="sourceTable">Source table</param> /// <param name="fieldName">field</param> /// <returns> A new DataTable with no duplicate lines</returns> public DataTable Distinct(string tableName, DataTable sourceTable, string fieldName)
{
DataTable dt = ();
= tableName;
object lastValue = null;
foreach ( DataRow dr in ( "", fieldName ) )
{
if ( lastValue == null || !( ColumnEqual( lastValue, dr[ fieldName ] ) ) )
{
lastValue = dr[ fieldName ];
( );
}
}
if ( ds != null && !( tableName ) )
{
( dt );
}
return dt;
}
/// <summary>
/// Follow fieldNames to select unduplicated rows from sourceTable. /// and contains all columns in sourceTable. /// </summary>
/// <param name="tableName">Table name</param> /// <param name="sourceTable">Source table</param> /// <param name="fieldNames">field</param> /// <returns> A new DataTable with no duplicate lines</returns> public DataTable Distinct(string tableName, DataTable sourceTable, string[] fieldNames)
{
DataTable dt = ();
= tableName;
string fields = "";
for ( int i = 0; i < ; i++ )
{
fields += fieldNames[ i ] + ",";
}
fields = ( - 1, 1 );
DataRow lastRow = null;
foreach ( DataRow dr in ( "", fields ) )
{
if ( lastRow == null || !( RowEqual( lastRow, dr, ) ) )
{
lastRow = dr;
( );
}
}
if ( ds != null && !( tableName ) )
{
( dt );
}
return dt;
}
#endregion
#region Select Table Into
/// <summary>
/// Sort by sort, filter sourceTable by rowFilter, /// Copy the data of the field specified in the fieldList to the new DataTable and return it /// </summary>
/// <param name="tableName">Table name</param> /// <param name="sourceTable">Source table</param> /// <param name="fieldList">field list</param> /// <param name="rowFilter">Filter Conditions</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 = ( ',' );
string[] FieldsParts;
string Expression;
foreach ( string Field in Fields )
{
FieldsParts = ().Split( " ".ToCharArray(), 3 ); // allow for spaces in the expression
// add fieldname and datatype
if ( == 2 )
{
dc = ( FieldsParts[ 0 ].Trim(), ( "System." + FieldsParts[ 1 ].Trim(), true, true ) );
= true;
}
else if ( == 3 ) // add fieldname, datatype, and expression
{
Expression = FieldsParts[ 2 ].Trim();
if ( () == "REQUIRED" )
{
dc = ( FieldsParts[ 0 ].Trim(), ( "System." + FieldsParts[ 1 ].Trim(), true, true ) );
= false;
}
else
{
dc = ( FieldsParts[ 0 ].Trim(), ( "System." + FieldsParts[ 1 ].Trim(), true, true ), Expression );
}
}
else
{
return null;
}
}
if ( ds != null )
{
( dt );
}
return dt;
}
public DataTable CreateTable(string tableName, string fieldList, string keyFieldList)
{
DataTable dt = CreateTable( tableName, fieldList );
string[] KeyFields = ( ',' );
if ( > 0 )
{
DataColumn[] KeyFieldColumns = new DataColumn[];
int i;
for ( i = 1; i == - 1; ++i )
{
KeyFieldColumns[ i ] = [ KeyFields[ i ].Trim() ];
}
= KeyFieldColumns;
}
return dt;
}
#endregion
}
}
For more information about relevant content, please view the topic of this site:Summary of operation json skills》、《Summary of string operation techniques》、《Summary of operating XML skills》、《Summary of file operation skills》、《Ajax tips summary topic"and"Summary of cache operation skills》。
I hope this article will be helpful to everyone's programming.