asp.net datatable sort numeric stored as varchar

 There was a table storing employee pay details as varchar. There was a requirement to sort the data ascending/descending on the UI. But it would not sort as data type is varchar.

  To resolve the issue I have implemented a workaround as follows and it worked well. 

1. Clone the data table

2. change the desired column's data type

3. change the desired column's data type    

4. Bind datatable to the data source

Sample code is as follows

Public Function GetDataTable(ByVal sortByExprerssion As String) As DataTable
       
        Dim dtBulkSalary As DataTable

        dtBulkSalary = bulkSalary.SelectByFilter()

        'workaround to sort varchar
        If (Not IsNothing(dtBulkSalary)) Then
            Dim dtBulkSalaryCloned As DataTable = dtBulkSalary.Clone()
            dtBulkSalaryCloned.Columns("Yearly_Salary").DataType = Type.[GetType]("System.Decimal")           
            dtBulkSalaryCloned.Columns("Normal_Rate").DataType = Type.[GetType]("System.Decimal")
            
            For Each bulkSalaryRow As DataRow In dtBulkSalary.Rows
                Dim drBulkSalaryCloned As DataRow = dtBulkSalaryCloned.NewRow
                drBulkSalaryCloned("BulkSalaryChange_ID") = bulkSalaryRow("BulkSalaryChange_ID")
                drBulkSalaryCloned("Yearly_Salary") = If(IsDBNull(bulkSalaryRow("Yearly_Salary")), 0.0D, CDec(DecryptValue(bulkSalaryRow("Yearly_Salary"))))
                drBulkSalaryCloned("Normal_Rate") = If(IsDBNull(bulkSalaryRow("Normal_Rate")), 0.00, CDec(DecryptValue(bulkSalaryRow("Normal_Rate"))))
                
            dtBulkSalaryCloned.AcceptChanges()
            Dim dvBulkSalary As DataView = dtBulkSalaryCloned.DefaultView
            dvBulkSalary.Sort = sortByExprerssion
            dtBulkSalary = dvBulkSalary.ToTable()
        End If

        Return dtBulkSalary
    End Function

Please note that data decrypted function is not listed here.

Cheers,
Samitha