们有时候需要对DataTable中数据进行Distinct处理,过滤掉重复的数据,本文给出了解决方法:
事例代码来源于:
Erik Porter's Blog Select DISTINCT on DataTable
http://weblogs.asp.net/eporter/archive/2005/02/10/370548.aspx 以及 HOWTOVisualC # .NET 中实现 DataSet SELECTDISTINCT Helper 类
http://support.microsoft.com/?id=326176
/// <summary> /// 返回执行Select distinct后的DataTable /// </summary> /// <param name="SourceTable"> 源数据表 </param> /// <param name="FieldNames"> 字段集 </param> /// <returns></returns>
private
DataTable SelectDistinct(DataTable SourceTable,
params
string
[] FieldNames)
{ object [] lastValues; DataTable newTable; DataRow[] orderedRows; if (FieldNames == null || FieldNames.Length == 0 ) throw new ArgumentNullException( " FieldNames " ); lastValues = new object [FieldNames.Length]; newTable = new DataTable(); foreach ( string fieldName in FieldNames) newTable.Columns.Add(fieldName, SourceTable.Columns[fieldName].DataType); orderedRows = SourceTable.Select( "" , string .Join( " , " , FieldNames)); foreach (DataRow row in orderedRows) { if ( ! fieldValuesAreEqual(lastValues, row, FieldNames)) { newTable.Rows.Add(createRowClone(row, newTable.NewRow(), FieldNames)); setLastValues(lastValues, row, FieldNames); } } return newTable; }
private
bool
fieldValuesAreEqual(
object
[] lastValues, DataRow currentRow,
string
[] fieldNames)
{ bool areEqual = true ; for ( int i = 0 ; i < fieldNames.Length; i ++ ) { if (lastValues[i] == null || ! lastValues[i].Equals(currentRow[fieldNames[i]])) { areEqual = false ; break ; } } return areEqual; }
private
DataRow createRowClone(DataRow sourceRow, DataRow newRow,
string
[] fieldNames)
{ foreach ( string field in fieldNames) newRow[field] = sourceRow[field]; return newRow; }
private
void
setLastValues(
object
[] lastValues, DataRow sourceRow,
string
[] fieldNames)
{ for ( int i = 0 ; i < fieldNames.Length; i ++ ) lastValues[i] = sourceRow[fieldNames[i]]; }
使用:
DataTable dt
=
(System.Data.DataTable)
this
.ViewState[
"
Mydt
"
];
string
[] fileds
=
{ " Filed1 " , " Filed2 " }
;
//
DISTINCT字段数组
DataTable newdt
=
this
.SelectDistinct(dt,fileds);
//
返回过滤后的DataTable
-======================================================================================
M_id, M_name 两个字段数据相同的DataRow必须是连续的,不能被分隔
string sql = string.Format("select M_id, M_name from dbo.BusinessWaitTable order by M_id, M_name ");
DataTable dt = GetDataTable(sql);
//得出datatable中所有行放入数组 DataRow[] drs = dt.Select();
//存放过滤后DataRow的可变长度数组 ArrayList al = new ArrayList();
//源数组不为空,开始过滤 if (drs.Length != 0) { //先加入源数组第一个元素 al.Add(drs[0]);
/*遍历源数组元素, * 将与目标数组最后一个元素不同的源数组元素加入到目标数组*/ for (int i = 0; i < drs.Length; i++) { if (drs[i]["M_id"].ToString() != ((DataRow)al[al.Count - 1])["M_id"].ToString() && drs[i]["M_name"].ToString() != ((DataRow)al[al.Count - 1])["M_name"].ToString()) { al.Add(drs[i]); } } }
2
用数组的方式实现DataTable中的distinct