Select DISTINCT from DataTable – Success!

So there was the evolution of ADO into .NET.. All these wonderful disconnected data objects to make life… well… easier? Turns out that the magic carpet of ADO.NET has it’s limitations. One such limitation is the lack of a "SELECT DISTINCT ..." against a DataTable. Why not? After all, it does have filtering and sorting… even a .Select(...) method.

Well, after scouring and searching the online resources, I found a half usable MS solution here: Microsofts one column hack

Nice, but I needed multiple columns…

So here’s my fix. Hope this helps someone somewhere:

       public static DataTable SelectDistinct(DataTable SourceTable, string[] FieldNames)
        {
            DataTable dt = new DataTable();
            foreach (string field in FieldNames)
            {
                dt.Columns.Add(field, SourceTable.Columns[field].DataType);
            }

            DataRow lastRow = null;
            DataRow tmpRow = null;
            DataRow[] sortedRows = SourceTable.Select("", CollectionHelper.ToStringCSV(FieldNames));
            bool added = false;
            foreach (DataRow dr in sortedRows)
            {
                added = true;

                if (lastRow == null)
                {
                    added = false;

                }
                else
                {

                    foreach (string field in FieldNames)
                    {
                        if (!ColumnEqual(dr[field], lastRow[field]))
                        {
                            added = false;
                        }
                    }
                }
                if (!added)
                {
                    tmpRow = dt.NewRow();
                    foreach (string field in FieldNames)
                    {
                        tmpRow[field] = dr[field];
                    }
                    dt.Rows.Add(tmpRow);
                    lastRow = tmpRow;
                }
            }

            return dt;
        }

        private static bool ColumnEqual(object A, object B)
        {

            // Compares two values to see if they are equal. Also compares DBNULL.Value.
            // Note: If your DataTable contains object fields, then you must extend this
            // function to handle them in a meaningful way if you intend to group on them.

            if (A == DBNull.Value && B == DBNull.Value) //  both are DBNull.Value
                return true;
            if (A == DBNull.Value || B == DBNull.Value) //  only one is DBNull.Value
                return false;
            return (A.Equals(B));  // value type standard comparison
        }

Cheers!
-Sean