Distinct - DataView/DataTable
-
Hi All, Does anybody of you know how to get distinct rows from DataView or DataTable. Thanks Ruchi
-
Hi All, Does anybody of you know how to get distinct rows from DataView or DataTable. Thanks Ruchi
The best way is to use the
DISTINCTkeyword in your SQL statement that fills theDataSet(which contains theDataTable) or theDataTable, but if for some reason you can't you could try a couple different ways. The default implementation does support getting distinct values as a simple method call, but you could either sort theDataView, enumerate it and skip adjacent duplicates; or keep a look-up table of what you've already enumerated and compare that as you enumerate yourDataTable. In most cases, the former method would be faster and would consume less space. If you use a look-up table, consider aHashtablewith the distinct field as the key and a reference to yourDataRowas the value (it'll be a reference, not a copy, making retrieval faster...unless you clone).Microsoft MVP, Visual C# My Articles
-
The best way is to use the
DISTINCTkeyword in your SQL statement that fills theDataSet(which contains theDataTable) or theDataTable, but if for some reason you can't you could try a couple different ways. The default implementation does support getting distinct values as a simple method call, but you could either sort theDataView, enumerate it and skip adjacent duplicates; or keep a look-up table of what you've already enumerated and compare that as you enumerate yourDataTable. In most cases, the former method would be faster and would consume less space. If you use a look-up table, consider aHashtablewith the distinct field as the key and a reference to yourDataRowas the value (it'll be a reference, not a copy, making retrieval faster...unless you clone).Microsoft MVP, Visual C# My Articles
HI, Similar to what you suggested, I got an article on Microsoft website. It works but the limitation is what if DISTINCT is to be applied on multiple columns http://support.microsoft.com/default.aspx?scid=kb;en-us;326176 Add the following Private method to the class definition. This method is the same as the method that is used in other DataSetHelper articles. It is used to compare field values (including NULL).
private 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 }Add the following Public method to the class definition. This method copies unique values of the field that you select into a new DataTable. If the field contains NULL values, a record in the destination table will also contain NULL values.public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName) { DataTable dt = new DataTable(TableName); dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType); object LastValue = null; foreach (DataRow dr in SourceTable.Select("", FieldName)) { if ( LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])) ) { LastValue = dr[FieldName]; dt.Rows.Add(new object[]{LastValue}); } } if (ds != null) ds.Tables.Add(dt); return dt; } -
HI, Similar to what you suggested, I got an article on Microsoft website. It works but the limitation is what if DISTINCT is to be applied on multiple columns http://support.microsoft.com/default.aspx?scid=kb;en-us;326176 Add the following Private method to the class definition. This method is the same as the method that is used in other DataSetHelper articles. It is used to compare field values (including NULL).
private 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 }Add the following Public method to the class definition. This method copies unique values of the field that you select into a new DataTable. If the field contains NULL values, a record in the destination table will also contain NULL values.public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName) { DataTable dt = new DataTable(TableName); dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType); object LastValue = null; foreach (DataRow dr in SourceTable.Select("", FieldName)) { if ( LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])) ) { LastValue = dr[FieldName]; dt.Rows.Add(new object[]{LastValue}); } } if (ds != null) ds.Tables.Add(dt); return dt; }Yes, all they're doing here is implementing a custom
IComparerwith which to sort (not really necessary in most cases since two columns withDBNull.Valuewould still have the same value, but whatever). The only good way to do this is to use theDISTINCTkeyword in your SQL statement. What's wrong with that? It's either that, or use look-up tables which could be very expensive, especially with large data sets.Microsoft MVP, Visual C# My Articles