Database

The Admin.Core source code project uses sqlite database by default. After the project starts, the database will be automatically generated in the bin\Debug\net10.0 directory. If you need to use another database, you can modify the ConfigCenter/DbConfig.json configuration file.

Using MySQL to create a bizdb business database as an example, other database operations are similar. All configurations below are in DbConfig.json.DbConfig.

Install Database Management Tool

Visit the Beekeeper Studio official website and click Download for Windows

On the download page, click Skip to the download and select the version suitable for your system to download and install.

Create Database

{
    // Listen for sync structure scripts, disabled by default
    "syncStructureSql": false,
    // Enable database creation
    "createDb": true,
    // Database creation connection string, modify Server=localhost; Port=3306; Uid=root; Pwd=pwd
    // Do not modify Database=mysql; only the default system database can connect to execute database creation scripts
    "createDbConnectionString": "Server=localhost; Port=3306; Database=mysql; Uid=root; Pwd=pwd; Charset=utf8mb4;",
    // Database creation script, complex creation scripts can be placed in createdbsql.txt
    "createDbSql": "CREATE DATABASE `bizdb` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci'"
}

Note

FreeSql database connection string examples

https://freesql.net/guide/#connectionstrings

Third-party database connection strings

https://www.connectionstrings.com

Sqlite database does not need database creation configuration; just configure the connection string and it will auto-create

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

Sqlite database will create bizdb.db in the bin\Debug\net9.0 folder

Connect to Database

{
  // Database type
  "type": "MySql",
  //default_authentication_plugin = caching_sha2_password 
  //MySql "Server=localhost; Port=3306; Database=bizdb; Uid=root; Pwd=pwd; Charset=utf8mb4;Min pool size=1;Allow User Variables=True"
  //default_authentication_plugin = mysql_native_password
  //MySql "Server=localhost; Port=3306; Database=bizdb; Uid=root; Pwd=pwd; Charset=utf8mb4;SslMode=none;Min pool size=1;Allow User Variables=True"
  // Connection string, modify Server=localhost; Port=3306; Database=bizdb; Uid=root; Pwd=pwd;
  "connectionString": "Server=localhost; Port=3306; Database=bizdb; Uid=root; Pwd=pwd; Charset=utf8mb4;Min pool size=1;Allow User Variables=True",
  // Specify assembly, required when connection fails or using TIDB
  //FreeSql.MySql.MySqlProvider`1,FreeSql.Provider.MySqlConnector
  "providerType": ""
}

Note

For MySQL 8.0 and above, default_authentication_plugin defaults to caching_sha2_password. Select "Server=localhost; Port=3306; Database=bizdb; Uid=root; Pwd=pwd; Charset=utf8mb4;Min pool size=1;Allow User Variables=True".

Sync Structure and Initialize Data

{
    // Assembly name, auto-fetch entity tables; if empty, configure via ConfigureFreeSql
    "assemblyNames": [ "MyCompanyName.MySys.Api" ],
    // Specify entity database list; if empty, sync all database table entities
    "includeEntityDbs": [],
    // Exclude entity database list; specified tables will not be synced
    "excludeEntityDbs": [],
    // Sync structure
    "syncStructure": true,
    // Sync structure batch entity count
    "syncStructureEntityBatchSize": 1,
    // Sync data batch processing size, default 500 records per batch; too large may cause memory overflow
    "syncDataBatchSize": 500,
    // Sync data
    "syncData": true,
    // Sync update data, enable with caution; disable if not needed
    "syncUpdateData": true,
    // Sync data path, default InitData/App; generally no need to modify
    //"SyncDataPath": "InitData/App",
    // Sync all tables
    // Sync specified tables ["base_api", "base_view", "base_permission", "base_permission_api"]
    // Sync data include tables; specified tables will be synced, if empty sync all tables
    "syncDataIncludeTables": [],
    // Sync data exclude tables ["base_user"]
    // Sync data exclude tables; specified tables will not be synced
    "syncDataExcludeTables": [],
    // Sync data operation user
    "syncDataUser": {
      "id": 161223411986501,
      "userName": "admin",
      "name": "Admin",
      "tenantId": 161223412138053
    },
}

Multiple Databases

Multiple Database Configuration

