CODEDIGEST
Home Articles CodeDigest Tutorials FAQs
Skip Navigation LinksHome » Article » ASP.Net Article » Custom GridView Paging with ObjectDataSource Control with ASP.Net 2.0   You are not logged in.
Search
 

Technologies
 

Sponsors
 

CodeDigest Navigation
 

Technology News
 

Community News
No News Feeds available at this time.
 
Custom GridView Paging with ObjectDataSource Control with ASP.Net 2.0

By Satheesh Babu
Posted On Jan 18,2009
Article Rating: (Login)
Average Rating: 5
No of Ratings: 1
No of Comments: 13
Print this article.
Category: ASP.Net

Subscribe to our feed!

Custom GridView Paging with ObjectDataSource Control with ASP.Net 2.0

 

ASP.Net is packed with many data bound control to present the data in tabular form to the users. For example: GridView, DataList, Repeater are some of the useful data bound control that will help in presenting data efficiently and easily. When the data grows drastically, it will be better and user friendly if we display the records with page numbers by restricting some x number of records per page. In GridView control, we can enable the inbuilt paging by setting AllowPaging and PageSize property. In this approach, it will fetch all the records from database to display the records in any page. This will affect the performance when the number of records is really huge.

 

It will be good, if we fetch only those records which are required to display in that particular page. This article will help us to implement the custom paging with ObjectDataSource control and SqlServer 2005.
DataSource controls are new additions to ASP.Net 2.0 controls set that help in binding the data from database. Not every time, we can use the DataSet and DataTable object to hold the data from database. We will have our own business object and its associated collection object to hold the data in our projects. We can use ObjectDataSource control to bind database records that is fetched from database, if we have our own business objects. Another advantage of using ObjectDataSource control is, it will help us in implementing the custom paging mechanism very easily without the need to build page numbers separately i.e. we can still use inbuilt paging feature of GridView to display page numbers and we can use ObjectDataSource control to fetch the records that is required only for that page.

 

Configuring ObjectDataSource Control for Custom Paging

To configure the custom paging with ObjectDataSource control, we need to configure the following property.

 

EnablePaging

This property accepts a Boolean to enable paging with ObjectDataSource control.

 

SelectCountMethod

We need to specify the name of the method that can fetch the total number of records available in the database.

 

StartRowIndexParameterName

This property will accept the start row index of the record to fetch from database.

 

MaximumRowsParameterName

This property will accept the maximum number of rows that can be fetched at one time. This will be equivalent to page size.

 

SelectMethod

This property will accept the name of the method that fetches actual database record.

 

TypeName

This property needs to be configured with the class or type name that has the implementation of SelectCountMethod and SelectMethod.

 

In order to configure the ObjectDataSource control, we need to first implement the SelectCountMethod and SelectMethod. The following code snippet will have 2 methods called GetUserCount() and BindUsers(). The BindUsers()  method will take 2 parameters called startRowIndex and maximumRows to fetch the data from database. To make our understanding easier, the BindUsers() method will return a DataTable object. The return type can also be a List<User> object, where User is a business object.

 

public class UserDAO

{     

    public DataTable BindUsers(int startRowIndex, int maximumRows)

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Sql"].ConnectionString);       

        SqlCommand com = new SqlCommand("GetUsers", con);

        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@RowIndex", SqlDbType.Int, 4).Value = startRowIndex;

        com.Parameters.Add("@MaxRows", SqlDbType.Int, 4).Value = maximumRows;

        SqlDataAdapter ada = new SqlDataAdapter(com);

        DataTable dt = new DataTable();

        ada.Fill(dt);

        return dt;       

    }

    public int GetUserCount()

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Sql"].ConnectionString);

        con.Open();

        SqlCommand com = new SqlCommand("GetUserCount", con);

        SqlDataReader dr = com.ExecuteReader();

        int count = 0;

        while (dr.Read())

        {

            if (dr["UserCOUNT"] != null)

                int.TryParse(dr["UserCOUNT"].ToString(), out count);          

        }

        return count;

    }

}

 



ASP.Net Hosting

Recent Articles

Now, we can configure the ObjectDataSource control with these methods.

 

        <asp:ObjectDataSource ID="ObjectDataSource1" EnablePaging="true" 

        runat="server" SelectCountMethod="GetUserCount"

        SelectMethod="BindUsers" TypeName="UserDAO"

        StartRowIndexParameterName="startRowIndex" MaximumRowsParameterName="maximumRows">

         </asp:ObjectDataSource>

 

