ASP.NET GridView sorting on a calculated field

Question

I have a DataBound GridView. However I have one column where the value comes from a calculation in the code behind - it is displayed within a TemplateField.

How can a sort my grid based on this calculated value ?

Answer 1

One possible solution would be to populate a datatable instead of binding the grid. Then use a dataview with the sort set to your calculated column, and bind that to the grid in code behind.

Answer 2

Put your initial returned data into a DATASET or a DATATABLE. Add to the DATATABLE a new column for you calculated field. Walked that data doing the necessary calculation, and putting the result into said calculated field.

Create a new view based on the datatable, and sort the view by the calculated field. Bind the grid to the data view.

Dim DT as DataTable 
DT = GetDataTableFromDataBaseMethod()
DT.Columns.Add(New DataColumn("CalculatedColumnName"))
For each row as DataRow in DT.Rows
    row("CalculatedColumnName") = PerformCalculations(row)
Next 

Dim view as New DataView 
view.DataTable =dt 
View.Sort = "CalculatedColumnName DESC"

datagrid1.Datasource = view
datagrid1.Databind

Or, if possible, perform the calculation in the SQL statement, re:

SELECT Col1, Col2, Col3, Col1+Col2+Col3 AS LineTotal FROM Table;

Answer 3

Another solution is to consider is adding calculated column to the database table itself. This has the benefit of returning the calculated column in the same SQL expression that loads the rest of the row, but unfortunately does require changing the database schema.

Answer 4

Note that adding a column to the datatable will mark all rows as updated, so when saving back to the database, it will try to update all records.