Solved

Gridview Custom Paging with SQL Server 2005

Posted on 2009-04-06
7
917 Views
Last Modified: 2012-05-06
I need to know how to setup custom sorting / paging with gridviews for large datasets with .NET 2.0, C# and SQL Server "2005". My current method for general SQL SELECT queries is below and does not have anything I need for custom paging, but I'd like to adapt what I have to work.

I am new to n-tier (presentation / BLL / DAL) and am not using that in this example unless it'd be easier to do.


<asp:ObjectDataSource ID="ObjDataSource1" runat="server" TypeName="SomeClass" SelectMethod="rtnDataSet">
        <SelectParameters>
            <asp:Parameter DefaultValue="Select * from employees" Name="_sqlStr" />
        </SelectParameters>
    </asp:ObjectDataSource> 
 
 
    public DataSet rtnDataSet(string _sqlStr)
    {
        DataSet _rtnDS = new DataSet();
        dataRetrieval.fillDataSet(ref _rtnDS, ref _sqlStr);
	return _rtnDS;
    }
 
 
    public static void fillDataSet(ref DataSet _dataSet, ref string _sqlStr)
    {
            SqlConnection _sqlCon = connectToSomeDB();
            SqlCommand _sqlCmd = new SqlCommand();
 
            _sqlCmd.Connection = _sqlCon;
            _sqlCmd.CommandType = CommandType.Text;
            _sqlCmd.CommandTimeout = 30;
            _sqlCmd.CommandText = _sqlStr;
 
            SqlDataAdapter _sqlAdptr = new SqlDataAdapter(_sqlCmd);
            _sqlAdptr.Fill(_dataSet);
 
            _sqlAdptr.Dispose();
            _sqlCmd.Dispose();
            _sqlCon.Close();
    }

Open in new window

0
Comment
Question by:devo00
  • 4
  • 2
7 Comments
 
LVL 27

Accepted Solution

by:
nmarun earned 500 total points
ID: 24081656
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24084186
0
 

Author Comment

by:devo00
ID: 24087129
raqi: Strange, why don't the standard sort / page controls work on that solution? I just need a normal Gridview with normal paging numbers at-bottom and normal heading sorts, but using Custom Sorting / Paging to return a small subset each time.
nmarun: Your first link may help, but it requires a re-write of what I'm using right? Is there no way to adapt what I have? Links 1 and 2 are for standard sorting / paging.
I'm hoping for a little hands-on assistance. :)
 
 
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 24087778
normal pagination is very very simple to implement because in that case you have the complete result set at the application level and you just move around that result set or sort it only
you dont hit the database to do the pagination or sorting

there is a very big flaw in this kind or approach - if the result set is very large and quite a lot of user start using  it then the performance of your application will decrease and will be quite noticable
thats why i suggested you what's in that link - it might not be complete but will definitely make your application more responsive
in such a customized pagination you will have to implement custom sorting
0
 

Author Comment

by:devo00
ID: 24101914
This article from nmarun should do the trick for sorting for the stored procedure, and there is an included link for paging using SQL Server 2005, although it does not help with the C# end:
http://www.4guysfromrolla.com/articles/032206-1.aspx
I found this article to be helpful and supplemental to the above procedure by giving presentation and DAO layer coding:
http://www.codedigest.com/Articles/ASPNET/180_Custom_GridView_Paging_with_ObjectDataSource_Control_with_ASPNet_20.aspx
 
0
 

Author Closing Comment

by:devo00
ID: 31567226
One 'link' was very helpful for the database end only, I also needed C# assistance but found it elsewhere.
0
 

Author Comment

by:devo00
ID: 24102030
This is the link mentioned above for paging as well: http://aspnet.4guysfromrolla.com/articles/031506-1.aspx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Foolproof security solutions has become one of the key necessities of every e-commerce or Internet banking website. If you too own an online shopping site then its vital for you to equip your web portal with customer security features that can allow…
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
This video teaches users how to migrate an existing Wordpress website to a new domain.
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

830 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