Solved

Gridview Custom Paging with SQL Server 2005

Posted on 2009-04-06
7
915 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction (All good things must come to an end (http://en.wikipedia.org/wiki/All_Good_Things...)) The original MySQL API (http://php.net/manual/en/book.mysql.php) has gone away, deprecated by PHP in Version 5.5, and removed from PHP in all curre…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now