ASP.NET Core Entity Framework(EF) 数据库访问 SqlSugar

ASP.NET Core 依赖注入
ASP.NET Core Entity Framework Core
ASP.NET Core Application to Existing Database (Database First)（设置连接数据库字符串，上下文）
EF Core 5.0 中的新增功能

.NET Core 使用 Autofac 依赖注入
EF Core 3.x 中包含的中断性变更

--------------------------Entity Framework Core--------------------------
1、数据库连接字符串  

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "SchoolContext": "Server=(localdb)\\mssqllocaldb;Database=SchoolContext;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

2、添加 TodoContext 数据库上下文  

using Microsoft.EntityFrameworkCore;

namespace TodoApi.Models
{
    public class TodoContext : DbContext
    {
        public TodoContext(DbContextOptions<TodoContext> options)
            : base(options)
        { }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            #region 打印SQL语句
            optionsBuilder.UseLoggerFactory(LoggerFactory.Create(builder =>
            {
                builder.AddConsole();
            }));
            #endregion
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<sys_resource>().ToTable("sys_resource");

            modelBuilder.Entity<sys_resource>(entity =>
            {
                #region 单主键
                //entity.HasKey(e => e.id).HasName("PK_sys_resource");
                #endregion

                #region 多主键
                //entity.HasKey(e => new { e.id, e.r_id }).HasName("PK_sys_resource");
                #endregion
            });

            base.OnModelCreating(modelBuilder);
        }

        public DbSet<sys_resource> Resources { get; set; }
        public DbSet<TodoItem> TodoItems { get; set; }
    }

    public class sys_resource
    {
        [Key]//主键
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]//自增
        [Column("resource_code")]//列名
        public int resource_code { get; set; }
        public string resource_name { get; set; }
        public string descriptions { get; set; }
        public string enable { get; set; }
        public int sort { get; set; }
        public int modify_code { get; set; }
        public string modify_name { get; set; }
        public DateTime modify_time { get; set; }
        public string status { get; set; }
    }
}

*、Controller 调用 Service

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Sso.Repository;
using Sso.Server.Models;

namespace Sso.Server.Controllers
{
    //[Authorize]
    public class HomeController : Controller
    {
        private readonly ILogger<HomeController> _logger;
        private readonly ICategoryService _categorySvc;

        public HomeController(ILogger<HomeController> logger, ICategoryService categorySvc)
        {
            _logger = logger;
            _categorySvc = categorySvc;
        }

        public IActionResult Index()
        {
            _categorySvc.GetAllList();
            return View();
        }
    }
}

*、ICategoryService【Service 调用 DbContext 上下文】

public class CategoryService : ICategoryService
{
    private readonly TodoContext _dbContext;
    public CategoryService(TodoContext context)
    {
        _dbContext = context;
    }

    public List<Categorys> GetAllList()
    {
        List<Categorys> list = new List<Categorys>();
        try
        {
            list = _dbContext.TodoItems.OrderByDescending(o => o.Id).Where(o => o.State != "D").ToList();
        }
        catch (Exception ex)
        {
        }
        return list;
    }
}

3、更新数据库上下文类  
*
4、Startup.cs  注册数据库上下文
ASP.NET Core 通过 依赖关系注入 进行生成

public void ConfigureServices(IServiceCollection services)
{
    services.AddRazorPages();

    services.AddDbContext<SchoolContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("SchoolContext")));
}

public void ConfigureServices(IServiceCollection services)
{
    /*
     * 配置连接数据库 
     */
    var connection=@"Data Source=.; Initial Catalog=dbCoffee; Uid=sa; Pwd=000000;";
    services.AddDbContext<ModelEntities>(options => options.UseSqlServer(connection));

    /*
     * 依赖注入 
     */
    services.AddTransient<ICategoryService, CategoryService>();       
    services.AddTransient<ILanguageService, LanguageService>();
    services.AddTransient<IProductService, ProductService>();
    services.AddTransient<IProductStepService, ProductStepService>();
    services.AddTransient<IProductStructureService, ProductStructureService>();

    /*
     * 批量注入 ok
     */
    foreach (var item in GetAssemblyName("***.Repository"))
    {
        services.AddTransient(item.Key, item.Value);
    }

    foreach (var item in GetAssemblyName123("***.Repository"))
    {
        foreach (var typeArray in item.Value)
        {
            if (!typeArray.Name.StartsWith("IRepository"))
                services.AddTransient(typeArray, item.Key);
        }
    }
}

private Dictionary<Type, Type> GetAssemblyName(string assemblyName)
{
    var result = new Dictionary<Type, Type>();
    if (!String.IsNullOrEmpty(assemblyName))
    {
        Assembly assembly = Assembly.Load(assemblyName);
        List<Type> ts = assembly.GetTypes().ToList();
        foreach (var item in ts.Where(x => x.IsClass && !x.IsAbstract && !x.IsGenericType))
            result.Add(item.GetInterfaces().FirstOrDefault(x => !x.Name.StartsWith("IRepository")), item);
    }
    return result;
}

private Dictionary<Type, Type[]> GetAssemblyName123(string assemblyName)
{
    var result = new Dictionary<Type, Type[]>();
    if (!String.IsNullOrEmpty(assemblyName))
    {
        Assembly assembly = Assembly.Load(assemblyName);
        List<Type> ts = assembly.GetTypes().ToList();
        foreach (var item in ts.Where(x => x.IsClass && !x.IsAbstract && !x.IsGenericType))
            result.Add(item, item.GetInterfaces());
    }
    return result;
}

5、注册数据库上下文  

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<TodoContext>(opt => opt.UseInMemoryDatabase("TodoList"));
    services.AddControllers();
}

6、创建数据库  
context.Database.EnsureCreated();
如果有上下文的数据库，则 EnsureCreated 方法不执行任何操作。 如果没有数据库，则它将创建数据库和架构。
*、
*、
*、
*、
--------------------------Dapper--------------------------
*、
services.AddTransient<IDbConnection>((sp) => new SqlConnection(Configuration.GetConnectionString("DefaultConnection")));
*、
*、
--------------------------SqlSugar--------------------------
1、管理 NuGet 程序包
     sqlSugarCore

services.AddSqlSugarClient<DbFactory>((sp, op) =>
{
    op.ConnectionString = sp.GetService<IConfiguration>().GetConnectionString("***");
    op.DbType = DbType.MySql;
    op.IsAutoCloseConnection = true;
    op.InitKeyType = InitKeyType.Attribute;
    op.IsShardSameThread = true;
});

///或 一下待测试

Assembly assemblys = Assembly.Load("QuestionnaireSurvey.BLL");
Assembly assemblysInterFace = Assembly.Load("QuestionnaireSurvey.IBLL");
var typesInterface = assemblysInterFace.GetTypes();
var typesImpl = assemblys.GetTypes();
foreach (var item in typesInterface)
{
    var name = item.Name.Substring(1);
    string implBLLImpName = name + "Server";
    var impl = typesImpl.FirstOrDefault(w => w.Name.Equals(implBLLImpName));

    if (impl != null)
    {
        services.AddTransient(item, impl);
    }
}
//注入数据层
services.AddTransient(typeof(DbSqlContext));
//注入仓储
services.AddTransient(typeof(IRepository<>), typeof(Repository<>));

*、
*、
*、