.NetCore通过EFCore操作数据库

安装nuget包

efcore官方支持的数据库包括SQLServer、MySql、PostgreSQL、Sqlite
我们这里使用SQLServer数据库

 

 添加数据库上下文类和数据模型

public class YFDbContext : DbContext
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public YFDbContext()
        {

        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //private string ConnstrSqlServer = "server=服务器名称;uid=登录名称;pwd=登录密码;database=数据库名称";
            optionsBuilder.UseSqlServer(@"server=数据库地址;uid=sa;pwd=数据库密码;database=CoreDB");
            base.OnConfiguring(optionsBuilder);
        }
        /// <summary>
        /// 通过依赖注入方式启动的构造函数
        /// </summary>
        /// <param name="options"></param>
        //public YFDbContext(DbContextOptions<YFDbContext> options) : base(options)
        //{

        //}
        public DbSet<UserInfo> UserInfo { get; set; }
        public DbSet<RoleInfo> RoleInfo { get; set; }

        /// <summary>
        /// 重写Dispose方法,便于查看数据库上下文什么时候释放。
        /// </summary>
        public override void Dispose()
        {
            base.Dispose();
            Console.WriteLine("\n***Dispose****\n\n");
        }
    }

public class UserInfo
    {
        public string Id { get; set; }
        public string UserName { get; set; }
        public string UserSex { get; set; }
    }

public class RoleInfo
    {
        public string Id { get; set; }
        public string RoleName { get; set; }
        public string RoleDescription { get; set; }
    }

 添加测试控制器

public class EFTestController : Controller
    {
        /// <summary>
        /// 测试EFCore插入数据
        /// 每次需要实例化数据库上下文
        /// </summary>
        /// <returns></returns>
        public JsonResult TestInsert()
        {
            List<UserInfo> uList;
            using (var db=new YFDbContext())
            {
                //1.新增
                UserInfo userInfo = new UserInfo()
                {
                    Id = Guid.NewGuid().ToString("N"),
                    UserName = "张三",
                    UserSex = "男"
                };
                //同步方法
                db.Add(userInfo);
                int count = db.SaveChanges();
                //异步方法
                //await _db.Set<UserInfo>().AddAsync(userInfo);
                //int count = await _db.SaveChangesAsync();
                Console.WriteLine($"成功插入{count}条数据");
                //2.查询
                uList = db.Set<UserInfo>().ToList();
                foreach (var item in uList)
                {
                    Console.WriteLine($"id为：{item.Id},名字为：{item.UserName},性别为：{item.UserSex}");
                }
            }
            return Json(uList);
        }
    }

执行结果：

我这里使用了谷歌浏览器JsonView插件。

 

 

 升级：通过依赖注入方式获取数据库上下文

在appsettings.json中配置数据库连接字符串

在Startup.cs文件ConfigureServices方法中添加服务

 

 

 

 

 改造控制器，通过依赖注入方式获取数据库上下文

public class EFTestIOCController : Controller
    {
        private readonly YFDbContext _db;
        private readonly DbContextOptions<YFDbContext> _options;
        public EFTestIOCController(YFDbContext db, DbContextOptions<YFDbContext> options)
        {
            _db = db;
            _options = options;
        }
        public IActionResult Index()
        {
            return View();
        }
        /// <summary>
        /// 测试EFCore插入数据
        /// 通过依赖注入方式获取数据库上下文
        /// </summary>
        /// <returns></returns>
        public JsonResult TestInsert()
        {
            List<UserInfo> uList;
            using (var db = new YFDbContext())
            {
                //1.新增
                UserInfo userInfo = new UserInfo()
                {
                    Id = Guid.NewGuid().ToString("N"),
                    UserName = "张三",
                    UserSex = "男"
                };
                //同步方法
                db.Add(userInfo);
                int count = db.SaveChanges();
                
                Console.WriteLine($"成功插入{count}条数据");
                //2.查询
                uList = db.Set<UserInfo>().ToList();
                foreach (var item in uList)
                {
                    Console.WriteLine($"id为：{item.Id},名字为：{item.UserName},性别为：{item.UserSex}");
                }
            }
            return Json(uList);
        }
}

执行结果：

 

 

 问题
再次调用http://localhost:5000/EFTestIOC/TestInsert方法或者使用异步方法插入数据时会报一下错误，该问题的原因是依赖注入方式的数据库上下文生命周期

解决办法：用 DbContextOptions 手工 new DbContext

System.ObjectDisposedException:“Cannot access a disposed object. A common cause of this error is disposing a context that was resolved from dependency injection and then later trying to use the same context instance elsewhere in your application. This may occur if you are calling Dispose() on the context, or wrapping the context in a using statement. If you are using dependency injection, you should let the dependency injection container take care of disposing context instances.
”

 

public async void TestMultInsert()
        {
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();  //开始监视代码运行时间
            using (var context = new YFDbContext(_options))
            {

                for (int i = 0; i < 10; i++)
                {
                    //1.新增
                    UserInfo userInfo = new UserInfo()
                    {
                        Id = Guid.NewGuid().ToString("N"),
                        UserName = "ypf3",
                        UserSex = "男"
                    };
                    await context.Set<UserInfo>().AddAsync(userInfo);
                    int count = await context.SaveChangesAsync();
                    Console.WriteLine($"成功插入{count}条数据");
                }
            }
            watch.Stop();  //停止监视
            TimeSpan timespan = watch.Elapsed;  //获取当前实例测量得出的总时间
            Console.WriteLine($"代码执行时间：{timespan.TotalMilliseconds}");
        }