Friday, May 29, 2009

Custom Paging in GridView without Object Data Source

All we need to use the Custom paging in grid view without object data source.

Here i am going to explain my code which is used in custom paging of grid view... What you will need to do is used your DataSource SP or query with this paging...

All magic lies in SQL Server 2005 ROWNumber() Function.... Simple SP for this gridview datasource is


Select Row,ID,Name
(
    Select ROW_Number()OVER(ORDER BY ID) As Row,ID,Name 
    from table1
) AS A
Where Row=>@PageIndex*PageSize
and Row<(@PageIndex+1)*PageSize;
--here @PageIndex and @PageSize are passed as parameter as gridview1.PageIndex and gridview1.PageSize  
    

if you are not familiar with RowNumber function than create one temp table use Row as primary key with auto increament number and than use insert select statement.... this will work in all database.....

Follow is the C# code for custom gridview...I m creating new grid view control here

using System; 
using System.Collections.Generic; 
using System.ComponentModel; 
using System.Text; 
using System.Web; 
using System.Web.UI; 
using System.Web.UI.WebControls;  
namespace CustomPagingGridView 
{  
    [DefaultProperty("Text")]  
    [ToolboxData("<{0}:CustomePagingGrid runat=server>CustomePagingGrid>")]  
    public class CustomePagingGrid : GridView 
    {   
        public CustomePagingGrid(): base()
        {}
        
        #region Custom properties       
        // this property is to use to find the total number of record for grid         
        [Browsable(true), Category("NewDynamic")]         
        [Description("Set the virtual item count for this grid")]         
        public int VirtualItemCount         
        {             
            get
            {
                if (ViewState["pgv_vitemcount"] == null)
                    ViewState["pgv_vitemcount"] = -1;                 
                return Convert.ToInt32(ViewState["pgv_vitemcount"]);             
            }             
            set             
            {                 
                ViewState["pgv_vitemcount"] = value;             
            }         
        }         
        
        // this is used to sort the gridview columns        
        [Browsable(true), Category("NewDynamic")] [Description("Get the order by string to use for this grid when sorting event is triggered")] 
        public string OrderBy         
        {             
            get             
            {                 
                if (ViewState["pgv_orderby"] == null)                     
                    ViewState["pgv_orderby"] = string.Empty;                 
                return ViewState["pgv_orderby"].ToString();             
            }             
            protected set             
            {                 
                ViewState["pgv_orderby"] = value;             
            }         
        }            
        
        private int Index         
        {             
            get             
            {                 
                if (ViewState["pgv_index"] == null)                     
                    ViewState["pgv_index"] = 0;                 
                return Convert.ToInt32(ViewState["pgv_index"]);             
            }             
            set             
            {                 
                ViewState["pgv_index"] = value;             
            }         
        }            
        
        public int CurrentPageIndex         
        {             
            get             
            {                 
                if (ViewState["pgv_pageindex"] == null)                     
                    ViewState["pgv_pageindex"] = 0;                 
                return Convert.ToInt32(ViewState["pgv_pageindex"]);             
            }             
            set             
            {                 
                ViewState["pgv_pageindex"] = value;             
            }         
        }            
        
        private int SetCurrentIndex         
        {             
            get             
            {                 
                return CurrentPageIndex;             
            }             
            set             
            {                 
                CurrentPageIndex = value;             
            }         
        }                 
        
        
        // if this property is set to greater than zero means custom paging neede                    
        private bool CustomPaging         
        {             
            get             
            {                 
                return (VirtualItemCount != -1);             
            }         
        }          
        #endregion             
        
        #region Overriding the parent methods           
        public override object DataSource         
        {             
            get             
            {                 
                return base.DataSource;             
            }             
            set             
            {                 
                base.DataSource = value;                 
                // we store the page index here so we dont lost it in databind                 
                CurrentPageIndex = PageIndex;             
            }         
        }     
        
        protected override void OnSorting(GridViewSortEventArgs e) 
        { 
            // We store the direction for each field so that we can work out whether next sort 
            // should be asc or desc order  PageIndex = CurrentPageIndex; SortDirection direction = SortDirection.Ascending;  
            if(ViewState[e.SortExpression]!=null&& (SortDirection)ViewState[e.SortExpression] == SortDirection.Ascending)  
            {   
                direction = SortDirection.Descending; 
            }  
            ViewState[e.SortExpression] = direction;             
            OrderBy = string.Format("{0} {1}", e.SortExpression, (direction == SortDirection.Descending ? "DESC" : ""));             
            base.OnSorting(e);         
        }            
        
        protected override void InitializePager(GridViewRow row, int columnSpan, PagedDataSource pagedDataSource)         
        {             
            // This method is called to initialise the pager on the grid. We intercepted this and override             
            // the values of pagedDataSource to achieve the custom paging using the default pager supplied             
            if (CustomPaging)             
            {                 
                pagedDataSource.AllowCustomPaging = true;                 
                pagedDataSource.VirtualCount = VirtualItemCount;                 
                pagedDataSource.CurrentPageIndex = CurrentPageIndex;             
            }             
            base.InitializePager(row, columnSpan, pagedDataSource);         
        }               
        
        // here we do custom paging         
        protected override void OnPageIndexChanging(GridViewPageEventArgs e)         
        {             
            if (CustomPaging)             
            {                 
                if (this.PagerSettings.Mode == PagerButtons.NumericFirstLast || this.PagerSettings.Mode == PagerButtons.Numeric)                 
                {                     
                    base.OnPageIndexChanging(e);                 
                }                 
                else                 
                {                     
                    if (e.NewPageIndex == -1)                     
                    {                         
                        Index -= 1;                     
                    }                     
                    else if (e.NewPageIndex == 0)
                    {                         
                        Index = 0;                     
                    }                     
                    else if (e.NewPageIndex == ((int)Math.Ceiling((decimal)(VirtualItemCount) / PageSize) - 1))                     
                    {                         
                        Index = ((int)Math.Ceiling((decimal)(VirtualItemCount) / PageSize) - 1);                     
                    }                     
                    else                     
                    {                         
                        Index += 1;                     
                    }                     
                    if (Index < 0)                     
                    { 
                        Index = 0; 
                    }
                    CurrentPageIndex = Index;                     
                    e.NewPageIndex = Index;                     
                    base.OnPageIndexChanging(e);                 
                }             
            }         
        }           
    #endregion     
    } 
}
    

if u have any doubt,please feel free to ask me....

No comments:

Post a Comment

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...