Kỹ thuật phân trang dữ liệu SQL server trong trang Asp.net

Phân trang dữ liệu là đề tài được nói đến khá nhiều, Nếu dữ liệu của bạn ít bạn có thể dùng Tools có sẵn của Visual Studio, Nhưng có điều với các công cụ có sẵn này khi bạn dùng thì lệnh truy vấn dữ liệu bạn luôn phải truy vấn toàn bộ và Bind vào một control, như vậy nếu dữ liệu của bạn nhiều thì đây không phải là giải pháp tốt.

Mỗi kỹ thuật phân trang đều có những ưu điểm, nhược điểm. Tôi thì sử dụng phân trang bằng kỹ thuật SQL server (như hmclip.net đang dùng) ở phần trình bày hiển thị trên giao diện web vì ở đây sẽ có nhiều người xem, còn dùng các tools có sẵn trong các phần quản trị. Bài viết này Tôi giới thiệu thuật toán trang bằng SQL và sẽ làm ví dụ demo trong trang Aspx

Bạn truy cập hmclip bạn xẽ thấy phân trang của tôi dạng: Trang đầu ... 1 2 3 [4] 5 6 7 ... Trang cuối tức là ta có trang 4 là trang đang xem, các liên kết đến trang đầu, trang cuối và hiển thị 7 trang cho mỗi phân đoạn trang. Ta sẽ viết hàm SQL để truyền vào các tham số:

@Total int - Tổng số bản ghi theo điều kiện truy vấn, 
@currPage int - Trang bạn đang xem, 
@PageSize int - Số trang hiển thị cho mỗi phân đoạn trang, 
@rowperpage int - Số bản ghi hiển thị trên trang

CREATE Function [dbo].[fPhanTrangSQL]( @Total int, @currPage int , @PageSize int, @rowperpage int ) Returns nvarchar(1000) AS Begin If @currPage=0 Set @currPage=1 DECLARE @PageNumber int SET @PageNumber=1 DECLARE @i int SET @i=1 DECLARE @TotalPage int IF @Total%@rowperpage>0 SET @TotalPage=(@Total/@rowperpage)+1 ELSE SET @TotalPage=@Total/@rowperpage DECLARE @Start int SET @Start=0 DECLARE @SQL nvarchar(4000) SET @SQL=' If @TotalPage >1 BEGIN SET @SQL=@SQL+ N'<div id='paging'> Trang: ' IF @currPage<=@TotalPage BEGIN IF @currPage=1 BEGIN SET @PageNumber=@PageSize IF @PageNumber>@TotalPage SET @PageNumber=@TotalPage SET @Start=1 END ELSE BEGIN SET @SQL=@SQL+ N' <a href='?page=1'> Trang đầu </a> &nbsp;' SET @SQL=@SQL+ ' <a href=?page='+ Cast((@currPage-1) AS nvarchar(4))+N'>&laquo;</a> ' IF(@TotalPage-@currPage)<@PageSize/2 BEGIN SET @Start=(@TotalPage-@PageSize)+1 IF @Start<=0 SET @Start=1 SET @PageNumber = @TotalPage END ELSE BEGIN IF (@currPage-(@PageSize/2))=0 BEGIN SET @Start=1 SET @PageNumber=@currPage+(@PageSize/2)+1 IF @TotalPage<@PageNumber SET @PageNumber=@TotalPage END ELSE BEGIN SET @Start=@currPage-(@PageSize/2) IF @Start<=0 SET @Start=1 SET @PageNumber=@currPage+(@PageSize/2) IF @TotalPage<@PageNumber SET @PageNumber=@TotalPage ELSE IF @PageNumber <@PageSize SET @PageNumber=@PageSize END END END SET @i=@Start WHILE @i<=@PageNumber BEGIN IF @i=@currPage SET @SQL=@SQL+'[<b>'+Cast(Cast(@i AS int) AS nvarchar(4))+'</b>]' ELSE SET @SQL=@SQL+'<a href='?page='+Cast(@i AS nvarchar(4))+'>' +Cast(@i AS nvarchar(4))+'</a> ' SET @i=@i+1 END IF @currPage<@TotalPage BEGIN SET @SQL=@SQL+ N'<a href='?page='+Cast((@currPage+1) AS nvarchar(4))+N'>&raquo;</a> ' SET @SQL=@SQL+ N' <a href='?page='+cast(@TotalPage AS nvarchar(6))+ N'> Trang cuối </a>' End Set @SQL=@SQL+'</div>' End END Return @SQL END

