Sorting a GridView based on weights in another database table...

Question

I have a GridView being populated from a non-SQL database that we use internally. One of those fields is a stockroom location. (Example: AAA, AAB, AAC, etc.)

In another database (SQL) I keep a list of all stockrooms and give them a weight. The weight is just an integer; the higher the integer, the further away the stockroom is.

I need to sort my GridView based on those stockroom weights. How can I have my GridView sort depend on data from another table? This seems like it should be an easy solution, but I'm getting stuck. If it matters, the GridView is bound to a System.Collections.ObjectModel.Collections<> class.

Answer 1

The SortExpression property does support "complex" property navigation. Have you tried using this as the SortExpression for your GridView column:

<asp:TemplateField SortExpression="Stockroom.Weight">

Answer 2

I am assuming that you have the data access code already worked out. So, my suggestion would be to;

1. Make a class that has all the information you need from the non-sql database and the column you need from the sql database.
2. Use your data access code to retrieve the information and populate something like a binding list with objects of the type you created in 1
3. You can bind the gridview to that binding source so you can sort on that value