Friday , 20 October 2017
HOT

Kỹ thuật phân trang dữ liệu trong SQL server sử dụng cho 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 ở 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 chiencong.com 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: h m c l i p . n e t

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

More in MSSQL Server (1 of 26 articles)


Trong ứng dụng khi cần tương tác với database, có lẽ một cách làm rất phổ biến là tạo lập một chuỗi chứa lệnh SQL, ghép các giá trị  nhập vào của người dùng thành một lệnh SQL hoàn chỉnh, rồi thực...