Geeks With Blogs

Michael Freidgeim's Blog MS .Net Development

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 on Friday, September 15, 2006 12:19 PM | Back to top


Comments on this post: DataSetHelper.SelectDistinct method for multiple columns

# re: DataSetHelper.SelectDistinct method for multiple columns
Requesting 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?

Left by Ricardo on Mar 20, 2007 2:04 AM

# re: DataSetHelper.SelectDistinct method for multiple columns
Requesting Gravatar...
Ricardo,
Use debugger to find spelling of which fields from your list does not match column names in DataTable.
Left by Michael Freidgeim on Mar 20, 2007 1:11 PM

Your comment:
 (will show your gravatar)


Copyright © Michael Freidgeim | Powered by: GeeksWithBlogs.net | Join free