Blog Archives

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