{
// Multiple databases
"dbs": [{
    // Database registration key: module database
    "key": "moduledb",
    // Assembly name, auto-fetch entity tables
    "assemblyNames": [ "MyCompanyName.MyModuleName.Api.Contracts" ],
    // Specify entity database list; if empty, sync all database table entities
    "includeEntityDbs": [],
    // Exclude entity database list; specified tables will not be synced
    "excludeEntityDbs": [],

    // Monitor all operations
    "monitorCommand": false,
    // Monitor CRUD operations
    "curd": true,
    // Monitor sync structure scripts
    "syncStructureSql": false,
    // Monitor sync data CRUD operations
    "syncDataCurd": false,

    // Create database
    "createDb": true,
    // SqlServer, PostgreSQL, Oracle, OdbcOracle, OdbcSqlServer, OdbcMySql, OdbcPostgreSQL, Odbc, OdbcDameng, MsAccess
    // Database creation connection string
    "createDbConnectionString": "",
    // Database creation script; complex creation scripts can be placed in createdbsql.txt
    "createDbSql": "",

    // Sync structure
    "syncStructure": true,
    // Sync structure batch entity count
    "syncStructureEntityBatchSize": 1,
    // Sync data batch processing size, default 500 records per batch; too large may cause memory overflow
    "syncDataBatchSize": 500,
    // Sync data
    "syncData": true,
    // Sync update data, use with caution in production; ensure table data is up-to-date before enabling
    "syncUpdateData": false,
    // Sync data path
    "SyncDataPath": "InitData/App",
    // Sync data include tables; specified tables will be synced, if empty sync all tables
    "syncDataIncludeTables": [],
    // Sync data exclude tables; specified tables will not be synced
    "syncDataExcludeTables": [],
    // Sync data operation user
    "syncDataUser": {
    "id": 161223411986501,
    "userName": "admin",
    "name": "Admin",
    "tenantId": 161223412138053
    },

    // Do not enable data generation during project initialization. Before publishing to production,
    // if dev environment has config data that needs to update the data package, enable data generation
    // and remember to disable it after use
    // Disable syncStructure syncData createDb before enabling data generation
    // Generate data
    "generateData": false,

    // Database configuration https://github.com/dotnetcore/FreeSql/wiki/getting-started
    // Connection string syntax https://www.connectionstrings.com
    // Database types MySql=0, SqlServer=1, PostgreSQL=2, Oracle=3, Sqlite=4, OdbcOracle=5, OdbcSqlServer=6, OdbcMySql=7, OdbcPostgreSQL=8, Odbc=9, OdbcDameng=10, MsAccess=11, Dameng=12, OdbcKingbaseES=13, ShenTong=14, KingbaseES=15, Firebird=16
    "type": "Sqlite",

    // Connection string
    "connectionString": "Data Source=|DataDirectory|\\moduledb.db; Pooling=true;Min Pool Size=1",

    // Specify assembly
    "providerType": "",

    // Read-write separation slave list
    "slaveList": [
    //{
    //  // Weight
    //  "Weight": 1,
    //  // Connection string
    //  "ConnectionString": "Data Source=|DataDirectory|\\moduledb.db; Pooling=true;Min Pool Size=1"
    //}
    ]
}]
}

Define Database Key Name

Create a DbKeys.cs class in the MyCompanyName.MyProjectName.Api.Core/Consts directory

using System.ComponentModel;

namespace MyCompanyName.MyProjectName.Api.Core.Consts;

/// <summary>
/// Database key names
/// </summary>
public class DbKeys
{
    /// <summary>
    /// Module database registration key
    /// </summary>
    [Description("Module database registration key")]
    public static string ModuleDb { get; set; } = "moduledb";
}

ModuleDb is the module database registration key and can be customized

Create Module Base Repository Class

Create a ModuleRepositoryBase.cs class in the MyCompanyName.MyProjectName.Api/Core/Repositories directory

using MyCompanyName.MySys.Api.Core.Consts;
using ZhonTai.Admin.Core.Db.Transaction;
using ZhonTai.Admin.Core.Repositories;

namespace MyCompanyName.MySys.Api.Core.Repositories;

/// <summary>
/// Module database base repository
/// </summary>
/// <typeparam name="TEntity"></typeparam>
public class ModuleRepositoryBase<TEntity> : RepositoryBase<TEntity> where TEntity : class
{
    public ModuleRepositoryBase(UnitOfWorkManagerCloud uowm) : base(DbKeys.ModuleDb, uowm)
    {

    }
}

