第十章：实战案例与最佳实践

10.1 项目架构设计

10.1.1 推荐的项目结构

MyProject/
├── MyProject.Entity/              # 实体层
│   ├── Entities/                  # 实体类
│   │   ├── UserEntity.cs
│   │   ├── OrderEntity.cs
│   │   └── ProductEntity.cs
│   ├── Interfaces/                # 实体接口
│   │   └── IUser.cs
│   └── MyProject.Entity.csproj
│
├── MyProject.DAL/                 # 数据访问层
│   ├── SqlMaps/                   # SQL-MAP配置文件
│   │   ├── User.SqlMap.config
│   │   └── Order.SqlMap.config
│   ├── Repositories/              # 仓储类
│   │   ├── UserRepository.cs
│   │   └── OrderRepository.cs
│   ├── DbContexts/                # 数据上下文
│   │   └── AppDbContext.cs
│   └── MyProject.DAL.csproj
│
├── MyProject.BLL/                 # 业务逻辑层
│   ├── Services/                  # 服务类
│   │   ├── UserService.cs
│   │   └── OrderService.cs
│   ├── DTOs/                      # 数据传输对象
│   │   ├── UserDto.cs
│   │   └── OrderDto.cs
│   └── MyProject.BLL.csproj
│
├── MyProject.API/                 # Web API层
│   ├── Controllers/
│   ├── Program.cs
│   └── MyProject.API.csproj
│
└── MyProject.sln                  # 解决方案文件

10.1.2 依赖关系

MyProject.API → MyProject.BLL → MyProject.DAL → MyProject.Entity
                                      ↓
                              PWMIS.SOD (NuGet)

10.2 完整案例：用户管理系统

10.2.1 实体类定义

UserEntity.cs:

using PWMIS.DataMap.Entity;
using System;

namespace MyProject.Entity.Entities
{
    /// <summary>
    /// 用户实体类
    /// </summary>
    public class UserEntity : EntityBase
    {
        public UserEntity()
        {
            TableName = "TbUser";
            IdentityName = "ID";
            PrimaryKeys.Add("ID");
        }

        public int ID
        {
            get { return getProperty<int>(nameof(ID)); }
            set { setProperty(nameof(ID), value); }
        }

        public string Username
        {
            get { return getProperty<string>(nameof(Username)); }
            set { setProperty(nameof(Username), value, 50); }
        }

        public string Password
        {
            get { return getProperty<string>(nameof(Password)); }
            set { setProperty(nameof(Password), value, 100); }
        }

        public string RealName
        {
            get { return getProperty<string>(nameof(RealName)); }
            set { setProperty(nameof(RealName), value, 50); }
        }

        public string Email
        {
            get { return getProperty<string>(nameof(Email)); }
            set { setProperty(nameof(Email), value, 100); }
        }

        public string Phone
        {
            get { return getProperty<string>(nameof(Phone)); }
            set { setProperty(nameof(Phone), value, 20); }
        }

        public int DeptId
        {
            get { return getProperty<int>(nameof(DeptId)); }
            set { setProperty(nameof(DeptId), value); }
        }

        public int RoleId
        {
            get { return getProperty<int>(nameof(RoleId)); }
            set { setProperty(nameof(RoleId), value); }
        }

        public int Status
        {
            get { return getProperty<int>(nameof(Status)); }
            set { setProperty(nameof(Status), value); }
        }

        public DateTime CreateTime
        {
            get { return getProperty<DateTime>(nameof(CreateTime)); }
            set { setProperty(nameof(CreateTime), value); }
        }

        public DateTime? LastLoginTime
        {
            get { return getProperty<DateTime?>(nameof(LastLoginTime)); }
            set { setProperty(nameof(LastLoginTime), value); }
        }
    }
}

10.2.2 数据上下文

AppDbContext.cs:

using PWMIS.DataMap.Entity;
using MyProject.Entity.Entities;

namespace MyProject.DAL.DbContexts
{
    public class AppDbContext : DbContext
    {
        public AppDbContext() : base("DefaultConnection")
        {
        }

