Skip to content

新建数据库

创建数据库

选择 Mysql 创建应用库,其它数据库操作类似。

可直接在 Mysql 中手动创建 appdb, 也可通过 DbConfig.json 配置自动创建 appdb

自动创建数据库配置如下:

json
// DbConfig.json
{
    //监听同步结构脚本,默认不监听
    "syncStructureSql": false,
    //开启建库
    "createDb": true,
    //建库连接字符串,修改Server=localhost; Port=3306; Uid=root; Pwd=pwd
    //不要修改Database=mysql,只有通过默认系统库才能连接数据库执行建库脚本
    "createDbConnectionString": "Server=localhost; Port=3306; Database=mysql; Uid=root; Pwd=pwd; Charset=utf8mb4;",
    //建库脚本,复杂建库脚本可放到createdbsql.txt中
    "createDbSql": "CREATE DATABASE `appdb` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci'",
    //同步结构
    "syncStructure": true,
    //同步数据
    "syncData": true
}

以下配置按需修改:

json
// DbConfig.json
{
    //同步更新数据,谨慎开启,不需要可以关闭
    "sysUpdateData": true,
    //同步数据地址,默认InitData/Admin,一般不调整不修改
    //"SyncDataPath": "InitData/Admin",
    //同步数据包含表,指定表同步,不填同步所有表
    //如["ad_api", "ad_view", "ad_permission", "ad_permission_api"]
    "syncDataIncludeTables": [],
    //同步数据排除表,指定表不同步,如["ad_user"]
    "syncDataExcludeTables": [],
    //同步数据的操作用户信息
    "syncDataUser": {
        "id": 161223411986501,
        "userName": "admin",
        "tenantId": 161223412138053
    }
}

提示

FreeSql数据库连接字符串示例

https://freesql.net/guide/getting-started.html#connectionstrings

数据库连接字符串

https://www.connectionstrings.com

Sqlite数据库无需配置建库,只需配置连库则会自动建库

json
// DbConfig.json
{
    "type": "Sqlite",
    "connectionString": "Data Source=|DataDirectory|\\appdb.db; Pooling=true;Min Pool Size=1"
}

Sqlite数据库默认在 bin\Debug\net8.0 下创建 appdb.db

连接数据库

选择 Mysql 连接应用库

连接MySql应用库配置如下:

json
// DbConfig.json
{
  //数据库类型
  "type": "MySql",
  //连接字符串,修改Server=localhost; Port=3306; Database=appdb; Uid=root; Pwd=pwd;
  "connectionString": "Server=localhost; Port=3306; Database=appdb; Uid=root; Pwd=pwd; Charset=utf8mb4;",
  //指定程序集,连接异常或使用TIDB时需要配置该项
  //FreeSql.MySql.MySqlProvider`1,FreeSql.Provider.MySqlConnector
  "providerType": "",
}

多数据库

dbs节点下配置,结构同dbconfig.json

如连接MySql权限库,配置如下:

json
// DbConfig.json
{
    "dbs": [
        {
            //权限库
            "key": "admindb",
            //程序集名称,自动获取实体表
            "assemblyNames": [ "ZhonTai.Admin" ],

            //监听所有操作
            "monitorCommand": false,
            //监听Curd操作
            "curd": true,

            //建库
            "createDb": false,
            //SqlServer,PostgreSQL,Oracle,OdbcOracle,OdbcSqlServer,OdbcMySql,OdbcPostgreSQL,Odbc,OdbcDameng,MsAccess
            //建库连接字符串
            "createDbConnectionString": "Server=localhost; Port=3306; Database=mysql; Uid=root; Pwd=pwd; Charset=utf8mb4;",
            //建库脚本,复杂建库脚本可放到createdbsql.txt中
            "createDbSql": "CREATE DATABASE `admindb` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci'",

            //同步结构
            "syncStructure": true,
            //同步数据
            "syncData": true,

            //数据库类型
            "type": "MySql",

            //连接字符串
            "connectionString": "Server=localhost; Port=3306; Database=admindb; Uid=root; Pwd=pwd; Charset=utf8mb4;",

            //指定程序集
            //FreeSql.MySql.MySqlProvider`1,FreeSql.Provider.MySqlConnector
            "providerType": "",

            //读写分离从库列表
            "slaveList": [
                //{
                //  权重
                //  "Weight": 1,
                //  连接字符串
                //  "ConnectionString": "Data Source=|DataDirectory|\\admindb.db; Pooling=true;Min Pool Size=1"
                //}
            ]
        }
    ]
}

