ASP.NET Core 对接已存在的PostgreSQL数据库

Posted on by

同系列文章：

  1. ASP.NET Core 通过Model创建PostgreSQL新数据库
  2. ASP.NET Core 对接已存在的PostgreSQL数据库

参考文章

ASP.NET Core Application to Existing Database (Database First)

前提条件

创建新工程

  • 工程模板：Visual C#->Web->ASP.NET Core Web Application (.NET Core)
  • 项目名称：EFGetStarted.AspNetCore.ExistingDb
  • 模板：Web应用程序
  • 身份验证：不进行身份验证

通过NuGet安装Entity Framework并更新文件project.json中“tools”区

  • Install-Package Npgsql.EntityFrameworkCore.PostgreSQL
  • Install-Package Npgsql.EntityFrameworkCore.PostgreSQL.Design
  • Install-Package Microsoft.EntityFrameworkCore.Tools -Pre

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

数据模型反向工程
工具 -> NuGet包管理器 -> 程序包管理器控制台运行如下命令

Scaffold-DbContext "Server=localhost;Database=postgres;User ID=postgres;Password=wantgirl;" Npgsql.EntityFrameworkCore.PostgreSQL -OutputDir Models

将在Models目录下自动生成Blogs.cs、Posts.cs、postgresContext.cs三个文件。
Blogs.cs

using System;
using System.Collections.Generic;

namespace EFGetStarted.AspNetCore.ExistingDb.Models
{
    public partial class Blogs
    {
        public Blogs()
        {
            Posts = new HashSet<Posts>();
        }

        public int BlogId { get; set; }
        public string Url { get; set; }

        public virtual ICollection<Posts> Posts { get; set; }
    }
}


Posts.cs

using System;
using System.Collections.Generic;

namespace EFGetStarted.AspNetCore.ExistingDb.Models
{
    public partial class Posts
    {
        public int PostId { get; set; }
        public int BlogId { get; set; }
        public string Content { get; set; }
        public string Title { get; set; }

        public virtual Blogs Blog { get; set; }
    }
}


postgresContext.cs

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace EFGetStarted.AspNetCore.ExistingDb.Models
{
    public partial class postgresContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            #warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
            optionsBuilder.UseNpgsql(@"Server=localhost;Database=postgres;User ID=postgres;Password=wantgirl;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blogs>(entity =>
            {
                entity.HasKey(e => e.BlogId)
                    .HasName("PK_Blogs");
            });

            modelBuilder.Entity<Posts>(entity =>
            {
                entity.HasKey(e => e.PostId)
                    .HasName("PK_Posts");

                entity.HasIndex(e => e.BlogId)
                    .HasName("IX_Posts_BlogId");

                entity.HasOne(d => d.Blog)
                    .WithMany(p => p.Posts)
                    .HasForeignKey(d => d.BlogId);
            });
        }

        public virtual DbSet<Blogs> Blogs { get; set; }
        public virtual DbSet<Posts> Posts { get; set; }
    }
}

通过依赖注入注册上下文
更新Models\postgresContext.cs，删除OnConfiguring成员方法，并添加postgresContext构造函数。
postgresContext.cs

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace EFGetStarted.AspNetCore.ExistingDb.Models
{
    public partial class postgresContext : DbContext
    {
        public postgresContext(DbContextOptions<postgresContext> options)
            : base(options)
        { }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blogs>(entity =>
            {
                entity.HasKey(e => e.BlogId)
                    .HasName("PK_Blogs");
            });

            modelBuilder.Entity<Posts>(entity =>
            {
                entity.HasKey(e => e.PostId)
                    .HasName("PK_Posts");

                entity.HasIndex(e => e.BlogId)
                    .HasName("IX_Posts_BlogId");

                entity.HasOne(d => d.Blog)
                    .WithMany(p => p.Posts)
                    .HasForeignKey(d => d.BlogId);
            });
        }

        public virtual DbSet<Blogs> Blogs { get; set; }
        public virtual DbSet<Posts> Posts { get; set; }
    }
}


在Startup.cs中引入命名空间

using EFGetStarted.AspNetCore.ExistingDb.Models;
using Microsoft.EntityFrameworkCore;


更新Startup.cs中的ConfigureServices成员方法

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddApplicationInsightsTelemetry(Configuration);

            services.AddDbContext<postgresContext>(options => options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection")));

            services.AddMvc();
        }


连接字符串格式

User ID=root;Password=myPassword;Host=localhost;Port=5432;Database=myDataBase;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;


在Appsetting.json中增加连接字符串

"ConnectionStrings": {
        "DefaultConnection": "User ID=postgres;Password=wantgirl;Host=localhost;Database=postgres;Pooling=true;"
    },

创建MVC控制器
BlogsController.cs

using EFGetStarted.AspNetCore.ExistingDb.Models;
using Microsoft.AspNetCore.Mvc;
using System.Linq;

namespace EFGetStarted.AspNetCore.ExistingDb.Controllers
{
    public class BlogsController : Controller
    {
        private postgresContext _context;

        public BlogsController(postgresContext context)
        {
            _context = context;
        }

        public IActionResult Index()
        {
            return View(_context.Blogs.ToList());
        }

        public IActionResult Create()
        {
            return View();
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public IActionResult Create(Blogs blog)
        {
            if (ModelState.IsValid)
            {
                _context.Blogs.Add(blog);
                _context.SaveChanges();
                return RedirectToAction("Index");
            }

            return View(blog);
        }

    }
}

创建视图
Index.cshtml

@model IEnumerable<EFGetStarted.AspNetCore.ExistingDb.Models.Blogs>

@{
    ViewBag.Title = "Blogs";
}

<h2>Blogs</h2>

<p>
    <a asp-controller="Blogs" asp-action="Create">Create New</a>
</p>

<table class="table">
    <tr>
        <th>Id</th>
        <th>Url</th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.BlogId)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Url)
            </td>
        </tr>
    }
</table>

Create.cshtml

@model EFGetStarted.AspNetCore.ExistingDb.Models.Blogs

@{
    ViewBag.Title = "New Blog";
}

<h2>@ViewData["Title"]</h2>

<form asp-controller="Blogs" asp-action="Create" method="post" class="form-horizontal" role="form">
    <div class="form-horizontal">
        <div asp-validation-summary="All" class="text-danger"></div>
        <div class="form-group">
            <label asp-for="Url" class="col-md-2 control-label"></label>
            <div class="col-md-10">
                <input asp-for="Url" class="form-control" />
                <span asp-validation-for="Url" class="text-danger"></span>
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
</form>

运行应用并导航到/Blogs

Comments are closed.