datalist 实现分页，C#实现，用SQL Server

最新推荐文章于 2021-01-10 12:08:46 发布

alexcao247

最新推荐文章于 2021-01-10 12:08:46 发布

阅读量610

点赞数

文章标签： sql server c# server dataset asp string

本文链接：https://blog.csdn.net/alexcao247/article/details/1660365

版权

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

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

<html>
<head>
<title></title>
</head>
<body>
<form id="Form1" runat="server">
<asp:Label id="lblRecordCount" ForeColor="red" runat="server" />条记录 
<asp:Label id="lblCurrentPage" ForeColor="red" runat="server" />/<asp:Label id="lblPageCount" ForeColor="red" runat="server" />页 
<asp:DataList id="score" runat="server"
HeaderStyle-BackColor="#aaaadd"
AlternatingItemStyle-BackColor="Gainsboro"
EditItemStyle-BackColor="yellow" RepeatColumns="2" DataKeyField="ID" RepeatDirection="Horizontal"
>
    <EditItemStyle BackColor="Yellow" />
    <AlternatingItemStyle BackColor="Gainsboro" />
    <HeaderStyle BackColor="#AAAADD" />
    <ItemTemplate>
        <asp:Label ID="Label1" runat="server" Text='<%# Eval("ID") %>'></asp:Label>
    </ItemTemplate>
</asp:DataList>
<asp:LinkButton id="lbnPrevPage" Text="上一页" CommandName="prev" OnCommand="Page_OnClick" runat="server" />
<asp:LinkButton id="lbnNextPage" Text="下一页" CommandName="next" OnCommand="Page_OnClick" runat="server" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:szmstc_ioa_dbConnectionString %>"
    SelectCommand="SELECT * FROM [IOA_VisitInfo]"></asp:SqlDataSource>
</form>
</body>
</html>

public partial class Default2 : System.Web.UI.Page
{
SqlConnection MyConn;
int PageSize, RecordCount, PageCount, CurrentPage;

    protected void Page_Load(object sender, EventArgs e)
    {
        PageSize = 4;// 根据实际定义常量
        string MyConnString = "Password=123456;Persist Security Info=True;User ID=truesa;Initial Catalog=szmstc_ioa_db;Data Source=TEC_TESTSRV";
        MyConn = new SqlConnection(MyConnString);
        MyConn.Open();
        if (!Page.IsPostBack)
        {
            ListBind();
            CurrentPage = 0;
            ViewState["PageIndex"] = 0;

            //计算总共有多少记录
            RecordCount = CalculateRecord();
            lblRecordCount.Text = RecordCount.ToString();

            //计算总共有多少页
            if (RecordCount > 0)
                PageCount = (RecordCount - 1) / PageSize + 1;
            else
                PageCount = 1;
            lblPageCount.Text = PageCount.ToString();
            ViewState["PageCount"] = PageCount;

        }
    }

    //计算总共有多少条记录
    public int CalculateRecord()
    {
        int intCount = 0;
        string strCount = "SELECT count([ID]) FROM [szmstc_ioa_db].[dbo].[IOA_VisitInfo]";
        SqlCommand MyComm = new SqlCommand(strCount, MyConn);

        SqlDataReader dr = MyComm.ExecuteReader();
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                intCount = int.Parse(dr[0].ToString());
            }
        }

        dr.Close();
        return intCount;
    }

    public DataTable CreateSource()
    {
        int StartIndex;

        //设定导入的起终地址
        StartIndex = CurrentPage * PageSize;
        string sql = "select top " + PageSize.ToString() + "[ID] from [dbo].[IOA_VisitInfo] where [ID] not in ";
        sql += "(select top " + StartIndex.ToString() + " [ID] from [dbo].[IOA_VisitInfo] order by [dateVisitTime]) ";
        sql += " order by [dateVisitTime]";
        //string strSel = "SELECT [ID] FROM [szmstc_ioa_db].[dbo].[IOA_VisitInfo] order by [dateVisitTime]";
        string strSel = sql;

DataSet ds = new DataSet();

        SqlDataAdapter MyAdapter = new SqlDataAdapter(strSel, MyConn);
        MyAdapter.Fill(ds, "score");
        return ds.Tables["score"];
    }
    public void ListBind()
    {
        score.DataSource = CreateSource();
        score.DataBind();

        lbnNextPage.Enabled = true;
        lbnPrevPage.Enabled = true;
        if (CurrentPage == (PageCount - 1)) lbnNextPage.Enabled = false;
        if (CurrentPage == 0) lbnPrevPage.Enabled = false;
        lblCurrentPage.Text = (CurrentPage + 1).ToString();

}

    public void Page_OnClick(Object sender, CommandEventArgs e)
    {
        CurrentPage = (int)ViewState["PageIndex"];
        PageCount = (int)ViewState["PageCount"];

        string cmd = e.CommandName;
        //判断cmd，以判定翻页方向
        switch (cmd)
        {
            case "next":
                if (CurrentPage < (PageCount - 1)) CurrentPage++;
                break;
            case "prev":
                if (CurrentPage > 0) CurrentPage--;
                break;
        }

ViewState["PageIndex"] = CurrentPage;

ListBind();
}
}