前言

连接数据库是任何应用必不可少的，是用户和数据库交互的必经之路。

数据库连接是客户端连接数据库服务器的通道，客户端通过连接来操作数据库和接受数据库返回的结果集。

数据库连接池作用

• 限定数据库的个数，不会导致由于数据库连接过多导致系统运行缓慢或崩溃
• 数据库连接不需要每次都去创建或销毁，节约了资源
• 数据库连接不需要每次都去创建，响应时间更快。

一、数据库连接配置

1.安装对应的包

Pomelo.EntityFrameworkCore.MySql
Npgsql.EntityFrameworkCore.PostgreSQL
Microsoft.EntityFrameworkCore.Tools
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Sqlite
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore
Microsoft.Data.SqlClient

2.进行数据库配置

DbContext的两个添加方式：

• DbContext：不是线程安全的.因此，您无法将同一DbContext对象同时用于多个查询(奇怪的事情会发生).通常的解决方案是每次需要一个新的DbContext对象.这就是AddDbContext的作用。
• DbContextPool：重用DbContext对象没有任何问题.这就是AddDbContextPool所做的，它使多个DbContext对象保持活动状态，并为您提供了一个未使用的对象，而不是每次都创建一个新对象。

1、注入

#region 数据库连接
var Mysql_connection = configuration.GetConnectionString("MySqlConn");
var SqlLite_connection = configuration.GetConnectionString("SqlLiteConn");
var SqlServer_connection = configuration.GetConnectionString("SqlServerConn");
var Postgre_connection = configuration.GetConnectionString("PostGresConn");
var database_config = configuration.GetSection("Database")["db"];
services.AddDbContextPool<SqlDBContext>(t =>
{
    if (database_config == "SqlLite")
    {
        t.UseSqlite(SqlLite_connection, b => b.MigrationsAssembly("ModernWMS"));
    }
    else if (database_config == "MySql")
    {
        t.UseMySql(Mysql_connection, new MySqlServerVersion(new Version(8, 0, 26)));
    }
    else if (database_config == "SqlServer")
    {
        t.UseSqlServer(SqlServer_connection);
    }

    else if (database_config == "PostGres")
    {
        t.UseNpgsql(Postgre_connection);
        AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
        AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);
    }
    //启用敏感数据记录
    t.EnableSensitiveDataLogging();
    //启用日志工厂，会打印在控制台
    t.UseLoggerFactory(new LoggerFactory(new[] { new DebugLoggerProvider() }));
}, 100);
#endregion

2、SqlDBContext

主要是配置继承BaseModel基础类Model和数据库的映射
数据库迁移的两个方法：

• EnsureCreated：EnsureCreated可以直接使用，不需要额外的配置，但无法进行代码更新数据库。
• Migration：通过使用Add-Migration在项目中创建迁移代码，然后才可以使用，Migration执行会在目标数据库中创建__EFMigrationsHistory表，用来记录更新过程，当数据库发生变化后，可以进行更新。

/// <summary>
/// SqlDBContext
/// </summary>
public class SqlDBContext : DbContext
{
    /// <summary>
    ///  current user's tenant_id
    /// </summary>
    public byte tenant_id { get; set; } = 1;

    /// <summary>
    /// Database
    /// </summary>
    /// <returns></returns>
    public DatabaseFacade GetDatabase() => Database;

    /// <summary>
    /// s
    /// </summary>
    /// <param name="options">options</param>
    public SqlDBContext(DbContextOptions options) : base(options)
    {
       
    }

    #region overwrite
    /// <summary>
    /// Auto Mapping Entity
    /// </summary>
    /// <param name="modelBuilder">ModelBuilder</param>
    private void MappingEntityTypes(ModelBuilder modelBuilder)
    {
        var baseType = typeof(Models.BaseModel);
        var path = AppDomain.CurrentDomain.RelativeSearchPath ?? AppDomain.CurrentDomain.BaseDirectory;
        var referencedAssemblies = System.IO.Directory.GetFiles(path, $"ModernWMS*.dll").Select(Assembly.LoadFrom).ToArray();
        var list = referencedAssemblies
            .SelectMany(a => a.DefinedTypes)
            .Select(type => type.AsType())
            .Where(x => x != baseType && baseType.IsAssignableFrom(x)).ToList();
        if (list != null && list.Any())
        {
            list.ForEach(t =>
            {
                var entityType = modelBuilder.Model.FindEntityType(t);
                if (entityType == null)
                {
                    modelBuilder.Model.AddEntityType(t);
                }
            });
        }
    }
    /// <summary>
    /// overwrite OnModelCreating
    /// </summary>
    /// <param name="modelBuilder"></param>
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        MappingEntityTypes(modelBuilder);
/*            foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            if (typeof(Models.IHasTenant).IsAssignableFrom(entityType.ClrType))
            {
                ConfigureGlobalFiltersMethodInfo
                   .MakeGenericMethod(entityType.ClrType)
                   .Invoke(this, new object[] { modelBuilder });
            }
        }*/
        base.OnModelCreating(modelBuilder);
    }

    /// <summary>
    /// 获取表的实体
    /// </summary>
    /// <typeparam name="T">实体</typeparam>
    /// <returns></returns>
    public virtual DbSet<T> GetDbSet<T>() where T : class
    {
        if (Model.FindEntityType(typeof(T)) != null)
        {
            return Set<T>();
        }
        else
        {
            throw new Exception($"type {typeof(T).Name} is not add into DbContext ");
        }
    }
    /// <summary>
    /// 创建整个数据库，但不会进行Migration更新数据库，因为没__EFMigrationsHistory表
    /// </summary>
    /// <returns></returns>
    public virtual bool EnsureCreated()
    {
        return Database.EnsureCreated();
    }
    /// <summary>
    /// 重写配置文件
    /// </summary>
    /// <param name="optionsBuilder"></param>
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
    }
    #endregion
}

3.使用数据库

/// <summary>
/// The DBContext
/// </summary>
private readonly SqlDBContext _dBContext;

/// <summary>
/// Localizer Service
/// </summary>
private readonly IStringLocalizer<ModernWMS.Core.MultiLanguage> _stringLocalizer;

/// <summary>
///User  constructor
/// </summary>
/// <param name="dBContext">The DBContext</param>
/// <param name="stringLocalizer">Localizer</param>
public UserService(
    SqlDBContext dBContext
  , IStringLocalizer<ModernWMS.Core.MultiLanguage> stringLocalizer
    )
{
    this._dBContext = dBContext;
    this._stringLocalizer = stringLocalizer;
}



/// <summary>
/// get select items
/// </summary>
/// <param name="currentUser">current user</param>
/// <returns></returns>
public async Task<List<FormSelectItem>> GetSelectItemsAsnyc(CurrentUser currentUser)
{
    var res = new List<FormSelectItem>();
    var userrole_DBSet = _dBContext.GetDbSet<UserroleEntity>();
    res.AddRange(await (from ur in userrole_DBSet.AsNoTracking()
                        where ur.is_valid == true && ur.tenant_id == currentUser.tenant_id
                        select new FormSelectItem
                        {
                            code = "user_role",
                            name = ur.role_name,
                            value = ur.id.ToString(),
                            comments = "user's role",
                        }).ToListAsync());
    return res;
}