        protected override bool CheckAllTableExists()
        {
            CheckTableExists<UserEntity>();
            CheckTableExists<DeptEntity>();
            CheckTableExists<RoleEntity>();
            
            // 创建索引
            InitializeTable<UserEntity>("CREATE INDEX IF NOT EXISTS idx_user_username ON {0}(Username)");
            InitializeTable<UserEntity>("CREATE INDEX IF NOT EXISTS idx_user_status ON {0}(Status)");
            
            return true;
        }
    }
}

10.2.3 仓储层

IRepository.cs:

using PWMIS.DataMap.Entity;
using System;
using System.Collections.Generic;

namespace MyProject.DAL.Repositories
{
    public interface IRepository<T> where T : EntityBase, new()
    {
        T GetById(int id);
        List<T> GetAll();
        List<T> Find(Func<OQL, OQL> query);
        int Add(T entity);
        int Update(T entity);
        int Delete(int id);
        int Count(Func<OQL, OQL> query = null);
    }
}

UserRepository.cs:

using PWMIS.DataMap.Entity;
using PWMIS.DataProvider.Data;
using MyProject.Entity.Entities;
using System;
using System.Collections.Generic;

namespace MyProject.DAL.Repositories
{
    public class UserRepository : IRepository<UserEntity>
    {
        private readonly AdoHelper _db;

        public UserRepository()
        {
            _db = MyDB.GetDBHelper();
        }

        public UserEntity GetById(int id)
        {
            var entity = new UserEntity { ID = id };
            var oql = OQL.From(entity).Select().Where(entity.ID).END;
            return EntityQuery<UserEntity>.QueryObject(oql, _db);
        }

        public List<UserEntity> GetAll()
        {
            var entity = new UserEntity();
            var oql = OQL.From(entity)
                .Select()
                .OrderBy(o => o.Desc(entity.ID))
                .END;
            return EntityQuery<UserEntity>.QueryList(oql, _db);
        }

        public List<UserEntity> Find(Func<OQL, OQL> query)
        {
            var entity = new UserEntity();
            var oql = query(OQL.From(entity));
            return EntityQuery<UserEntity>.QueryList(oql, _db);
        }

        public int Add(UserEntity entity)
        {
            entity.CreateTime = DateTime.Now;
            return EntityQuery<UserEntity>.Instance.Insert(entity, _db);
        }

        public int Update(UserEntity entity)
        {
            return EntityQuery<UserEntity>.Instance.Update(entity, _db);
        }

        public int Delete(int id)
        {
            var entity = new UserEntity { ID = id };
            return EntityQuery<UserEntity>.Instance.Delete(entity, _db);
        }

        public int Count(Func<OQL, OQL> query = null)
        {
            var entity = new UserEntity();
            OQL oql;
            
            if (query != null)
            {
                oql = query(OQL.From(entity).Select(OQL.Count));
            }
            else
            {
                oql = OQL.From(entity).Select(OQL.Count).END;
            }
            
            return Convert.ToInt32(_db.ExecuteScalar(oql.ToString(), oql.Parameters));
        }

        // 扩展方法
        public UserEntity GetByUsername(string username)
        {
            var entity = new UserEntity { Username = username };
            var oql = OQL.From(entity).Select().Where(entity.Username).END;
            return EntityQuery<UserEntity>.QueryObject(oql, _db);
        }

        public List<UserEntity> GetByDept(int deptId)
        {
            var entity = new UserEntity { DeptId = deptId };
            var oql = OQL.From(entity)
                .Select()
                .Where(entity.DeptId)
                .OrderBy(o => o.Desc(entity.ID))
                .END;
            return EntityQuery<UserEntity>.QueryList(oql, _db);
        }

        public (List<UserEntity> Users, int TotalCount) GetByPage(
            string keyword, int? status, int pageIndex, int pageSize)
        {
            var entity = new UserEntity();
            var oql = OQL.From(entity)
                .Select()
                .Where(cmp =>
                {
                    var condition = cmp.Property(entity.ID) > 0;
                    
                    if (!string.IsNullOrEmpty(keyword))
                    {
                        condition = condition & 
                            (cmp.Comparer(entity.Username, "like", $"%{keyword}%") |
                             cmp.Comparer(entity.RealName, "like", $"%{keyword}%"));
                    }
                    
                    if (status.HasValue)
                    {
                        condition = condition & cmp.Property(entity.Status) == status.Value;
                    }
                    
                    return condition;
                })
                .OrderBy(o => o.Desc(entity.ID))
                .END;

            oql.Limit(pageSize, pageIndex, true);
            var users = EntityQuery<UserEntity>.QueryList(oql, _db);
            
            return (users, oql.PageWithAllRecordCount);
        }
    }
}

