YTK2DBOperate 1.7.38

YTK2DBOperate

采用EFCore5 调用数据库操作

表定义

public class Describe : BaseOperate<TableDefine>
    {
        public Describe(BaseDB db) : base(db)
        {
        }
    }    
    
public class TableDefine:BaseEntity
    {
        [key]
        public int? Id              {get;set;}//数值型要定义为可空类型(?)
        public string BaseTypeId    {get;set;}
        public string DescStateName {get;set;}
    }

实例化

    //定义链接字符串
    string connStr = "server=192.168.1.114;database=YTK2Manager;uid=root;pwd=Password123456.;Charset=utf8;";
    //定义数据集实力
    BaseDB db = new BaseDB(connStr);
         
    //定义表实例
    YTK2DBInstance.Describe dsc = new YTK2DBInstance.Describe(db);

增加

    //定义一个模型
    TableDefine info=new TableDefine()
    {
        Id=1,
        BaseTypeId="1",
        DescStateName="测试"
    }
    
    //增加
    sc.Add(info);

查询

    
    //定义查询表达式    
    System.Linq.Expressions.Expression<Func<TableDefine, bool>> expr = x => x.BaseTypeId !=0;
    
    //执行查询
    sc.GetList();
    
    //查询条件,当前页码,每页行数
    sc.GetList(expr, pageNum, pageRows);
    
    //查询条件,当前页码,每页行数,排序类型,排序字段
    sc.GetList(expr, pageNum, pageRows,OrderType.Desc,"Id");
    
    //获取数量
    sc.GetRecordCount(expr)

通过linq 实现 复杂查询 1

//查询公司和机构关系
IQueryable<object> GetCompanyWithDepartments(string companyUuid)
{
    YTK2DBOperate.Base.BaseOperate<CompanyDep> companyDep = new YTK2DBOperate.Base.BaseOperate<CompanyDep>(db);
    YTK2DBOperate.Base.BaseOperate<CompanyInfo> companyInfo = new YTK2DBOperate.Base.BaseOperate<CompanyInfo>(db);
    YTK2DBOperate.Base.BaseOperate<DepInfo> depInfo = new YTK2DBOperate.Base.BaseOperate<DepInfo>(db);

    //书写Linq
    var query = from CP in companyDep.DbSet
        where CP.F_COMPANY_UUID == companyUuid
        join company in companyInfo.DbSet on CP.F_COMPANY_UUID equals company.F_COMPANY_UUID
        join dep in depInfo.DbSet on CP.F_DEP_UUID equals dep.F_DEP_UUID
        select new
        {
            //只要部分字段
            company.F_COMPANY_NAME,
            dep.F_DEP_NAME,
            
            //或者直接拿模型所有字段
            CompanyInfo= company,
            DepInfo=dep,
            
            //或者重新定义字段名
            Fileld1=company.F_COMPANY_NAME,
            Fileld2=dep.F_DEP_NAME
        };
    
    //产生的Sql语句
    var sql = query.ToParametrizedSql();
    Console.WriteLine(sql.Item1.ToString());
    
    //查询结果
    var result = query.ToList();
    return query;
}

通过linq 实现 复杂查询 2

class Program
{
    static void Main()
    {
        string connStr = "server=192.168.1.114;database=YTK2Manager;uid=root;pwd=Password123456.;Charset=utf8;AllowLoadLocalInfile=true";
        BaseDB db = new BaseDB(connStr);

        var res = GetCompanyWithDepartments(db, "0b0a877e-312c-4a").ToList();
        
        //
        foreach (var item in res)
        {
            var i = item;
        }
        Console.WriteLine("Hello, World!");
    }

    #region 联表查询

