Asp. Net Core project practice-permission management system (3) using PostgreSQL through EntityFramework Core,

Asp. Net Core project practice-permission management system (3) using PostgreSQL through EntityFramework Core,

0 Asp. Net Core: permission management system (0)

1 Asp. Net Core project practice-permission management system (1) Use AdminLTE to build a front-end

2 Asp. Net Core project practice-permission management system (2) function and entity Design

3. Asp. Net Core: permission management system (3) using PostgreSQL through EntityFramework Core

4 Asp. Net Core project practices-permission management system (4) User Logon

Github Source Code address

0 PostgreSQL installation and configuration 0.0 PostgreSQL Introduction

Since Asp. Net Core has the biggest feature of cross-platform, it can be used together with a cross-platform database. PostgreSQL is a powerful open-source database system. After more than 15 years of active development and continuous improvement, PostgreSQL has gained a high reputation in the industry in terms of reliability, stability, and data consistency. Currently, PostgreSQL can run on all mainstream operating systems, including Linux, Unix (AIX, BSD, HP-UX, sgi irix, Mac OS X, Solaris, and Tru64), and Windows. PostgreSQL is a complete transaction security database that fully supports foreign keys, Federation, views, triggers, and stored procedures (and supports the development of stored procedures in multiple languages ). It supports most SQL: 2008 standard data types, including integer, numerical value, Boolean, byte, dense, date, interval, and time, it also supports storing Binary large images, including images, sounds, and videos. PostgreSQL provides native programming interfaces for many advanced development languages, such as C/C ++, Java, and ,.. Net, Perl, Python, Ruby, Tcl, ODBC, and other languages.

0.1 PostgreSQL installation and configuration

Go to the PostgreSQL official website to download the version that meets your own system and start installation. From my own installation experience, there is nothing to pay special attention to, just install it step by step as prompted, at the end, select the following language as needed and set the logon password for the Super User role S.

Create a role used by the System

Open the installed PostgreSQL database and enter the password to enter the management interface. Right-click "Logon role" and create a role named "fonour". On the "role Permissions" tab, select all the functions that can be selected.

PM> Install-Package Npgsql. EntityFrameworkCore. PostgreSQLPM> Install-Package Npgsql. EntityFrameworkCore. PostgreSQL. DesignPM> Install-Package Microsoft. EntityFrameworkCore. Tools

You can also search for the relevant class library in the NuGet Package Manager to install the library.