10.2.4 业务服务层

UserService.cs:

using MyProject.DAL.Repositories;
using MyProject.Entity.Entities;
using MyProject.BLL.DTOs;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;

namespace MyProject.BLL.Services
{
    public class UserService
    {
        private readonly UserRepository _userRepo;

        public UserService()
        {
            _userRepo = new UserRepository();
        }

        // 用户登录
        public UserDto Login(string username, string password)
        {
            var user = _userRepo.GetByUsername(username);
            
            if (user == null)
            {
                throw new Exception("用户不存在");
            }
            
            if (user.Status != 1)
            {
                throw new Exception("用户已被禁用");
            }
            
            var hashedPassword = HashPassword(password);
            if (user.Password != hashedPassword)
            {
                throw new Exception("密码错误");
            }
            
            // 更新最后登录时间
            user.LastLoginTime = DateTime.Now;
            _userRepo.Update(user);
            
            return MapToDto(user);
        }

        // 用户注册
        public int Register(RegisterDto dto)
        {
            // 检查用户名是否已存在
            var existUser = _userRepo.GetByUsername(dto.Username);
            if (existUser != null)
            {
                throw new Exception("用户名已存在");
            }
            
            var user = new UserEntity
            {
                Username = dto.Username,
                Password = HashPassword(dto.Password),
                RealName = dto.RealName,
                Email = dto.Email,
                Phone = dto.Phone,
                DeptId = dto.DeptId,
                RoleId = dto.RoleId,
                Status = 1
            };
            
            return _userRepo.Add(user);
        }

        // 获取用户列表
        public PagedResult<UserDto> GetUsers(string keyword, int? status, int pageIndex, int pageSize)
        {
            var (users, totalCount) = _userRepo.GetByPage(keyword, status, pageIndex, pageSize);
            
            return new PagedResult<UserDto>
            {
                Items = users.Select(MapToDto).ToList(),
                TotalCount = totalCount,
                PageIndex = pageIndex,
                PageSize = pageSize
            };
        }

        // 获取单个用户
        public UserDto GetUser(int id)
        {
            var user = _userRepo.GetById(id);
            return user != null ? MapToDto(user) : null;
        }

        // 更新用户
        public bool UpdateUser(UpdateUserDto dto)
        {
            var user = _userRepo.GetById(dto.ID);
            if (user == null)
            {
                throw new Exception("用户不存在");
            }
            
            user.RealName = dto.RealName;
            user.Email = dto.Email;
            user.Phone = dto.Phone;
            user.DeptId = dto.DeptId;
            user.RoleId = dto.RoleId;
            
            return _userRepo.Update(user) > 0;
        }

        // 删除用户
        public bool DeleteUser(int id)
        {
            return _userRepo.Delete(id) > 0;
        }

        // 重置密码
        public bool ResetPassword(int userId, string newPassword)
        {
            var user = _userRepo.GetById(userId);
            if (user == null)
            {
                throw new Exception("用户不存在");
            }
            
            user.Password = HashPassword(newPassword);
            return _userRepo.Update(user) > 0;
        }

        // 启用/禁用用户
        public bool SetUserStatus(int userId, int status)
        {
            var user = _userRepo.GetById(userId);
            if (user == null)
            {
                throw new Exception("用户不存在");
            }
            
            user.Status = status;
            return _userRepo.Update(user) > 0;
        }

        // 密码哈希
        private string HashPassword(string password)
        {
            using (var sha256 = SHA256.Create())
            {
                var bytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(password));
                return Convert.ToBase64String(bytes);
            }
        }

        // 实体转DTO
        private UserDto MapToDto(UserEntity entity)
        {
            return new UserDto
            {
                ID = entity.ID,
                Username = entity.Username,
                RealName = entity.RealName,
                Email = entity.Email,
                Phone = entity.Phone,
                DeptId = entity.DeptId,
                RoleId = entity.RoleId,
                Status = entity.Status,
                StatusText = entity.Status == 1 ? "启用" : "禁用",
                CreateTime = entity.CreateTime,
                LastLoginTime = entity.LastLoginTime
            };
        }
    }
}

