locked
Data Pager on DataList or Listview RRS feed

  • Question

  • User1343215443 posted

    I have photos gallery, with datalist. i wan to make pagination on it. Please help how to make it, and if better make with listview how to make it too. thanks

    <table align="center">
                                <tr>
                                    <td>
                                        <asp:DataList ID="DataList4" runat="server" RepeatColumns="5" HorizontalAlign="Center" RepeatDirection="Horizontal">
                                            <ItemTemplate>
                                                <a id="Test" href='<%# Eval("example") %>' title='' rel="lightbox[Brussels]" runat="server">
                                                    <asp:Image ID="Image2" ImageUrl='<%# Eval("exmaple1") %>' runat="server" Width="155" Height="90" />
                                                </a>
                                            </ItemTemplate>
                                        </asp:DataList>
                                    </td>
                                </tr>
                            </table>



    Thursday, June 8, 2017 1:05 AM

Answers

  • User-271186128 posted

    Hi rifqiwqiw,

    According to your description, I have created two demos to make pagination on the image gallery. You could refer to them:

    1. Using DataList:

    1) using the following sql statement:

    --create tblFiles to store file name and file path.
    --Set your data ike this:
    --FileName    FilePath
    --1.jpg    Picture/1.jpg
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblFiles](
           [ID] [int] IDENTITY(1,1) NOT NULL,
           [FileName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
       [FilePath] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    CONSTRAINT [PK_tblFiles] PRIMARY KEY CLUSTERED 
    (
       [ID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    
    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
    

    2)  Right click your project to create a folder named ‘Picture’, and put your picture in this folder.

    Code in web page:

    <form id="form1" runat="server">
            <div >
                <asp:DataList ID="DataList1" runat="server" RepeatColumns = "2"  HorizontalAlign="Center" RepeatDirection="Horizontal">
              <ItemTemplate>
                <br />
                <table class="dlTable">
                <tr>
                    <td>
                        <asp:Image ID="Image1" runat="server" ImageUrl = '<%# Eval("FilePath")%>'
                        Width = "155px" Height = "90px"/>
                    </td>
                </tr>
                </table>
                <br />
            </ItemTemplate>
    </asp:DataList><br />
    <asp:Panel ID="pnlPager" runat="server"  HorizontalAlign="Center" >
        <asp:LinkButton ID="lnkPrev" runat="server" CommandName = "Previous"
            Text = "<< Previous" OnClick = "Pager_Click"></asp:LinkButton>
        &nbsp;
        <asp:LinkButton ID="lnkNext" runat="server" CommandName = "Next"
            Text = "Next >>"  OnClick = "Pager_Click"></asp:LinkButton>
    </asp:Panel>
            </div>
    </form>
    

    Code in page (.aspx.cs)

         private int CurrentPage = 1;
            private int ItemsPerPage = 2;
            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);
                }
            }
            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;
                }
            }
            private int BindList(int PageNo)
            {
                int TotalRows = 0;
                DataTable dt = new DataTable();
                String strConnString = System.Configuration
                    .ConfigurationManager.ConnectionStrings["connStr"].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;
            }
            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;
            }
            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;
                }
            }
    
    

    The output as below:

    2. Using ListView:

    Code in page:

    <style type="text/css"> 
            table
            {
                border: 1px solid #ccc;
            }
            table th
            {
                background-color: #F7F7F7;
                color: #333;
                font-weight: bold;
            } 
        </style>
    </head>
    <body>
    <form id="form1" runat="server">
    <div style="margin-left:300px">
        <asp:ListView ID="lvCustomers" runat="server" GroupPlaceholderID="groupPlaceHolder1"
            ItemPlaceholderID="itemPlaceHolder1" OnPagePropertiesChanging="OnPagePropertiesChanging">
            <LayoutTemplate>
                <table >
                    <tr>
                        <th>
                            My Picture
                        </th>
                    </tr>
                    <asp:PlaceHolder runat="server" ID="groupPlaceHolder1"></asp:PlaceHolder>
                    <tr>
                        <td colspan="3">
                            <asp:DataPager ID="DataPager1" runat="server" PagedControlID="lvCustomers" PageSize="2">
                                <Fields>
                                    <asp:NextPreviousPagerField ButtonType="Link" ShowFirstPageButton="false" ShowPreviousPageButton="true"
                                        ShowNextPageButton="false" />
                                    <asp:NumericPagerField ButtonType="Link" />
                                    <asp:NextPreviousPagerField ButtonType="Link" ShowNextPageButton="true" ShowLastPageButton="false"
                                        ShowPreviousPageButton="false" />
                                </Fields>
                            </asp:DataPager>
                        </td>
                    </tr>
                </table>
            </LayoutTemplate>
            <GroupTemplate>
                <tr>
                    <asp:PlaceHolder runat="server" ID="itemPlaceHolder1"></asp:PlaceHolder>
                </tr>
            </GroupTemplate>
            <ItemTemplate>
                <td>
               <asp:Image ID="Image1" runat="server" ImageUrl = '<%# Eval("FilePath")%>'
                        Width = "155px" Height = "90px"/>
                </td>
            </ItemTemplate>
    </asp:ListView>
    </div>
        </form>
    </body>
    

    Code in page(.aspx.cs):

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!this.IsPostBack)
                {
                    this.BindListView();
                }
            }
    
            private void BindListView()
            {
                string constr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandText = "SELECT ID, FileName, FilePath FROM tblFiles";
                        cmd.Connection = con;
                        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            lvCustomers.DataSource = dt;
                            lvCustomers.DataBind();
                        }
                    }
                }
            }
    
            protected void OnPagePropertiesChanging(object sender, PagePropertiesChangingEventArgs e)
            {
                (lvCustomers.FindControl("DataPager1") as DataPager).SetPageProperties(e.StartRowIndex, e.MaximumRows, false);
                this.BindListView();
            }
    
    

    The screenshot as below:

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 8, 2017 9:36 AM

