Sorting on gridview using datareader

Have a look at this code below.........................................................................

        strSQL = "SELECT * FROM dbo.vw_contactsView " + 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();

..................................................................................................................................................

What is the best way to populate a gridview so that I can page and sort rows?  I know that datareader is forward only but I need to sort and page.
mugseyAsked:
Who is Participating?
 
JimBrandleyConnect With a Mentor Commented:
You need to change your select to something like this to just get the pages you want, ordered by the sort_column selected by the user.
SELECT various columns
  FROM
  (SELECT primary_key, ROW_NUMBER() OVER (ORDER BY sort_column, primary_key) rnum
     FROM dbo.vw_testView
    WHERE a.primary_key = ?) pageset
  INNER JOIN dbo.vw_testView a ON pageset.primary_key = a.primary_key
 WHERE rnum >= @firstRow AND rnum <= @lastRow
 ORDER BY rnum;

You can calculate firstRow by:
startRow = (PageNumber * PageSize) + 1;

and endRow by:
endRow = startRow + PageSize - 1;

Jim
0
 
Raju SrivatsavayeSoftware EngineerCommented:
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.
0
 
Abu HamdanEnterprise Architect, PM ExpertCommented:
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 ...
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
mugseyAuthor Commented:
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();
    }
0
 
Abu HamdanEnterprise Architect, PM ExpertCommented:
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)


0
 
Raju SrivatsavayeSoftware EngineerCommented:
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.
0
 
JimBrandleyCommented:
One mistake:
WHERE a.primary_key = ?) pageset

Should be:
WHERE " + strWhereClause + ") pageset
0
 
mugseyAuthor Commented:
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???    :-)


0
 
mugseyAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.