asp.net代码练习 work025 SQL SERVER自编代码尝试分页查询的SQL语句

最新推荐文章于 2023-11-24 13:56:08 发布

虾米大王

最新推荐文章于 2023-11-24 13:56:08 发布

阅读量217

点赞数

分类专栏： asp.net c# 文章标签： c#

本文链接：https://blog.csdn.net/modern358/article/details/116431448

版权

c# 同时被 2 个专栏收录

284 篇文章 0 订阅

订阅专栏

asp.net

258 篇文章 2 订阅

订阅专栏

webform1.aspx.cs

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="work025.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table border="1" style="width:100%;border-collapse:collapse;text-align:center;">
            <tr>
                <th>user_id</th>
                <th>user_name</th>
                <th>real_name</th>
                <th>age</th>
                <th>sex</th>
                <th>mobile</th>
                <th>phone</th>
                <th>email</th>
            </tr>
            <asp:Literal ID="Literal1" runat="server"></asp:Literal>
        </table>
        <asp:Button ID="Button1" runat="server" Text="上一页" Visible="false" OnClick="Button1_Click" />
        <asp:Button ID="Button2" runat="server" Text="下一页" Visible="false" OnClick="Button2_Click" />
    </div>
    </form>
</body>
</html>

webform1.aspx.cs

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

namespace work025
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //首次页显示，使用session记录当前页PageIndex，每页记录数PageSize，计算后的总页数PageCount
            if (!IsPostBack)
            {
                if (Session["PageIndex"] == null)
                {
                    //保存，默认起始页，第一页
                    Session["PageIndex"] = 1;
                    //保存，默认每页记录，2个，这里可以修改
                    Session["PageSize"] = 2;
                }
                int pageSize = int.Parse(Session["PageSize"].ToString());
                int pageIndex = int.Parse(Session["PageIndex"].ToString());
                //计算总页数
                int pageCount = InitData(pageSize);
                //保存，总页数
                Session["PageCount"] = pageCount;
                //初始化翻页
                InitControl(pageCount, pageIndex);
                //显示数据
                ShowData(pageIndex, pageSize);
            }

        }

        //初始化总页数
        private int InitData(int pSize)
        {
            System.Data.SqlClient.SqlConnectionStringBuilder bu = new System.Data.SqlClient.SqlConnectionStringBuilder();
            bu.DataSource = "(local)";
            bu.InitialCatalog = "test";
            bu.UserID = "sa";
            bu.Password = "123456";

            string conString = bu.ConnectionString;
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conString);
            con.Open();

            //统计表中总记录数
            string sql = "select count(*) as RowsCount from userinfo";
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, con);
            //取得1行1列的值
            int totalCount = int.Parse(cmd.ExecuteScalar().ToString());

            //总页数 = 总记录数 除以 每页记录数， 取余是为了判断是否有剩余记录要显示
            int pageCount = ((totalCount % pSize) == 0) ? (totalCount / pSize) : (totalCount / pSize) + 1;

            con.Close();

            return pageCount;
        }

        //初始化翻页
        private void InitControl(int pCount,int pIndex=1)
        {
            //总页数大于0是，翻页才显示
            if (pCount >= 1)
            {
                Button1.Visible = true;
                Button2.Visible = true;

            }
            else
            {
                Button1.Visible = false;
                Button2.Visible = false;
            }

            //根据当前页，设定翻页按钮
            if (pIndex <= 1)
            {
                Button1.Enabled = false;
                Button2.Enabled = true;
            }
            else if (pIndex >= pCount)
            {
                Button1.Enabled = true;
                Button2.Enabled = false;
            }
            else
            {
                Button1.Enabled = true;
                Button2.Enabled = true;
            }
        }

        //显示数据
        public void ShowData(int pIndex,int pSize=2)
        {
            System.Data.SqlClient.SqlConnectionStringBuilder bu = new System.Data.SqlClient.SqlConnectionStringBuilder();
            bu.DataSource = "(local)";
            bu.InitialCatalog = "test";
            bu.UserID = "sa";
            bu.Password = "123456";

            string conString = bu.ConnectionString;
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conString);
            con.Open();

            //这个sql语句是本页代码的核心，意思是选择前多少条记录显示，并且根据每页记录数排除掉之前已经显示过，从而达到分页的目的
            string sql = string.Format("select top {0} *  from userinfo where user_id not in (select top {1} user_id from userinfo order by user_id asc) order by user_id asc",pSize,(pIndex -1) * pSize);
            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(sql, con);
            System.Data.DataSet ds = new System.Data.DataSet();
            adapter.Fill(ds);

            //显示之前，先清空上次的
            Literal1.Text = "";

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                Literal1.Text += "<tr>";
                Literal1.Text += string.Format("<td>{0}</td>", ds.Tables[0].Rows[i]["user_id"].ToString());
                Literal1.Text += string.Format("<td>{0}</td>", ds.Tables[0].Rows[i]["user_name"].ToString());
                Literal1.Text += string.Format("<td>{0}</td>", ds.Tables[0].Rows[i]["real_name"].ToString());
                Literal1.Text += string.Format("<td>{0}</td>", ds.Tables[0].Rows[i]["age"].ToString());
                Literal1.Text += string.Format("<td>{0}</td>", ds.Tables[0].Rows[i]["sex"].ToString());
                Literal1.Text += string.Format("<td>{0}</td>", ds.Tables[0].Rows[i]["mobile"].ToString());
                Literal1.Text += string.Format("<td>{0}</td>", ds.Tables[0].Rows[i]["phone"].ToString());
                Literal1.Text += string.Format("<td>{0}</td>", ds.Tables[0].Rows[i]["email"].ToString());
                Literal1.Text += "</tr>";
            }
        }

        //上一页
        protected void Button1_Click(object sender, EventArgs e)
        {
            //先判断是否有当前页
            if (Session["PageIndex"] != null)
            {
                int pageIndex = int.Parse(Session["PageIndex"].ToString());
                int pageSize = int.Parse(Session["PageSize"].ToString());
                int pageCount = int.Parse(Session["PageCount"].ToString());
                //当前页向前时，减1，并保存
                pageIndex = pageIndex - 1;
                Session["PageIndex"] = pageIndex;
                ShowData(pageIndex, pageSize);
                InitControl(pageCount, pageIndex);
            }
            
        }

        //下一页
        protected void Button2_Click(object sender, EventArgs e)
        {
            if (Session["PageIndex"] != null)
            {
                int pageIndex = int.Parse(Session["PageIndex"].ToString());
                int pageSize = int.Parse(Session["PageSize"].ToString());
                int pageCount = int.Parse(Session["PageCount"].ToString());
                //当前页向后时，加1，并保存
                pageIndex = pageIndex + 1;
                Session["PageIndex"] = pageIndex;
                ShowData(pageIndex, pageSize);
                InitControl(pageCount, pageIndex);
            }
           
        }
    }
}

