.NetCore3.1 API和MySQL数据库开发

1、任务需求




条件3：提供以下几个RESTful API
A.返回产品类型分页列表，每页5条，过滤已删除数据
B.软删除单条产品类型数据
C.返回产品表分页列表，每页5条，过滤已删除数据，返回字段要包含产品类型名称

C#代码：

vs2019

1、建表（实体类库（Model））

Product.cs代码：

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace Model
{
    public class Product
    {
        public long Id { get; set; }

        public long TypeId { get; set; }

        [Column(TypeName = "varchar(64)")]
        public string ProductName { get; set; }

        public DateTime CreateTime { get; set; }

        public bool IsDelete { get; set; }

    }
}

ProductType.cs代码：

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace Model
{

    [Table("product_type")]
    public class ProductType
    {
        public long Id { get; set; }

        [Column(TypeName ="varchar(64)")]
        public string Name { get; set; }

        public DateTime CreateTime { get; set; }

        public bool IsDelete { get; set; }

    }
}

2、数据访问层（DAL）

DbClassContext.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Model;

namespace DAL
{
    public partial class DBClassConext : DbContext
    {
        public DBClassConext()
        {
        }

        public DBClassConext(DbContextOptions<DBClassConext> options)
            : base(options)
        {
        }

        public virtual DbSet<Product> Products { get; set; }
        public virtual DbSet<ProductType> Producttypes { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.EnableSensitiveDataLogging(true);  //关闭打印参数
            InitTenant(optionsBuilder);
            base.OnConfiguring(optionsBuilder);
        }

        private void InitTenant(DbContextOptionsBuilder optionsBuilder)
        {
            //var tenantId = _httpContextAccessor.GetTenantId();

            var tenantId = "CBCloudOMSDBContext";

            //IConfiguration config = new StreamConfigurationProvider();

            //string connect = _configuration.GetConnectionString(tenantId);

            optionsBuilder.UseMySql("server=10.0.104.36;user id=root;database=cbtest;password=Root@123456");

        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

ProductTypeManagerApp.cs

using Model;
using System;
using System.Collections.Generic;
using System.Text;
using Dapper;
using System.Data;
using Microsoft.EntityFrameworkCore;
using System.Linq;

namespace DAL
{
    public class ProductTypeManagerApp
    {
        public List<ProductType> GetList(int pageIndex)
        {
            var _context = new DBClassConext();

            int pageSize = 5;

            // lamdba,依赖注入(Autofac)

            //var query = from i in _context.Producttypes
                      //  select i;

            //var result = query.Skip(5).Take(10).ToList();

            var sql = $"select Id,`Name`,CreateTime,IsDelete from product_type where IsDelete = 0 limit {(pageIndex - 1) * pageSize},{pageSize};";

            var list = _context.Database.GetDbConnection().Query<ProductType>(sql).ToList();

            // var sqlone = $"select Id,`Name`,CreateTime,IsDelete from list where IsDelete = 0;";

            // var list1 = _context.Database.GetDbConnection().Query<ProductType>(sql).ToList();

            return list;
        }
    }
}

ProductTypeSoftDeleted.cs

using Model;
using System;
using System.Collections.Generic;
using System.Text;
using Dapper;
using System.Data;
using Microsoft.EntityFrameworkCore;
using System.Linq;

namespace DAL
{
    public class ProductTypeSoftDeleted
    {
     

        public int DeleteAsync(int id)
        { 
            return 0;
        }

        public int Delete(int idIndex)
        {
            var _context = new DBClassConext();

            var sql = $"UPDATE product_type SET IsDelete = 1 WHERE Id = {idIndex};";

            //ado 

            var result = _context.Database.GetDbConnection().Execute(sql, null);

            //var list = _context.Database.GetDbConnection().Query<ProductType>(sql).ToList();

            return result;
        }
    }
}

3、业务逻辑层（BLL）

ProductTypeManagerBLL.cs

using DAL;
using Model;
using System;
using System.Collections.Generic;
using System.Text;

namespace BLL
{
    public class ProductTypeManagerBLL
    {
        public List<ProductType> GetList(int pageIndex)
        {
            return new ProductTypeManagerApp().GetList(pageIndex);

        }
    }
}

ProductTypeSoftDeletedBLL.cs

using Model;
using DAL;
using System;
using System.Collections.Generic;
using System.Text;

namespace BLL
{
     public class ProductTypeSoftDeletedBLL
    {
        public int Delete(int idIndex)
        {
            return new ProductTypeSoftDeleted().Delete(idIndex);

        }
    }
}

4、webapi 的controllers

ProductTypeController.cs

using BLL;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Newtonsoft.Json;
using System.Text.Json;
using System.Text.Encodings.Web;
using System.Text.Unicode;
using DAL;

namespace WebApi.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class ProductTypeController : Controller
    {
        [HttpGet]
        public JsonResult Result(int pageIndex)
        {
            var list = new ProductTypeManagerBLL().GetList(pageIndex);

            // var option = new JsonSerializerOptions { Encoder = JavaScriptEncoder.Create(UnicodeRanges.All) };

            // return new JsonResult(list, option);
            return new JsonResult(list);
        }


        public JsonResult ResultEnd(bool boolIndex)
        {
            var list = new ProductTypeIsDeletedBLL().GetList(boolIndex);

            return Json(list);
        }


        /// <summary>
        /// postman
        /// </summary>
        /// <param name="idIndex"></param>
        /// <returns></returns>
        [HttpPost]
        public JsonResult ResultId(int idIndex)
        {
            var list = new ProductTypeSoftDeleted().Delete(idIndex);

            return Json(list);
        }
    }
}


/*
    {
        code:0,
        msg:null,
        data:null
    }
     {
        code:0,
        msg:null,
        data:{
            msg:"删除成功"
        }
    }
 */

ProductTypeDeletedController.cs

using BLL;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace WebApi.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class ProductTypeDeletedController : Controller
    {
        [HttpGet]
        public JsonResult ResultEnd(bool boolIndex)
        {
            var list = new ProductTypeIsDeletedBLL().GetList(boolIndex);

            return Json(list);
        }
    }
}

5、中文乱码解决方法
Program.cs

using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace WebApi
{
    public class Program
    {
        public static void Main(string[] args)
        {
            CreateHostBuilder(args).Build().Run();
        }

        public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup<Startup>();
                });
    }
}

Startup.cs

using DAL;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.Encodings.Web;
using System.Text.Unicode;
using System.Threading.Tasks;

namespace WebApi
{
    public class Startup
    {
        // This method gets called by the runtime. Use this method to add services to the container.
        // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<DBClassConext>();

            services.AddControllers();

            services.AddControllers().AddJsonOptions(cfg =>
            {
                cfg.JsonSerializerOptions.Encoder = JavaScriptEncoder.Create(UnicodeRanges.All);
            });
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseRouting();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
                //endpoints.MapControllers();
                //endpoints.MapGet("/", async context =>
                //{
                //    await context.Response.WriteAsync("Hello World!");
                //});

            });
        }
    }
}

中文乱码解决方案参考连接：.NetCore3.1 API 返回Json中文乱码设置https://blog.csdn.net/zhou_xuexi/article/details/107377912