C#

ASP.Net存储过程实现分页+三层架构

来自 |已发表

版权声明：转载原创文章请以超链接形式请注明原文章出处，尊重作者，尊重原创！


恰饭广告




实现效果：

分页效果图

创建存储过程 

CREATE PROCEDURE sp_page
@strTable varchar(50), --表名
@strColumn varchar(50), --按该列来进行分页
@intColType int, --@strColumn列的类型,0-数字类型,1-字符类型,2-日期时间类型
@intOrder bit, --排序,0-顺序,1-倒序
@strColumnlist varchar(800), --要查询出的字段列表,*表示全部字段
@intPageSize int, --每页记录数
@intPageNum int, --指定页
@strWhere varchar(800), --查询条件
@intPageCount int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000) --用于构造SQL语句
DECLARE @where1 varchar(800) --构造条件语句
DECLARE @where2 varchar(800) --构造条件语句
IF @strWhere is null or rtrim(@strWhere)=''
-- 为了避免SQL关键字与字段、表名等连在一起，首先为传入的变量添加空格
BEGIN --没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN --有查询条件
SET @where1=' WHERE ('+@strWhere+') AND '
SET @where2=' WHERE ('+@strWhere+') '
END
set @strColumn = ' ' + @strColumn + ' '
set @strColumnlist = ' ' + @strColumnlist + ' '
--构造SQL语句，计算总页数。计算公式为 总页数 = Ceiling ( 记录个数 / 页大小 )
SET @sql='SELECT @intPageCount=CEILING((COUNT(*)+0.0)/'
+ CAST(@intPageSize AS varchar)
+ ') FROM ' + @strTable + @where2
--执行SQL语句，计算总页数，并将其放入@intPageCount变量中
EXEC sp_executesql @sql,N'@intPageCount int OUTPUT',@intPageCount OUTPUT
--将总页数放到查询返回记录集的第一个字段前，此语句可省略
SET @strColumnlist= Cast(@intPageCount as varchar(30)) + ' as PageCount,' + @strColumnlist
IF @intOrder=0 --构造升序的SQL
SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) + ' ' +
@strColumnlist +
' FROM ' + @strTable + @where1 +
@strColumn + '>(SELECT MAX('+@strColumn+') '+
' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) +
@strColumn + ' FROM '+ @strTable+@where2+'ORDER BY '+@strColumn+') t) ORDER BY '+ @strColumn
ELSE --构造降序的SQL
SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) + ' ' +
@strColumnlist+
' FROM '+ @strTable + @where1 +
@strColumn + '<(SELECT MIN('+@strColumn+') '+
' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) +
@strColumn + ' FROM '+ @strTable+@where2+'ORDER BY '+@strColumn+' DESC) t) ORDER BY '+
@strColumn + ' DESC'
IF @intPageNum=1--第一页
SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar(10)) + ' ' + @strColumnlist + ' FROM '+@strTable+
@where2+' ORDER BY '+@strColumn + CASE @intOrder WHEN 0 THEN '' ELSE ' DESC'
END
--PRINT @sql
EXEC(@sql)
GO

存储过程使用示例

declare @intPageCount int
exec sp_page
'stuInfo', --表名
'stuNO', --按该列来进行分页
0, -- 0-数字类型,1-字符类型,2-日期时间类型
0, -- 0-顺序,1-倒序
'*', --要查询出的字段列表,*表示全部字段
2, --每页记录数
1, --指定页
'', --查询条件
@intPageCount output
print @intPageCount

数据库表：

数据库表

项目的数据模块图：

项目结构图

BLL层： 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WebApplication3.DAL;
using WebApplication3.Model;
namespace WebApplication3.BLL
{
   
    public class UserInfoBLL
    {
        UserInfoDAL userDal = new UserInfoDAL();
        public object loadData(UserInfo user)
        {
           return userDal.loadData(user);
        }
        public int Count()
        {
            int count = UserInfoDAL.count;
            return count;
        }
    }
}

DAL层：

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using WebApplication3.Model;
namespace WebApplication3.DAL
{
    public class UserInfoDAL
    {
        SQLHelper sqlHelp = new SQLHelper();
        public static int  count;
        public object loadData(UserInfo user)
        {
            SqlParameter[] paraValues = {
                                        new SqlParameter("@strTable",SqlDbType.VarChar),
                                        new SqlParameter("@strColumn",SqlDbType.VarChar),
                                        new SqlParameter("@intColType",SqlDbType.Int),
                                        new SqlParameter("@intOrder",SqlDbType.Int),
                                        new SqlParameter("@strColumnlist",SqlDbType.VarChar),
                                        new SqlParameter("@intPageSize",SqlDbType.Int),
                                        new SqlParameter("@intPageNum",SqlDbType.Int),
                                        new SqlParameter("@strWhere",SqlDbType.VarChar),
                                        new SqlParameter("@intPageCount",SqlDbType.Int)
                                        };
            paraValues[0].Value = user.tableName;
            paraValues[1].Value = user.Column;
            paraValues[2].Value = user.ColType;
            paraValues[3].Value = user.Order;
            paraValues[4].Value =user.Columnlist;
            paraValues[5].Value = user.PageSize;
            paraValues[6].Value = user.PageNum;
            paraValues[7].Value = user.Where;
            paraValues[8].Direction = ParameterDirection.Output;
            object obj = sqlHelp.ExecuteProcTable("sp_page", paraValues);
            count = Convert.ToInt32(paraValues[8].Value);
            return obj;
        }
    }
}