Next, we can implement the stored procedure that fetches the records only for that page by accepting startRowIndex and maximumRows as parameters.

 

CREATE PROC [dbo].[GetUsers]

(@RowIndex INT ,

@MaxRows INT)

AS

DECLARE @StartRow INT

DECLARE @EndRow INT

 

SET @StartRow = (@RowIndex+1)

SET @EndRow = @StartRow + @MaxRows

 

SELECT * FROM (

SELECT     FirstName, LastName, ROW_NUMBER() OVER (ORDER BY UserID) AS ROW

FROM         CD_Users) As NumberedUsers

WHERE ROW BETWEEN @StartRow AND @EndRow

 

The start row index parameter that will be returned from GridView control will be 0. Hence, we need to increment the row index parameter by 1 before querying the database.

 

Finally, we will configure the GridView control to accept the datasource control.

 

        <asp:GridView ID="gvUsers" runat="server" AllowPaging="True" DataSourceID="ObjectDataSource1" PageSize="2">

        </asp:GridView>

 

Thus, we have implemented the custom paging with the help of ObjectDataSource control and Sql Server 2005.

 

Practically, when we implement the custom paging there are chances that we should restrict the result set based on some filter condition. In other words, we can restrict the result set by some parameters. For example,

 

public DataTable BindUsers(string FirstName, string LastName, int startRowIndex, int maximumRows)

{

}

public int GetUserCount(string FirstName, string LastName)

{

}

 

These extra parameters can be configured from ObjectDataSource control SelectParameter collection.

 

       <asp:ObjectDataSource ID="ObjectDataSource1" EnablePaging="true" 

        runat="server" SelectCountMethod="GetUserCount"

        SelectMethod="BindUsers" TypeName="UserDAO"

        StartRowIndexParameterName="startRowIndex" MaximumRowsParameterName="maximumRows">      

         <SelectParameters>

        <asp:ControlParameter  ControlID="txtFirstName" Name="FirstName" Type="string"  />

        <asp:ControlParameter  ControlID="txtLastName" Name="LastName" Type="string"  />

        </SelectParameters>

         </asp:ObjectDataSource>

 

Finally, the stored procedure should be changed to accept the filter parameters.

 

Downloads

Source Code 


Conclusion

Thus, we have understood how to implement custom paging with the help of ObjectDataSource control and Sql server 2005. Also, the introduction of Sql 2005 gave us the new feature called Row_Number() which made our job easier in fetching only required number of records. Download source attached with this article to understand it better. Configure the database server and database to use the source code attached with this article.

 

Similar Articles
  • You can contribute to CodeDigest.Com:
    Article Feedback
    Title  
    Submitted By  
    Comment  
    Enter the verification number
     
    Comments
    Thanks
    It Simplifies Paging using Object Datasource and increases the performance...

    Thanks For This Article...It works!!!
    Good Article
    It Makes Paging So Simple...
    Not Working: GetUserCount()
    Hello,

    I tried the code. But I don't understand the GetUserCount command.

    There is no stored procedure named like that. What is the code for this command??

    I understand that the code is to count the number of users, but how does it work??

    Please help. Thnx
    Thanks
    This was exacly what I needed thaks for writting this up.
    Why sql
    seems pointless to query sql with an object data source. classes via hibernate or another source would be better
    Very Good
    very good fot understanding the Custom paging that is wounderful features in asp.net .
    Sorting
    The article is very simple to understand. But the current paging is done based on sorting of UserID (OVER (ORDER BY UserID) ). How can we change it to be sorted on different parameters?

    Also I think its better to cache the result from GetUserCount as it remains fairly constant.

    Really great article.
    exellent
    This article really helpfull in implementing custom paging.
    Thanks a lot again.

    I realy appriciate your work.
    How to use this without a stored procedure
    I really like the above implementation. However, I do not have the option of a stored procedure. I usually have my query in the code file itself.

    How can I do it in this case?
    Good Example
    Very simple way of custom paging with object datasource
    Thanks (it works!)
    Clear and step by step example.
    This helped me to get it working.
    RE:Not working
    Hi Renjith,
    GetUserCount() method returns the number of users that is currently available for paging.
    Pls read the SelectCountMethod section for more info..
    Not working
    SqlCommand com = new SqlCommand("GetUserCount", con);

    SqlDataReader dr = com.ExecuteReader();

    In the above code what is GetUserCount ????????

    what is the use of GetUserCount() Methode ?