Sorting on gridview using datareader

You can use a sqldatasource to let .net automatically handle paging and sorting.
OR
You can get the data into an arraylist using datasets or datareaders and then handle the paging and sorting.

I think you should convert you datareader to dataset and set it to the grid and then enable gridview default paging, sorting techniques that will take care for this ,, the following site shows u how to convert datareader to dataset ,,

http://authors.aspalliance.com/stevesmith/articles/convertReadertoSet.asp

there are several samples online for converting readers to dataset ...

OK

Here is my code for the dynamic search using a datareader

How could I amend this ?


 public void doSearch()
    {

        //Load the clauses into a collection of structs.
        //from the collection, there must always be at least one clause in the
        //where query. Use the test for "enabled=1" for this.
        //iterate down the collection to build up the where clause, which is used
        //in a select statement against a view, which already holds all of the
        //joined information.

        string strWhereClause = "";

        Collection<where_clause> cClauses = new Collection<where_clause>();
        where_clause oClause = new where_clause();
        int intCurrentClauseCount = 0;


        //foreach (ListItem ctrl in this.chkListMyInterests.Items)

        //foreach (Control ctrl in this.chkListMyInterests.Items)
        foreach (ListItem ctrl in this.chkListMyInterests.Items)
        {
         
               
                if (ctrl.Text == "opton1")
                {
                    if (ctrl.Selected)
                    {
                        oClause.column = "[opton1]";
                        oClause.operand = " = ";
                        oClause.criteria = "1";
                        cClauses.Add(oClause);

                    }
                }

                if (ctrl.Text == "opton2")
                {
                    if (ctrl.Selected)
                    {
                        oClause.column = "[opton2]";
                        oClause.operand = " = ";
                        oClause.criteria = "1";
                        cClauses.Add(oClause);

                    }
                }

                if (ctrl.Text == "opton3")
                {
                    if (ctrl.Selected)
                    {
                        oClause.column = "[opton13]";
                        oClause.operand = " = ";
                        oClause.criteria = "1";
                        cClauses.Add(oClause);

                    }
                }

                  //and so on

           
        }

        //ok we've looped through the checkbox      
        //now test to make sure there's at least one clause in the statement.
        if (cClauses.Count == 0)
        {
            //there's no clauses; remove any data from there where string, it's not needed.
            strWhereClause = "";
        }
        else
        {
            foreach (where_clause oCurrentClause in cClauses)
            {
                intCurrentClauseCount = intCurrentClauseCount + 1;
                if (intCurrentClauseCount > 1)
                {
                    //make sure and AND is added to the where string
                    strWhereClause = strWhereClause + " AND ";
                }
                else
                {
                    strWhereClause = strWhereClause + " WHERE ";
                }

                strWhereClause = strWhereClause + oCurrentClause.column + " " + oCurrentClause.operand + " " + oCurrentClause.criteria;
            }
        }


        //generated the where clause, build the rest of the sql, and run it against the microsoft
        //data application blocks, returning an active datareader.
        string strSQL;
        strSQL = "SELECT * FROM dbo.vw_testView " + strWhereClause + " ORDER BY lastname DESC";

        // Create a database object
        Database db = DatabaseFactory.CreateDatabase();
        // Get back a DataReader
        IDataReader reader = db.ExecuteReader(CommandType.Text, strSQL);
        GVContacts.DataSource = reader;
        GVContacts.DataBind();
    }

You are using class for executing the database jobs:
Database db = DatabaseFactory.CreateDatabase();

does this class have a method other than db.ExecuteReader ,, that execute DataSet or return dataset ,, if yes than jus use it and it will return the dataset for you ,, other than that you have 2 options
1- convert the reader  (DataReader object) to dataset with my given sample ,,
2- dont use the db class and do you own code to get dataset from database (not recommended)

OK few clarifications here.
1) When you use a custom dataset binding to the grid you can use paging but sorting will not work.
2) There is no need to convert datareader to a dataset. Just use dataset instead of a datareader.

A quick google search to sort and page grids using custom binding will fetch a lot of results.

One mistake:
WHERE a.primary_key = ?) pageset

Should be:
WHERE " + strWhereClause + ") pageset

OK I can amend the above method so that is uses a dataset like this??
string strSQL;
        strSQL = "SELECT * FROM dbo.vw_testView " + strWhereClause + " ORDER BY lastname DESC";

        // Create a database object
        Database db = DatabaseFactory.CreateDatabase();
        //The db has has a method of  db.ExecuteDataSet
       db.ExecuteDataSet

WHAT IS THE BEST WAY TO GO FROM HERE???    :-)

OK thanks

How can I change the above so that it uses a dataset.  I can then sort easily?  I want to do this programmatically