10.2.5 DTO定义

UserDto.cs:

using System;

namespace MyProject.BLL.DTOs
{
    public class UserDto
    {
        public int ID { get; set; }
        public string Username { get; set; }
        public string RealName { get; set; }
        public string Email { get; set; }
        public string Phone { get; set; }
        public int DeptId { get; set; }
        public string DeptName { get; set; }
        public int RoleId { get; set; }
        public string RoleName { get; set; }
        public int Status { get; set; }
        public string StatusText { get; set; }
        public DateTime CreateTime { get; set; }
        public DateTime? LastLoginTime { get; set; }
    }

    public class RegisterDto
    {
        public string Username { get; set; }
        public string Password { get; set; }
        public string RealName { get; set; }
        public string Email { get; set; }
        public string Phone { get; set; }
        public int DeptId { get; set; }
        public int RoleId { get; set; }
    }

    public class UpdateUserDto
    {
        public int ID { get; set; }
        public string RealName { get; set; }
        public string Email { get; set; }
        public string Phone { get; set; }
        public int DeptId { get; set; }
        public int RoleId { get; set; }
    }

    public class PagedResult<T>
    {
        public List<T> Items { get; set; }
        public int TotalCount { get; set; }
        public int PageIndex { get; set; }
        public int PageSize { get; set; }
        public int TotalPages => (int)Math.Ceiling((double)TotalCount / PageSize);
    }
}

10.2.6 Web API控制器

UserController.cs:

using Microsoft.AspNetCore.Mvc;
using MyProject.BLL.Services;
using MyProject.BLL.DTOs;

namespace MyProject.API.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class UserController : ControllerBase
    {
        private readonly UserService _userService;

        public UserController()
        {
            _userService = new UserService();
        }

        [HttpPost("login")]
        public ActionResult<UserDto> Login([FromBody] LoginRequest request)
        {
            try
            {
                var user = _userService.Login(request.Username, request.Password);
                return Ok(user);
            }
            catch (Exception ex)
            {
                return BadRequest(new { message = ex.Message });
            }
        }

        [HttpPost("register")]
        public ActionResult Register([FromBody] RegisterDto dto)
        {
            try
            {
                var userId = _userService.Register(dto);
                return Ok(new { id = userId });
            }
            catch (Exception ex)
            {
                return BadRequest(new { message = ex.Message });
            }
        }

        [HttpGet]
        public ActionResult<PagedResult<UserDto>> GetUsers(
            [FromQuery] string keyword,
            [FromQuery] int? status,
            [FromQuery] int pageIndex = 1,
            [FromQuery] int pageSize = 10)
        {
            var result = _userService.GetUsers(keyword, status, pageIndex, pageSize);
            return Ok(result);
        }

        [HttpGet("{id}")]
        public ActionResult<UserDto> GetUser(int id)
        {
            var user = _userService.GetUser(id);
            if (user == null)
            {
                return NotFound();
            }
            return Ok(user);
        }

        [HttpPut("{id}")]
        public ActionResult UpdateUser(int id, [FromBody] UpdateUserDto dto)
        {
            try
            {
                dto.ID = id;
                _userService.UpdateUser(dto);
                return Ok();
            }
            catch (Exception ex)
            {
                return BadRequest(new { message = ex.Message });
            }
        }

        [HttpDelete("{id}")]
        public ActionResult DeleteUser(int id)
        {
            _userService.DeleteUser(id);
            return Ok();
        }

        [HttpPut("{id}/status")]
        public ActionResult SetStatus(int id, [FromBody] SetStatusRequest request)
        {
            _userService.SetUserStatus(id, request.Status);
            return Ok();
        }
    }

    public class LoginRequest
    {
        public string Username { get; set; }
        public string Password { get; set; }
    }

    public class SetStatusRequest
    {
        public int Status { get; set; }
    }
}

10.3 最佳实践总结

10.3.1 实体类设计原则

