The previous CRUD used simulated data, and this article completed the access to the real database. Since sql server is Microsoft's own database and naturally friendly, I chose sql server and used Microsoft's SQL Server Express LocalDB. LocalDB is more lightweight than express and does not even need to be installed separately on a PC.

SQL Server Express LocalDB

In Visual Studio 2019, Open the database browsing window through the menu [View] - [SQL Server Object Explorer]. In this interface, you can perform common database operations, such as creating a database, defining a table, executing sql statements, maintaining data, etc. select the Databases navigation menu, right-click Add New Database, and create a database named StudentDb:

For the convenience of the following connection string, do not change the location of the database file and accept the default location:

Create a new Students table visually:

If you are used to using sql statements, you can also use DDL to create tables in Visual Studio:

CREATE TABLE [dbo].[Students] (
    [Id]    INT           NOT NULL,
    [Name]  NVARCHAR (50) NOT NULL,
    [Major] NVARCHAR (50) NULL,
    [Email] NVARCHAR(100)   NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Then insert some data:

INSERT INTO [dbo].[Students] ([Id], [Name], [Major], [Email]) VALUES (1, N'Zhang San', N'computer technology', N'zs@qq.com')
INSERT INTO [dbo].[Students] ([Id], [Name], [Major], [Email]) VALUES (2, N'Li Si', N'artificial intelligence', N'ls@qq.com')
INSERT INTO [dbo].[Students] ([Id], [Name], [Major], [Email]) VALUES (3, N'Wang Wu', N'Electronic Commerce', N'ww@qq.com')

EF Core

In ASP Net core to access the database through EF core, the following steps are required:

1. To install packages related to Entity Framework Core, you need to install the following two packages. The method of NuGet installation package will not be expanded here.

2. Connection string

Write the connection string in Appsettings In the JSON file, the connection string is written as follows:

3. Create a new ApplicationDbContext class, which inherits from DbContext. DbContext represents a session with the database. The "add, delete, modify query" operations in EF Core are based on this class. Define the DbSet property in the class of ApplicationDbContext Net objects are associated with entities in the database.

4. Define Student model. We have finished it before, so it is omitted here

5. Define IStudentRepository, including the following interface methods:

6. Define Sql Server Based implementations:

namespace StudentManagement.Models
{    
    public class MsSqlStudentRepository : IStudentRepository
    {
        private readonly ApplicationDbContext dbContext;

        public MsSqlStudentRepository(ApplicationDbContext context)
        {
            dbContext = context;
        }

        public Student Create(Student student)
        {
            dbContext.Students.Add(student);
            dbContext.SaveChanges();
            return student;
        }

        public Student Delete(int id)
        {
            var s = dbContext.Students.Find(id);
            if (s != null)
            {
                dbContext.Students.Remove(s);
                dbContext.SaveChanges();
            }
            return s;
        }

        public IEnumerable<Student> GetAllStudents()
        {
            return dbContext.Students;
        }

        public Student GetStudent(int id)
        {
            return dbContext.Students.Find(id);
        }

        public Student Update(Student student)
        {
            var s = dbContext.Students.Attach(student);
            s.State = Microsoft.EntityFrameworkCore.EntityState.Modified;
            dbContext.SaveChanges();

            return student;
        }
    }
}

7. In startup Register IStudentRepository in CS:

Source code

The source code is hosted in gitee aspnetcore-studentmanagement , in order to record the complete writing process, an important step is to submit the tag for marking. This time, the code tag is v0 04.

reference resources

SQL Server Express LocalDB - SQL Server | Microsoft Docs