this is an amasing set of codes which can help programmers to query for distinct rows in datatable by column(s) from humancompiler. I did little bit of tweaking or match one of my projects needs.
Here i am posting the code below:
VB
Public Shared Function SelectDistinct(ByVal SourceTable As DataTable, ByVal ParamArray FieldNames() As String) As DataTable
Dim lastValues() As Object
Dim newTable As DataTable
If FieldNames Is Nothing OrElse FieldNames.Length = 0 Then
Throw New ArgumentNullException(“FieldNames”)
End If
lastValues = New Object(FieldNames.Length – 1) {}
newTable = New DataTable
For Each field As String In FieldNames
newTable.Columns.Add(field, SourceTable.Columns(field).DataType)
Next
For Each Row As DataRow In SourceTable.Select(“”, String.Join(“, “, FieldNames))
If Not fieldValuesAreEqual(lastValues, Row, FieldNames) Then
newTable.Rows.Add(createRowClone(Row, newTable.NewRow(), FieldNames))
setLastValues(lastValues, Row, FieldNames)
End If
Next
Return newTable
End Function
Private Shared Function fieldValuesAreEqual(ByVal lastValues() As Object, ByVal currentRow As DataRow, ByVal fieldNames() As String) As Boolean
Dim areEqual As Boolean = True
For i As Integer = 0 To fieldNames.Length – 1
If lastValues(i) Is Nothing OrElse Not lastValues(i).Equals(currentRow(fieldNames(i))) Then
areEqual = False
Exit For
End If
Next
Return areEqual
End Function
Private Shared Function createRowClone(ByVal sourceRow As DataRow, ByVal newRow As DataRow, ByVal fieldNames() As String) As DataRow
For Each field As String In fieldNames
newRow(field) = sourceRow(field)
Next
Return newRow
End Function
Private Shared Sub setLastValues(ByVal lastValues() As Object, ByVal sourceRow As DataRow, ByVal fieldNames() As String)
For i As Integer = 0 To fieldNames.Length – 1
lastValues(i) = sourceRow(fieldNames(i))
Next
End Sub
C#
private 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 i = 0; i < fieldNames.Length; i++)
{
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]];
}
This can be also done in bit simpler manner below:
public static DataTable SelectDistinct(string[] pColumnNames, DataTable pOriginalTable)
{
DataTable distinctTable = new DataTable();
int numColumns = pColumnNames.Length;
for (int i = 0; i < numColumns; i++)
{
distinctTable.Columns.Add(pColumnNames[i], pOriginalTable.Columns[pColumnNames[i]].DataType);
}
Hashtable trackData = new Hashtable();
foreach (DataRow currentOriginalRow in pOriginalTable.Rows)
{
StringBuilder hashData = new StringBuilder();
DataRow newRow = distinctTable.NewRow();
for (int i = 0; i < numColumns; i++)
{
hashData.Append(currentOriginalRow[pColumnNames[i]].ToString());
newRow[pColumnNames[i]] = currentOriginalRow[pColumnNames[i]];
}
if (!trackData.ContainsKey(hashData.ToString()))
{
trackData.Add(hashData.ToString(), null);
distinctTable.Rows.Add(newRow);
}
}
return distinctTable;
}
However, there is even simpler code if you use dataview as below:
VB Code:
——————————————————
Public Function SelectDistinct(ByVal SourceTable As DataTable, ByVal ParamArray Columns() As String) As DataTable
Dim Result As DataTable = New DataTable()
If SourceTable IsNot Nothing Then
Dim DView As DataView = SourceTable.DefaultView
Try
Result = DView.ToTable(True, Columns)
Catch ex As Exception
End Try
End If
Return Result
End Function
——————————————————
C# Code:
——————————————————
public DataTable SelectDistinct(DataTable SourceTable, params string[] Columns) {
DataTable Result = new DataTable();
if (SourceTable != null) {
DataView DView = SourceTable.DefaultView;
try {
Result = DView.ToTable(true, Columns);
}
catch (Exception ex) {
}
}
return Result;
}
Well, thats about how to select distinct rows by column in datatable but then one should be remembered that create datatable takes memory spaces since they are virtual tables.
Posted by Vishal Reddy 