使用nameof：使用nameof(PropertyName)代替字符串字面量
统一构造函数：在构造函数中配置TableName、IdentityName、PrimaryKeys
合理设置字段长度：在setProperty时指定字段长度
区分业务接口：实体类可以实现业务接口

10.3.2 查询优化原则

只查询需要的字段：避免SELECT *
使用分页：避免一次性加载大量数据
利用索引：确保WHERE条件中的字段有索引
避免循环查询：使用IN查询代替循环

10.3.3 事务处理原则

使用事务：多个操作需要在事务中执行
正确处理异常：异常时回滚事务
及时关闭连接：使用try-finally确保连接关闭

10.3.4 代码组织原则

分层架构：Entity → DAL → BLL → API
使用DTO：不直接返回实体类给前端
服务封装：业务逻辑封装在Service中
仓储模式：数据访问封装在Repository中

10.4 常见问题解答

Q1：如何处理并发冲突？

// 使用乐观锁（版本号）
public class EntityWithVersion : EntityBase
{
    public int Version
    {
        get { return getProperty<int>(nameof(Version)); }
        set { setProperty(nameof(Version), value); }
    }
}

// 更新时检查版本
public bool UpdateWithVersion(EntityWithVersion entity, AdoHelper db)
{
    var oldVersion = entity.Version;
    entity.Version = oldVersion + 1;
    
    var oql = OQL.From(entity)
        .Update(entity.PropertyNames.ToArray())
        .Where(cmp => cmp.Property(entity.ID) == entity.ID 
                    & cmp.Property(entity.Version) == oldVersion)
        .END;
    
    int affected = EntityQuery<EntityWithVersion>.ExecuteOql(oql, db);
    
    if (affected == 0)
    {
        throw new Exception("数据已被其他用户修改");
    }
    
    return true;
}

Q2：如何实现软删除？

// 使用Status或IsDeleted字段标记删除
public bool SoftDelete(int id)
{
    var entity = new UserEntity { ID = id, Status = -1 };  // -1表示已删除
    
    var oql = OQL.From(entity)
        .Update(entity.Status)
        .Where(entity.ID)
        .END;
    
    return EntityQuery<UserEntity>.ExecuteOql(oql, _db) > 0;
}

// 查询时排除已删除的记录
public List<UserEntity> GetActiveUsers()
{
    var entity = new UserEntity();
    var oql = OQL.From(entity)
        .Select()
        .Where(cmp => cmp.Property(entity.Status) >= 0)  // 排除-1
        .END;
    
    return EntityQuery<UserEntity>.QueryList(oql, _db);
}

Q3：如何处理大数据量导入？

public void BatchImport(List<UserEntity> users)
{
    AdoHelper db = MyDB.GetDBHelper();
    
    const int batchSize = 1000;
    int totalCount = users.Count;
    int batchCount = (int)Math.Ceiling((double)totalCount / batchSize);
    
    for (int i = 0; i < batchCount; i++)
    {
        var batch = users.Skip(i * batchSize).Take(batchSize).ToList();
        
        db.OpenSession();
        db.BeginTransaction();
        
        try
        {
            foreach (var user in batch)
            {
                EntityQuery<UserEntity>.Instance.Insert(user, db);
            }
            db.Commit();
            
            Console.WriteLine($"批次 {i + 1}/{batchCount} 导入完成");
        }
        catch
        {
            db.Rollback();
            throw;
        }
        finally
        {
            db.CloseSession();
        }
    }
}

10.5 本章小结

本章通过完整的实战案例展示了SOD框架在真实项目中的应用：

项目架构：推荐的分层架构设计
完整案例：用户管理系统的完整实现
代码组织：实体类、仓储、服务、控制器的组织方式
最佳实践：实体设计、查询优化、事务处理等原则
常见问题：并发冲突、软删除、大数据导入的处理方式

通过本章的学习，你应该能够使用SOD框架构建完整的企业级应用。

附录：参考资源

官方网站：http://www.pwmis.com/sqlmap
GitHub仓库：https://github.com/znlgis/sod
Gitee仓库：https://gitee.com/znlgis/sod
作者博客：https://www.cnblogs.com/bluedoctor
QQ群：18215717、154224970
官方图书：《SOD框架企业级应用数据架构实战》

感谢您阅读本教程，祝您使用SOD框架开发愉快！