• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 950
  • 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">
        <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
devo00
Asked:
devo00
  • 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. :)
 
 
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Anurag ThakurCommented:
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
 
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:
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
 
devo00Author Commented:
One 'link' was very helpful for the database end only, I also needed C# assistance but found it elsewhere.
0
 
devo00Author Commented:
This is the link mentioned above for paging as well: http://aspnet.4guysfromrolla.com/articles/031506-1.aspx
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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