[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sorting on gridview using datareader

Posted on 2007-10-19
9
Medium Priority
?
397 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:mugsey
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 9

Expert Comment

by:Raju Srivatsavaye
ID: 20109735
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
 
LVL 7

Expert Comment

by:Abu Hamdan
ID: 20109774
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
 

Author Comment

by:mugsey
ID: 20109826
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:Abu Hamdan
ID: 20109896
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
 
LVL 22

Accepted Solution

by:
JimBrandley earned 2000 total points
ID: 20109918
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
 
LVL 9

Expert Comment

by:Raju Srivatsavaye
ID: 20109936
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
 
LVL 22

Expert Comment

by:JimBrandley
ID: 20109945
One mistake:
WHERE a.primary_key = ?) pageset

Should be:
WHERE " + strWhereClause + ") pageset
0
 

Author Comment

by:mugsey
ID: 20110064
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
 

Author Comment

by:mugsey
ID: 20146428
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question