Kết quả trả về của hàm này là chuỗi Nvarchar và khi ta thực thi hàm này sẽ cho ta mã html hiển thị phân trang. Tiếp theo ta sẽ viết Store procedure thực hiện phân trang dữ liệu, sử dụng hàm trên. Giả sử tôi có bảng TB_News có các trường cơ bản là idNews, CateID, Title, Descript, Content Store phân trang với các bản tin lấy theo CateID như sau

CREATE PROCEDURE TB_News_PagingByCateID @currPage int, @recodperpage int, @Pagesize int, @CateID int AS BEGIN DECLARE @Tolal int Begin WITH s AS ( SELECT ROW_NUMBER() OVER(ORDER BY tn.idNews desc) As RowNum, tn.idNews, tn.CateID, tn.Title, tn.Descript FROM TB_News tn Where tn.CateID=@CateID ) Select * From s Where RowNum Between (@currPage - 1)*@recodperpage+1 AND @currPage*@recodperpage END -- Tính tổng số bản ghi SELECT @Tolal=Count(*) FROM TB_News Where CateID=@CateID --Gọi hàm phân trang Select dbo.fPhanTrangSQL(@Tolal, @currPage, @Pagesize, @recodperpage) As Paging END

Demo trong trang ASP.net sử dụng kỹ thuật này. Tôi sẽ minh họa với DataList và hiển thị dữ liệu 2 cột - Với các control khác như Gridview, ListView, Repeater cách làm cũng tương tự:

Video clip hướng dẫn thực hiện chi tiết:

Trong trang aspx bạn cần tạo một ListView như sau:

<asp:DataList ID="lvListNew" runat="server" RepeatColumns="2" CellPadding="10" CellSpacing="10" RepeatDirection="Horizontal"> <ItemStyle Width="50%" HorizontalAlign="Left" VerticalAlign="Top" /> <ItemTemplate> <a href='http://hmclip.net/?newid=<%# Eval("idNews") %>' title='<%# Eval("Title") %>'><%# Eval("Title") %></a><br /> <%# Eval("Description") %> <a href='http://hmclip.net/?newid=<%# Eval("idNews") %>' title='<%# Eval("Title") %>' class="Xemtiep">Xem tiếp ...</a> </ItemTemplate> <FooterTemplate> <div class="paging"><%=strPaging %></div> </FooterTemplate> </asp:DataList>

Trong code C# ta cần viết hàm kết nối dữ liệu và thực thị Store trả vể DataSet như sau:

private DataSet GetPhanTrang_DataSet( string strTenStore, int currentPage, int recordPerpage, int pageSize, int CateID) { string strConnect = @"Server =.SQL2005;Initial Catalog=YourDatabase;User ID=sa;Password=***"; DataSet ds = new DataSet(); SqlConnection sqlCn = new SqlConnection(strConnect); try { //Mo ket noi sqlCn.Open(); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlCn; sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.CommandText = strTenStore; sqlCmd.Parameters.Add(new SqlParameter("@currPage", currentPage)); sqlCmd.Parameters.Add(new SqlParameter("@recodperpage", recordPerpage)); sqlCmd.Parameters.Add(new SqlParameter("@Pagesize", pageSize)); sqlCmd.Parameters.Add(new SqlParameter("@CateID", CateID)); SqlDataAdapter sqlDa = new SqlDataAdapter(); sqlDa.SelectCommand = sqlCmd; sqlDa.Fill(ds); } catch { } finally { if (sqlCn.State == ConnectionState.Open) sqlCn.Close(); sqlCn.Dispose(); } return ds; }

Code hiển thị dữ liệu:

public string strPaging = ""; private int page = 0; protected void Page_Load(object sender, EventArgs e) { page = (Request.QueryString["page"] + "" != "") ? int.Parse("0" + Request.QueryString["page"]) : 1; DataSet ds = GetPhanTrang_DataSet("TB_News_PagingByCateID", page, 10, 7, 94); if (ds.Tables.Count > 0) { if (ds.Tables[0].Rows.Count > 0) { lvListNew.DataSource = ds.Tables[0]; lvListNew.DataBind(); } if (ds.Tables[1].Rows.Count > 0) { strPaging = ds.Tables[1].Rows[0][0] + ""; } } }

Nguồn:thietkewebsmart.com