Get distinct values from a column of DataTable in

问题:

I am working on a legacy project which was developed using .NET Framework 2.0. In this project, I get distinct values from DataRowCollection by ItemNo column. I am only interested in ItemNo. The DataRow consist of ItemNo, Qty and Date.

I am thinking of iterating the DataRowCollection and adding the unique ItemNo into a list of string as below (not tested)

var items =  new List<string>();
foreach (DataRow orderItem in rows)
{
     var itemNo = orderItem["ITEMNO"].ToString().Trim();
     if(items.Find(delegate(string str) { return str == itemNo ;}) == null)
     {
            items.Add(itemNo);
     }
 }

Is there a better way of doing this without LINQ (.Net Framework 2.0 doesnt like LINQ)

回答1:

// Given a data table:
var dt = new DataTable();
dt.Columns.Add("ITEMNO");
dt.Rows.Add("1 ");
dt.Rows.Add(" 1");
dt.Rows.Add("2");

var dict = new Dictionary<string, bool>();

foreach(DataRow dr in dt.Rows)
{
    var itemNo = dr["ITEMNO"].ToString().Trim();

    // Take advantage of O(1) lookup:
    if (!dict.ContainsKey(itemNo))
    {
        dict.Add(itemNo, true);
    }
}

// Get list from dictionary keys:
var items = new List<string>(dict.Keys);

If you can install .Net 3.5 on the server, and reference System.Core.dll in your application, you can leverage HashSets which would modify the above code to:

var hashSet = new HashSet<string>();

foreach(DataRow dr in dt.Rows)
{
    var itemNo = dr["ITEMNO"].ToString().Trim();    

    // Only unique elements are added to the hash set, 
    // no need to check for duplicates
    hashSet.Add(itemNo);    
}

var items = new List<string>(hashSet);

The benefit of using HashSet over a Dictionary is admittedly trivial, but I'd prefer it since I don't care for the arbitrary bool value in the dictionary, but you'd need to meet the .Net 3.5 and reference requisites.

回答2:

To get distinct values form a column you can use this method:

List<T> SelectDistict<T>(DataTable table, string column)
{
    DataTable temp = new DataView(table).ToTable(true, column);
    List<T> items = new List<T>();
    foreach (DataRow row in temp.Rows)
        items.Add(row.Field<T>(column));
    return items;
}

In above method I used DataView.ToTable which by passing true as first argument, selects distinct values.

Here is the usage example:

List<string> items = SelectDistict<string>(yourDataTable, "ITEMNO");

Note

If you need to trim values, you can change above code and first create a clone copy of the DataTable. Then add a computed column which contains, trimmed value from the given column name for distinct values by assigning TRIM(column) to Expression property of column. Then follow the steps using the new trimmed column like above code.