{"Version": "1.0.0-*", "dependencies": {"Fonour. domain ":" 1.0.0-0 "," Microsoft. entityFrameworkCore. tools ":" 1.0.0-preview2-final "," NETStandard. library ":" 1.6.0 "," Npgsql. entityFrameworkCore. postgreSQL ":" 1.0.1 "," Npgsql. entityFrameworkCore. postgreSQL. design ":" 1.0.1 "}," frameworks ": {" netcoreapp1.0 ": {" imports ": [" dotnet5.6 "," portable-net45 + win8 "] }}," tools ": {"Microsoft. entityFrameworkCore. tools ": {" version ":" 1.0.0-preview2-final "," imports ": [" portable-net45 + win8 + dnxcore50 "," portable-net45 + win8 "]}

Note: In the frameworks section, if the default value is the netstandard1.6 framework, you must modify it. Otherwise, the system will prompt that related dependencies are not supported.

1. Create DbContext

According to EF Core's requirements on multiple-to-multiple associations, two correlated entities, UserRole and RoleMenu, were added, and the original entities were adjusted.

public class UserRole
{
    public Guid UserId { get; set; }
    public User User { get; set; }

    public Guid RoleId { get; set; }
    public Role Role { get; set; }

}
public class RoleMenu
{
    public Guid RoleId { get; set; }
    public Role Role { get; set; }

    public Guid MenuId { get; set; }
    public Menu Menu { get; set; }
}

Create a data context operation class under the Fonour. EntityFrameworkCore project named "FonourDBContext", and add the definition of DbSet for the relevant entities of the permission management system. The final code is as follows:

public class FonourDbContext: DbContext
{
    public FonourDbContext (DbContextOptions <FonourDbContext> options): base (options)
    {

    }
    public DbSet <Department> Departments {get; set;}
    public DbSet <Menu> Menus {get; set;}
    public DbSet <Role> Roles {get; set;}
    public DbSet <User> Users {get; set;}
    public DbSet <UserRole> UserRoles {get; set;}
    public DbSet <RoleMenu> RoleMenus {get; set;}

    protected override void OnModelCreating (ModelBuilder builder)
    {
        // UserRole association configuration
        builder.Entity <UserRole> ()
          .HasKey (ur => new {ur.UserId, ur.RoleId});

        // RoleMenu association configuration
        builder.Entity <RoleMenu> ()
          .HasKey (rm => new {rm.RoleId, rm.MenuId});
        builder.Entity <RoleMenu> ()
          .HasOne (rm => rm.Role)
          .WithMany (r => r.RoleMenus)
          .HasForeignKey (rm => rm.RoleId) .HasForeignKey (rm => rm.MenuId);

        // Enable Guid primary key type expansion
        builder.HasPostgresExtension ("uuid-ossp");

        base.OnModelCreating (builder);
    }
}

1.1 configure database connection in the Fonour. MVC Project

0. Add related dependencies.

In Asp. in. Net Core, the configuration file in json format is used to configure system-related parameters, and related configuration files are managed in a unified manner through ConfigurationBuilder to obtain the configuration instance of IConfigurationRoot, obtain the configuration file configuration node information. To use services related to the configuration file, you need to add dependencies.

• Microsoft. Extensions. Configuration
• Microsoft. Extensions. Configuration. FileExtensions
• Microsoft. Extensions. Configuration. Json

You also need to add references to the Fonour. EntityFrameworkCore project.

The final configuration of the project. json file of the Fonour. MVC project is as follows:

{ "dependencies": {
    "Microsoft.NETCore.App": "1.0.1",
    "Microsoft.AspNetCore.Diagnostics": "1.0.0",
    "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
    "Microsoft.AspNetCore.Server.Kestrel": "1.0.1",
    "Microsoft.Extensions.Logging.Console": "1.0.0",
    "Microsoft.AspNetCore.Mvc": "1.0.1",
    "Microsoft.AspNetCore.StaticFiles": "1.0.0",
    "Microsoft.Extensions.Configuration": "1.0.0",
    "Microsoft.Extensions.Configuration.FileExtensions": "1.0.0",
    "Microsoft.Extensions.Configuration.Json": "1.0.0",
    "Fonour.EntityFrameworkCore": "1.0.0-*"
  },

  "tools": {
    "Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.0.0-preview2-final"
  },

  "frameworks": {
    "netcoreapp1.0": {
      "imports": [
        "dotnet5.6",
        "portable-net45+win8"
      ]
    }
  },

  "buildOptions": {
    "emitEntryPoint": true,
    "preserveCompilationContext": true
  },

  "runtimeOptions": {
    "configProperties": {
      "System.GC.Server": true
    }
  },

  "publishOptions": {
    "include": [
      "wwwroot",
      "web.config"
    ]
  },

  "scripts": {
    "postpublish": [ "dotnet publish-iis --publish-folder %publish:OutputPath% --framework %publish:FullTargetFramework%" ]
  },
  "runtimes": {
    "win10-x64": {}
  }
}

1. added the appsettings. json configuration file.

Right-click the Fonour. MVC project and add an Asp. Net configuration file type named etettings. json.

{"ConnectionStrings": {"Default": "User ID = fonour; Password = 123456; Host = localhost; Port = 5432; Database = Fonour; Pooling = true ;"}}

2. enable database connection

Define an IConfigurationRoot attribute in Startup. cs, and manage the configuration file in the system Startup class Startup. cs constructor.

public IConfigurationRoot Configuration { get; }
public Startup(IHostingEnvironment env)
{
    var builder = new ConfigurationBuilder()
        .SetBasePath(env.ContentRootPath)
        .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
        .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);
    builder.AddEnvironmentVariables();
    Configuration = builder.Build();
}

Obtain the database connection string in the ConfigureServices method and add the Database Connection Service.

public void ConfigureServices (IServiceCollection services)
{
     // Get the database connection string
     var sqlConnectionString = Configuration.GetConnectionString ("Default");

     // Add data context
     services.AddDbContext <FonourDbContext> (options =>
         options.UseNpgsql (sqlConnectionString)
     );

     services.AddMvc ();
}

1.2 use the CodeFirst database migration command to create a database

There are two methods for database migration in EntityFrameworkCore.

0 use the command line tool

In the application root directory, press shift and right-click, select "Open command window here", and enter the database migration command

dotnet ef migrations add Initdotnet ef database update

1. Use the Package Manager Console

In the Package Manager Console, select Fonour. EntityFrameworkCore as the default project, and enter the following command to automatically create a database migration file.

Add-Migration Init

Note that you must set Fonour. MVC as the startup project.

After the execution is complete, the database migration file is added to the project.

Update-Database

After the prompt is displayed, check the database and the database table.

Public static class SeedData {public static void Initialize (IServicePr {using (var context = new FonourDbCon {if (context. users. any () {return; // data already initialized} Guid required mentid = Guid. newGuid // Add a department context. parameters. add (new Department {Id = mongomentid, Name = "Fonour Group Headquarters", ParentId = Guid. empty}); // Add a super administrator user context. users. add (new User {UserName = "admin", Password = "123456", // Name = "Super administrator", mongomentid = mongome }); // added four basic function menus: context. menus. addRange (new Menu {Name = "organization management", Code = "Department", SerialNumber = 0, ParentId = Guid. empty, Icon = "fa-link"}, new Menu {Name = "Role management", Code = "Role", SerialNumber = 1, ParentId = Guid. empty, Icon = "fa-link"}, new Menu {Name = "User Management", Code = "User", SerialNumber = 2, ParentId = Guid. empty, Icon = "fa-link"}, new Menu {Name = "function Management", Code = "Department", SerialNumber = 3, ParentId = Guid. empty, Icon = "fa-link"}); context. saveChanges ();}}}

Add the data initialization operation to the Configure method in Startup. cs of the Fonour. MVC project.

public void Configure (IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
     loggerFactory.AddConsole ();

     if (env.IsDevelopment ())
     {
         // Development environment exception handling
         app.UseDeveloperExceptionPage ();
     }
     else
     {
         // Production environment exception handling
         app.UseExceptionHandler ("/ Shared / Error");
     }
     // Use a static file
     app.UseStaticFiles ();
     // Use Mvc, set the default route as the system login
     app.UseMvc (routes =>
     {
         routes.MapRoute (
             name: "default",
             template: "{controller = Login} / {action = Index} / {id?}");
     });

     SeedData.Initialize (app.ApplicationServices); // Initialize the data
} 

Run the program. Check the database and find that the initialization data has been generated successfully.

3. Some pitfalls

• To use a Guid-type primary key, you must add a reference to "Npgsql. EntityFrameworkCore. PostgreSQL. Design": "1.0.1". Note that version 1.0.0 is not supported.
• To use a Guid-type primary key, you must enable uuid extension builder. HasPostgresExtension ("uuid-ossp") in the OnModelCreating Rewriting Method of DbContext ");
• Entity Design of many-to-many relationships

In the previous EntityFramework, for multi-to-Multi-link entities, you only need to create two entities, which respectively contain the navigation attributes of one set of the other. Therefore, you do not need to create an associated entity class. For example, the User object contains the navigation attribute of an ICollection <Role> Roles and the Role object contains the navigation attribute of an ICollection <User> Users, EF automatically creates a User_Role table based on the relationship between objects. to modify the name of the intermediate table, configure the table.

In EF Core, you must create an object of the association.

• Currently, EF Core does not support LayzingLoad.

4. Summary

This section mainly studies how to use PostgreSQL through EntityFramework Core, create a database based on the designed entity through CodeFirst database migration, and finally create a database based on the system requirements, some related data is initialized.

Next, we need to implement the user login function.