All replies

  • User-271186128 posted

    Hi rifqiwqiw,

    According to your description, I have created two demos to make pagination on the image gallery. You could refer to them:

    1. Using DataList:

    1) using the following sql statement:

    --create tblFiles to store file name and file path.
    --Set your data ike this:
    --FileName    FilePath
    --1.jpg    Picture/1.jpg
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblFiles](
           [ID] [int] IDENTITY(1,1) NOT NULL,
           [FileName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
       [FilePath] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    CONSTRAINT [PK_tblFiles] PRIMARY KEY CLUSTERED 
    (
       [ID] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    SET ANSI_PADDING OFF
    
    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
    

    2)  Right click your project to create a folder named ‘Picture’, and put your picture in this folder.

    Code in web page:

    <form id="form1" runat="server">
            <div >
                <asp:DataList ID="DataList1" runat="server" RepeatColumns = "2"  HorizontalAlign="Center" RepeatDirection="Horizontal">
              <ItemTemplate>
                <br />
                <table class="dlTable">
                <tr>
                    <td>
                        <asp:Image ID="Image1" runat="server" ImageUrl = '<%# Eval("FilePath")%>'
                        Width = "155px" Height = "90px"/>
                    </td>
                </tr>
                </table>
                <br />
            </ItemTemplate>
    </asp:DataList><br />
    <asp:Panel ID="pnlPager" runat="server"  HorizontalAlign="Center" >
        <asp:LinkButton ID="lnkPrev" runat="server" CommandName = "Previous"
            Text = "<< Previous" OnClick = "Pager_Click"></asp:LinkButton>
        &nbsp;
        <asp:LinkButton ID="lnkNext" runat="server" CommandName = "Next"
            Text = "Next >>"  OnClick = "Pager_Click"></asp:LinkButton>
    </asp:Panel>
            </div>
    </form>
    

    Code in page (.aspx.cs)

         private int CurrentPage = 1;
            private int ItemsPerPage = 2;
            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);
                }
            }
            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;
                }
            }
            private int BindList(int PageNo)
            {
                int TotalRows = 0;
                DataTable dt = new DataTable();
                String strConnString = System.Configuration
                    .ConfigurationManager.ConnectionStrings["connStr"].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;
            }
            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;
            }
            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;
                }
            }
    
    

    The output as below:

    2. Using ListView:

    Code in page:

    <style type="text/css"> 
            table
            {
                border: 1px solid #ccc;
            }
            table th
            {
                background-color: #F7F7F7;
                color: #333;
                font-weight: bold;
            } 
        </style>
    </head>
    <body>
    <form id="form1" runat="server">
    <div style="margin-left:300px">
        <asp:ListView ID="lvCustomers" runat="server" GroupPlaceholderID="groupPlaceHolder1"
            ItemPlaceholderID="itemPlaceHolder1" OnPagePropertiesChanging="OnPagePropertiesChanging">
            <LayoutTemplate>
                <table >
                    <tr>
                        <th>
                            My Picture
                        </th>
                    </tr>
                    <asp:PlaceHolder runat="server" ID="groupPlaceHolder1"></asp:PlaceHolder>
                    <tr>
                        <td colspan="3">
                            <asp:DataPager ID="DataPager1" runat="server" PagedControlID="lvCustomers" PageSize="2">
                                <Fields>
                                    <asp:NextPreviousPagerField ButtonType="Link" ShowFirstPageButton="false" ShowPreviousPageButton="true"
                                        ShowNextPageButton="false" />
                                    <asp:NumericPagerField ButtonType="Link" />
                                    <asp:NextPreviousPagerField ButtonType="Link" ShowNextPageButton="true" ShowLastPageButton="false"
                                        ShowPreviousPageButton="false" />
                                </Fields>
                            </asp:DataPager>
                        </td>
                    </tr>
                </table>
            </LayoutTemplate>
            <GroupTemplate>
                <tr>
                    <asp:PlaceHolder runat="server" ID="itemPlaceHolder1"></asp:PlaceHolder>
                </tr>
            </GroupTemplate>
            <ItemTemplate>
                <td>
               <asp:Image ID="Image1" runat="server" ImageUrl = '<%# Eval("FilePath")%>'
                        Width = "155px" Height = "90px"/>
                </td>
            </ItemTemplate>
    </asp:ListView>
    </div>
        </form>
    </body>
    

    Code in page(.aspx.cs):

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!this.IsPostBack)
                {
                    this.BindListView();
                }
            }
    
            private void BindListView()
            {
                string constr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.CommandText = "SELECT ID, FileName, FilePath FROM tblFiles";
                        cmd.Connection = con;
                        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                        {
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            lvCustomers.DataSource = dt;
                            lvCustomers.DataBind();
                        }
                    }
                }
            }
    
            protected void OnPagePropertiesChanging(object sender, PagePropertiesChangingEventArgs e)
            {
                (lvCustomers.FindControl("DataPager1") as DataPager).SetPageProperties(e.StartRowIndex, e.MaximumRows, false);
                this.BindListView();
            }
    
    

    The screenshot as below:

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 8, 2017 9:36 AM
  • User1343215443 posted

    thankyou very much Zhi Lv

    Friday, June 9, 2017 3:26 AM