如何使用多数据库

1、在MyCompanyName.MyProjectName.Api/Core/Consts下的DbKeys.cs类中新增权限库注册键AdminDb

cs
using System.ComponentModel;

namespace MyCompanyName.MyProjectName.Api.Core.Consts;

/// <summary>
/// 数据库键名
/// </summary>
public class DbKeys
{
    /// <summary>
    /// 应用库注册键
    /// </summary>
    [Description("应用库注册键")]
    public static string AppDb { get; set; } = "appdb";

    /// <summary>
    /// 权限库注册键
    /// </summary>
    [Description("权限库注册键")]
    public static string AdminDb { get; set; } = "admindb";
}

2、直接通过FreeSqlCloud或实体仓储获得多数据库

  • FreeSqlCloud
cs
public class ModuleService : BaseService, IDynamicApi
{
    private readonly Lazy<FreeSqlCloud> _freeSqlCloud;

    public ModuleService(Lazy<FreeSqlCloud> freeSqlCloud)
    {
        _freeSqlCloud = freeSqlCloud;
    }

    public void GetData()
    {
        var fsql = _freeSqlCloud.Value.Use(DbKeys.AdminDb);
    }
}
  • 实体仓储 IModuleRepository
cs
public class MyAdminRepositoryBase<TEntity> : RepositoryBase<TEntity> where TEntity : class
{
    public AppRepositoryBase(UnitOfWorkManagerCloud uowm) : base(DbKeys.AdminDb, uowm)
    {

    }
}

public class ModuleRepository : MyAdminRepositoryBase<ModuleEntity>, IModuleRepository
{
    public ModuleRepository(UnitOfWorkManagerCloud uowm) : base(uowm)
    {
    }
}

[Order(1010)]
[DynamicApi(Area = ApiConsts.AreaName)]
public class ModuleService : BaseService, IModuleService, IDynamicApi
{
    private readonly Lazy<IModuleRepository> _moduleRepository;

    public ModuleService(Lazy<IModuleRepository> moduleRepository)
    {
        _moduleRepository = moduleRepository;
    }
}
  • 泛型实体仓储
public interface IAppBaseRepository<TEntity>:IRepositoryBase<TEntity>
    where TEntity : class
{
}

public class AppBaseRepository<TEntity> : RepositoryBase<TEntity>, IAppBaseRepository<TEntity>,IRegisterIOC where TEntity : class
{
    public AppBaseRepository(UnitOfWorkManagerCloud uowm) : base(ApplicationConsts.DbKey, uowm)
    {

    }
}

[Order(1010)]
[DynamicApi(Area = ApiConsts.AreaName)]
public class ModuleService : BaseService, IModuleService, IDynamicApi
{
    private readonly IAppBaseRepository<Entity> _repo;

    public ModuleService (IAppBaseRepository<Entity> repo)
    {
        _repo = repo;
    }
}

生成数据

开启生成数据前先关闭 syncStructuresyncDatacreateDb

生成数据到InitData/Appjson文件,配置如下:

json
// DbConfig.json
 {
    //建库
    "createDb": false,
    //同步结构
    "syncStructure": false,
    //同步数据
    "syncData": false,
    //生成数据
    "generateData": true
 }

提示

项目初始化不开启生成数据

发布生产环境前,如果开发环境有配置数据需要更新数据包,可以开启生成数据包,使用完记得关闭

MyCompanyName.MyProjectName.Api/Core 文件夹下新建 Data 文件夹,新增 CustomGenerateData.cs

