.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; } } }
--