ObjectDataSource+ListView高效分页(SQL2005适用)_select top(@maximumrows)*from book where id not in-CSDN博客

本文链接：https://blog.csdn.net/yx017893/article/details/6758503

原理:每页显示10条数据,每次只取10条出来,要几条去取几条.DataSet→objectdatasource→ListView.

1，首先创建强类型DataSet,定义两个方法:GetDataCount(),GetPageData().

GetDataCount() //取得总数据条数

SELECT COUNT(*) FROM dbo.book

GetPageData() //取得特定条数数据,其中row_number() over()函数表示给查询结果建立内置行号,参数startRowIndex表示起始行号,maximumRows表示每页显示条数

SELECT * FROM 

(SELECT bookId, bookName, class, author, faceUrl, type, hits, hot, bytes, intro, stop, upTime, row_number() over(Order By bookId) rownum FROM dbo.book)t 

WHERE t.rownum>@startRowIndex and t.rownum<=@startRowIndex+@maximumRows

由于DataSet并不能识别row_number() over()函数,不能自动添加参数,所以要手动来添加:

2，在页面添加一个ObjectDataSourc和一个ListView控件,分别指定数据源为强类型DataSet,和方法,配置ListView,然后设置ObjectDataSourc的EnablePaging="True",SelectMethod="GetPageData" ,SelectCountMethod="GetDataCount"即可.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="ListView高效分页_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
          EnablePaging="True"
            OldValuesParameterFormatString="original_{0}" SelectMethod="GetPageData" 
             SelectCountMethod="GetDataCount"
            TypeName="BookTableAdapters.bookTableAdapter" 
            onselected="ObjectDataSource1_Selected">
            
        </asp:ObjectDataSource>
    
    </div>
    <asp:ListView ID="ListView1" runat="server" DataKeyNames="bookId" 
        DataSourceID="ObjectDataSource1" >
        <ItemTemplate>
            <tr style="">
                <td>
                    <asp:Label ID="bookIdLabel" runat="server" Text='<%# Eval("bookId") %>' />
                </td>
                <td>
                    <asp:Label ID="bookNameLabel" runat="server" Text='<%# Eval("bookName") %>' />
                </td>
                <td>
                    <asp:Label ID="classLabel" runat="server" Text='<%# Eval("class") %>' />
                </td>
                <td>
                    <asp:Label ID="authorLabel" runat="server" Text='<%# Eval("author") %>' />
                </td>
                <td>
                    <asp:Label ID="faceUrlLabel" runat="server" Text='<%# Eval("faceUrl") %>' />
                </td>
                <td>
                    <asp:Label ID="typeLabel" runat="server" Text='<%# Eval("type") %>' />
                </td>
                <td>
                    <asp:Label ID="hitsLabel" runat="server" Text='<%# Eval("hits") %>' />
                </td>
                <td>
                    <asp:Label ID="hotLabel" runat="server" Text='<%# Eval("hot") %>' />
                </td>
                <td>
                    <asp:Label ID="bytesLabel" runat="server" Text='<%# Eval("bytes") %>' />
                </td>
                <td>
                    <asp:Label ID="introLabel" runat="server" Text='<%# Eval("intro") %>' />
                </td>
                <td>
                    <asp:Label ID="stopLabel" runat="server" Text='<%# Eval("stop") %>' />
                </td>
                <td>
                    <asp:Label ID="upTimeLabel" runat="server" Text='<%# Eval("upTime") %>' />
                </td>
            </tr>
        </ItemTemplate>
        
        <EmptyDataTemplate>
            <table runat="server" style="">
                <tr>
                    <td>
                        未返回数据。</td>
                </tr>
            </table>
        </EmptyDataTemplate>
        
        <LayoutTemplate>
            <table runat="server">
                <tr runat="server">
                    <td runat="server">
                        <table ID="itemPlaceholderContainer" runat="server" border="0" style="">
                            <tr runat="server" style="">
                                <th runat="server">
                                    bookId</th>
                                <th runat="server">
                                    bookName</th>
                                <th runat="server">
                                    class</th>
                                <th runat="server">
                                    author</th>
                                <th runat="server">
                                    faceUrl</th>
                                <th runat="server">
                                    type</th>
                                <th runat="server">
                                    hits</th>
                                <th runat="server">
                                    hot</th>
                                <th runat="server">
                                    bytes</th>
                                <th runat="server">
                                    intro</th>
                                <th runat="server">
                                    stop</th>
                                <th runat="server">
                                    upTime</th>
                            </tr>
                            <tr ID="itemPlaceholder" runat="server">
                            </tr>
                        </table>
                    </td>
                </tr>
                <tr runat="server">
                    <td runat="server" style="">
                    总条数:<asp:Label ID="label1" runat="server" Text=""></asp:Label>
                    ,共<asp:Label ID="label2" runat="server" Text=""></asp:Label>页,每页显示10条
                        <asp:DataPager ID="DataPager1" runat="server">
                            <Fields>
                                
                                <asp:NextPreviousPagerField ButtonType="Button" ShowFirstPageButton="True" 
                                    ShowNextPageButton="False" ShowPreviousPageButton="True" />
                                <asp:NumericPagerField />
                                <asp:NextPreviousPagerField ButtonType="Button" ShowLastPageButton="True" 
                                    ShowNextPageButton="True" ShowPreviousPageButton="False" />
                            </Fields>
                        </asp:DataPager>
                    </td>
                </tr>
            </table>
        </LayoutTemplate>
        
        
    </asp:ListView>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class ListView高效分页_Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void ObjectDataSource1_Selected(object sender, ObjectDataSourceStatusEventArgs e)
    {
        if (e.ReturnValue.GetType() == typeof(Int32))
        {
            int count = (int)e.ReturnValue;
            Label label1 = (Label)ListView1.FindControl("label1");
            label1.Text = e.ReturnValue.ToString();
            Label label2 = (Label)ListView1.FindControl("label2");
            if (count % 10 == 0)
            {
                label2.Text = Convert.ToString(count / 10);
            }
            else
            {
                label2.Text = Convert.ToString(count / 10 + 1);
            }
        }
    }
}