cs
using System.Linq;
using System.Threading.Tasks;
using ZhonTai.Admin.Core.Configs;
using ZhonTai.Admin.Domain.Tenant;
using MyCompanyName.MyProjectName.Api.Domain.Module;
using ZhonTai.Admin.Core.Db.Data;

namespace ZhonTai.Admin.Repositories;

public class CustomGenerateData : GenerateData, IGenerateData
{
    public virtual async Task GenerateDataAsync(IFreeSql db, AppConfig appConfig)
    {
        var isTenant = appConfig.Tenant;

        var modules = await db.Queryable<ModuleEntity>().ToListAsync();

        if (isTenant)
        {
            var tenantIds = await db.Queryable<TenantEntity>().ToListAsync(a => a.Id);
            SaveDataToJsonFile<ModuleEntity>(modules.Where(a => tenantIds.Contains(a.TenantId.Value)));
        }

        SaveDataToJsonFile<ModuleEntity>(modules, isTenant, path: "InitData/App");
    }
}

同步数据

同步数据前先关闭 generateData

同步 InitData/App/*.json 数据到数据库中,配置如下:

json
// DbConfig.json
 {
    //同步结构
    "syncStructure": true,
    //同步数据
    "syncData": true,
    //同步更新数据
    "sysUpdateData": true,
    //生成数据
    "generateData": false
 }

MyCompanyName.MyProjectName.Api/Core 文件夹下新建 Data 文件夹,新增 CustomSyncData.cs

cs
using System.Threading.Tasks;
using ZhonTai.Admin.Core.Configs;
using MyCompanyName.MyProjectName.Api.Domain.Module;
using ZhonTai.Admin.Core.Db.Data;
using FreeSql;
using System.Linq;
using System;

namespace ZhonTai.Admin.Repositories;

public class CustomSyncData : SyncData, ISyncData
{
    /// <summary>
    /// 初始化模块
    /// </summary>
    /// <param name="db"></param>
    /// <param name="unitOfWork"></param>
    /// <param name="dbConfig"></param>
    /// <returns></returns>
    private async Task InitModuleAsync(IFreeSql db, IRepositoryUnitOfWork unitOfWork, DbConfig dbConfig)
    {
        var tableName = GetTableName<ModuleEntity>();
        try
        {
            if (!IsSyncData(tableName, dbConfig))
            {
                return;
            }

            var rep = db.GetRepository<ModuleEntity>();
            rep.UnitOfWork = unitOfWork;

            //数据列表
            var dataList = GetData<ModuleEntity>(path: "InitData/App");

            if (!(dataList?.Length > 0))
            {
                Console.WriteLine($"table: {tableName} import data []");
                return;
            }

            //查询
            var ids = dataList.Select(e => e.Id).ToList();
            var recordList = await rep.Where(a => ids.Contains(a.Id)).ToListAsync();

            //新增
            var recordIds = recordList.Select(a => a.Id).ToList();
            var insertDataList = dataList.Where(a => !recordIds.Contains(a.Id));
            if (insertDataList.Any())
            {
                await rep.InsertAsync(insertDataList);
            }

            //修改
            if (dbConfig.SysUpdateData && recordList?.Count > 0)
            {
                var updateDataList = dataList.Where(a => recordIds.Contains(a.Id));
                await rep.UpdateAsync(updateDataList);
            }

            Console.WriteLine($"table: {tableName} sync data succeed");
        }
        catch (Exception ex)
        {
            var msg = $"table: {tableName} sync data failed.\n{ex.Message}";
            Console.WriteLine(msg);
            throw new Exception(msg);
        }
    }

    public virtual async Task SyncDataAsync(IFreeSql db, DbConfig dbConfig = null, AppConfig appConfig = null)
    {
        using var unitOfWork = db.CreateUnitOfWork();

        try
        {
            var isTenant = appConfig.Tenant;

            await InitModuleAsync(db, unitOfWork, dbConfig);

            unitOfWork.Commit();
        }
        catch (Exception)
        {
            unitOfWork.Rollback();
            throw;
        }
    }
}