ASP.NETCore MVC+Layui uses EF Core to connect MySQL for simple CRUD operations

Preface:

This chapter is mainly explained by a complete exampleASP.NETCore MVC+EF Core performs a simple CRUD operation on the MySQL database, hoping to provide a complete reference instance for the buddies who are just getting started with.NET Core.aboutASP.NETThe Core MVC+EF operation MsSQL Server is detailed in the official documentation ( https://docs.microsoft.com/zh-cn/aspnet/core/data/ef-mvc/?view=aspnetcore-3.1).

Preview of sample implementation capabilities:

Blog Instance Source Download Address:

https://github.com/YSGStudyHards/ASP.NET-Core-MVC-Layui-EF-Core-CRUD_Sample

1. CreationASP.NETCore Web application:

Note that this chapter is mainly aboutAPS.NETCore 3.1 as an example of a blog style!

 

2. Add EF Core NuGet package:

To use EF Core to operate MySQL databases in your project, you need to install the appropriate database driver packages.This tutorial mainly uses MySQL databases, so we need to install the related driver packages MySql.Data.EntityFrameworkCore.

Installation method:

Click Tools=>NuGet Package Manager=>Package Manager Console to enter the following commands:

Install-Package MySql.Data.EntityFrameworkCore -Version 8.0.20

Click Tools=>NuGet Package Manager=>NuGet Package for Managing Solutions:

Search:MySql.Data.EntityFrameworkCore Click Install.

3. Create entity models for corresponding database tables:

Note that this blog uses a manual model-first approach for mapping database table fields to model attributes, although if you find this more cumbersome, you can actually create a model that gives priority to the model in its true sense.Program.csConfigure the database logic code to create the corresponding model in to create the database without manually creating the database. Refer to the Official Web Documentation Tutorial( https://docs.microsoft.com/zh-cn/aspnet/core/data/ef-rp/intro?view=aspnetcore-3.1&tabs=visual-studio#create-the-database).

Create a user model (UserInfo):

Note: Attribute case is consistent with table fields in the database, and the Id attribute becomes the primary key column of the corresponding database table.By default, EF Core treats an attribute named Id or xxxID as the primary key.For more information, see F Core - Key.

/// <summary>
    /// Student Information Model
    /// </summary>
    public class UserInfo
    {
        /// <summary>
        /// Student Number
        /// </summary>
        [Description("Student Number")]
        public int? Id { get; set; }

        /// <summary>
        /// Student Name
        /// </summary>
        [Description("Student Name")]
        public string UserName { get; set; }

        /// <summary>
        /// Student Gender
        /// </summary>
        [Description("Student Gender")]
        public string Sex { get; set; }

        /// <summary>
        /// Student Contact Phone
        /// </summary>
        [Description("Student Contact Phone")]
        public string Phone { get; set; }

        /// <summary>
        /// Student Description
        /// </summary>
        [Description("Student Description")]
        public string Description { get; set; }

        /// <summary>
        /// Student hobbies
        /// </summary>
        [Description("Student hobbies")]
        public string Hobby { get; set; }
    }

4. Add database connection string toAppsettings.json:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
        "MySqlConnection":"Data Source=127.0.0.1;User ID=root;Password=root;DataBase=SchoolUserInfo_db"
  }
}

5. Create a database context:

Summary:

The database context class is the primary class that coordinates EF Core functionality for a given data model.Context derived from Microsoft.EntityFrameworkCore.DbContext .The context specifies which entities are included in the data model.Name the database context class SchoolUserInfoContext in this project.

Establish:

using Microsoft.EntityFrameworkCore;
using Model;

namespace Dal
{
    public class SchoolUserInfoContext : DbContext
    {
        public SchoolUserInfoContext(DbContextOptions<SchoolUserInfoContext> options)
            : base(options)
        {
        }

        /// <summary>
        /// DbSet Entity set attributes correspond to tables in the database(Note that entity set names must match the representation)
        /// </summary>
        public DbSet<UserInfo> UserInfos { get; set; }

