Datatable sort problem.

A

axum

Active member
Joined
Mar 28, 2003
Messages
40
Hi, i have a TreeView that has the usual Parent > Child relationship.

I populate my Treeview by using a DataAdapter to fill a DataTable by running a Stored Proc.

This Stored Proc returns a list of IDs Ordered by their Title, so they will appear alphabetically. But when i insert the records into the DataTable it seems to order them by ID and lose their original ordering.

I know i can use the Sort method on the DataTable :

dRowColl = dTable.Select(selectSql,"Title");

but using this has slowed down my program immensely! Obviously as its doing a textual sort, its running about 5 times as slow.

My selectSql above is the usual "parentID=CurrentParent and childID <> CurrentParent" to select the Parent > Child relationships. What i really would like to do is to add an "ORDER BY" bit to the end but you cant do this can you?

Has anyone had similar problems or knows of a way to speed things up?

Many thanks..
 
WHERE parentID = CurrentParent AND childID != CurrentParent ORDER BY title

is a perfectly legal SQL statement. If its not giving you the order by title then it could be that your TreeView is sorting them for some reason or another (or your DataTable/View is for some reason)

If your selection is quite large (a million or so records) then ordering by a non-clustered column could be quite painful. If you are ALWAYS going to ORDER BY title, then set the title as the tables clustered index, it will speed up your selection drastically.
 
err yes, but you cant use an ORDER BY query in a Select SQL statement when using it against a DataTable can you?!

This is my Select statement i am using for my DataTable :

selectSql = dTable.Columns[0].ToString() + "=" + currentParent + " and " + dTable.Columns[1].ToString() + "<> " + currentParent + " ORDER BY title";

dRowColl = dTable.Select(selectSql);

foreach(DataRow dRow in dRowColl)
{
// do something
}

But this returns :

Syntax error: Missing operand after ORDER operator
when executing the dTable.Select statement.

I didnt think it was possible to use this ORDER BY SQL when using a DataTable?
 
Code: 
Dim filters As String = dTable.Columns[0].ToString() + "=" + currentParent + " and " + dTable.Columns[1].ToString() + "<> " + currentParent 
Dim sorts As String = "title"
dRowColl = dTable.Select(filters, sorts)

Hope this helps.
 
errr, no, not one little bit, its remarkably similar as what i already wrote down :

dTable.Select(selectSql,"Title");

but youve just split into 2 unnecessary lines!

thanks anyway!
 
Ok, found a way round this small problem if anyone is interested..!

Basically i retrieved my Row Collection from my DataTable by using the .Select syntax, if you remember it was adding in the "Sort" property that was slowing the whole thing down.

So all i did was loop through the records retrieved by my DataTable.Select call, and add them into a SortedList object. But i added the Title as the Key and the ID as the value, thus keeping the alphabetical sorting intact.

Then i just looped round the SortedList to build my TreeView as normal, hey presto :)

Just for anyones information..

Axum
 
Back
Top