DataView not Sorting properly after DataTable GroupBy

Question

I am currently getting data from SQL Server and storing in a DataTable. I am applying GroupBy operation on that DataTable in order to display the table in various forms in DataGrid. After making into sub DataTables, I am sorting the DataTable by placing in a DataView. After Sorting the DataView, I am binding it to a DataGridView. But the problem is that the Rows which are made by GroupBy are not sorting properly

Can I get a Solution for this?

Here is the DataView GroupBy code.

DataView view = new DataView(dtfullreport); // Datatable to dataview.
            var drdatedisp = from row in dtfullreport.AsEnumerable()
                             group row by row.Field<string>("Order_Date") into g
                             select new
                             {
                                 Order_Date = g.Key,
                                 totalQnty = g.Sum(a => a.Field<int>("Item_Quantity")),
                                 totalTax = g.Sum(a => float.Parse(a.Field<decimal>("TAXAMT").ToString())),
                                 totalAmt = g.Sum(a => float.Parse(a.Field<decimal>("VALAMT").ToString()))
                             };
            DataTable dtdatedisp = new DataTable();
            dtdatedisp.Columns.Add("Order_Date");
            dtdatedisp.Columns.Add("Item_Quantity");
            dtdatedisp.Columns.Add("TAXAMT");
            dtdatedisp.Columns.Add("VALAMT");
            dtdatedisp.Rows.Clear();
            foreach (var g in drdatedisp)
            {
                DataRow newRow1 = dtdatedisp.NewRow();
                newRow1[0] = g.Order_Date;
                newRow1[1] = g.totalQnty;
                newRow1[2] = String.Format("{0:0.00}", g.totalTax);
                newRow1[3] = String.Format("{0:0.00}", g.totalAmt);
                dtdatedisp.Rows.Add(newRow1);
            }

After Grouping the DataView and placing in the DataTable, I'm sorting the DataTable

Here is the Sorting code. This will be written in GridView Sorting Event.

string sortingDirection = string.Empty;
    if (dir == SortDirection.Ascending)
    {
        dir = SortDirection.Descending;
        sortingDirection = "Desc";
    }
    else
    {
        dir = SortDirection.Ascending;
        sortingDirection = "Asc";
    }
            DataView sortedView = new DataView((DataTable)ViewState["dtdatedisp"]);
        sortedView.Sort = e.SortExpression + " " + sortingDirection;
        gv_reports_date.DataSource = sortedView; // Gridview datasource and binding
        gv_reports_date.DataBind();

I'm sorting based on Order_Date column but after sorting the DataView in Descending Order but the output is,

Datatable before Grouping

enter image description here

Default View before sorting. (Sorted according to date in database).

enter image description here

After sorting in Descending order of Quantity Sold.

enter image description here

After sorting in Ascending order of Quantity Sold.

enter image description here

The Rows which are Grouped are not being displayed in correct Sort Order

Jakub Kaleta Jakub Kaleta · Accepted Answer · 2012-08-14T02:04:45

The answer is very simple. If you don't provide types for the columns in your sorted DataTable, string is assumed, and the resulting sort is a dictionary sort.

Numbers 10, 6, 30 are going to be ordered as {6, 10, 30} if sorted as integers, but {10, 30, 6} if sorted as strings.

Change:

DataTable dtdatedisp = new DataTable();
dtdatedisp.Columns.Add("Order_Date");
dtdatedisp.Columns.Add("Item_Quantity");
dtdatedisp.Columns.Add("TAXAMT");
dtdatedisp.Columns.Add("VALAMT");

to:

DataTable dtdatedisp = new DataTable();
dtdatedisp.Columns.Add("Order_Date", typeof(DateTime));
dtdatedisp.Columns.Add("Item_Quantity", typeof(int));
dtdatedisp.Columns.Add("TAXAMT", typeof(decimal));
dtdatedisp.Columns.Add("VALAMT", typeof(decimal));

... and it should start working.

Here you can read more about adding columns to a DataTable

DataView not Sorting properly after DataTable GroupBy

1 Answers