EvaFrameWork.Sql 2.0.6

EvaFrameWork.Sql

EvaFrameWork.Sql 一个轻量级的数据库访问框架。 此框架依托于 Microsoft.Data.SqlClient 原生组件开发。 旨在为开发人员提供更简单的配置，更快捷的数据操作方式。

以下我们将用四张基础数据表来讲述框架的基础用法。

1. Member 会员信息主表
2. MemberAccount 会员资金账户总表
3. MemberAccountDetail 会员资金账户变动表
4. UserLog 系统用户操作日志表

项目配置以及容器管理工具 Asp.Net Core 6.0 MVC + Autofac + EvaFrameWork.Sql

初始化参数配置

using EvaFrameWork.Sql; using EvaFrameWork.Sql.Caches; using EvaFrameWork.Sql.Domains; using EvaFrameWork.Sql.GenericRepository;

public static void Builder(IConfiguration Configuration, IServiceCollection services) { var containerBuilder = new ContainerBuilder();

        string connectionString = Configuration.GetConnectionString("DefaultConnection");
        
        IEva IEva =  new EvaFrameWork.Sql.Eva(DataBaseType.SqlServer, connectionString);

        ///全局主键配置
        IEva.SetGlobalKey("Id");           

        IEva.AddTypeKey<Member>(x => x.Id);
        IEva.AddTypeKey<Member>(x => x.Code);

        ///开启数据缓存功能
        IEva.SetCache(true);

        ///配置 Member 表为缓存对象，且仅缓存Id, Code, RealName, NickName 四个字段
        IEva.AddCache(new CacheSet<Member>(x => x.Id, true).Add(x => x.Code).Add(x => x.RealName).Add(x => x.NickName));


        ///初始化返回数据库上下文实例
        IEva.Init(out IEvaContext _evaContext);


        ///代码管理有关的配置
        ///将所有数据库表转换成实体 Class.cs 文件
        ///IEva.ToEntity("EvaFrameWork.EvaDomains", "D:\\Projects\\Eva.FrameWork\\Eva.FrameWork\\EvaDomains");

        ///将数据库表(Actions, ActiveInfo) 转换成实体 Class.cs 文件
        ///IEva.ToEntity("Eva.FrameWork.EvaDomains", "D:\\Projects\\Eva.FrameWork\\Eva.FrameWork\\EvaDomains", new string[] { "Actions", "ActiveInfo" });


        //将 _evaContext 注入容器
        containerBuilder.Register(x => _evaContext).As<_evaContext>().InstancePerLifetimeScope();  

        //注册启用框架提供的泛型数据仓储。此为非并要操作。您完全可以不使用框架提供的仓储容器，而自己根据需求搭建配置。
        containerBuilder.RegisterGeneric(typeof(Repository<>)).As(typeof(IRepository<>)).InstancePerLifetimeScope();                 

        containerBuilder.Populate(services);

        // 创建autofac 容器
        _container = containerBuilder.Build();
        _autofacServiceProvider = new AutofacServiceProvider(_container);            
    }

新建会员信息并且记录系统日志

    推荐操作，针对不同的数据类型定义对应的数据仓储

    private readonly IRepository<Member> _memberRepository;     
    private readonly IRepository<MemberAccount> _memberAccountRepository;
    private readonly IRepository<MemberAccountDetail> _memberAccountDetailRepository;
    private readonly IRepository<UserLog> _userLogRepository;

    public ResultInfo AddMember(Member member)
    {
        //获取最新的会员编号
        member.Code = this.GetCode(this._memberRepository.Max(x => x.Code), 100001);

        if (member.Code.IsCode())
        {
            //新建会员资金总表实例
            MemberAccount memberAccount = new() { Id = StringExtension.GetNewGuid(), MemberCode = member.Code };

            this._memberRepository.Add(member); 
            this._memberAccountRepository.Add(memberAccount);

            this._userLogRepository.Add(this.CreateUserLog("会员管理", member.Id, member.RealName, "创建学员"));

            _unitOfWork.Flush(); // 数据到数据库，默认使用非锁表 事务提交
            _resultInfo.SetSucc("", member);
        }
        else
        {
            _resultInfo.SetNoData();
        }

        return _resultInfo;
    }

    您也可以使用下面的方式来完成 ，不使用仓储容器，直接解析框架的上下文来完成所有操作
    public ResultInfo AddMember(Member member)
    {
        //获取最新的会员编号
        member.Code = _evaContext.Max<Member>(x => x.Code) +1;

        if (member.Code.IsCode())
        {
            //新建会员资金总表实例
            MemberAccount memberAccount = new() { Id = StringExtension.GetNewGuid(), MemberCode = member.Code };

            _evaContext.Add<Member>(member);
_evaContext.Add<memberAccount>(memberAccount);  
           _evaContext.Add<UserLog >(this.CreateUserLog("会员管理", member.Id, member.RealName, "创建会员"));

             _evaContext.Flush();  // 数据到数据库，默认使用非锁表 事务提交
            _resultInfo.SetSucc("", member);
        }
        else
        {
            _resultInfo.SetNoData();
        }

        return _resultInfo;
    }

查询已存在的会员信息，编辑后提交保存

public ResultInfo UpdateMember(Member member) { Member currentMember = this._memberRepository.Find(x=>x.Id.Equals(member.Id));

        if (currentMember != null)
        {
            currentMember.RealName = member.RealName;
            currentMember.NickName = member.NickName;

            this._memberRepository.Update(currentMember);
            this._userLogRepository.Add(this.CreateUserLog("会员管理", currentMember.Id, currentMember.RealName, "编辑会员"));

            _unitOfWork.Flush();
            _resultInfo.SetSucc();
        }
        else
        {
            _resultInfo.SetFalse("会员数据不存在!");
        }

        return _resultInfo;
    }

