How to implement Custom paging Using ASP.NET Data Grid


Here we will examine how can we implement custom paging for data Grid using stored procedure. Here is the implementation of stored procedure. Here you have 3 parameter for stored procedure.
@page is basically page number in data grid
@pageSize is Number of records you are going to show one page
@TotalNofRecords is output parameter. It return total Number of records in the Query
Here when you go through the stored procedure you have create a tempory table and you retrieve only required data rows from that tempory table. Using @StartRecordNo and @EndRecordNo
Create PROCEDURE [dbo].[GetEmployee]
(
@Page int,
@PageSize int,
@TotalNofRecords int output
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @StartRecordNo int, @EndRecordNo int
IF (@Page=0)
BEGIN
SET @StartRecordNo = 1
SET @EndRecordNo = @PageSize
END
ELSE
BEGIN
SET @StartRecordNo = @PageSize * @Page + 1
SET @EndRecordNo = (@PageSize * (@Page+1) )
END
CREATE TABLE [#TmpEmployee]
(
[RowNo] int identity(1,1),
[EmployName] int not null,
[Address] varchar(255)
)
INSERT INTO [#TmpEmployee]([EmployName], [Address])
SELECT
FirstName, Address1
FROM Employee
ORDER BY FirstName
SELECT @TotalNofRecords = COUNT( FirstName) FROM [#TmpEmployee]
SET NOCOUNT OFF
SELECT [RowNo], [FirstName], [Address]
FROM [#TmpEmployee]
WHERE [RowNo]>= @StartRecordNo
AND [RowNo]<= @EndRecordNo
RETURN 0
END
When you bind the grid you can have method call BindGrid method
private void BindGrid(int pageNo)
{
try
{
int locationId ;
int recordsPerPage = 10 ;
int totalRecords = 0;
DataTable empList = EmpService.GetEmployee( pageNo, recordsPerPage ,ref totalRecords);
if (empList.Rows.Count > 0)
{
uxEmployeGrid.DataSource = jobsList;
uxEmployeGrid.VirtualItemCount = totalRecords;
uxEmployeGrid.DataBind();
}
}
catch(Exception ex)
{
throw ex;
}
}
When call the first time call the BindGrid Method with pageNo = 0
BindGrid(0);
When click the paging call the BindGrid method
protected void uxEmployeGrid_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
uxEmployeGrid.CurrentPageIndex = e.NewPageIndex;
BindGrid(uxEmployeGrid.CurrentPageIndex);
}

Comments

Post a Comment

Popular posts from this blog

GROUP BY, CUBE, ROLLUP and SQL SERVER 2005

Image
  I learn about CUBE and ROLLUP function today and I found it really usefull for generating reports. Let me give you one example where we can use ROLLUP and CUBE. Lets say you are developing the E-Commerce application and administrator wants the report which shows products purchased by all user group by product and buyer. You will say that’s really easy and can write the query as shown below, 1: SELECT CustomerName,CustomerName,SUM(Quantity*PricePerItem) 2: FROM Orders GROUP BY CustomerName,CustomerName Fig – (1) Group By clause.      Which will returns the result as shown below, Fig – (2) Result of GROUP BY clause     However what if you want result as display below, Fig – (3) Desire result        Here ROLLUP and CUBE comes into the picture and help us. The above result is generated using ROLLUP.  ROLLUP adds new row for each column used in GROUP BY clause. First have a look ...
Read more

How to get content of Ckeditor & Fckeditor using Javascript

CKEDITOR: To get content of ckeditor we will create object of ckeditor and then we will provide id of textarea that is replaced by ckeditor. Syntax: CKEDITOR.instances.textarea_id.getData();getData(); Where CKEDITOR.instances   :  gives all the instances of CKEDITOR; textarea_id   : it is ID of textarea that is containing data ; getData()       : return content of Ckeditor current instance. FCKEDITOR: To get content of Fckeditor we will create Instance of Fckeditor for textarea and then we will get data using GetHTML method. Syntax : FCKeditorAPI.GetInstance('textarea_id'). GetHTML(); Where textarea_id     :  it is ID of textarea that is containing data ; GetHTML()             :  This function returns content of textarea with id textarea_id.  
Read more

How to Fix Error- Sys.WebForms.PageRequestManagerTimeoutException - The server request timed out

To solve this problem we can increase the timeout. You can change the timeout time by adding a new property to the script manager control. This property is called AsyncPostBackTimeOut and it can help us to define the number of seconds before the request will throw a request timeout exception*. For example if you want that the timeout will take maximum 10 minutes your code should be look like this: < asp : ScriptManager ID ="ScriptManager1" runat ="server" AsyncPostBackTimeOut ="600" > </ asp : ScriptManager >       * The default value of the AsyncPostBackTimeOut property is 90 seconds.
Read more