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

Question

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?

Tim Schmelter · Accepted Answer · 2012-09-01 00:12:17Z

7

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>.

edited Sep 1, 2012 at 0:12

answered Aug 31, 2012 at 23:52

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mahbub Over a year ago

Fantastic! Tim, Thanks for your support. But what if RollNo field also contains alphanumeric data along with numeric?

Tim Schmelter Over a year ago

That depends on the format. In general Regex is your friend. You can use any kind of code in Linq.

Tim Schmelter Over a year ago

I need sample data to know how to sort that numerically. But that would be out of scope of this question anyway, because it would a a Regex question. I would ask another question "How to get a number out of this string".

L.B · Accepted Answer · 2012-09-01 00:02:22Z

1

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.

answered Sep 1, 2012 at 0:02

L.B

116k20 gold badges189 silver badges229 bronze badges

Comments

andy · Accepted Answer · 2012-09-01 07:22:25Z

0

        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;

answered Sep 1, 2012 at 7:22

andy

6,0792 gold badges29 silver badges50 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related