0

Issue encountered in .net framework 4.0

I have 2 dropdownlist currently with one for months selection and another for year selection.

Both of their datasource are a datatable from a SQL which returns rows with year and month as columns. I then use the datatextfield and datavaluefield to specify which column to be used. Example:

string sql = "select.... group by...";//the query

DataTable dtMonthYear = db.getDataTable(sql); //got the datatable

ddlMonth.DataSource = dtMonthYear;
ddlMonth.DataTextField = "Month";
ddlMonth.DataValueField = "Month";
ddlMonth.DataBind();

ddlYear.DataSource = dtMonthYear;
ddlMonth.DataTextField = "Year";
ddlMonth.DataValueField = "Year";
ddlYear.DataBind();

Because of that, the year will have duplicated items in it, and i wish to eliminate the duplicated items.

I'd found out that linq can do it but unfortunately my framework is until 4.0 only and can't use the datarowextension. Here's the link that i got: LINQ query on a DataTable

Anyone has other idea?

Thanks

Community
  • 1
  • 1
ipohfly
  • 1,959
  • 6
  • 30
  • 57
  • *unfortunately my framework is until 4.0* Whats the meaning of this? – Raghuveer Sep 24 '12 at 05:06
  • because from the example that i found it seems that i can use linq to achieve that but to use the extension to convert it back to datatable i will need to use the DataRowExtensions but it is only available in .net 4.5....or am i wrong? – ipohfly Sep 24 '12 at 05:11
  • DataRowExtensions is available in .NET 4.5, 4.0, 3.5. Check this [LINK](http://msdn.microsoft.com/en-us/library/system.data.datarowextensions.aspx) once – Raghuveer Sep 24 '12 at 05:15

1 Answers1

1

You can use Linq-To-DataSet in .NET 4 (even in 3.5):

DataTable years = dtMonthYear.AsEnumerable()
                  .GroupBy(r => r.Field<int>("Year"))
                  .Select( g => g.OrderBy(r => r.Field<int>("Month")).First())
                  .CopyToDataTable();

Note that you need to add using System.Linq;

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • I still can't use CopyToDataTable() for unknown reason, even after i added `System.Ling` to my code. I combined your answer with Cuong Le's answer and got the result as a List...however when i try to run it gives me error during the dropdown list databinding, saying the int data type doesn't have the property name Year – ipohfly Sep 24 '12 at 07:16
  • You can only use `CopyToDataTable` on an `IEnumerable` (like in my approach, whereas a `List` is not). But you don't need a `DataTable` for the DataSource, you can also use a `List`. – Tim Schmelter Sep 24 '12 at 07:19
  • I see. Any reason why List can't use the `.DataBind()` method? I tried that and it seems that it is anticipating some key value pairs from the list. – ipohfly Sep 24 '12 at 07:21
  • You can either use `ddlMonth.Items.Add` or an anonymous type as backing type: `ddlMonth.DataSource = list.Select(i => mew {Key=i.ToString(),Value=i.ToString()});`. – Tim Schmelter Sep 24 '12 at 07:28
  • hmm the `list.Select` method is giving me error. Sorry if this troubles you but i'm a noob with linq =( – ipohfly Sep 24 '12 at 07:56
  • @ipohfly: Maybe because of my typo `i => mew ...` instead of `i => new ...`. – Tim Schmelter Sep 24 '12 at 08:03