如何基于带flag参数的存储过程在三层架构中实现CRUD操作

原存储过程代码 #

@eid int=null,
@ename varchar(30)=null,
@ecity varchar(20)=null,
@eemail varchar(30)=null,
@pass nvarchar(20)=null,
@flag varchar(20)=null
as
begin
if @flag='i' -- 新增
begin
insert into emp values(@ename,@ecity,@eemail,@pass)
end

if @flag='u' -- 修改
begin
    update emp set ename=@ename,ecity=@ecity,eemail=@eemail where eid=@eid
end

if @flag='d' -- 删除
begin
    delete from emp where eid=@eid
end

if @flag='s' -- 查询全量列表
begin
    select * from emp
end

if @flag='l' -- 登录校验
begin
    select eid,ename from emp where eemail=@eemail and pass=@pass
end

if @flag='cp' -- 修改密码
begin
    update emp set pass=@pass where eemail=@eemail
end
end

三层架构分步实现 #

三层架构从上到下分为表示层（UI）、业务逻辑层（BLL）、数据访问层（DAL），存储过程的调用逻辑封装在DAL层，业务规则放在BLL层，UI层仅做交互和结果展示。

1. 数据访问层（DAL）实现 #

DAL层统一封装对该存储过程的调用，根据不同操作场景传入对应的@flag参数和业务参数即可，不需要重复写多个存储过程的调用逻辑。

• 新增操作：传入@flag='i'，对应参数为@ename、@ecity、@eemail、@pass
• 修改操作：传入@flag='u'，对应参数为@eid、@ename、@ecity、@eemail
• 删除操作：传入@flag='d'，对应参数为@eid
• 全量查询：传入@flag='s'，无额外业务参数
• 登录校验：传入@flag='l'，对应参数为@eemail、@pass
• 修改密码：传入@flag='cp'，对应参数为@eemail、@pass

示例代码（C#为例）：

// 新增员工DAL方法示例
public int AddEmployee(string ename, string ecity, string eemail, string encryptedPass)
{
    SqlParameter[] spParams = {
        new SqlParameter("@ename", ename),
        new SqlParameter("@ecity", ecity),
        new SqlParameter("@eemail", eemail),
        new SqlParameter("@pass", encryptedPass),
        new SqlParameter("@flag", "i")
    };
    // 调用公共SQL helper执行存储过程，返回受影响行数
    return SqlHelper.ExecuteNonQuery(Config.ConnStr, CommandType.StoredProcedure, "sp_EmpOperate", spParams);
}

其余操作的DAL方法逻辑一致，仅调整传入参数和@flag的取值即可。

2. 业务逻辑层（BLL）实现 #

BLL层调用DAL层的方法，在执行数据操作前增加业务校验逻辑，避免非法请求直接操作数据库：

• 新增/修改员工时，校验邮箱格式合法性、姓名非空、城市字段合法
• 删除员工时，先校验该员工是否存在关联业务数据（如关联的订单、排班记录等），避免误删
• 登录校验时，先校验参数非空、邮箱格式合法，再调用DAL取数
• 修改密码时，校验新密码强度符合要求、新旧密码不重复

示例代码：

// 登录校验BLL方法示例
public EmpModel CheckLogin(string eemail, string inputPass)
{
    // 参数非空校验
    if(string.IsNullOrWhiteSpace(eemail) || string.IsNullOrWhiteSpace(inputPass))
        return null;
    // 邮箱格式校验
    if(!Regex.IsMatch(eemail, @"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$"))
        return null;
    // 密码加密后传给DAL（不要传明文密码到数据库）
    string encryptedPass = MD5Encrypt(inputPass);
    return EmpDAL.GetLoginUser(eemail, encryptedPass);
}

3. 表示层（UI）实现 #

UI层仅负责接收用户输入、调用BLL层方法、给用户返回操作反馈，不涉及任何业务逻辑和数据库操作：

• 员工新增页：用户填写表单后调用BLL的新增员工方法，成功提示"新增成功"，失败返回对应错误原因
• 员工列表页：页面加载时调用BLL的查询全量员工方法，将返回的集合绑定到列表控件展示
• 登录页：用户输入邮箱密码后调用BLL的登录校验方法，返回有效用户信息则跳转至系统首页，否则提示"账号或密码错误"
• 修改密码页：用户提交邮箱和新密码后调用BLL的修改密码方法，根据返回结果提示操作是否成功

优化建议 #

1. 存储过程的insert语句建议显式指定字段名，避免表结构变更后存储过程执行报错，修改为insert into emp(ename,ecity,eemail,pass) values(@ename,@ecity,@eemail,@pass)
2. 禁止明文存储密码，所有密码入库前需做哈希加密，存储过程的@pass参数仅接收加密后的密文
3. 所有用户输入的参数需做前后端双重校验，配合参数化查询可完全避免SQL注入风险

内容的提问来源于stack exchange，提问作者jay