1. Stored Procedure
2. Business Class Functionality
3. User Interface and its Code Behind.
1. Stored Procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[GRID_PAGING]
-- Add the parameters for the stored procedure here
@PageIndex INT = 0,
@PageSize INT = 10,
@NoOfRows NUMERIC(18,0) OUTPUT,
@NoOfPages INT OUTPUT
-- @Sort VARCHAR(100)=''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #GridPaging (ItemID Numeric(18,0),
ItemName NVARCHAR(500),
SpecialDescription NVARCHAR(500),
ItemStatus NVARCHAR(255))
INSERT INTO #GridPaging (ItemID,
ItemName,
SpecialDescription,
ItemStatus)
SELECT ItemID,
ItemName,
SpecialDescription,
ItemStatus
FROM Product
DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
DECLARE @RowsToReturn INT
--First Set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
--Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
--Create a temp table to store the select results
CREATE TABLE #PageIndex
(IndexID INT IDENTITY(1,1) NOT NULL,
ItemID NUMERIC(18,1))
--Insert into the temp table
INSERT INTO #PageIndex ( #PageIndex.ItemID )
SELECT #GridPaging.ItemID FROM #GridPaging ORDER BY #GridPaging.ItemID ASC
--Important SQL Statement
SELECT GP.ItemID,
GP.ItemName,
GP.SpecialDescription,
GP.ItemStatus
FROM #GridPaging GP, #PageIndex PIN
WHERE GP.ItemID = PIN.ItemID
AND ( PIN.IndexID > @PageLowerBound
AND PIN.IndexID < @PageUpperBound )
ORDER BY PIN.IndexID
SET @NoOfRows=(SELECT COUNT(1) FROM #GridPaging)
SET @NoOfPages=@NoOfRows/@PageSize + 1
END
2. Business Class Function
//For Grid Paging
public DataTable GetProducts_PageWise(Int32 PageIndex, Int32 PageSize, out Int64 NoOfRows, out Int32 NoOfPages)
{
try
{
oDal.OpenConnection();
oCon = oDal.GetConnection();
cmd = new SqlCommand("GRID_PAGING", oCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
SqlParameter oNoOfRows = new SqlParameter();
oNoOfRows.ParameterName = "@NoOfRows";
oNoOfRows.DbType = DbType.Int64;
oNoOfRows.Direction = ParameterDirection.Output;
oNoOfRows.Value = 0;
cmd.Parameters.Add(oNoOfRows);
SqlParameter oNoOfPages = new SqlParameter();
oNoOfPages.ParameterName = "@NoOfPages";
oNoOfPages.DbType = DbType.Int32;
oNoOfPages.Direction = ParameterDirection.Output;
oNoOfPages.Value = 0;
cmd.Parameters.Add(oNoOfPages);
SqlDataReader oSDR;
oSDR = cmd.ExecuteReader();
DataTable oDT = new DataTable();
oDT.Load(oSDR);
NoOfRows = Convert.ToInt64(oNoOfRows.Value);
NoOfPages = Convert.ToInt32(oNoOfPages.Value);
return oDT;
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
NoOfRows = 0;
NoOfPages = 0;
return null;
}
finally
{
oCon.Close();
cmd.Dispose();
oCon.Dispose();
}
}
3. User Interface and its Code Behind
3.1 User Interface:
<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional" >
<ContentTemplate>
<asp:UpdateProgress ID="UpdateProgress1" AssociatedUpdatePanelID="UpdatePanel1" runat="server">
<ProgressTemplate>
Populating Grid...
</ProgressTemplate>
</asp:UpdateProgress>
<asp:ListView ID="lvItemsTable" runat="server" ItemPlaceholderID="layoutTableTemplate"
DataKeyNames="ItemID" >
<LayoutTemplate>
<table cellpadding="5" style="width:100%" >
<thead style="position:relative;">
<tr style="background-color:#EAF8FF;text-align:left;">
<th style="position:relative;">Item ID</th>
<th style="position:relative">Item Name</th>
<th style="position:relative">Special Desc.</th>
<th style="position:relative">Item Status</th>
</tr>
</thead>
<tbody id="layoutTableTemplate" runat="server" ></tbody>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr>
<td style="text-align:left;"><%#DataBinder.Eval(Container.DataItem, "ItemID")%></td>
<td style="text-align:left;"><%#DataBinder.Eval(Container.DataItem, "ItemName")%></td>
<td style="text-align:left;"><%#DataBinder.Eval(Container.DataItem, "SpecialDescription")%></td>
<td style="text-align:left;"><%#DataBinder.Eval(Container.DataItem, "ItemStatus")%></td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr style="background-color:#EAF8FF">
<td style="text-align:left;"><%#DataBinder.Eval(Container.DataItem, "ItemID")%></td>
<td style="text-align:left;"><%#DataBinder.Eval(Container.DataItem, "ItemName")%></td>
<td style="text-align:left;"><%#DataBinder.Eval(Container.DataItem, "SpecialDescription")%></td>
<td style="text-align:left;"><%#DataBinder.Eval(Container.DataItem, "ItemStatus")%></td>
</tr>
</AlternatingItemTemplate>
<EmptyDataTemplate>No Records Found
</EmptyDataTemplate>
</asp:ListView>
<br /><br />
<div id="divPager" runat="server" style="width:100%;">
Go To Page No. <asp:DropDownList ID="ddlPageIndex" AutoPostBack="true"
runat="server" onselectedindexchanged="ddlPageIndex_SelectedIndexChanged"></asp:DropDownList>
Page Size <asp:DropDownList ID="ddlPageSize" AutoPostBack="true"
runat="server" onselectedindexchanged="ddlPageSize_SelectedIndexChanged">
<asp:ListItem Selected="True" Text="10" Value="10" ></asp:ListItem>
<asp:ListItem Text="20" Value="20" ></asp:ListItem>
<asp:ListItem Text="30" Value="30" ></asp:ListItem>
<asp:ListItem Text="40" Value="40" ></asp:ListItem>
</asp:DropDownList>
</div>
</ContentTemplate>
</asp:UpdatePanel>
3.2 Code Behind of User Interface (Page.aspx.cs)
protected void Page_Load(object sender, EventArgs e)
{
if (this.IsPostBack.Equals(false))
{
Boolean blnResult = false;
blnResult= DisplayItems(0,Convert.ToInt32(ddlPageSize.SelectedValue.ToString()));
}
}
private Boolean DisplayItems(Int32 PageIndex, Int32 PageSize)
{
DataTable oDT = new DataTable();
try
{
cProduct oProduct = new cProduct();
Int64 NoOfRows = 0;
Int32 NoOfPages = 0;
oDT = oProduct.GetProducts_PageWise(PageIndex, PageSize, out NoOfRows, out NoOfPages);
lvItemsTable.DataSource = oDT;
lvItemsTable.DataBind();
if (ddlPageIndex.Items.Count.Equals(0))
{
PopulatePageNumbers(NoOfPages);
}
else if (!(ddlPageIndex.Items[ddlPageIndex.Items.Count - 1].Value.ToString().Equals(NoOfPages.ToString())))
{
PopulatePageNumbers(NoOfPages);
}
return true;
}
catch (Exception ex)
{
return false;
}
finally
{
oDT = null;
}
}
private void PopulatePageNumbers(Int32 NoOfPages)
{
ddlPageIndex.Items.Clear();
for (int intCtr = 1; intCtr <= NoOfPages; intCtr++)
{
ListItem oLI = new ListItem();
oLI.Text = intCtr.ToString();
oLI.Value = intCtr.ToString();
ddlPageIndex.Items.Add(oLI);
}
}
protected void ddlPageIndex_SelectedIndexChanged(object sender, EventArgs e)
{
Boolean blnResult = false;
blnResult = DisplayItems(Convert.ToInt32(ddlPageIndex.SelectedValue.ToString()) - 1, Convert.ToInt32(ddlPageSize.SelectedValue.ToString()));
}
protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
{
Boolean blnResult = false;
blnResult = DisplayItems(0, Convert.ToInt32(ddlPageSize.SelectedValue.ToString()));
}
Very Good Stuff.
ReplyDelete