Datatable sort problem.

[axum]

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 ID's 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 it's doing a textual sort, it's 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 can't do this can you?

 

Has anyone had similar problems or know's of a way to speed things up?

 

Many thanks..

[wyrd]

WHERE parentID = 'CurrentParent' AND childID != 'CurrentParent' ORDER BY title

 

is a perfectly legal SQL statement. If it's 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 table's clustered index, it will speed up your selection drastically.

[axum]

err yes, but you can't 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 didn't think it was possible to use this ORDER BY SQL when using a DataTable?

[zy_abc]

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.

[axum]

errr, no, not one little bit, it's remarkably similar as what i already wrote down :

 

dTable.Select(selectSql,"Title");

 

but you've just split into 2 unnecessary lines!

 

thanks anyway!

[axum]

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 anyone's information..

 

Axum