How can I sort a DataSet before doing a DataBind?

Question

I have data coming from the database in the form of a DataSet. I then set it as the DataSource of a grid control before doing a DataBind(). I want to sort the DataSet/DataTable on one column. The column is to complex to sort in the database but I was hoping I could sort it like I would sort a generic list i.e. using a deligate.

Is this possible or do I have to transfer it to a different data structure?

Edit I can't get any of these answer to work for me, I think because I am using .Net 2.0.

Answer 1

I think DataView.Sort property would help. You can access it through DataTable.DefaultView.

Answer 2

If you don't mind losing paging/sorting on the control you can use something like:

var dt = new DataTable();
gvWhatever.DataSource = dt.Select().ToList().Sort();

And that sort will take IComparables etc as per the overloads so you can sort however you like.

Answer 3

Because of how DataTable (and DataView) sorting works, you can't use the delegate approach directly. One workaround is to add a column to the data-table that represents the order, and set the value (per row) based on the desired sequence. You can then add a Sort to the view on this new column. For example (using LINQ to do the sort, just for brevity):

var sorted = table.Rows.Cast<DataRow>().OrderBy(row => your code);
int sequence = 0;
foreach(var row in sorted)
{
    row["sequence"] = sequence++;
}

(if you have a typed data-set, then I don't think you need the Cast step, or you would use your typed DataRow subclass)

[edit to include 2.0]

In 2.0 (i.e. without LINQ etc) you could use a List<T> to do the sort - a bit more long-winded, but:

        List<DataRow> sorted = new List<DataRow>();
        foreach(DataRow row in table.Rows)
        {
            sorted.Add(row);
        }
        sorted.Sort(delegate(DataRow x, DataRow y) { your code });
        int sequence = 0;
        foreach(DataRow row in sorted)
        {
            row["sequence"] = sequence++;
        }

(again, substitute DataRow if you are using a typed data-set)

Answer 4

You can sort the Database in memory , but I don't know what "too complex to sort in the database" would be. If I were you I would try to get it sorted in the database, use Views, Redundancy Indexes, complex SQL statements, it's probably going to be faster than sorting a large Dataset in memory, with the added advantage that it keeps the sort for other kind of clients ( e.g. Web Services, etc)

Answer 5

Why is the column to complex to sort in the database? It suppose the complexity of the sort will be the same in the database or in the framework, unless of course it's a computed column, and even in that case I'm guessing that sorting in the DB is still faster.

However, as John said, you can change the sorting at the form level via DataView.Sort property. I would recommend doing this if the too much time consuming, and it that case it would be useful to cache the results.

Answer 6

You can do it by

myTableName.DefaultView.Sort = "MyFieldName DESC";

Answer 7

Hi

protected void grdResult_Sorting(object sender, GridViewSortEventArgs e) { DataTable dt = ((DataTable)Session["myDatatable"]); grdResult.DataSource = dt; DataTable dataTable = grdResult.DataSource as DataTable;

    //Code added to fix issue with sorting for Date datatype fields
    if (dataTable != null)
    {   

        DataView dataView = new DataView(dataTable);
        for (int i = 0; i < dataView.Table.Rows.Count; i++)
        {
            try
            {
                dataView.Table.Rows[i]["RESP_DUE_DT"] = common.FormatDateWithYYYYMMDD(Convert.ToDateTime(dataView.Table.Rows[i]["RESP_DUE_DT"]));
                dataView.Table.Rows[i]["RECD_DT"] = common.FormatDateWithYYYYMMDD(Convert.ToDateTime(dataView.Table.Rows[i]["RECD_DT"]));
            }
            catch (Exception ex)
            {

            }
        }

        dataView.Sort = "[" + e.SortExpression + "]" + " " + GetSortDirection(e.SortExpression);

        for (int i = 0; i < dataView.Table.Rows.Count; i++)
        {
            try
            {
                dataView.AllowEdit = true;
                dataView[i].BeginEdit();
                dataView[i]["RESP_DUE_DT"] = common.FormatDateFromStringYYYYMMDD(dataView[i]["RESP_DUE_DT"].ToString());
                dataView[i]["RECD_DT"] = common.FormatDateFromStringYYYYMMDD(dataView[i]["RECD_DT"].ToString());
            }
            catch (Exception ex)
            {

            }
        }
        //End code added to fix the issue with sorting for Date data type fields


        grdResult.DataSource = dataView;
        grdResult.DataBind();


    }
}