    // 查询公司和机构关系
    static IQueryable<IDictionary<string, object>> GetCompanyWithDepartments(BaseDB db, string companyUuid)
    {
        YTK2DBOperate.Base.BaseOperate<CompanyDep> companyDep = new YTK2DBOperate.Base.BaseOperate<CompanyDep>(db);
        YTK2DBOperate.Base.BaseOperate<CompanyInfo> companyInfo = new YTK2DBOperate.Base.BaseOperate<CompanyInfo>(db);
        YTK2DBOperate.Base.BaseOperate<DepInfo> depInfo = new YTK2DBOperate.Base.BaseOperate<DepInfo>(db);

        // 书写Linq
        var query = from CP in companyDep.DbSet
            where CP.F_COMPANY_UUID == companyUuid
            join company in companyInfo.DbSet on CP.F_COMPANY_UUID equals company.F_COMPANY_UUID
            join dep in depInfo.DbSet on CP.F_DEP_UUID equals dep.F_DEP_UUID
            select MergeObjects(company, dep);

        // 产生的Sql语句
        var sql = query.ToParametrizedSql();
        return query;
    }

    /// <summary>
    /// 合并对象
    /// </summary>
    /// <param name="objects"></param>
    /// <returns></returns>
    static IDictionary<string, object> MergeObjects(params object[] objects)
    {
        var result = new Dictionary<string, object>();
        foreach (var obj in objects)
        {
            foreach (var property in obj.GetType().GetProperties())
            {
                result[property.Name] = property.GetValue(obj);
            }
        }
        return result;
    } 
 
    #endregion
}

通过linq 实现 复杂查询 3

// 查询公司和机构关系
    static List<DepAndCompany>  GetCompanyWithDepartments(BaseDB db, string companyUuid)
    {
        YTK2DBOperate.Base.BaseOperate<CompanyDep> companyDep = new YTK2DBOperate.Base.BaseOperate<CompanyDep>(db);
        YTK2DBOperate.Base.BaseOperate<CompanyInfo> companyInfo = new YTK2DBOperate.Base.BaseOperate<CompanyInfo>(db);
        YTK2DBOperate.Base.BaseOperate<DepInfo> depInfo = new YTK2DBOperate.Base.BaseOperate<DepInfo>(db);

        // 书写Linq
        var query = from CP in companyDep.DbSet
            where CP.F_COMPANY_UUID == companyUuid
            join company in companyInfo.DbSet on CP.F_COMPANY_UUID equals company.F_COMPANY_UUID
            join dep in depInfo.DbSet on CP.F_DEP_UUID equals dep.F_DEP_UUID
            select MergeObjects(company, dep);

        // 产生的Sql语句
        var sql = query.ToParametrizedSql();
        
        //将返回值映射到自己的对象
        var mapperConfig = new MapperConfiguration(cfg =>
        {
            cfg.CreateMap<IDictionary<string, object>, DepAndCompany>()
                .ForAllMembers(opt => opt.MapFrom(src => src.ContainsKey(opt.DestinationMember.Name) ? src[opt.DestinationMember.Name] : null));
        });

        IMapper mapper = mapperConfig.CreateMapper();
        var result = query.Select(item => mapper.Map<DepAndCompany>(item)).ToList();
        
        return result;
    }

    /// <summary>
    /// 拼接字段; 懒得一个个写了
    /// </summary>
    /// <param name="objects"></param>
    /// <returns></returns>
    static IDictionary<string, object> MergeObjects(params object[] objects)
    {
        var result = new Dictionary<string, object>();
        foreach (var obj in objects)
        {
            foreach (var property in obj.GetType().GetProperties())
            {
                result[property.Name] = property.GetValue(obj);
            }
        }
        return result;
    } 

举一反十八: https://learn.microsoft.com/zh-cn/ef/core/querying/complex-query-operators

机构 递归父亲

    #region 机构 递归父亲

    static object GetDepTree(BaseDB db)
    {
        YTK2DBOperate.Base.BaseOperate<DepInfo> dbInfo = new YTK2DBOperate.Base.BaseOperate<DepInfo>(db);

        // 获取所有 DepInfo 记录
        var depInfos = dbInfo.DbSet.ToList();

        // 获取层级结构
        var hierarchy = GetAllHierarchy(depInfos,"");
        
        return hierarchy;
    }
    
    /// <summary>
    /// 通过Linq实现递归 创造机构树
    /// </summary>
    /// <param name="depInfos"></param>
    /// <param name="parentUuid"></param>
    /// <returns></returns>
    public static List<DepInfo> GetAllHierarchy(List<DepInfo> depInfos, string parentUuid = null)
    {
        var result= depInfos
            .Where(dep => dep.F_DEP_PARENT_UUID == parentUuid)
            .Select(dep => new DepInfo
            {
                F_DEP_UUID = dep.F_DEP_UUID,
                F_DEP_NAME = dep.F_DEP_NAME,
                F_DEP_PARENT_UUID = dep.F_DEP_PARENT_UUID,
                F_TYPE_UUID = dep.F_TYPE_UUID,
                F_CREATE_TIME = dep.F_CREATE_TIME,
                F_CREATE_USR_UUID = dep.F_CREATE_USR_UUID,
                F_CREATE_USR_NAME = dep.F_CREATE_USR_NAME,
                F_LAST_UPDATE_TIME = dep.F_LAST_UPDATE_TIME,
                F_REMARK = dep.F_REMARK,
                F_ISDELETED = dep.F_ISDELETED,
                F_CLIENT_ID = dep.F_CLIENT_ID,
                 Children = GetAllHierarchy(depInfos, dep.F_DEP_UUID)
            })
            .ToList();
        Console.WriteLine($"Parent UUID: {parentUuid}, Children Count: {result.Count}");
        return result;
    }
    #endregion
}

更新

    //定义更新方法
    Action<TableDefine> newInfo = x =>
        {
            x.DescStateName = "新内容";
        };
    //执行更新
    var res=  dsc.Update(expr, newInfo);
    

执行事物

    db.StartTrans();
    for (int i = 0; i <= 10; i++)
        {
        var resAdd = dsc.AddObject(addMdl);
        }
    db.Commit();

执行SQL语句

    //执行sql查询
    List<object> parm = new List<object>()
    {
        new MySqlConnector.MySqlParameter("base_type_id", 2),
    };
    object res = dsc.FromSql("select * from base_describe where base_type_id=@base_type_id",parm);
    
    //执行sql语句,可以操作任何表 不支持Query
    var execute= db.ExecuteSql("insert into base_describe values(6,11,111,111)");
    
    //执行sql语句返回dataset
    var exe=db.ExecuteSqlQuery("select * from table")

asp.net core 注入

//依赖注入，取得DbContext实例 使用DbContext池，提高性能 数据库上下文注入
services.AddDbContextPool<BaseDB>(options => options.UseMysql(connStr));

执行 Bulk

执行任何bluk时候 链接字符串要加上: AllowLoadLocalInfile=true

使用事物共享

//定义表结构
[Table("t_base_describe")]
public class InBaseDescribe : BaseEntity
{
    [Key] public int F_BASE_ID { get; set; }

    /// <summary>
    /// 客户端名称
    /// </summary>
    public int F_BASE_TYPE { get; set; }

    /// <summary>
    /// 创建时间
    /// </summary>
    public int F_BASE_STATE_ID { get; set; }

    /// <summary>
    /// 创建人UUID
    /// </summary>
    public string? F_BASE_STATE_NAME { get; set; }

    /// <summary>
    /// 备注
    /// </summary>
    public string? F_REMARK { get; set; }

    /// <summary>
    /// 是否删除
    /// </summary>
    public int F_ISDELETED { get; set; }
}

//定一个这个表达 DbContext
public class InBaseDescribeContext : DbContext
{
    public InBaseDescribeContext(DbContextOptions<InBaseDescribeContext> options)
        : base(options)
    {
    }

    public DbSet<InBaseDescribe> BaseDescribe { get; set; }
}


    static void TestTrans3(BaseDB db)
    {
        //创建options,所有关系到的option都要创建; 即用即创建
        var options1 = db.CreateDbContextOptions<InBaseDescribeContext>();
        var options2 = db.CreateDbContextOptions<CompanyInfoContext>();

        //使用 TransactionScope 来控制多个 DbContext
        /*
         * 
           	1.	TransactionScopeOption.Required:
           	•	这个参数指定了事务的范围行为。TransactionScopeOption 是一个枚举，有以下选项：
           	•	Required：如果当前代码块中已经存在一个事务，则加入该事务；如果不存在，则创建一个新的事务。通常情况下，这是最常用的选项。
           	•	RequiresNew：无论当前代码块中是否存在事务，都会创建一个新的事务。该事务与现有事务无关。
           	•	Suppress：即使当前代码块中存在事务，也不会参与任何事务（禁用事务处理）。
           代码中，TransactionScopeOption.Required 意味着如果外部已经存在事务，它会加入该事务；如果不存在事务，它将创建一个新的事务。
           	2.	new TransactionOptions:
           	•	这是一个结构体，用于指定事务的详细设置，例如隔离级别和超时时间。它包含两个主要属性：
           	•	IsolationLevel：指定事务的隔离级别，控制并发事务之间的相互影响。隔离级别定义了事务可以看到其他事务未提交的更改的程度。常见的隔离级别有：
           	•	ReadUncommitted：允许读取未提交的更改。并发性能最高，但可能导致脏读。
           	•	ReadCommitted：只能读取已提交的更改，防止脏读。这是 SQL Server 的默认隔离级别。
           	•	RepeatableRead：保证在事务期间读取的数据不会被修改。可以防止不可重复读。
           	•	Serializable：最高级别的隔离，事务之间完全隔离，防止幻读和不可重复读。
           代码中，IsolationLevel.ReadCommitted 选择的是只允许读取已提交的数据，这样可以防止脏读。
           	•	Timeout（可选）：指定事务的超时时间。如果事务在这个时间内没有完成，它会被自动回滚。默认值通常为 1 分钟。
           	3.	TransactionScopeAsyncFlowOption.Enabled:
           	•	TransactionScopeAsyncFlowOption 控制事务在异步代码中的行为。默认情况下，事务范围不支持异步代码的流动。
           	•	Enabled：允许事务在异步代码中传播。当你在 async 和 await 方法中使用事务时，必须启用这个选项，否则异步操作可能会导致事务范围不一致或者丢失。
           代码中，TransactionScopeAsyncFlowOption.Enabled 确保事务能够在异步方法中继续保持一致性。
         */
        using (var scope = new TransactionScope(
                   TransactionScopeOption.Required, 
                   new TransactionOptions
                   {
                       IsolationLevel = IsolationLevel.ReadCommitted
                   },
                   TransactionScopeAsyncFlowOption.Enabled))
        {
            try
            {
                //创建第一个context
                using (var context1 = new InBaseDescribeContext(options1))
                {
                    //查询
                    var context1List = context1.BaseDescribeObj
                        .OrderBy(b => b.F_BASE_TYPE)
                        .ToList();

                    //第一次保存操作
                    context1.BaseDescribeObj.Add(new InBaseDescribe
                    {
                        F_BASE_TYPE = 1,
                        F_BASE_STATE_NAME = "test1"
                    });
                    context1.SaveChanges();
                }

                //创建第二个context
                using (var context2 = new CompanyInfoContext(options2))
                {
                    //查询
                    var context2List = context2.CompanyInfoObj
                        .OrderBy(b => b.F_CREATE_TIME)
                        .ToList();

                    //第二次保存操作
                    context2.CompanyInfoObj.Add(new CompanyInfo
                    {
                        F_COMPANY_NAME = "test2"
                    });
                    context2.SaveChanges();
                }

                //提交 TransactionScope 事务
                scope.Complete();
            }
            catch (Exception ex)
            {
                //这里不需要 rollback，TransactionScope 会自动回滚未完成的事务
                Console.WriteLine("事务回滚: " + ex.Message);
            }
        }
    }