public class DataTableDistinctHelper
{
/// <summary>
/// 返回执行Select distinct后的DataTable,注意,只返回FieldNames列中所有行上都有值的行。
/// 举例说明:
/// FieldNames:一级部门、二级部门、三级部门
/// 如果sourceTable中所有的行中,没有一行是“一级部门、二级部门、三级部门”同时都有值的行,那么返回的DataTable表中
/// 没有任何行。
/// </summary>
/// <param name="SourceTable">源数据表</param>
/// <param name="FieldNames">字段集</param>
/// <returns></returns>
public static 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 static bool fieldValuesAreEqual(object[] lastValues, DataRow currentRow, string[] fieldNames)
{
bool areEqual = true;
//检查是否所有列都不为空的行,如果不存在,则直接过滤掉
for (int j = 0; j < fieldNames.Length; j++)
{
if (currentRow[fieldNames[j]] == DBNull.Value || currentRow[fieldNames[j]].ToString() == "")
{
return areEqual;
}
}
for (int i = 0; i < fieldNames.Length; i++)
{
//if (currentRow[fieldNames[i]] != DBNull.Value && currentRow[fieldNames[i]].ToString() != "") //如果为空 直接过滤掉
//{
if (lastValues[i] == null || !lastValues[i].Equals(currentRow[fieldNames[i]]))
{
areEqual = false;
break;
}
//}
}
return areEqual;
}
private static DataRow createRowClone(DataRow sourceRow, DataRow newRow, string[] fieldNames)
{
foreach (string field in fieldNames)
newRow[field] = sourceRow[field];
return newRow;
}
private static void setLastValues(object[] lastValues, DataRow sourceRow, string[] fieldNames)
{
for (int i = 0; i < fieldNames.Length; i++)
lastValues[i] = sourceRow[fieldNames[i]];
}
}
{
/// <summary>
/// 返回执行Select distinct后的DataTable,注意,只返回FieldNames列中所有行上都有值的行。
/// 举例说明:
/// FieldNames:一级部门、二级部门、三级部门
/// 如果sourceTable中所有的行中,没有一行是“一级部门、二级部门、三级部门”同时都有值的行,那么返回的DataTable表中
/// 没有任何行。
/// </summary>
/// <param name="SourceTable">源数据表</param>
/// <param name="FieldNames">字段集</param>
/// <returns></returns>
public static 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 static bool fieldValuesAreEqual(object[] lastValues, DataRow currentRow, string[] fieldNames)
{
bool areEqual = true;
//检查是否所有列都不为空的行,如果不存在,则直接过滤掉
for (int j = 0; j < fieldNames.Length; j++)
{
if (currentRow[fieldNames[j]] == DBNull.Value || currentRow[fieldNames[j]].ToString() == "")
{
return areEqual;
}
}
for (int i = 0; i < fieldNames.Length; i++)
{
//if (currentRow[fieldNames[i]] != DBNull.Value && currentRow[fieldNames[i]].ToString() != "") //如果为空 直接过滤掉
//{
if (lastValues[i] == null || !lastValues[i].Equals(currentRow[fieldNames[i]]))
{
areEqual = false;
break;
}
//}
}
return areEqual;
}
private static DataRow createRowClone(DataRow sourceRow, DataRow newRow, string[] fieldNames)
{
foreach (string field in fieldNames)
newRow[field] = sourceRow[field];
return newRow;
}
private static void setLastValues(object[] lastValues, DataRow sourceRow, string[] fieldNames)
{
for (int i = 0; i < fieldNames.Length; i++)
lastValues[i] = sourceRow[fieldNames[i]];
}
}