ASP.NET三层架构如何实现多条件检索？完整示例及代码解析详解

ASP.NET 三层架构多条件检索示例

实体层 (Entity Layer)

// User.cs
public class UserEntity
{
    public int ID { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    public bool Status { get; set; } // 状态: 启用/禁用
}

数据访问层 (DAL)

1 接口定义

// IUserDal.cs
public interface IUserDal
{
    List<UserEntity> GetUserByConditions(
        string userName, 
        string email, 
        bool? status,
        int? pageSize,
        int? pageNumber);
}

2 实现类

// UserDal.cs
public class UserDal : IUserDal
{
    private readonly string _connectionString;
    public UserDal(string connectionString)
    {
        _connectionString = connectionString;
    }
    public List<UserEntity> GetUserByConditions(
        string userName, 
        string email, 
        bool? status,
        int? pageSize,
        int? pageNumber)
    {
        string sql = "SELECT * FROM Users WHERE 1=1";
        List<SqlParameter> parameters = new List<SqlParameter>();
        // 构建条件查询
        if (!string.IsNullOrEmpty(userName))
        {
            sql += " AND UserName LIKE @UserName";
            parameters.Add(new SqlParameter("@UserName", $"%{userName}%"));
        }
        if (!string.IsNullOrEmpty(email))
        {
            sql += " AND Email LIKE @Email";
            parameters.Add(new SqlParameter("@Email", $"%{email}"));
        }
        if (status.HasValue)
        {
            sql += " AND Status = @Status";
            parameters.Add(new SqlParameter("@Status", status.Value));
        }
        // 分页处理
        if (pageSize.HasValue && pageNumber.HasValue)
        {
            sql += " ORDER BY ID OFFSET (@PageNumber - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY";
            parameters.Add(new SqlParameter("@PageSize", pageSize.Value));
            parameters.Add(new SqlParameter("@PageNumber", pageNumber.Value));
        }
        using (SqlConnection conn = new SqlConnection(_connectionString))
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(parameters.ToArray());
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                List<UserEntity> users = new List<UserEntity>();
                while (reader.Read())
                {
                    users.Add(new UserEntity
                    {
                        ID = reader.GetInt32(0),
                        UserName = reader.GetString(1),
                        Email = reader.GetString(2),
                        Status = reader.GetBoolean(3)
                    });
                }
                return users;
            }
        }
    }
}

业务逻辑层 (BLL)

1 接口定义

// IUserBll.cs
public interface IUserBll
{
    List<UserEntity> GetUserByConditions(
        string userName, 
        string email, 
        bool? status,
        int? pageSize,
        int? pageNumber);
}

2 实现类

// UserBll.cs
public class UserBll : IUserBll
{
    private readonly IUserDal _userDal;
    public UserBll(IUserDal userDal)
    {
        _userDal = userDal;
    }
    public List<UserEntity> GetUserByConditions(
        string userName, 
        string email, 
        bool? status,
        int? pageSize,
        int? pageNumber)
    {
        return _userDal.GetUserByConditions(userName, email, status, pageSize, pageNumber);
    }
}

表示层 (ASPX页面)

1 页面布局

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Search.aspx.cs" Inherits="Search" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">多条件检索示例</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <h2>用户多条件检索</h2>
            <div>
                <asp:TextBox ID="txtUserName" runat="server" placeholder="用户名" />
                <asp:TextBox ID="txtEmail" runat="server" placeholder="邮箱" />
                <asp:CheckBox ID="chkStatus" runat="server" Text="启用" Checked="true" />
                <asp:Button ID="btnSearch" runat="server" Text="搜索" OnClick="btnSearch_Click" />
            </div>
            <asp:GridView ID="gvUsers" runat="server" 
                AutoGenerateColumns="False" 
                AllowPaging="True" 
                OnPageIndexChanging="gvUsers_PageIndexChanging">
                <Columns>
                    <asp:BoundField DataField="ID" HeaderText="ID" />
                    <asp:BoundField DataField="UserName" HeaderText="用户名" />
                    <asp:BoundField DataField="Email" HeaderText="邮箱" />
                    <asp:BoundField DataField="Status" HeaderText="状态" />
                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>
</html>

2 代码隐藏文件

// Search.aspx.cs
public partial class Search : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindUsers();
        }
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        BindUsers();
    }
    protected void BindUsers()
    {
        // 获取查询参数
        string userName = txtUserName.Text.Trim();
        string email = txtEmail.Text.Trim();
        bool? status = chkStatus.Checked;
        // 获取分页参数
        int pageSize = 10; // 默认每页10条
        int pageNumber = gvUsers.PageIndex + 1;
        // 创建BLL实例
        UserBll userBll = new UserBll(new UserDal(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString));
        // 调用BLL方法
        List<UserEntity> users = userBll.GetUserByConditions(
            userName, 
            email, 
            status,
            pageSize,
            pageNumber);
        // 绑定数据
        gvUsers.DataSource = users;
        gvUsers.DataBind();
    }
    protected void gvUsers_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvUsers.PageIndex = e.NewPageIndex;
        BindUsers();
    }
}

使用说明

1. 数据库准备：

   

   • 创建Users表,包含ID, UserName, Email, Status字段
   • 插入测试数据

2. 配置连接字符串：

   在Web.config中添加数据库连接字符串

   

3. 运行效果：

   • 用户可以通过输入用户名、邮箱和状态来筛选
   • 支持分页显示结果

4. 关键特性：

   

   • 三层架构分离关注点
   • 参数化查询防止SQL注入
   • 分页功能集成
   • 响应式搜索逻辑

这个示例展示了如何使用三层架构实现灵活的多条件检索功能,同时保持代码的可维护性和扩展性。