.NET5 ASP.NET Core 使用 EF Core MS SQL SERVER DB First

.NET5 ASP.NET Core 使用 EF Core MS SQL SERVER DB First

开发工具：VS2019

1。修改appsettings.json，增加一项。

"ConnectionStrings": {
    "DefaultConnection": "Server=127.0.0.1;Database=db1; User=sa;Password=dd;"
  }

完整appsettings.json：

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",

  "ConnectionStrings": {
    "DefaultConnection": "Server=127.0.0.1;Database=db1; User=sa;Password=dd;"
  }

}

2。nuget 引用Microsoft.EntityFrameworkCore，Microsoft.EntityFrameworkCore.SqlServer

3.新建实体类

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace WebApp5.DbModels.db1
{
    //[Table("Table1")]
    public class Table1
    {
        [Key] //主键 
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  //设置自增
        public long aa { get; set; }

        public string bb { get; set; }
        public string cc { get; set; }
    }
}

数据库表结构：

CREATE TABLE [dbo].[Table1](
    [aa] [bigint] IDENTITY(1,1) NOT NULL,
    [bb] [nvarchar](50) NULL,
    [cc] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
    [aa] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

4。添加db1DbContext，依赖注入模式。

using Microsoft.EntityFrameworkCore;
using WebApp5.DbModels.db1;

namespace WebApp5.MyDbContext
{
    public class db1DbContext : DbContext
    {
        public db1DbContext(DbContextOptions options) : base(options)
        {
        }
        //添加表实体
        public DbSet<Table1> Table1 { get; set; }
    }
}

注意“Table1”这个属性名要和数据库表名一致，如果想不一致，在实体类上加[Table("Table1")]

5。修改Startup.cs，在ConfigureServices方法中增加：

services.AddDbContext<db1DbContext>(options =>
                           options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

其中，DefaultConnection是appsettings.json里的数据库连接字符串。db1DbContext 是刚新加的DbContext。

6，在controller 中使用，部分代码：

private readonly ILogger<HomeController> _logger;
        public readonly db1DbContext _db1Context;

        public HomeController(ILogger<HomeController> logger, db1DbContext db1Context)
        {
            _logger = logger;
            _db1Context = db1Context;
        }

        public IActionResult Index()
        {
            try
            { 
                WebApp5.DbModels.db1.Table1 mdl = new DbModels.db1.Table1();
                mdl.bb = "bb-" + DateTime.Now.ToString();
                mdl.cc = "cc-" + DateTime.Now.ToString();
                _db1Context.Table1.Add(mdl);
                _db1Context.SaveChanges();

                var myModel = _db1Context.Table1.FirstOrDefault(x => x.bb == "11");
                if (myModel == null)
                {
                    ViewBag.aa = "未找到";
                }
                else
                {
                    ViewBag.aa = "找到";
                }
            }
            catch (Exception ex)
            {
                ViewBag.aa = ex.Message;
            }
            return View();
        }

首先要声明一个“public readonly db1DbContext _db1Context;”，构造函数里加一个参数“db1DbContext db1Context”，赋值“_db1Context = db1Context;”，在action里就可以使用了。

 --

非依赖注入：

Startup.cs 里就不用加services.AddDbContext<db1DbContext> 这行代码了，controller 构造函数也不用加 db1DbContext db1Context 。

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;
using WebApp5.DbModels.db1;

namespace WebApp5.MyDbContext
{
    public class db1DbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            IConfiguration configuration = new ConfigurationBuilder()
            //.AddJsonFile("appsettings.json")
            .Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true })
            .Build();

            string tmpStr = configuration.GetConnectionString("DefaultConnection");

            optionsBuilder.UseSqlServer(
                connectionString: tmpStr
            );
        }

        //添加表实体
        public DbSet<Table1> Table1 { get; set; }
    }
}

使用：

using (db1DbContext _db1Context = new db1DbContext())
                {
                    WebApp5.DbModels.db1.Table1 mdl = new DbModels.db1.Table1();
                    mdl.bb = "bb-" + DateTime.Now.ToString();
                    mdl.cc = "cc-" + DateTime.Now.ToString();
                    _db1Context.Table1.Add(mdl);
                    _db1Context.SaveChanges();

                    var myModel = _db1Context.Table1.FirstOrDefault(x => x.bb == "11");
                    if (myModel == null)
                    {
                        ViewBag.aa = "未找到";
                    }
                    else
                    {
                        ViewBag.aa = "找到";
                    }
                }

如果不想每次在OnConfiguring时读取一次配置文件，可以在Startup.cs-ConfigureServices方法中读取配置到静态字符串里，DbContext里使用这个静态字符串即可。

public void ConfigureServices(IServiceCollection services)
        {
            //services.AddDbContext<db1DbContext>(options =>
            //               options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

            WebApp5.MyDbContext.GDB.dbConnStr1 = Configuration.GetConnectionString("DefaultConnection");

            services.AddControllersWithViews();
        }

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;
using WebApp5.DbModels.db1;

namespace WebApp5.MyDbContext
{
    public class db1DbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //IConfiguration configuration = new ConfigurationBuilder()
            ////.AddJsonFile("appsettings.json")
            //.Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true })
            //.Build();

            //string tmpStr = configuration.GetConnectionString("DefaultConnection");

            optionsBuilder.UseSqlServer(
                connectionString: WebApp5.MyDbContext.GDB.dbConnStr1
            );
        }

        //添加表实体
        public DbSet<Table1> Table1 { get; set; }
    }
}

--