sql

create database test;

create table UserInfo(
	user_id int identity(1,1) not null,
	user_name varchar(20) not null unique,
	real_name nvarchar(8) not null,
	age tinyint not null,
	sex bit not null,
	mobile varchar(14),
	phone varchar(14),
	email varchar(50) not null,
	primary key(user_id) 
);

insert into UserInfo values('zhangfei','张飞',36,1,'13011110001','8845996','zf@qq.com');
insert into UserInfo values('guanyu','关羽',38,1,'13011110002','8845995','gy@qq.com');
insert into UserInfo values('liubei','刘备',42,1,'13011110003','8845994','lb@qq.com');
insert into UserInfo values('zhaoyun','赵云',32,1,'13011110004','8845993','zy@qq.com');
insert into UserInfo values('huangzhong','黄忠',50,1,'13011110005','8845992','hz@qq.com');
insert into UserInfo values('caocao','曹操',48,1,'13011110006','8845991','cc@qq.com');
insert into UserInfo values('sunquan','孙权',33,1,'13011110007','8845990','sq@qq.com');
insert into UserInfo values('diaochan','貂蝉',20,0,'13011110008','8845880','dc@qq.com');
insert into UserInfo values('daqiao','大乔',21,0,'13011110009','8845881','dq@qq.com');
insert into UserInfo values('xiaoqiao','小乔',20,0,'13011110010','8845882','xq@qq.com');