        /// <summary>
        /// TODO:When the database is created, EF Create a series of tables with default table names and DbSet The property names are the same.Collection attribute names are usually plural, but different developers may have different naming habits.
///Developers determine whether to use the plural form based on their own circumstances.After defining the code for the DbSet property, add the following code to override the default table name by specifying a singular table name for the DbContext.
/// </summary> /// <param name="modelBuilder"></param> protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<UserInfo>().ToTable("UserInfo"); } } }

6. Add Context to Startup.cs Dependency injection in:

// This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            //injection EF Core Database Context Service
            services.AddDbContext<SchoolUserInfoContext>(options =>
                options.UseMySQL(Configuration.GetConnectionString("MySqlConnection")));

            services.AddControllersWithViews();
        }

7. Introduce Layui style and js:

Go to the official website to download Layui-related styles and js packages at: https://www.layui.com/

Layui Pop-up Layer PluginLayer.js(There are many places to use pop-ups), download address: https://layer.layui.com/

Store the related files under the wwwroot file:

 

Introduce related files into the default layout page:

8.ASP.NETCore MVC and EF Core implement MySQL CRUD functions:

Note the code for the EF Core and database operations shown here. Detailed code can be downloaded to see the sample source code.

Create:

/// <summary>
        /// Student Information Addition
        /// </summary>
        /// <param name="addUserInfo"></param>
        /// <returns></returns>
        public async Task<bool> Create(AddUserInfoViewModel addUserInfo)
        {
            try
            {
                var userInfo=new UserInfo()
                {
                    UserName = addUserInfo.UserName,
                    Sex = addUserInfo.Sex,
                    Hobby = addUserInfo.Hobby,
                    Phone = addUserInfo.Phone,
                    Description = addUserInfo.Description
                };

                _shoSchoolUserInfoContext.UserInfos.Add(userInfo);

                await _shoSchoolUserInfoContext.SaveChangesAsync();

                return true;
            }
            catch
            {
                return false;
            }
        }

Retrieve:

/// <summary>
        /// Get user information
        /// </summary>
        /// <param name="page">CurrentPage</param>
        /// <param name="limit">Show Number of Bars</param>
        /// <param name="userName">User Name</param>
        /// <returns></returns>
        public async Task<PageSearchModel> GetPageListData(int page = 1, int limit = 15, string userName = "")
        {
            try
            {
                List<UserInfo> listData;
                var totalCount = 0;
                if (!string.IsNullOrWhiteSpace(userName))
                {
                    listData = await _shoSchoolUserInfoContext.UserInfos.Where(x => x.UserName.Contains(userName)).OrderByDescending(x => x.Id).Skip((page - 1) * limit).Take(limit).ToListAsync();

                    totalCount = _shoSchoolUserInfoContext.UserInfos
                        .Count(x => x.UserName.Contains(userName));
                }
                else
                {
                    listData = await _shoSchoolUserInfoContext.UserInfos.OrderByDescending(x => x.Id).Skip((page - 1) * limit).Take(limit).ToListAsync();

                    totalCount = _shoSchoolUserInfoContext.UserInfos.Count();
                }

                return new PageSearchModel()
                {
                    ResultMsg = "success",
                    Code = 200,
                    TotalCount = totalCount,
                    DataList = listData
                };
            }
            catch (Exception e)
            {
                return new PageSearchModel() { Code = 400, ResultMsg = e.Message };
            }
        }

Update:

/// <summary>
        /// Student Information Modification
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public async Task<bool> Update(UserInfo userInfo)
        {

            try
            {
                _shoSchoolUserInfoContext.UserInfos.Update(userInfo);

                await _shoSchoolUserInfoContext.SaveChangesAsync();

                return true;
            }
            catch
            {
                return false;
            }
        }

Delete:

/// <summary>
        /// Delete Student Information
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<bool> Delete(int? id)
        {
            try
            {
                var searchUserInfo = await _shoSchoolUserInfoContext.UserInfos.FindAsync(id);

                if (searchUserInfo == null)
                {
                    return false;
                }

                _shoSchoolUserInfoContext.UserInfos.Remove(searchUserInfo);
                await _shoSchoolUserInfoContext.SaveChangesAsync();

                return true;
            }
            catch
            {
                return false;
            }
        }

Tags: Database MySQL Attribute github

Posted on Sat, 06 Jun 2020 21:21:06 -0400 by [ArcanE]