How can I sort a DataSet before doing a DataBind?

Question

I have data coming from a database in a form DataSet. Then I set it as a DataSourcegrid control before I do it DataBind(). I want to sort DataSet/ DataTablein a single column. The column will be difficult to sort in the database, but I was hoping that I could sort it as if I would sort the general list, i.e. Using deligate.

Is this possible, or do I need to transfer it to another data structure?

Edit I cannot get any of these answers to work for me, I think, because I use .Net 2.0.

Answer 1

Due to how the sorting of the DataTable (and DataView) works, you cannot use the delegate approach directly. One way is to add a column to the data table that represents the order, and set a value (for each row) based on the desired sequence. Then you can add sorting to the view in this new column. For example (using LINQ to sort, just for short):

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 dataset, then I don’t think you need the Cast step, or you will use your own typed subclass of DataRow)

[edit to include 2.0]

In 2.0 (i.e. without LINQ, etc.) you can use List<T>to perform sorting - a little longer, 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, replace DataRow if you are using a typed dataset)

Answer 2

, DataView.Sort . DataTable.DefaultView.

Answer 3

/ , - :

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

IComparables .. , , .

Answer 4

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();


    }
}

Answer 5

, , " ". , , Views, Redundancy Indexes, SQL-, , , , , (, - ..)

Answer 6

Why sort a column to sort in a database? Suppose the sorting complexity is the same in the database or in the structure, unless, of course, this is a calculated column, and even then I assume that sorting in the database is even faster.

However, as John said, you can change the sort at the form level using the DataView.Sort property. I would recommend doing this if there is too much time, in which case it would be useful to cache the results.

Answer 7

You can do it with

myTableName.DefaultView.Sort = "MyFieldName DESC";