Configure Host Application

Configure the host application in Program.cs

// Host application instance
new HostApp(new HostAppOptions
{
    // Configure pre-services
    ConfigurePreServices = context =>
    {
        // Set module database registration key
        DbKeys.ModuleDb = "moduledb";
    },
    // Configure Autofac container
    ConfigureAutofacContainer = (builder, context) => 
    {
        // Register generic module base repository class
        builder.RegisterGeneric(typeof(ModuleRepositoryBase<>)).InstancePerLifetimeScope().PropertiesAutowired();
    }
}

Using Multiple Databases

Method 1: Use ModuleRepositoryBase generic module base repository class

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

    public ModuleService (ModuleRepositoryBase<Entity> moduleRepo)
    {
        _moduleRepo = moduleRepo;
    }
}

Method 2: Use FreeSqlCloud

public class ModuleService : BaseService, IDynamicApi
{
    private readonly FreeSqlCloud _freeSqlCloud;

    public ModuleService(FreeSqlCloud freeSqlCloud)
    {
        _freeSqlCloud = freeSqlCloud;
    }

    public void GetData()
    {
        var fsql = _freeSqlCloud.Use(DbKeys.ModuleDb);
    }
}

Method 3: Use module repository interface

Define IModuleRepository module repository interface

using MyCompanyName.MySys.Api.Contracts.Domain.Module;
using ZhonTai.Admin.Core.Repositories;

namespace MyCompanyName.MySys.Api.Contracts;

/// <summary>
/// Module repository interface
/// </summary>
public interface IModuleRepository : IRepositoryBase<ModuleEntity>
{
}

Define ModuleRepository module repository

using MyCompanyName.MySys.Api.Contracts;
using MyCompanyName.MySys.Api.Contracts.Domain.Module;
using MyCompanyName.MySys.Api.Core.Repositories;
using ZhonTai.Admin.Core.Db.Transaction;

namespace MyCompanyName.MySys.Api.Repositories.Module;

/// <summary>
/// Module repository
/// </summary>
public class ModuleRepository : ModuleRepositoryBase<ModuleEntity>, IModuleRepository
{
    public ModuleRepository(UnitOfWorkManagerCloud uowm) : base(uowm)
    {
    }
}

Use IModuleRepository module repository interface

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

    public ModuleService (IModuleRepository moduleRepo)
    {
        _moduleRepo = moduleRepo;
    }
}

Generate Data

Generate *.json data files to the InitData/App folder with the following configuration:

{
    // Create database
    "createDb": false,
    // Sync structure
    "syncStructure": false,
    // Sync data
    "syncData": false,
    // Sync update data
    "syncUpdateData": false,
    // Generate data
    "generateData": true
}

Note

Do not enable data generation during project development startup

Before publishing to production, if dev environment has config data that needs to update the data package, enable data generation and remember to disable it after use

After project publishing, if the data package needs to be updated, enable data generation and remember to disable it after use

Create a Data folder under MyCompanyName.MyProjectName.Api/Core and add a CustomGenerateData.cs class

using System.Threading.Tasks;
using ZhonTai.Admin.Core.Configs;
using ZhonTai.Admin.Core.Db.Data;
using MyCompanyName.MySys.Api.Contracts.Domain.Module;

namespace MyCompanyName.MySys.Api.Core.Data;

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();

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

Sync Data

