DataSetHelper.SelectDistinct method for multiple columns

UPDATE: In .Net 2.0 there is DataView.ToTable Method (String, Boolean, String[]) that does, what my function (and MS KB  article 326176) was created to address. The article is now obsolete.

UPDATE: there is also optimized for performance class in CodeProject DataTable with SelectDistinct in VB .

I was using a DataSetHelper class based on MS kb article 326176 HOW TO: Implement a DataSet SELECT DISTINCT Helper Class.

However SelectDistinct  function from the article works only if you select distinct on single field.
To support multiple fields I created a function, based on Nageswara Reddy's post .

            public static DataTable SelectDistinct( DataTable SourceTable, string[] FieldNames)

            {// select distinct on multiple fields.

//From: Nageswara Reddy http://www.dotnet247.com/247reference/msgs/43/218182.aspx

            FieldNames = StringHelper.ToLower(FieldNames);

            DataTable DistinctTable = SourceTable.Clone();

                  DataColumn [] constraintColumns = new DataColumn[FieldNames.Length];

            int nFound=0;

                  for (int i =0 ; i< DistinctTable.Columns.Count ; i++)

            {

                if (Array.IndexOf(FieldNames, DistinctTable.Columns[i].ColumnName.ToLower()) >= 0)

                {

                    if (nFound >= FieldNames.Length)

                    {

                        throw new ApplicationException("Too many fields are similar to passed FieldNames " + FieldNames.ToString());

                    }

                    constraintColumns[nFound++] = DistinctTable.Columns[i];

                }

                  }

            //Report if passed field names are not found in the table

            if (nFound < FieldNames.Length)

            {

                throw new ApplicationException("Some of fields " + FieldNames.ToString() + " not found in the database");

            }

                  UniqueConstraint _UniqueConstraint = new UniqueConstraint(constraintColumns);

                  DistinctTable.Constraints.Add(_UniqueConstraint);

 

                  for (int i =0 ; i< SourceTable.Rows.Count ; i++)

                  {

                        try

                        {

                              DistinctTable.ImportRow(SourceTable.Rows[i]);

                        }

                        catch(Exception ex)

                        { // Keep quite

                              Debug.WriteLine(ex.ToString());

                        }

                  }

                  return DistinctTable;

            }

 

See also my previous post: “Handling missing source columns in DataSetHelper.InsertInto method”
posted @ Friday, September 15, 2006 12:19 PM
Print

Comments on this entry:

# re: DataSetHelper.SelectDistinct method for multiple columns

Left by Ricardo at 3/20/2007 2:04 AM
Gravatar
I keep getting this error:

"Some of fields not found in the database"

I know I passed all of them, do you what's wrong?

# re: DataSetHelper.SelectDistinct method for multiple columns

Left by Michael Freidgeim at 3/20/2007 1:11 PM
Gravatar
Ricardo,
Use debugger to find spelling of which fields from your list does not match column names in DataTable.

Your comment:



(not displayed)

 
 
 
 
 

Live Comment Preview:

 
«April»
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910