删除已有会员数据

   public ResultInfo RemoveMember(string keyId)
    {
        Member currentMember = this._memberRepository.Find(x => x.Id.Equals(keyId));

        if (currentMember != null)
        {
            ///1. 删除主表                       
            this._memberRepository.Delete(currentMember);

            ///2. 删除资金总表
            MemberAccount memberAccount = this._memberAccountRepository.Find(x => x.MemberCode == currentMember.Code);
            if (memberAccount != null) { this._memberAccountRepository.Delete(memberAccount); }

            ///3. 删除资金变动表
            IList<MemberAccountDetail> details = this._memberAccountDetailRepository.FindList(x => x.MemberCode == currentMember.Code);
            foreach (var d in details) { this._memberAccountDetailRepository.Delete(d); }

            this._userLogRepository.Add(this.CreateUserLog("学员管理", currentMember.Id, currentMember.RealName, "删除学员"));

            _unitOfWork.Flush();
            _resultInfo.SetSucc("", currentMember);
        }
        else
        {
            _resultInfo.SetFalse("数据不存在或已审核");
        }

        return _resultInfo;
    }

分页列表展示会员数据

    public IList<Member> ViewMemberListForPage(string realName, string nickName, string phoneNumber, 
                                                DateTime? addDateStart, DateTime? addDateEnd, int pageIndex, int pageSize, out int recordCount)
    {
        //定义查询表达式，默认条件为不显示已删除数据
        Expression<Func<Member, bool>> queryList = x => !x.IsDelete; 

        //查询条件 姓名
        if (realName.IsNotEmpty()) { queryList = queryList.Add(x => x.RealName.Contains(realName)); }

        //查询条件 手机号
        if (phoneNumber.IsPhoneNumber()) { queryList = queryList.Add(x => x.PhoneNumber.Contains(phoneNumber)); }

        //查询条件 昵称
        if (nickName.IsNotEmpty()) { queryList = queryList.Add(x => x.NickName.Contains(nickName)); }

        //查询条件 注册日期 (开始日期)
        if (addDateStart.HasValue)
        {
            addDateStart = addDateStart.Value.ToStartTime();
            queryList = queryList.Add(x => x.AddDate >= addDateStart.Value);
        }

        //查询条件 注册日期 (结束日期)
        if (addDateEnd.HasValue)
        {
            addDateEnd = addDateEnd.Value.ToEndTime();
            queryList = queryList.Add(x => x.AddDate <= addDateEnd.Value);
        }

        //排序条件 分页数据展示中，排序字段是必须的 
        OrderExp<Member> orderExp = new(m => m.AddDate, false);
        orderExp.Add(x => x.RealName, false); //多字段排序，直接添加即可

        return _memberRepository.FindList(queryList, orderExp, pageIndex, pageSize, out recordCount);
    }

带锁表机制的事务处理

    public ResultInfo MemberDeposit(int memberCode, decimal money)  
    {
        ITransaction _tran = _unitOfWork.BeginTransaction();  

        try
        {
            Member member = this._memberRepository.Find(x => x.Code == memberCode);
            MemberAccount account = this._accountRepository.Find(x => x.MemberCode == memberCode);

            if (member != null && account != null)
            {
    // 若在此处设置断点，去手工执行对 member  MemberAccount  表的update 操作将会失败。
                // 账户余额调整
                account.Purchase += money;
                account.BalancePurchase += money;
                
                // 生成资金变动明细记录
                MemberAccountDetail accountDetail = this.CreateAccountDetail(account, "充值预存", money);                  

                this._accountRepository.Update(account);
                this._accountDetailRepository.Add(accountDetail);

                this._userLogRepository.Add(this.CreateUserLog("会员管理", member.Id, member.PhoneNumber, string.Format("金额:{0}", money)));

                _tran.Commit();  //带有锁表机制的事务 需要以此方式提交
                _resultInfo.SetSucc();
            }
            else
            {
                _resultInfo.SetFalse("会员数据或账户信息异常");
            }
        }
        catch
        {
            throw;
        }
        finally
        {
            _unitOfWork.EndTransaction(_tran);  //结束事务，放在此处是需要强调无论事务是否执行成功，请求的最后都必须释放掉资源。
        }

        return _resultInfo;
    }

数据缓存功能的使用

通过这样的配置来启用缓存功能
///开启数据缓存功能
IEva.SetCache(true);
///配置 Member 表为缓存对象，且仅缓存Id, Code, RealName, NickName 四个字段
IEva.AddCache(new CacheSet<Member>(x => x.Id, true).Add(x => x.Code).Add(x => x.RealName).Add(x => x.NickName));

框架提供了缓存容器的访问类 EvaCache

///缓存取值测试
///测试主键：01105969108c4f0cbdffd82cd3f093c5
string testKey = "01105969108c4f0cbdffd82cd3f093c5";

Member member = EvaCache.GetEntity<Member>(testKey);
object value = EvaCache.GetValue<Member>(testKey, x => x.Sex);
int? code = EvaCache.GetInt<Member>(testKey, x => x.Code);
decimal? dec = EvaCache.GetDecimal<Member>(testKey, x => x.Code);
string name = EvaCache.GetString<Member>(testKey, x => x.RealName);
List<Member> members = EvaCache.GetList<Member>();

此版本为1.2x fix bug