CODEWITHSUNDEEP
c#
Mahbub
Mahbub

Reputation: 124

Sorting a particular column of datatable of string type that contains numeric data

I have a datatable (.Net) with multiple columns. One of the columns say RollNo is of string type but contains numeric data. Eg. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14. I am trying to sort using the following:

string sql = 
  "Select StudentID, RollNo, AdmissionID,(FirstName + Space(1) + Isnull(MiddleName,'') + Space(1) + Isnull(LastName,'')) as Name," +
  " PermState as State from Students where ClassId  = '" + ddlClass.SelectedValue + "'" +
  " order by RollNo";

DataTable dt = bl.GetDataSet(sql);       
dt.DefaultView.Sort = "RollNo";

But after sorting I get the result as 1, 10, 11, 12, 13, 14, 2, 3, 4, 5, 6, 7, 8, 9.

How to solve it?

Upvotes: 3

Views: 7202

Answers (3)

andy
andy

Reputation: 6079

        DataTable dt = GetData();
        dt.Columns.Add("RollNo1", typeof(Int32));
        foreach (DataRow item in dt.Rows)
        {
            item["RollNo1"] = item["RollNo"];
        }
        dt.DefaultView.Sort = "RollNo1";
        dt.Columns.Remove("RollNo");
        dataGridView1.DataSource = dt;

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460298

You can use Linq-To-DataSet and cast the string to int:

DataTable ordered = dt.AsEnumerable()
                      .OrderBy(r => int.Parse(r.Field<String>("RollNo")))
                      .CopyToDataTable();

You need to add a reference to System.Data.DataSetExtensions.dll and using System.Linq;

Note that you can omit the CopyToDataTable if you don't need it, i've showed it just to demonstrate how to get a DataTable from the IEnumerable<DataRow>.

Upvotes: 7

L.B
L.B

Reputation: 116178

Don't do it if your DB is large. You have to either make "string to numeric" conversion in your sql statement(that means on the DB server), which is costly, or have to read all the table to memory and make this sort there which is also costly.

I would create a new numeric field(column) and make this conversion once as a batch process.

Upvotes: 1

Related Questions