C# question, DataTable.Sort();

[Grooverider]

This has been frustrating me for days, so I thought i'd shoot in on here.

I'm loading a CSV into a DataTable, then I want to sort the datatable. The contents of the datatable is:

AIRPORT DEPARTURE, AIRPORT ARRIVAL, PRICE

i.e.

LHR, BHK, 300
LHR, BHK, 299
LGW, NYC, 450
LGW, NYC, 210

etc etc, usually about 10,000 rows.

What i'm trying to do is filter the datatable, and return a datatable resultset that contains only the cheapest flights for each departure/arrival set.

strExpr = "OrderDate >= '01.03.1998' AND OrderDate <= '31.03.1998'";

strSort = "OrderDate DESC";

foundRows = DataTable.Select(strExpr, strSort);

Anyone got experience with this? It's driving me crazy. I prototyped it in VB.NET and sorted it using a Oledb SQL request but the same method in VB didn't work in C#, and I really want to do this directly to the datatable

[Phelps McManus]

What do you mean when you say it "didn't work" in C#? It gives a compilation error? A runtime error? It returns an empty data set?

I assume OrderDate is a column in this table/csv on top of AIRPORT DEPARTURE, AIRPORT ARRIVAL, PRICE. I have never used "." to separate date fields, so hopefully it isn't something as simple as that.

Finally, what do you mean when you say you "really want to do this directly to the dataset"? You want to store it in sorted order? You can do that when you first build the table, and keep it that way as long as inserts occur in the same order (by OrderDate). Other than that, the ADO.NET paradigm is to select rows from a physical table (on disk) and store them in a dataset (in memory). You manipulate the dataset and then commit any changes back to disk when you are done (or throw them away).

It is kind of a pain in the ass to setup, because you have to write routines that handle updates, inserts, and deletes for every type of row. It has been a few years since I messed with it, though, so maybe they have simplified things.

[Hachima]

Been a while since I've worked with datasets. LINQ ftw.

Anyway try

strExpr = "((OrderDate >= '01.03.1998') AND (OrderDate <= '31.03.1998))'";

[Grooverider]

Phelps;

When I say it doesn't work in C sharp, I mean it returns results - unfortunately the results are completed different from the results the same query SQL returns.

Sorry the OrderDate stuff was just an example of how datatable.select sorting works - which i'm sure you're well aware of, please disregard it.

Basically, i'm pulling a CSV into a DataTable. Now I want to remove all but the cheapest flights from the datatable for each PAIR of flight departure airport/arrival airports.

I know of two ways of doing this:

1. Use oleDb SQL call on the file; with a query (this query isn't correct, but i have the correct wokring one stored in my VB.NET prototype) such as: Select co1.Dest co1.Depart co1.Cost from Flights.csv as co1 where co1.Cost = (select min(price) from Flights.csv as co2 where co1.Dest = co2.Dest and co1.Depart = co2.Depart)

2. Do a DataTable.select (or DataTable.sort - can't remember which one off hand), which I am not sure how to do for the conditions listed above.

For some reason, query 1 returns different results for VB.NET (result set works) from C# (returns results into a datatable, but they basically don't work).

I'd prefer to use method 2, as i'd like to understand the select method better.

Hope this is clearer

[Niceshot23]

in sql, it would be like this...

create table #tmp( val int, frm varchar(5), [to] varchar(50))

insert into #tmp
select 100, 'dfw', 'mia' union
select 105, 'dfw', 'mia' union
select 110, 'dfw', 'mia' union
select 95, 'mia', 'dfw' union
select 105, 'mia', 'dfw' union
select 110, 'mia', 'dfw' union
select 115, 'mia', 'dfw'

select a.* from #tmp a
where a.val in (select min(b.val) from #tmp b where a.frm = b.frm and a.[to] = b.[to])
group by a.frm, a.[to], a.val

drop table #tmp

..............


so i'm guessing that #1 is not working cause you're missing the group by ..

[Grooverider]

the group by statement isn't necessary..

I worked it out, I used odbc and used a SQL query on the csv file. It's slow, but it'll do.

[Jhentar_AN|Trib]

So essentially you are looking to have 2 calendar objects for selecting dates and use that to generate the date range, and a dropdown to select the ordering method to generate the sort order string?

First thing I'd suggest is looking at using LINQ-to-SQL, if you have the time to get up to speed with LINQ, as it will increase your speed...especially if you can use an XML file rather than the CSV. C# native LINQ-to-XML would make this fairly trivial and fast, that would be the direction I'd look if you have the time to do son.

[Niceshot23]

Originally Posted by Grooverider

the group by statement isn't necessary..

I worked it out, I used odbc and used a SQL query on the csv file. It's slow, but it'll do.

Right.... but you'll need either the group by or distinct in order to not return duplicate results (if any)

[Grooverider]

Yeah you're right about the duplicate results actually, I was completely wrong. I'll put that in there tomorrow as it will improve things somewhat. I didn't even notice the dupes. Cheers for putting your query in here it'll save me a bunch of time.

I'll look into learning some linq from next week, as I really wasn't happy with using odbc and sql to query the csv dataset.

[Grooverider]

Can't get the order by in the statement without throwing an exception.

Using the following;

System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select co1.F1, co1.F2, co1.F3, co1.F4, co1.F5, co1.F6, co1.F7, co1.F8, co1.F9 from SO.csv AS co1 where co1.F8 = (SELECT min(F8) As MaxPrice FROM SO.csv AS co2 WHERE co2.F1 = co1.F1 AND co2.F2 = co1.F2)", conn);

if I add an, "GROUP BY co1.F8" it throws an exception. Any ideas?

[AladainAF]

What exception are you getting?

Me and my friend looked at it, your inner query is only returning 1 row (Min(F8)) and your outer query queries all the rows where F8 = that min. If you're grouping on the outer query that doesn't make sense.

In other words.. inner query gets min value, outer one gets all rows with F8 set to that value.. so group by doesn't make sense for either the inner or outer query on the F8 column.

Can prolly help more if you post up the exception.

[lendarios]

Originally Posted by Grooverider

Can't get the order by in the statement without throwing an exception.

Using the following;

System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select co1.F1, co1.F2, co1.F3, co1.F4, co1.F5, co1.F6, co1.F7, co1.F8, co1.F9 from SO.csv AS co1 where co1.F8 = (SELECT min(F8) As MaxPrice FROM SO.csv AS co2 WHERE co2.F1 = co1.F1 AND co2.F2 = co1.F2)", conn);

if I add an, "GROUP BY co1.F8" it throws an exception. Any ideas?

I dont think that query is correct. Inner queries should be separate from the outer query. You cant do in the inner query = value from the outer query.
I suggest you just create a function for the inner query, since you are returing a scalar "Min (F8) " a function is better suitable

[Grooverider]

I solved the entire problem, by using an external mySql database and querying it using the C# framework.