Model层（Entity类）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace WebApplication3.Model
{
    public class UserInfo
    {
        public string tableName { get; set; }
        public string Column { get; set; }
        public int ColType { get; set; }
        public int Order { get; set; }
        public string Columnlist { get; set; }
        public int PageSize { get; set; }
        public int PageNum { get; set; }
        public string Where { get; set; }
        //public string PageCount { get; set; }
    }
}

UI层：前台代码（WebForm1.aspx）

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication3.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>
    <script type="text/javascript">
　　function integer() {
　　　　var num = document.getElementById("TextBox1").value;
　　　　if (num=="") {
　　　　　　alert('请输入内容');
　　　　　　return false;
　　　　}
　　　　if (!(/(^[1-9]\d*$)/.test(num))) {
　　　　　　alert('输入的不是正整数');
　　　　　　return false;
    } else {
        return true;
　　　　}
　　}
</script>
</head>
<body>
    <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField DataField="stuNO" HeaderText="学号" />
                <asp:BoundField DataField="stuName" HeaderText="姓名" />
            </Columns>
        </asp:GridView>
        <div>
            <asp:Button ID="btnFirst" runat="server" OnClick="btnFirst_Click" Text="首页" style="height: 21px" />
            <asp:Button ID="btnPre" runat="server" OnClick="btnPre_Click" Text="上一页" />
            <asp:Button ID="btnNext" runat="server" OnClick="btnNext_Click" Text="下一页" style="height: 21px" />
            <asp:Button ID="btnLast" runat="server" OnClick="btnLast_Click" Text="最后一页" />
            <asp:TextBox ID="TextBox1" runat="server" Width="83px"></asp:TextBox>
            <asp:Button ID="btnGO" runat="server" OnClientClick="integer()" OnClick="btnGO_Click" Text="跳转" />
        </div>
        <p>
            <asp:Label ID="Label1" runat="server"></asp:Label>
            <asp:Label ID="Label2" runat="server"></asp:Label>
            <asp:Label ID="Label3" runat="server" Text="每页显示条数"></asp:Label>
            <asp:DropDownList ID="DropDownList1" runat="server">
                <asp:ListItem>1</asp:ListItem>
                <asp:ListItem Selected="True">2</asp:ListItem>
                <asp:ListItem>5</asp:ListItem>
                <asp:ListItem>10</asp:ListItem>
            </asp:DropDownList>
            <asp:Button ID="btnOK" runat="server" OnClick="btnOK_Click" Text="确认" />
        </p>
    </form>
</body>
</html>

UI层：后台代码（WebForm1.aspx.cs）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using WebApplication3.BLL;
using WebApplication3.DAL;
using WebApplication3.Model;
namespace WebApplication3
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        UserInfoBLL userBll = new UserInfoBLL();
        UserInfo user = new UserInfo();
        int count = 1;
        private void loadData()
        {
            count = Convert.ToInt32(ViewState["count"]);
            user.tableName = "stuInfo";
            user.Column = "stuNO";
            user.ColType = 0;
            user.Order = 0;
            user.Columnlist = "*";
            user.PageSize = Convert.ToInt32(DropDownList1.SelectedItem.Text);
            user.PageNum = count;
            user.Where = "";
            GridView1.DataSource = userBll.loadData(user);
            GridView1.DataBind();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ViewState["count"] = 1;
                loadData();
                Label1.Text  = "当前页：" + count;
                Label2.Text = "总页数：" + userBll.Count();
            }
        }
        protected void btnFirst_Click(object sender, EventArgs e)
        {
            ViewState["count"] = 1;
            loadData();
            Label1.Text  = "当前页：" + count;
            Label2.Text = "总页数：" + userBll.Count();
        }
        protected void btnPre_Click(object sender, EventArgs e)
        {
            if (Convert.ToInt32(ViewState["count"]) > 1)
            {
                ViewState["count"] = Convert.ToInt32(ViewState["count"]) - 1;
                loadData();
                Label1.Text = "当前页：" + count;
            }
        }
        protected void btnNext_Click(object sender, EventArgs e)
        {
            if (Convert.ToInt32(ViewState["count"]) < userBll.Count())
            {
                ViewState["count"] = Convert.ToInt32(ViewState["count"]) + 1;
                loadData();
                Label1.Text = "当前页：" + count;
            }
        }
        protected void btnLast_Click(object sender, EventArgs e)
        {
            ViewState["count"] = userBll.Count();
            loadData();
            Label1.Text = "当前页：" + count;
        }
        protected void btnGO_Click(object sender, EventArgs e)
        {
            try
            {
                ViewState["count"] = Convert.ToInt32(TextBox1.Text);
            }
            catch
            {
                return;
            }
            if (Convert.ToInt32(ViewState["count"]) <= userBll.Count())
            {
                loadData();
                Label1.Text = "当前页：" + count;
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "", "alert('超出范围');", true);
            }
        }
        protected void btnOK_Click(object sender, EventArgs e)
        {
            ViewState["count"] = 1;
            loadData();
            Label1.Text = "当前页：" + count;
            Label2.Text = "总页数：" + userBll.Count();
        }
    }
}

还有数据库访问类（SQLHelper.cs）代码太多就不贴出来了

GitHub源码下载：https://github.com/kiritobin/FenYeSqlProc3Fram

原文链接：https://www.idaobin.com/archives/1230.html

让我恰个饭吧.ヘ(￣ω￣ヘ)

支付宝 ——————- 微信
图片加载中图片加载中



恰饭广告


广告

你可能也喜欢

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注

52 − 48 =