Web Forms User Controls

Paging for Gridview used Store procedure



ALTER PROCEDURE [dbo].[ProductGetAttributeNOTEmpty]
(
@CurrentPage tinyint=0,
@PageSize As tinyint=20,
@TotalRecords As int OUTPUT
)
AS
BEGIN

SET NOCOUNT ON;

-- Declare variables.
Declare @FirstRec int
Declare @LastRec int

-- Initialize variables.
Set @FirstRec = (@CurrentPage - 1) * @PageSize
Set @LastRec = (@CurrentPage * @PageSize + 1)

-- Create a temp table to hold the current page of data
-- Add an ID column to count the records
Create Table #TempTable
(
[TempId] int IDENTITY(1,1) PRIMARY KEY,
[productID] [int],
[productName] [nvarchar](150),
[createdOn] [datetime],
[createdBy] [nvarchar](50),
[modifiedOn] [datetime],
[modifiedBy] [nvarchar](50)
)

--Fill the temp table with the reminders
Insert Into #TempTable
(
[productID],
[attribute],
[productName],
[createdOn],
[createdBy],
[modifiedOn],
[modifiedBy]

)
Select
*
From
[dbo].[Product]
WHERE DATALENGTH([Products].[attribute]) <> 0

--Select one page of data based on the record numbers above
Select *
From
#TempTable As Products
Where
TempID > @FirstRec
And TempID < @LastRec

--Return the total number of records available as an output parameter
Select
@TotalRecords = Count(*)
From #TempTable
END