ASP.NET Core 入门教程 8、ASP.NET Core

作者: KenTalk | 来源:发表于2018-12-29 10:07 被阅读6次

    一、前言

    1、本教程主要内容

    • ASP.NET Core MVC 集成 EF Core 介绍&操作步骤
    • ASP.NET Core MVC 使用 EF Core + Linq to Entity 访问MySQL数据库
    • ASP.NET Core MVC 使用 EF Core + 原生SQL访问MySql数据库
    • EF Core + MySQL数据库插入数据后获取自增列的值

    Entity Framework Core 简称为 EF Core

    2、本教程环境信息

    软件/环境 说明
    操作系统 Windows 10
    SDK 2.1.401
    ASP.NET Core 2.1.3
    MySQL 8.0.x
    IDE Visual Studio Code 1.30
    浏览器 Chrome 70
    VS Code插件 版本 说明
    C# 1.17.1 提供C#智能感知, .NET Core 调试、编译等
    vscdoe-solution-explorer 0.3.1 提供解决方案视图

    本篇代码以下代码进行调整:https://github.com/ken-io/asp.net-core-tutorial/tree/master/chapter-02

    3、准备工作

    安装MySQL数据库:https://dev.mysql.com/downloads/mysql/

    • Windows环境

    下载安装包后双击安装,一直下一步即可。

    • MacOS环境

    参考:https://ken.io/note/macos-mysql8-install-config-tutorial

    • CentOS环境

    参考:https://ken.io/note/centos-mysql8-setup

    4、前置知识

    • 控制反转(IOC)原则与依赖注入(DI)

    ASP.NET Core 默认集成了DI。所有官方模块的引入都要使用DI的方式引入。

    https://baike.baidu.com/item/IOC

    • Linq使用教程

    https://docs.microsoft.com/zh-cn/dotnet/csharp/tutorials/working-with-linq

    二、EF Core + MySQL 前置准备

    EF Core 全称:Entity Framework Core,为微软为 .NET Core平台开发的ORM框架。对应是 .NET Framework平台的 Entity Framework(EF),无论是EF还是EF Core都可以说是 .NET 平台开发效率最高的ORM框架。

    1、引入 EF Core + MySQL Provider

    EF Core已经集成在 ASP.NET Core 中,但默认并不支持MySQL,如果需要连接MySQL,需要添加MySQL相关的Provider,这里我选择的是:Pomelo.EntityFrameworkCore.MySql

    任意命令行操作即可,我用的是VS Code自带的命令行

    //进入项目根目录: Ken.Tutorial.Web
    cd Ken.Tutorial.Web
    
    //添加Package
    dotnet add package Pomelo.EntityFrameworkCore.MySql
    

    这里我添加的 MySql.Data.EntityFrameworkCore 版本是 8.0.13,如果你想跟我使用一样的版本,可以使用以下命令:

    dotnet add package Pomelo.EntityFrameworkCore.MySql --version 2.1.4
    

    2、创建MySQL库表

    • 创建数据库
    CREATE DATABASE ken_tutorial;
    
    • 创建表
    USE ken_tutorial;
    
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `hobby` varchar(500) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    3、配置链接字符串

    在项目根目录Ken.Tutorial.Web中创建配置文件appsettings.json,并写入以下配置

    {
      "ConnectionStrings": {
        "testdb": "server=localhost;database=Ken.Tutorial;uid=root;pwd=root;"
      }
    }
    
    

    4、实体&DbContext准备

    创建user表对应实体

    在项目根目录Ken.Tutorial.Web中创建目录Models,并在其中创建类:UserEntity.cs

    using System;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    
    namespace Ken.Tutorial.Web.Models
    {
        [Table("user")]
        [Serializable]
        public class UserEntity
        {
            [Key]//主键
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]//自增列
            [Column("id")]
            public int Id { get; set; }
    
            [Column("name")]
            public string Name { get; set; }
    
            [Column("age")]
            public int Age { get; set; }
    
            [Column("hobby")]
            public string Hobby { get; set; }
        }
    }
    

    创建DBContext

    在项目根目录Ken.Tutorial.Web中创建目录Repositories,并在其中创建类:TutorialDbContext.cs

    using System;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.Extensions.Configuration;
    
    namespace Ken.Tutorial.Web.Repositories
    {
        public class TutorialDbContext : DbContext
        {
            private IConfiguration Configuration { get; }
    
            public TutorialDbContext(IConfiguration configuration)
            {
                this.Configuration = configuration;
            }
    
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseMySql(Configuration.GetConnectionString("testdb"));
            }
            
            ppublic DbSet<UserEntity> Users { get; set; }
        }
    }
    

    TutorialDbContext 继承了 DbContext,然后再Context初始化时配置MySQL链接。

    由于 ASP.NET Core 默认使用了DI组件,所以我们取配置文件,就需要在构造函数中获取 IConfiguration 注入的实例。

    在Startup.cs配置注入,以便数据访问类可以访问DI方式获取TutorialDbContext

    public void ConfigureServices(IServiceCollection services)
    {
        //引入MVC模块
        services.AddMvc();
    
        //配置DbContext注入
        services.AddTransient<TutorialDbContext>();
    }
    

    三、EF Core + Linq to entity 访问数据库

    1、创建Linq To Entity 数据访问类

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using Ken.Tutorial.Web.Models;
    
    namespace Ken.Tutorial.Web.Repositories
    {
        public class TutorialRepository
        {
            private TutorialDbContext DbContext { get; }
    
            public TutorialRepository(TutorialDbContext dbContext)
            {
                //在构造函数中注入DbContext
                this.DbContext = dbContext;
            }
    
            //添加
            public int Add(UserEntity user)
            {
                using (DbContext)
                {
                    //由于我们在UserEntity.Id配置了自增列的Attribute,EF执行完成后会自动把自增列的值赋值给user.Id
                    DbContext.Users.Add(user);
                    return DbContext.SaveChanges();
                }
    
            }
    
            //删除
            public int Delete(int id)
            {
                using (DbContext)
                {
                    var userFromContext = DbContext.Users.FirstOrDefault(u => u.Id == id);
                    DbContext.Users.Remove(userFromContext);
                    return DbContext.SaveChanges();
                }
            }
    
            //更新
            public int Update(UserEntity user)
            {
                using (DbContext)
                {
                    var userFromContext = DbContext.Users.FirstOrDefault(u => u.Id == user.Id);
                    userFromContext.Name = user.Name;
                    userFromContext.Age = user.Age;
                    userFromContext.Hobby = user.Hobby;
                    return DbContext.SaveChanges();
                }
            }
    
            //查询
            public UserEntity QueryById(int id)
            {
                using (DbContext)
                {
                    return DbContext.Users.FirstOrDefault(u => u.Id == id);
                }
            }
    
            //查询集合
            public List<UserEntity> QueryByAge(int age)
            {
                using (DbContext)
                {
                    return DbContext.Users.Where(u => u.Age == age).ToList();
                }
            }
    
            //查看指定列
            public List<string> QueryNameByAge(int age)
            {
                using (DbContext)
                {
                    return DbContext.Users.Where(u => u.Age == age).Select(u => u.Name).ToList();
                }
            }
    
            //分页查询
            public List<UserEntity> QueryUserPaging(int pageSize, int page)
            {
                using (DbContext)
                {
                    return DbContext.Users.Skip(pageSize * (page - 1)).Take(pageSize).ToList();
                }
            }
    
            //事务:将年龄<0的用户修改年龄为0
            public int FixAge()
            {
                using (DbContext)
                {
                    using (var transaction = DbContext.Database.BeginTransaction())
                    {
                        try
                        {
                            var userListFromContext = DbContext.Users.Where(u => u.Age < 0);
                            foreach (UserEntity u in userListFromContext)
                            {
                                u.Age = 0;
                            }
                            var count = DbContext.SaveChanges();
                            transaction.Commit();
                            return count;
                        }
                        catch
                        {
                            transaction.Rollback();
                            return 0;
                        }
                    }
                }
            }
        }
    }
    

    2、创建测试API

    Controllers文件夹下创建EfCoreController.cs作为访问入口

    using System;
    using Ken.Tutorial.Web.Models;
    using Ken.Tutorial.Web.Repositories;
    using Microsoft.AspNetCore.Mvc;
    
    namespace Ken.Tutorial.Web.Controllers
    {
        public class EfCoreController : Controller
        {
            public TutorialRepository Repository { get; }
    
            public EfCoreController(TutorialRepository repository)
            {
                this.Repository = repository;
            }
    
            public IActionResult Add(UserEntity user)
            {
                var message = Repository.Add(user) > 0 ? "success" : "failed";
                return Json(new { Message = message, User = user });
            }
    
            public IActionResult Delete(int id)
            {
                var message = Repository.Delete(id) > 0 ? "success" : "failed";
                return Json(new { Message = message });
            }
    
            public IActionResult Update(UserEntity user)
            {
                var message = Repository.Update(user) > 0 ? "success" : "failed";
                return Json(new { Message = message, User = user });
            }
    
            public IActionResult QueryById(int id)
            {
                var user = Repository.QueryById(id);
                return Json(new { User = user });
            }
    
            public IActionResult QueryByAge(int age)
            {
                var users = Repository.QueryByAge(age);
                return Json(new { Users = users });
            }
    
            public IActionResult QueryNameByAge(int age)
            {
                var users = Repository.QueryNameByAge(age);
                return Json(new { Users = users });
            }
    
            public IActionResult QueryUserPaging(int pageSize, int page)
            {
                var users = Repository.QueryUserPaging(pageSize, page);
                return Json(new { Users = users });
            }
    
            public IActionResult FixAge()
            {
                var count = Repository.FixAge();
                return Json(new { FixCount = count });
            }
    
        }
    }
    
    

    3、启动测试

    • 在Startup.cs中配置Repository注入
    public void ConfigureServices(IServiceCollection services)
    {
        //其他代码省略
    
        //配置Repository注入
        services.AddTransient<TutorialRepository>();
    }
    
    • 启动项目并测试
    API 示例
    添加用户 /efcore/add?name=ken&age=18&hobby=coding
    删除用户 /efcore/delete?id=0
    更新用户 /efcore/update?id=1&name=ken&age=666&hobby=codingOrGaming
    查询单个用户 /efcore/querybyid?id=0
    查询多个用户 /efcore/querybyage?age=18
    查询多个用户名 /efcore/querynamebyage?age=18
    分页查询用户 /efcore/queryuserpaging?pagesize=3&page=1
    修复异常年龄 /efcore/fixage

    四、EF Core + 原生SQL 访问数据库

    1、创建EF Core + 原生SQL 数据访问类

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using Ken.Tutorial.Web.Models;
    using Microsoft.EntityFrameworkCore;
    using MySql.Data.MySqlClient;
    
    namespace Ken.Tutorial.Web.Repositories
    {
        public class TutorialWithSqlRepository
        {
            private TutorialDbContext DbContext { get; }
    
            public TutorialWithSqlRepository(TutorialDbContext dbContext)
            {
                //在构造函数中注入DbContext
                this.DbContext = dbContext;
            }
    
            //添加
            public int Add(UserEntity user)
            {
                using (var connection = DbContext.Database.GetDbConnection())
                {
                    connection.Open();
                    var command = connection.CreateCommand() as MySqlCommand;
                    command.CommandText = "INSERT INTO user (name,age,hobby) VALUES(@name,@age,@hobby)";
                    command.Parameters.Add(new MySqlParameter()
                    {
                        ParameterName = "@name",
                        DbType = DbType.String,
                        Value = user.Name
                    });
                    command.Parameters.Add(new MySqlParameter()
                    {
                        ParameterName = "@age",
                        DbType = DbType.Int32,
                        Value = user.Age
                    });
                    command.Parameters.Add(new MySqlParameter()
                    {
                        ParameterName = "@hobby",
                        DbType = DbType.String,
                        Value = user.Hobby
                    });
                    var count = command.ExecuteNonQuery();
                    //获取插入时产生的自增列Id并赋值给user.Id使用
                    user.Id = (int)command.LastInsertedId;
                    return count;
                }
    
            }
    
            //删除
            public int Delete(int id)
            {
                using (DbContext)
                {
                    return DbContext.Database.ExecuteSqlCommand(
                        "DELETE FROM user WHERE id={0}", id);
                }
            }
    
            //更新
            public int Update(UserEntity user)
            {
                using (DbContext)
                {
                    return DbContext.Database.ExecuteSqlCommand(
                         "UPDATE user SET name={0}, age={1}, hobby={2} WHERE id={3}",
                          user.Name, user.Age, user.Hobby, user.Id);
                }
            }
    
            //查询
            public UserEntity QueryById(int id)
            {
                using (DbContext)
                {
                    return DbContext.Users.FromSql("SELECT id,name,age,hobby FROM user WHERE id={0}", id).FirstOrDefault();
                }
            }
    
            //查询集合
            public List<UserEntity> QueryByAge(int age)
            {
                using (DbContext)
                {
                    return DbContext.Users.FromSql("SELECT id,name,age,hobby FROM user WHERE age={0}", age).ToList();
                }
            }
    
            //查看指定列
            public List<string> QueryNameByAge(int age)
            {
                using (DbContext)
                {
                    return DbContext.Users.FromSql("SELECT id,name FROM user WHERE age={0}", age).Select(u => u.Name).ToList();
                }
            }
    
            //分页查询
            public List<UserEntity> QueryUserPaging(int pageSize, int page)
            {
                using (DbContext)
                {
                    return DbContext.Users.FromSql("SELECT id,name,age,hobby FROM user LIMIT {0},{1}", pageSize * (page - 1), pageSize).ToList();
                }
            }
    
            //事务:将年龄<0的用户修改年龄为0
            public int FixAge()
            {
                using (DbContext)
                {
                    using (var connection = DbContext.Database.GetDbConnection())
                    {
                        //打开连接
                        connection.Open();
                        //开启事务
                        using (var transaction = connection.BeginTransaction())
                        {
                            try
                            {
                                //获取命令对象
                                var command = connection.CreateCommand();
                                command.Transaction = transaction;
                                command.CommandText = "UPDATE user SET age=@age WHERE age<@age";
                                command.Parameters.Add(new MySqlParameter()
                                {
                                    ParameterName = "@age",
                                    DbType = DbType.Int32,
                                    Value = 0
                                });
                                var count = command.ExecuteNonQuery();
                                transaction.Commit();
                                return count;
                            }
                            catch (Exception ex)
                            {
                                connection.Close();
                                transaction.Rollback();
                                return 0;
                            }
                        }
    
                    }
                }
            }
        }
    }
    

    2、创建测试API

    Controllers文件夹下创建EfCoreWithSqlController.cs作为访问入口

    using System;
    using Ken.Tutorial.Web.Models;
    using Ken.Tutorial.Web.Repositories;
    using Microsoft.AspNetCore.Mvc;
    
    namespace Ken.Tutorial.Web.Controllers
    {
        public class EfCoreWithSqlController : Controller
        {
            public TutorialWithSqlRepository Repository { get; }
    
            public EfCoreWithSqlController(TutorialWithSqlRepository repository)
            {
                this.Repository = repository;
            }
    
            public IActionResult Add(UserEntity user)
            {
                var message = Repository.Add(user) > 0 ? "success" : "failed";
                return Json(new { Message = message, User = user });
            }
    
            public IActionResult Delete(int id)
            {
                var message = Repository.Delete(id) > 0 ? "success" : "failed";
                return Json(new { Message = message });
            }
    
            public IActionResult Update(UserEntity user)
            {
                var message = Repository.Update(user) > 0 ? "success" : "failed";
                return Json(new { Message = message, User = user });
            }
    
            public IActionResult QueryById(int id)
            {
                var user = Repository.QueryById(id);
                return Json(new { User = user });
            }
    
            public IActionResult QueryByAge(int age)
            {
                var users = Repository.QueryByAge(age);
                return Json(new { Users = users });
            }
    
            public IActionResult QueryNameByAge(int age)
            {
                var users = Repository.QueryNameByAge(age);
                return Json(new { Users = users });
            }
    
            public IActionResult QueryUserPaging(int pageSize, int page)
            {
                var users = Repository.QueryUserPaging(pageSize, page);
                return Json(new { Users = users });
            }
    
            public IActionResult FixAge()
            {
                var count = Repository.FixAge();
                return Json(new { FixCount = count });
            }
    
        }
    }
    
    

    3、启动测试

    • 在Startup.cs中配置Repository注入
    public void ConfigureServices(IServiceCollection services)
    {
        //其他代码省略
    
        //配置Repository注入
        services.AddTransient<TutorialWithSqlRepository>();
    }
    
    • 启动项目并测试
    API 示例
    添加用户 /efcorewithsql/add?name=ken&age=18&hobby=coding
    删除用户 /efcorewithsql/delete?id=0
    更新用户 /efcorewithsql/update?id=1&name=ken&age=666&hobby=codingOrGaming
    查询单个用户 /efcorewithsql/querybyid?id=0
    查询多个用户 /efcorewithsql/querybyage?age=18
    查询多个用户名 /efcorewithsql/querynamebyage?age=18
    分页查询用户 /efcorewithsql/queryuserpaging?pagesize=3&page=1
    修复异常年龄 /efcorewithsql/fixage

    五、备注

    1、附录

    • 本文代码示例

    https://github.com/ken-io/asp.net-core-tutorial/tree/master/chapter-08

    • 本文参考

    https://docs.microsoft.com/zh-cn/ef/core
    https://www.learnentityframeworkcore.com
    https://mysql-net.github.io/MySqlConnector/tutorials/net-core-mvc/


    本文首发于我的独立博客:https://ken.io/note/asp.net-core-tutorial-entity-framework-core-mysql

    相关文章

      网友评论

        本文标题:ASP.NET Core 入门教程 8、ASP.NET Core

        本文链接:https://www.haomeiwen.com/subject/phkulqtx.html