Problem: Sorting for GridView/ObjectDataSource changes depending on page

Question

I have a GridView tied to an ObjectDataSource using paging. The paging works fine, except that the sort order changes depending on which page of the results is being viewed. This causes items to reappear on subsequent pages among other issues. I traced the problem to my DAL, which reads a page at a time and then sorts it. Obviously the sorting is going to change as the result set size changes. Is there an improvement to this algorithm. I would like to use a datareader if possible:

    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
    public static WordsCollection LoadForCriteria(string sqlCriteria, int maximumRows, int startRowIndex, string sortExpression)
    {
        //DEFAULT SORT EXPRESSION
        if (string.IsNullOrEmpty(sortExpression)) sortExpression = "OrderBy";
        //CREATE THE DYNAMIC SQL TO LOAD OBJECT
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.Append("SELECT");
        if (maximumRows > 0) selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString());
        selectQuery.Append(" " + Words.GetColumnNames(string.Empty));
        selectQuery.Append(" FROM sw_Words");
        string whereClause = string.IsNullOrEmpty(sqlCriteria) ? string.Empty : " WHERE " + sqlCriteria;
        selectQuery.Append(whereClause);
        selectQuery.Append(" ORDER BY " + sortExpression);
        Database database = Token.Instance.Database;
        DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
        //EXECUTE THE COMMAND
        WordsCollection results = new WordsCollection();
        int thisIndex = 0;
        int rowCount = 0;
        using (IDataReader dr = database.ExecuteReader(selectCommand))
        {
            while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows)))
            {
                if (thisIndex >= startRowIndex)
                {
                    Words varWords = new Words();
                    Words.LoadDataReader(varWords, dr);
                    results.Add(varWords);
                    rowCount++;
                }
                thisIndex++;
            }
            dr.Close();
        }
        return results;
    }

Answer 1

I found a solution to this problem from reading MSDN. The trick is to perform the full query and return only the interesting subset that way the sort is always consistent. The procedure,however, only works using SQL 2005.

    [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
    public static WordsCollection LoadForCriteria(string sqlCriteria, int maximumRows, int startRowIndex, string sortExpression)
    {
        //DEFAULT SORT EXPRESSION
        if (string.IsNullOrEmpty(sortExpression)) sortExpression = "OrderBy";
        //CREATE THE DYNAMIC SQL TO LOAD OBJECT
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.Append("SELECT ");
        selectQuery.Append(Words.GetColumnNames(string.Empty));
        selectQuery.Append(" FROM (");
        selectQuery.Append("SELECT ");
        selectQuery.Append(Words.GetColumnNames(string.Empty));
        selectQuery.Append(", ROW_NUMBER() OVER (ORDER BY " + sortExpression + ") AS RowRank");
        selectQuery.Append(" FROM sw_Words) AS WordsWithRowNumbers");
        selectQuery.Append(" WHERE RowRank >" + startRowIndex.ToString() + " AND " + "RowRank <=" + (startRowIndex + maximumRows).ToString());
        string whereClause = string.IsNullOrEmpty(sqlCriteria) ? string.Empty : " AND " + sqlCriteria;
        selectQuery.Append(whereClause);
        Database database = Token.Instance.Database;
        DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
        //EXECUTE THE COMMAND
        WordsCollection results = new WordsCollection();
        int rowCount = 0;
        using (IDataReader dr = database.ExecuteReader(selectCommand))
        {
            while (dr.Read())
            {
                Words varWords = new Words();
                Words.LoadDataReader(varWords, dr);
                results.Add(varWords);
                rowCount++;
            }
            dr.Close();
        }
        return results;
    }