Solved

Gridview Custom Paging with SQL Server 2005

Posted on 2009-04-06
7
916 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to access a DataBinder.Eval variable using C# ? 4 27
unable to set breakpoints in chrome source tab 1 29
Syntax error 9 45
Angular - break the dataset in chunks? 7 38
If I have to fix slow responding website my first thoughts are server side optimizations: the database may not be optimized or caching is not enabled, or things like that. We often overlook another major part of our web application: the client. We o…
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.
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

937 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

10 Experts available now in Live!

Get 1:1 Help Now