GridView with built in CustomPaging – Asp.Net 4.5

Every web developer hates Gridview ViewState specially when its large in size. Solution to this is to create a custom paging solution. but it was too much of work. You need to Create a custom control and then handling all the combinations of click on page links. Styling of the pager was also not standard.

Asp.Net 4.5 resolved this by providing a Gridview attribute AllowCustomPaging = “true” which allow us to utilize built in gridview paging with custom paging logic. There is one more property VirtualItemCount added to gridview which should be set to the total number of records.

Below is the simple example. It seems so easy that you can do it right away in your existing code with minimal changes.

UI Code:


 <asp:GridView ID="gvPersons" runat="server" 
  AllowCustomPaging="true"
  AllowPaging="True"           AutoGenerateColumns="False" PageSize="20"
  OnPageIndexChanging="gvPersons_PageIndexChanging">

 <Columns>
 <asp:BoundField DataField="BusinessEntityID" HeaderText="Product"
 SortExpression="BusinessEntityID" />

 <asp:BoundField DataField="PersonType" HeaderText="PersonType"
 ReadOnly="True" SortExpression="PersonType" />

 <asp:BoundField DataField="FirstName" HeaderText="FirstName"
 SortExpression="FirstName" />

 <asp:BoundField DataField="LastName" HeaderText="LastName"
 SortExpression="LastName" />

 </Columns>
 <PagerStyle HorizontalAlign="Right" />
 <PagerSettings Mode="Numeric" />
 </asp:GridView>

Custom Paging Stored Procedure in Sql server 2012

This query is the heart of this custom paging logic. Sql Server 2012 provides simply “Awesome” way of writing a very complex query (in earlier versions).


CREATE PROCEDURE usp_GetPersonsPaged
	@StartRowIndex  int,
	@MaximumRows	int
AS
BEGIN
	
	SET NOCOUNT ON;
	    DECLARE @lastRowIndex int
		SET @lastRowIndex = (@StartRowIndex + @MaximumRows) - 1;
		 

	 SELECT   [BusinessEntityID]
		 ,[PersonType]     
		 ,[FirstName]     
		 ,[LastName]  FROM [AdventureWorks2012].Person.Person
	 ORDER BY BusinessEntityID
	 OFFSET @StartRowIndex ROWS
	 FETCH NEXT @lastRowIndex ROWS ONLY;	

END

Code Behind:

Nothing changes here. Only your data access layer method returns Maximum rows equal to page size.

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                gvPersons.VirtualItemCount = Person.GetTotalPersonCount() ;
                BindGrid(0,gvPersons.PageSize);                
            }
        }

        private void BindGrid(int pageIndex,int pageSize)
        {            
            gvPersons.DataSource = Person.GetPersonListPaged(pageIndex, gvPersons.PageSize);
            gvPersons.DataBind();
        }

        protected void gvPersons_PageIndexChanging(object sender, GridViewPageEventArgs e)
        { 
            gvPersons.PageIndex = e.NewPageIndex;
            BindGrid(gvPersons.PageIndex,gvPersons.PageSize);
        }

Data Access Code:

you can use AdventureWorks Sample DB for Sql server 2012 to check out performance.


public class Person
    {
        public int BusinessEntityID { get; set; }

        public string PersonType { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }


        public static List<Person> GetPersonListPaged(int startIndex, int maxPazeSize)
        {
            List<Person> persons = new List<Person> ();

            SqlConnection con = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            try
            {
                con.ConnectionString = "data source=.\\sqlserver2012;initial catalog=AdventureWorks2012;integrated security=True;";

                cmd.Connection = con;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "usp_GetPersonsPaged";

                cmd.Parameters.AddWithValue("StartRowIndex", startIndex);
                cmd.Parameters.AddWithValue("MaximumRows"  , maxPazeSize);
                con.Open();
                
                SqlDataReader dr;
                dr = cmd.ExecuteReader();
                
                while (dr.Read())
                {
                    Person p = new Person() {BusinessEntityID=dr.GetInt32(0),PersonType=dr.GetString(1),FirstName=dr.GetString(2),LastName=dr.GetString(3) };

                    persons.Add(p);                    
                }                
                                
                con.Close();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                con.Dispose();
                cmd.Dispose();
            }

            return persons;
        }



        public static int GetTotalPersonCount()
        {
            int totalCount = 0;

            SqlConnection con = new SqlConnection();
            SqlCommand cmd = new SqlCommand();            
            try
            {
                con.ConnectionString = "data source=.\\sqlserver2012;initial catalog=AdventureWorks2012;integrated security=True;";
                
                cmd.Connection = con;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "select count(1) from [AdventureWorks2012].[Person].[Person];";
                con.Open();
                totalCount = (int)cmd.ExecuteScalar();

                con.Close();
            }
            catch (Exception ex)
            {


            }
            finally
            {
                con.Dispose();
                cmd.Dispose();
            }

            return totalCount;
        }
    }
}

I hope it helps!!

Advertisements

About saxenapraveen

I work with Microsoft and have 12 years of experience in developing,designing and leading reliable and scalable solutions for cloud and device ready businesses.

Posted on February 19, 2013, in .NET, Asp.Net WebForms, Sql Server and tagged , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: