1.思路

通过获取到数据库表的所有字段，根据是否显示该字段来进行保存，生成动态页面的时候根据保存字段来显示表头和分页数据。

2.实现步骤

2.1 获取所有数据库

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

对应C#语句（通过在ABP中引入dapper实现）：

        /// <summary>
        /// 获取数据数据库
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<string>> GetDataBases()
        {
            return await (await GetDbConnectionAsync()).QueryAsync<string>(@"SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA", transaction: await GetDbTransactionAsync());
        }

2.2 获取指定数据库所有表数据

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables where TABLE_SCHEMA =‘数据库名称’

对应C#语句（通过在ABP中引入dapper实现）：

        /// <summary>
        /// 获取指定数据库所有表数据
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <returns></returns>
        public async Task<IEnumerable<string>> GetTablesByDBName(string dbName)
        {
            return await (await GetDbConnectionAsync()).QueryAsync<string>(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables where TABLE_SCHEMA =@DBName", new { DBName = dbName }, transaction: await GetDbTransactionAsync());
        }

2.3 获取指定数据库表数据

SELECT COLUMN_NAME as ColumnName,
       IS_NULLABLE as IsNullable,
       DATA_TYPE as DataType,
       CHARACTER_MAXIMUM_LENGTH as CharMaxLen,
       COLUMN_COMMENT as ColumnComment
       FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME =‘表名称’ AND TABLE_SCHEMA =‘数据库名称’ order by ordinal_position;

对应C#语句（通过在ABP中引入dapper实现）：

        /// <summary>
        /// 获取指定数据库表数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public async Task<IEnumerable<TableInfoDto>> GetTableInfoByTableName(string dbName, string tableName)
        {
            return await (await GetDbConnectionAsync()).QueryAsync<TableInfoDto>(@"SELECT 
                    COLUMN_NAME as ColumnName,
                    IS_NULLABLE as IsNullable,
                    DATA_TYPE as DataType,
                    CHARACTER_MAXIMUM_LENGTH as CharMaxLen,
                    COLUMN_COMMENT as ColumnComment
                    FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME =@TableName AND TABLE_SCHEMA =@DBName order by ordinal_position;", new { DBName = dbName , TableName = tableName }, transaction: await GetDbTransactionAsync());
        }

    /// <summary>
    /// 数据库表信息
    /// </summary>
    public class TableInfoDto
    {
        public  string ColumnName { get; set; }
        public string IsNullable { get; set; }
        public string DataType { get; set; }
        public string CharMaxLen { get; set; }
        public string ColumnComment { get; set; }
    }

2.4 保存勾选的字段信息

2.4.1 建表

    /// <summary>
    /// 动态页面配置表
    /// </summary>
    public class DynamicPageSetting: AuditedAggregateRoot<Guid>
    {
        /// <summary>
        /// 页面名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 数据标识
        /// </summary>
        public string Tag { get; set; }

        /// <summary>
        /// 描述
        /// </summary>
        public string Description { get; set; }

        /// <summary>
        /// sql
        /// </summary>
        public string SqlRaw { get; set; }

        /// <summary>
        /// 页面字段
        /// </summary>
        public string JsonRaw { get; set; }

        /// <summary>
        /// 状态
        /// </summary>
        public int Status { get; set; }
    }

2.4.2 保存

保存的数据格式如下：

c#保存语句：

 public async Task SaveSettingAsync(DynamicPageSetting input)
        {
            var dbConn = await GetDbConnectionAsync();
            var tran = await GetDbTransactionAsync();
            var item = await dbConn.QueryFirstOrDefaultAsync<DynamicPageSetting>(@"select Id,Name,Tag,Description,SqlRaw,JsonRaw,Status,ConcurrencyStamp,CreationTime,CreatorId,LastModificationTime,LastModifierId from EnyDynamicPageSetting where 1=1 and Tag=@Tag ", new { Tag = input.Tag }, transaction: tran);
            if (item is null)
            {
                var sql = "insert into EnyDynamicPageSetting(Id,Name,Tag,Description,SqlRaw,JsonRaw,ExtraProperties,ConcurrencyStamp,CreationTime,CreatorId) values(@Id,@Name,@Tag,@Description,@SqlRaw,@JsonRaw,@ExtraProperties,@ConcurrencyStamp,@CreationTime,@CreatorId)";
                await dbConn.ExecuteAsync(sql, new
                {
                    Id = _guidGenerator.Create(),
                    Name = input.Name,
                    Tag = input.Tag,
                    Description = input.Description,
                    SqlRaw = input.SqlRaw,
                    JsonRaw = input.JsonRaw,
                    ExtraProperties = "{}",
                    ConcurrencyStamp = input.ConcurrencyStamp, 
                    CreationTime = DateTime.Now,
                    CreatorId = input.CreatorId
                }, transaction: tran);
            }
            else
            {
                var sql = "update EnyDynamicPageSetting set Name=@Name,Description=@Description,SqlRaw=@SqlRaw,JsonRaw=@JsonRaw,LastModifierId=@LastModifierId,LastModificationTime=@LastModificationTime where Tag=@Tag ";
                await dbConn.ExecuteAsync(sql, new
                {
                    Name = input.Name,
                    Tag = input.Tag,
                    Description = input.Description,
                    SqlRaw = input.SqlRaw,
                    JsonRaw = input.JsonRaw,
                    LastModificationTime = DateTime.Now,
                    LastModifierId = input.LastModifierId
                }, transaction: tran);
            }
        }

2.4.3 根据页面标识获取表头数据

        /// <summary>
        ///根据页面标识获取
        /// </summary>
        /// <param name="pageName">页面标识</param>
        /// <returns></returns>
        public async Task<DynamicPageSetting> GetByTagAsync(string pageTag)
        {
            return await(await GetDbConnectionAsync()).QueryFirstOrDefaultAsync<DynamicPageSetting>(@"select Id,Name,Tag,Description,SqlRaw,JsonRaw,Status,ConcurrencyStamp,CreationTime,CreatorId,LastModificationTime,LastModifierId from EnyDynamicPageSetting where 1=1 and Tag = @Tag", new { Tag = pageTag}, transaction: await GetDbTransactionAsync());
        }

根据返回的jsonRow字段来绘制表头

2.4.4 根据页面标识获取分页数据

public async Task<Tuple<int, IEnumerable<DynamicClass>>> Query(string tag, int pageIndex, int pageSize)
        {
            var dbConn = await GetDbConnectionAsync();
            var tran = await GetDbTransactionAsync();
            var item = await dbConn.QueryFirstOrDefaultAsync<DynamicPageSetting>(@"select Id,Name,Tag,Description,SqlRaw,JsonRaw,Status,ConcurrencyStamp,CreationTime,CreatorId,LastModificationTime,LastModifierId from EnyDynamicPageSetting where 1=1 and Tag=@Tag ", new { Tag = tag }, transaction: tran);

            if (item is null)
            {
                throw new Volo.Abp.UserFriendlyException("数据标识错误!");
            }

            var totalNumber = dbConn.QueryFirstOrDefault<int>("SELECT COUNT(1) FROM "+item.SqlRaw.Replace("SELECT * FROM", ""));

            var sql = $"{item.SqlRaw} limit {(pageIndex - 1) * pageSize} ,{pageSize}";
            var dt = new System.Data.DataTable("tmp_dt");
            var reader = await dbConn.ExecuteReaderAsync(sql);
            dt.Load(reader);

            var dps = new List<DynamicProperty>();
            foreach (System.Data.DataColumn col in dt.Columns)
            {
                var isDbNull = col.AllowDBNull;
                if (isDbNull && !col.DataType.FullName.Equals("System.String"))
                {
                    dps.Add(new DynamicProperty(col.ColumnName, typeof(Nullable<>).MakeGenericType(col.DataType)));
                }
                else
                {
                    dps.Add(new DynamicProperty(col.ColumnName, col.DataType));
                }
            }

            var dtType = DynamicClassFactory.CreateType(dps);
            var list = new List<DynamicClass>();
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var dynamicClass = Activator.CreateInstance(dtType) as DynamicClass;
                if (dynamicClass is null)
                {
                    throw new Volo.Abp.UserFriendlyException("动态创建class出错了!");
                }

                object val;
                foreach (var dp in dps)
                {
                    val = dt.Rows[i][dp.Name];
                    if (val.Equals(DBNull.Value))
                    {
                        val = default;
                    }

                    dynamicClass.SetDynamicPropertyValue(dp.Name, val);
                }

                list.Add(dynamicClass);
            }

            return new Tuple<int, IEnumerable<DynamicClass>>(totalNumber, list);
        }

3.完整仓储层和服务层代码

仓储层：

 public class EfCoreDynamicPageSettingRepository : DapperRepository<EnergyDbContext>, IDynamicPageSettingRepository, ITransientDependency
    {
        private readonly IGuidGenerator _guidGenerator;

        public EfCoreDynamicPageSettingRepository(IDbContextProvider<EnergyDbContext> dbContextProvider, IGuidGenerator guidGenerator) : base(dbContextProvider)
        {
            _guidGenerator = guidGenerator;
        }

        /// <summary>
        /// 获取数据数据库
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<string>> GetDataBases()
        {
            return await (await GetDbConnectionAsync()).QueryAsync<string>(@"SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA", transaction: await GetDbTransactionAsync());
        }

        /// <summary>
        /// 获取数据数据库表
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <returns></returns>
        public async Task<IEnumerable<string>> GetTablesByDBName(string dbName)
        {
            return await (await GetDbConnectionAsync()).QueryAsync<string>(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables where TABLE_SCHEMA =@DBName", new { DBName = dbName }, transaction: await GetDbTransactionAsync());
        }

        /// <summary>
        /// 获取数据数据库表信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public async Task<IEnumerable<TableInfoDto>> GetTableInfoByTableName(string dbName, string tableName)
        {
            return await (await GetDbConnectionAsync()).QueryAsync<TableInfoDto>(@"SELECT 
                    COLUMN_NAME as ColumnName,
                    IS_NULLABLE as IsNullable,
                    DATA_TYPE as DataType,
                    CHARACTER_MAXIMUM_LENGTH as CharMaxLen,
                    COLUMN_COMMENT as ColumnComment
                    FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME =@TableName AND TABLE_SCHEMA =@DBName order by ordinal_position;", new { DBName = dbName , TableName = tableName }, transaction: await GetDbTransactionAsync());
        }


        /// <summary>
        ///根据页面标识获取
        /// </summary>
        /// <param name="pageName">页面标识</param>
        /// <returns></returns>
        public async Task<DynamicPageSetting> GetByTagAsync(string pageTag)
        {
            return await(await GetDbConnectionAsync()).QueryFirstOrDefaultAsync<DynamicPageSetting>(@"select Id,Name,Tag,Description,SqlRaw,JsonRaw,Status,ConcurrencyStamp,CreationTime,CreatorId,LastModificationTime,LastModifierId from EnyDynamicPageSetting where 1=1 and Tag = @Tag", new { Tag = pageTag}, transaction: await GetDbTransactionAsync());
        }

        public async Task SaveSettingAsync(DynamicPageSetting input)
        {
            var dbConn = await GetDbConnectionAsync();
            var tran = await GetDbTransactionAsync();
            var item = await dbConn.QueryFirstOrDefaultAsync<DynamicPageSetting>(@"select Id,Name,Tag,Description,SqlRaw,JsonRaw,Status,ConcurrencyStamp,CreationTime,CreatorId,LastModificationTime,LastModifierId from EnyDynamicPageSetting where 1=1 and Tag=@Tag ", new { Tag = input.Tag }, transaction: tran);
            if (item is null)
            {
                var sql = "insert into EnyDynamicPageSetting(Id,Name,Tag,Description,SqlRaw,JsonRaw,ExtraProperties,ConcurrencyStamp,CreationTime,CreatorId) values(@Id,@Name,@Tag,@Description,@SqlRaw,@JsonRaw,@ExtraProperties,@ConcurrencyStamp,@CreationTime,@CreatorId)";
                await dbConn.ExecuteAsync(sql, new
                {
                    Id = _guidGenerator.Create(),
                    Name = input.Name,
                    Tag = input.Tag,
                    Description = input.Description,
                    SqlRaw = input.SqlRaw,
                    JsonRaw = input.JsonRaw,
                    ExtraProperties = "{}",
                    ConcurrencyStamp = input.ConcurrencyStamp, 
                    CreationTime = DateTime.Now,
                    CreatorId = input.CreatorId
                }, transaction: tran);
            }
            else
            {
                var sql = "update EnyDynamicPageSetting set Name=@Name,Description=@Description,SqlRaw=@SqlRaw,JsonRaw=@JsonRaw,LastModifierId=@LastModifierId,LastModificationTime=@LastModificationTime where Tag=@Tag ";
                await dbConn.ExecuteAsync(sql, new
                {
                    Name = input.Name,
                    Tag = input.Tag,
                    Description = input.Description,
                    SqlRaw = input.SqlRaw,
                    JsonRaw = input.JsonRaw,
                    LastModificationTime = DateTime.Now,
                    LastModifierId = input.LastModifierId
                }, transaction: tran);
            }
        }

        public async Task<IEnumerable<DynamicPageSample>> GetAllAsync()
        {
            return await(await GetDbConnectionAsync()).QueryAsync<DynamicPageSample>(@"select Name,Tag from EnyDynamicPageSetting where 1=1 ", transaction: await GetDbTransactionAsync());
        }

        public async Task<Tuple<int, IEnumerable<DynamicClass>>> Query(string tag, int pageIndex, int pageSize)
        {
            var dbConn = await GetDbConnectionAsync();
            var tran = await GetDbTransactionAsync();
            var item = await dbConn.QueryFirstOrDefaultAsync<DynamicPageSetting>(@"select Id,Name,Tag,Description,SqlRaw,JsonRaw,Status,ConcurrencyStamp,CreationTime,CreatorId,LastModificationTime,LastModifierId from EnyDynamicPageSetting where 1=1 and Tag=@Tag ", new { Tag = tag }, transaction: tran);

            if (item is null)
            {
                throw new Volo.Abp.UserFriendlyException("数据标识错误!");
            }

            var totalNumber = dbConn.QueryFirstOrDefault<int>("SELECT COUNT(1) FROM "+item.SqlRaw.Replace("SELECT * FROM", ""));

            var sql = $"{item.SqlRaw} limit {(pageIndex - 1) * pageSize} ,{pageSize}";
            var dt = new System.Data.DataTable("tmp_dt");
            var reader = await dbConn.ExecuteReaderAsync(sql);
            dt.Load(reader);

            var dps = new List<DynamicProperty>();
            foreach (System.Data.DataColumn col in dt.Columns)
            {
                var isDbNull = col.AllowDBNull;
                if (isDbNull && !col.DataType.FullName.Equals("System.String"))
                {
                    dps.Add(new DynamicProperty(col.ColumnName, typeof(Nullable<>).MakeGenericType(col.DataType)));
                }
                else
                {
                    dps.Add(new DynamicProperty(col.ColumnName, col.DataType));
                }
            }

            var dtType = DynamicClassFactory.CreateType(dps);
            var list = new List<DynamicClass>();
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var dynamicClass = Activator.CreateInstance(dtType) as DynamicClass;
                if (dynamicClass is null)
                {
                    throw new Volo.Abp.UserFriendlyException("动态创建class出错了!");
                }

                object val;
                foreach (var dp in dps)
                {
                    val = dt.Rows[i][dp.Name];
                    if (val.Equals(DBNull.Value))
                    {
                        val = default;
                    }

                    dynamicClass.SetDynamicPropertyValue(dp.Name, val);
                }

                list.Add(dynamicClass);
            }

            return new Tuple<int, IEnumerable<DynamicClass>>(totalNumber, list);
        }

    }

服务层：

 public class DynamicPageService : EnergyAppService, IDynamicPageService
    {
        private readonly IDynamicPageSettingRepository _dynamicPageSettingRepository;

        public DynamicPageService(IDynamicPageSettingRepository dynamicPageSettingRepository)
        {
            _dynamicPageSettingRepository = dynamicPageSettingRepository;
        }

        /// <summary>
        /// 获取数据数据库
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<string>> GetDatabases()
        {
            return await _dynamicPageSettingRepository.GetDataBases();
        }

        /// <summary>
        /// 获取数据数据库表
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <returns></returns>
        public async Task<IEnumerable<string>> GetTables(string dbName)
        {
            return await _dynamicPageSettingRepository.GetTablesByDBName(dbName);
        }

        /// <summary>
        /// 获取数据数据库表信息
        /// </summary>
        /// <param name="dbName">数据库表名</param>
        /// <param name="tableName">数据库表名</param>
        /// <returns></returns>
        public async Task<IEnumerable<TableInfoDto>> GetTable(string dbName, string tableName)
        {
            return await _dynamicPageSettingRepository.GetTableInfoByTableName(dbName, tableName);
        }

        /// <summary>
        /// 保存或更新
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        [Authorize(EnergyPermissions.DynamicPagesPermission.Create)]
        public async Task Save(SaveDynamicPageSettingReq req)
        {
            var existPagePoint = await _dynamicPageSettingRepository.GetByTagAsync(req.Tag);
            if (existPagePoint is not null)
            {
                throw new UserFriendlyException("数据标识已存在!");
            }
            var item = new DynamicPageSetting
            {
                Name = req.Name,
                Tag = req.DBName + req.Tag,
                Description = req.Description,
                SqlRaw = "SELECT * FROM " + req.DBName + "." + req.TableName,
                JsonRaw = req.JsonRaw,
                Status = 1
            };
            await _dynamicPageSettingRepository.SaveSettingAsync(item);
        }

        /// <summary>
        /// 获取所有页面
        /// </summary>
        /// <returns></returns>
        public async Task<GetAllRes> GetAll()
        {
            var items = await this._dynamicPageSettingRepository.GetAllAsync();
            var rows = ObjectMapper.Map<IEnumerable<DynamicPageSample>, IEnumerable<GetAllRes.DynamicPage>>(items);
            return new GetAllRes(rows.Count(), rows);
        }

        /// <summary>
        /// 通过页面标识获取数据
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        public async Task<GetByTagRes> GetByTag(GetByTagReq req)
        {
            var result = await this._dynamicPageSettingRepository.GetByTagAsync(req.Tag);
            return ObjectMapper.Map<DynamicPageSetting, GetByTagRes>(result);
        }

        /// <summary>
        /// 根据动态页面标识生成分页数据
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        public async Task<QueryRes> GetData(GetDataByTagReq req)
        {
            var result = await this._dynamicPageSettingRepository.GetByTagAsync(req.Tag) ?? throw new UserFriendlyException("未检索到信息!");

            var data = await this._dynamicPageSettingRepository.Query(req.Tag, req.PageIndex, req.PageSize);
            return new QueryRes(data.Item1, new List<object>(data.Item2));
        }
    }