Sorting ASP.NET GridView in a calculated field
I have a DataBound GridView. However, I have one column where the value comes from the calculation in the code behind - it is displayed inside the TemplateField.
How can I sort my grid based on this calculated value?
Put the original returned data in DATASET or DATATABLE. Add a new column to DATATABLE for the calculated field. Pass this data by doing the necessary calculations and placing the result in the specified calculated field.
Create a new view based on the datatable and sort the view by the calculated field. Bind the grid to your 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, do the calculations in the SQL statement, re:
SELECT Col1, Col2, Col3, Col1+Col2+Col3 AS LineTotal FROM Table;
One possible solution would be data padding rather than grid binding. Then use dataview with sort set to your computed column and bind to grid in code.
Another solution is to add a calculated column to the database table. This allows the computed column to be returned in the same SQL statement that loads the rest of the row, but unfortunately requires a database schema change.
Note that adding a column to datatable marks all rows as updated, so when saving the database it will try to update all records.