• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 952
  • Last Modified:

Gridview Custom Paging with SQL Server 2005

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">
            <asp:Parameter DefaultValue="Select * from employees" Name="_sqlStr" />
    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);

Open in new window

  • 4
  • 2
1 Solution
devo00Author Commented:
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. :)
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Anurag ThakurTechnical ManagerCommented:
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
devo00Author Commented:
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:
I found this article to be helpful and supplemental to the above procedure by giving presentation and DAO layer coding:
devo00Author Commented:
One 'link' was very helpful for the database end only, I also needed C# assistance but found it elsewhere.
devo00Author Commented:
This is the link mentioned above for paging as well: http://aspnet.4guysfromrolla.com/articles/031506-1.aspx
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now