Sync json data from InitData/App/*.json to the database with the following configuration:

{
    // Sync structure
    "syncStructure": true,
    // Sync data
    "syncData": true,
    // Sync update data
    "syncUpdateData": true,
    // Generate data
    "generateData": false
}

Create a Data folder under MyCompanyName.MyProjectName.Api/Core and add a CustomSyncData.cs class.

using System;
using System.Threading.Tasks;
using ZhonTai.Admin.Core.Configs;
using ZhonTai.Admin.Core.Db.Data;
using MyCompanyName.MySys.Api.Contracts.Domain.Module;
using System.Linq;

namespace MyCompanyName.MySys.Api.Core.Data;

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

        try
        {
            var isTenant = appConfig.Tenant;

            await SyncEntityAsync<ModuleEntity>(db, unitOfWork, dbConfig, appConfig,
            whereFunc: (select, batchDataList) =>
            {
                if (appConfig.Tenant)
                    return select.Where(a =>
                        batchDataList.Any(b => a.Id == b.Id
                        || (a.TenantId == b.TenantId && !string.IsNullOrWhiteSpace(a.Name) && a.Name == b.Name))
                    );
                else
                    return select.Where(a =>
                        batchDataList.Any(b => a.Id == b.Id
                        || (!string.IsNullOrWhiteSpace(a.Name) && a.Name == b.Name))
                    );
            },
            insertDataFunc: (batchDataList, dbDataList) =>
            {
                return batchDataList.Where(a => !dbDataList.Any(b => a.Id == b.Id));
            });

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

Database Transactions

Using Transactions

Add the [AppTransaction] attribute to service methods to use the current project's primary database transaction

Note

Transactions can span methods and support both synchronous and asynchronous methods.
If the service is a dynamic Api, methods with transactions must be defined as virtual for transactions to work properly.

For example, the following user update involves multiple CUD operations; either all succeed or all fail.

[AppTransaction]
public virtual async Task UpdateAsync(UserUpdateInput input)
{
	if (!(input?.Id > 0))
	{
		// Transaction rollback
		throw ResultOutput.Exception("Please select a user");
	}

	// Query user
	var user = await _userRepository.GetAsync(input.Id);
	if (!(user?.Id > 0))
	{
        // Transaction rollback
		throw ResultOutput.Exception("User does not exist");
	}

	// Data mapping
	_mapper.Map(input, user);

	// Update user
	await _userRepository.UpdateAsync(user);

	// Delete user roles
	await _userRoleRepository.DeleteAsync(a => a.UserId == user.Id);
	if (input.RoleIds != null && input.RoleIds.Any())
	{
		var roles = input.RoleIds.Select(a => new UserRoleEntity { UserId = user.Id, RoleId = a });
		// Batch insert user roles
		await _userRoleRepository.InsertAsync(roles);
	}
}

Transaction Rollback

Throw a friendly exception (recommended)

throw ResultOutput.Exception(msg);

throw new AppException(msg);

Commit Transaction

If no exception is thrown, the transaction will be committed automatically

Transaction Attributes

Parameter	Description	Type	Default
Propagation	Transaction propagation	enum	Required
IsolationLevel	Transaction isolation level	enum	-

Propagation transaction propagation options:

Required: If no transaction exists, create a new one; if one already exists, join it. This is the default.
Supports: Support the current transaction; if none exists, execute as a non-transactional method.
Mandatory: Use the current transaction; if none exists, throw an exception.
NotSupported: Execute as a non-transactional method; if a transaction exists, suspend it.
Never: Execute as a non-transactional method; if a transaction exists, throw an exception.
Nested: Execute in a nested transaction.

IsolationLevel transaction isolation level options:

Chaos: Cannot override pending changes from transactions with a higher isolation level.
ReadCommitted: Holds shared locks while reading data to prevent dirty reads, but data can be changed before the transaction ends, leading to non-repeatable reads or phantom data.
ReadUncommitted: Allows dirty reads, meaning no shared locks are issued and no exclusive locks are honored.
RepeatableRead: Places locks on all data used in queries, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows may still occur.
Serializable: Places range locks on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction completes.
Snapshot: Reduces blocking by storing a version of data that another application can read while the current application is modifying the data. You cannot see changes made in other transactions even if you re-query.
Unspecified: A different isolation level than the one specified is being used, but the level cannot be determined.

Database ​

Install Database Management Tool ​

Create Database ​

Connect to Database ​

Sync Structure and Initialize Data ​

Multiple Databases ​

Multiple Database Configuration ​

Define Database Key Name ​

Create Module Base Repository Class ​

Configure Host Application ​

Using Multiple Databases ​

Generate Data ​

Sync Data ​

Database Transactions ​

Using Transactions ​

Transaction Rollback ​

Commit Transaction ​

Transaction Attributes ​

Database

Install Database Management Tool

Create Database

Connect to Database

Sync Structure and Initialize Data

Multiple Databases

Multiple Database Configuration

Define Database Key Name

Create Module Base Repository Class

Configure Host Application

Using Multiple Databases

Generate Data

Sync Data

Database Transactions

Using Transactions

Transaction Rollback

Commit Transaction

Transaction Attributes