This is the first part of my tutorial series in which I’ll explain how to build an Image or Picture gallery using ASP.Net DataList control.
In this part I’ll explain how to implement simple paging in ASP.Net DataList control. ASP.Net DataList Control does not have inbuilt paging capabilities hence I’ll implement custom paging.
Database
Here is the design of the database table that I’ll be using. I am using SQL Server 2005 Database for this tutorial
![DataList Custom Paging using Stored Procedures SQL Server 2005]()
As you’ll notice I am storing the Image Filename in the database table and the images in a folder so that based on the Image File Path I can display the related images in the DataList control
DataList Control
Right now I am simply concentrating on the Pagination of the ASP.Net DataList. Below is the markup of the DataList control used for this tutorial.
<asp:DataList ID="DataList1" runat="server" RepeatColumns = "2" RepeatLayout = "Table" Width = "500px">
<ItemTemplate>
<br />
<table cellpadding = "5px" cellspacing = "0" class="dlTable">
<tr>
<td>
<asp:Image ID="Image1" runat="server" ImageUrl = '<%# Eval("FilePath")%>'
Width = "200px" Height = "200px"/>
</td>
</tr>
</table>
<br />
</ItemTemplate>
</asp:DataList>
Basically as you can view I am simply binding the File Path to the Asp.Net Image Control.
Stored Procedure for Pagination
For pagination I am using the stored procedure
CREATE PROCEDURE [dbo].[spx_Pager]
@PageNo int = 1,
@ItemsPerPage int = 2,
@TotalRows int out
AS
BEGIN
SET NOCOUNT ON
DECLARE
@StartIdx int,
@SQL nvarchar(max),
@SQL_Conditions nvarchar(max),
@EndIdx int
IF @PageNo < 1 SET @PageNo = 1
IF @ItemsPerPage < 1 SET @ItemsPerPage = 10
SET @StartIdx = (@PageNo -1) * @ItemsPerPage + 1
SET @EndIdx = (@StartIdx + @ItemsPerPage) - 1
SET @SQL = 'SELECT FilePath
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS Row, *
FROM tblFiles ) AS tbl WHERE Row >= '
+ CONVERT(varchar(9), @StartIdx) + ' AND
Row <= ' + CONVERT(varchar(9), @EndIdx)
EXEC sp_executesql @SQL
SET @SQL = 'SELECT @TotalRows=COUNT(*) FROM tblFiles'
EXEC sp_executesql
@query = @SQL,
@params = N'@TotalRows INT OUTPUT',
@TotalRows = @TotalRows OUTPUT
END
Below are the parameters and their significances
|
Parameter
|
Significance
|
Type
|
|
@PageNo
|
Current Page Number
|
Input
|
|
@ItemsPerPage
|
Total Numbers of Items to be fetched
|
Input
|
|
@TotalRows
|
Returns the Total Records in Table
|
Output
|
That’s it once these three parameters are passed your stored procedure is ready to run. Now we’ll have to look for the Pagination logic which resides in our page.
Pagination Logic
The below two variables play an important role. CurrentPage stores the Current Page that is displayed in the ASP.Net DataList control while ItemsPerPage stores the total number of Items to be displayed on a page.
C#
private int CurrentPage = 1;
private int ItemsPerPage = 2;
VB.Net
Private CurrentPage As Integer = 1
Private ItemsPerPage As Integer = 2
In order to enable pagination I’ll add two LinkButtons (Previous and Next) in a panel that will act as the Pager for the ASP.Net DataList control.
<asp:Panel ID="pnlPager" runat="server" Height="20px" Width="153px">
<asp:LinkButton ID="lnkPrev" runat="server" CommandName = "Previous"
Text = "<< Previous" OnClick = "Pager_Click"></asp:LinkButton>
<asp:LinkButton ID="lnkNext" runat="server" CommandName = "Next"
Text = "Next >>" OnClick = "Pager_Click"></asp:LinkButton>
</asp:Panel>
As you can view above I have simply set the CommandName property so that I can identify the buttons and I am calling a function Pager_Click which will do the pagination when the LinkButtons are clicked.
C#
protected void Pager_Click(object sender, EventArgs e)
{
LinkButton lnkPager = (LinkButton)sender;
int PageNo = 1;
switch (lnkPager.CommandName)
{
case "Previous":
PageNo = this.CurrentPage - 1;
break;
case "Next":
PageNo = this.CurrentPage + 1;
break;
}
int TotalRows = this.BindList(PageNo);
int PageCount = this.CalculateTotalPages(TotalRows);
ViewState["CurrentPage"] = PageNo;
if (PageNo == 1)
{
lnkPrev.Enabled = false;
}
else
{
lnkPrev.Enabled = true;
}
if (PageNo == PageCount)
{
lnkNext.Enabled = false;
}
else
{
lnkNext.Enabled = true;
}
}
VB.Net
Protected Sub Pager_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim lnkPager As LinkButton = DirectCast(sender, LinkButton)
Dim PageNo As Integer = 1
Select Case lnkPager.CommandName
Case "Previous"
PageNo = Me.CurrentPage - 1
Exit Select
Case "Next"
PageNo = Me.CurrentPage + 1
Exit Select
End Select
Dim TotalRows As Integer = Me.BindList(PageNo)
Dim PageCount As Integer = Me.CalculateTotalPages(TotalRows)
ViewState("CurrentPage") = PageNo
If PageNo = 1 Then
lnkPrev.Enabled = False
Else
lnkPrev.Enabled = True
End If
If PageNo = PageCount Then
lnkNext.Enabled = False
Else
lnkNext.Enabled = True
End If
End Sub
The above function simply identifies the clicked button and simply increments or decrements the Current Page variable and simply updates the same in the database. Then calls the BindList and CalculateTotalPages functions to get the Total Rows and the Count of pages which is then used to Enable or Disable the LinkButtons.
Data Binding the DataList Control
C#
private int BindList(int PageNo)
{
int TotalRows = 0;
DataTable dt = new DataTable();
String strConnString = System.Configuration
.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("spx_Pager");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@PageNo", SqlDbType.Int).Value = PageNo;
cmd.Parameters.Add("@ItemsPerPage", SqlDbType.Int).Value = ItemsPerPage;
cmd.Parameters.Add("@TotalRows", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Connection = con;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
DataList1.DataSource = dt;
DataList1.DataBind();
TotalRows = Convert.ToInt32(cmd.Parameters["@TotalRows"].Value);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
return TotalRows;
}
VB.Net
Private Function BindList(ByVal PageNo As Integer) As Integer
Dim TotalRows As Integer = 0
Dim dt As New DataTable()
Dim strConnString As [String] = System.Configuration _
.ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim sda As New SqlDataAdapter()
Dim cmd As New SqlCommand("spx_Pager")
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@PageNo", SqlDbType.Int).Value = PageNo
cmd.Parameters.Add("@ItemsPerPage", SqlDbType.Int).Value = ItemsPerPage
cmd.Parameters.Add("@TotalRows", SqlDbType.Int).Direction = ParameterDirection.Output
cmd.Connection = con
Try
con.Open()
sda.SelectCommand = cmd
sda.Fill(dt)
DataList1.DataSource = dt
DataList1.DataBind()
TotalRows = Convert.ToInt32(cmd.Parameters("@TotalRows").Value)
Catch ex As Exception
Response.Write(ex.Message)
Finally
con.Close()
sda.Dispose()
con.Dispose()
End Try
Return TotalRows
End Function
The BindList function simply calls the stored procedure and binds the returned data to the ASP.Net DataList Control.
Calculating the Total Number of Pages
The function below calculates the Total count of the pages based on the Total Rows and the count of the items to be displayed for a single page.
C#
private int CalculateTotalPages(int intTotalRows)
{
int intPageCount = 1;
double dblPageCount = (double)(Convert.ToDecimal(intTotalRows) / Convert.ToDecimal(this.ItemsPerPage));
intPageCount = Convert.ToInt32(Math.Ceiling(dblPageCount));
return intPageCount;
}
VB.Net
Private Function CalculateTotalPages(ByVal intTotalRows As Integer) As Integer
Dim intPageCount As Integer = 1
Dim dblPageCount As Double = CDbl((Convert.ToDecimal(intTotalRows) / Convert.ToDecimal(Me.ItemsPerPage)))
intPageCount = Convert.ToInt32(Math.Ceiling(dblPageCount))
Return intPageCount
End Function
Preparing the Pager
This function prepares the Pagers for pagination based the Total Rows and the Current Page. Basically it simply enables or disables the Pager LinkButtons
C#
private void Prepare_Pager(int TotalRows)
{
int intPageCount = this.CalculateTotalPages(TotalRows);
if (intPageCount > 1 && this.CurrentPage < intPageCount)
{
this.lnkNext.Enabled = true;
}
if (this.CurrentPage != 1)
{
this.lnkPrev.Enabled = true;
}
else
{
this.lnkPrev.Enabled = false;
}
}
VB.Net
Private Sub Prepare_Pager(ByVal TotalRows As Integer)
Dim intPageCount As Integer = Me.CalculateTotalPages(TotalRows)
If intPageCount > 1 AndAlso Me.CurrentPage < intPageCount Then
Me.lnkNext.Enabled = True
End If
If Me.CurrentPage <> 1 Then
Me.lnkPrev.Enabled = True
Else
Me.lnkPrev.Enabled = False
End If
End Sub
Page Load Event
The page load event plays an important role too Refer below.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (ViewState["CurrentPage"] != null)
{
this.CurrentPage = Convert.ToInt32(ViewState["CurrentPage"]);
}
if (!IsPostBack)
{
int TotalRows = this.BindList(1);
this.Prepare_Pager(TotalRows);
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If ViewState("CurrentPage") IsNot Nothing Then
Me.CurrentPage = Convert.ToInt32(ViewState("CurrentPage"))
End If
If Not IsPostBack Then
Dim TotalRows As Integer = Me.BindList(1)
Me.Prepare_Pager(TotalRows)
End If
End Sub
Below is the ASP.Net DataList Controls with Custom Pagination using Stored Procedures
![DataList Picture Gallery displaying Images ASP.Net]()
That’s it the first part of this tutorial comes to an end. In the next part I’ll explain how to enhance the Image Gallery by adding Preview and Slide Show features. The complete code will be available for download in the next part.
