mysql 三层架构开发_搭建连接MySql的三层架构的ASP.NetCore2.0的WebApi

这里我们用三层架构搭建一个连接MySql的ASP.netCore模板的WebApi项目

首先添加WebApi项目(ASP.NetCore版本)

右键解决方案>新建项目>

选择Web>ASP.NET Core Web应用程序(.NET Core)

选择Web API

此时的目录结构:

添加实体层Entity

右键添加>新建项目>.Net Core类库

添加后的目录结构

BaseEntity:

usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel.DataAnnotations;usingSystem.Runtime.Serialization;usingSystem.Text;namespaceEntity.Core

{///

///DB表基础属性///

public abstract class BaseEntity{publicBaseEntity()

{

CreteTime=DateTime.Now;

}///

///主键Id///

[DataMember]

[Key]public T Id { get; set; }///

///DB版号,Mysql详情参考;http://www.cnblogs.com/shanyou/p/6241612.html

///

//[Timestamp]//Mysql不允许byte[]类型上标记TimeStamp/RowVersion，这里使用DateTime类型配合标记ConcurrencyCheck达到并发控制

[ConcurrencyCheck]public DateTime RowVersion { get; set; }///

///创建时间///

public DateTime CreteTime { get; set; }

}

}

Product:

usingEntity.Core;usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel.DataAnnotations;usingSystem.Text;namespaceEntity.Table

{///

///商品类///

public classProduct : BaseEntity

{///

///名称///

[StringLength(20)]

[Required]public string Name { get; set; }///

///描述///

[StringLength(500)]

[Required]public string Description { get; set; }///

///类别///

[Range(1, int.MaxValue)]public int Category { get; set; }///

///原价///

[Required]public decimal Price { get; set; }///

///现价///

public decimal Discount { get; set; }

}

}

添加数据层DAL:

右键添加>新建项目>.NET Core 类库

添加引用:

Microsoft.EntityFrameworkCore(也可加入Microsoft.AspNetCore.All,但会有用不到的功能造成浪费)

Microsoft.EntityFrameworkCore.Tools(迁移支持)

Pomelo.EntityFrameworkCore.MySql(Mysql支持)具体使用细则,请参考:Pomelo.EntityFrameworkCore.MySql使用细则

netcoreapp2.0

添加DbContext数据上下文

usingEntity.Table;usingMicrosoft.EntityFrameworkCore;usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;namespaceDAL

{public classProductContext : DbContext

{//https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/complex-data-model

public ProductContext(DbContextOptions options) : base(options)

{//在此可对数据库连接字符串做加解密操作

}public DbSet Courses { get; set; }protected override voidOnModelCreating(ModelBuilder modelBuilder)

{ base.OnModelCreating(modelBuilder);

}

}

}

ASP.Net Core API项目中引用刚创建的DAL类库

添加Service服务层

右键添加>新建项目>.NetCore 类库

添加引用:

添加Entity和DAL引用,其次再添加第三方数据仓储Microsoft.EntityFrameworkCore.UnitOfWork(最新)

文件目录如下:

IProductService:

usingSystem;usingSystem.Collections.Generic;usingSystem.Text;namespaceService.ProductService

{public interfaceIProductService

{stringTest();

}

}

ProductService:

usingEntity.Table;usingMicrosoft.EntityFrameworkCore;namespaceService.ProductService

{public classProductService : IProductService

{private readonlyIUnitOfWork _unitOfWork;publicProductService(IUnitOfWork unitOfWork)

{

_unitOfWork=unitOfWork;

}public stringTest()

{var repo = _unitOfWork.GetRepository();

repo.Insert(newProduct

{

Category= 1,

Description= "此商品为澳洲代购,买不了吃亏买不了上当",

Discount= (decimal)899.21,

Price= (decimal)98.2,

Name= "澳洲袋鼠粉",

});

_unitOfWork.SaveChanges();//提交到数据库

var result = repo.GetFirstOrDefault()?.Description ?? string.Empty;returnresult;

}

}

}

ASP.Net Core API添加刚创建的Service类库引用

完整csproj如下:

netcoreapp2.0

控制器中使用service

using System.Collections.Generic;using Microsoft.AspNetCore.Mvc;

using Service.ProductService;

namespaceASP.Net_Core_API.Controllers

{

[Route("api/[controller]")]public classValuesController : Controller

{privateIProductService _productService;publicValuesController(IProductService productService)

{

_productService=productService;

}//GET api/values

[HttpGet]public IEnumerableGet()

{var result =_productService.Test();return new string[] { "value1", result };

}

}

}

Startup文件中加入Mysql支持和对应的需要的注入的service还有UnitOfWork的支持

完整文件如下:

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;usingMicrosoft.AspNetCore.Builder;usingMicrosoft.AspNetCore.Hosting;usingMicrosoft.Extensions.Configuration;usingMicrosoft.Extensions.DependencyInjection;usingMicrosoft.Extensions.Logging;usingMicrosoft.Extensions.Options;usingMicrosoft.EntityFrameworkCore;usingEntity.Table;usingDAL;usingService.ProductService;namespaceASP.Net_Core_API

{public classStartup

{publicStartup(IConfiguration configuration)

{

Configuration=configuration;

}public IConfiguration Configuration { get; }//This method gets called by the runtime. Use this method to add services to the container.

public voidConfigureServices(IServiceCollection services)

{

services.AddDbContext(options =>options.UseMySql(Configuration.GetConnectionString("MySqlConnection")));//添加Mysql支持

services.AddUnitOfWork();//添加UnitOfWork支持

services.AddScoped(typeof(IProductService), typeof(ProductService));//用ASP.NET Core自带依赖注入(DI)注入使用的类

services.AddMvc();

}//This method gets called by the runtime. Use this method to configure the HTTP request pipeline.

public voidConfigure(IApplicationBuilder app, IHostingEnvironment env)

{if(env.IsDevelopment())

{

app.UseDeveloperExceptionPage();

}

app.UseMvc();

}

}

}

配置appsettings.json中Mysql连接字符串

{"ConnectionStrings": {"MySqlConnection": "Server=localhost;database=NetCore_WebAPI-Mysql;uid=root;pwd=111111;"},"Logging": {"IncludeScopes": false,"Debug": {"LogLevel": {"Default": "Warning"}

},"Console": {"LogLevel": {"Default": "Warning"}

}

}

}

迁移数据库:

打开程序包管理器控制台:工具>NuGet包管理器>程序包管理器控制台,默认项目选中包含了DbCOntext的程序集,这里是DAL,程序包源选择全部

输入:

PM>add-migration init

待执行后输出"To undo this action,use Remove-Migration"表示生成了迁移代码

然后再输入:

PM>update-database

待执行后输出"Done"表示已成功更新数据库

完整操作如下

Tip:如果是非第一次迁移,就不能再输入PM>add-migration init,而是输入:

PM>add-migration "对本次迁移的说明"

例如,本次对Entity的某张表添加了Name属性.迁移时输入PM>add-migration AddName

输入以上待执行后,依旧输入以下即可

PM>update-database

会发现在DAL程序家下成功生成了以下目录

再打开数据库成功依据实体Entity生成了Product表

运行程序

成功Run通,奖励个鸡腿压压惊

专案下载链接:Demo

github源码链接(持续更新)：